Adding Access DB Columns Using VBA

Hello.  I have an access 2010 table (table 1sample in the attached spreadsheet) that I need to append or update (via query or vba script) to another table (table 2 sample in the attached spreadsheet).  I ultimately need to generate a report that looks like table 2 or is tied to table 2 sample.  Can someone give some guidance on this.  At least in script how I move the location number and name to columns.  Thank you.
EESample.xlsx
ilfocorpAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
You cannot do that in Access "easily" with a report.

What you have there is more of a "crosstab".

You can group a report by Location Name, then Product Group, ...or vice versa
Looks something like this:
Report
You can play around with the report options in the attached sample...

FWIW you can make a crosstab query that "mostly" simulates what you need.
Then insert that into a report (or use the query as the report directly)
...I am sure another Expert can help with that...

JeffCoachman
Database127.mdb
Russell FoxDatabase DeveloperCommented:
You don't want to create a new table because they cannot be formatted that way, not like in Excel. You'll want to create a report with the original table as the data source and probably a filter on WeekEndingDt, like "give me the most recent" or a dropdown the user can select in a form. Getting into the details of how to do that is beyond the scope of an EE response, so you'll need to research creating Access reports (though starting with the Access report wizard will get you a long way). Or since Access and Excel play well together, you can probably work out some VBA code that will export the data into an Excel spreadsheet in the way you need.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ilfocorpAuthor Commented:
Thanks for your responses.  I am still trying to do the following;
1. Get the location names from a query and store them in an array
2. Create a table using the locations in the array as fields
3. Begin inputting data in each locations column.

With the below script I have managed to accomplish # 1 and the part of # 2 that is creating the table.  However, the part that is attempting to create the fields from the locations in the array is not happening.  I know it's pretty messy but I also know it can work.  I just need a bit of help cleaning it up.  Any suggestions will be great.  Thanks.

 Set rstLoc = db.OpenRecordset("qryt30Loc", dbOpenDynaset)
   
    'ADD ALL THE LOCATION NAMES TO AN ARRAY TO BE USED TO CREATE TABLE FIELDS IN THE "tblT30RptData_Final"
   
    If Not rstLoc.EOF Then

        rstLoc.MoveFirst   'Ensure we begin on the first row

    'The size of the array should be equal to the number of rows in the table
        intArraySize = rstLoc.RecordCount - 1
        iCounter = 0
        ReDim myArray(intArraySize) 'Need to size the array

    Do Until rstLoc.EOF

        myArray(iCounter) = rstLoc.Fields(1)

        iCounter = iCounter + 1
        rstLoc.MoveNext
    Loop

    End If

    If IsObject(rstLoc) Then Set rstLoc = Nothing

   
   
'CREATE TABLE AND APPEND FIRST RECORD TO APPROPRIATE FIELDS AND RECORDS

    Set tdfNew = db.CreateTableDef("tblT30RptData_Final")
   
'LOOP THRU ARRAY AND SET FIELD DEFINITIONS

    For i = 0 To UBound(myArray())
        Set indexID(i) = tdf.CreateField()
               With indexID(i)
                   .name = myArray(i)
                   .Type = dbText
                   .Size = 20
                   .AllowZeroLength = False
                   .Required = True
               End With
    Next

   
    'Append Fields to table
    For i = 0 To UBound(myArray())
       tdf.Fields.Append indexID
    Next
    'Debug.Print myArray()
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Russell FoxDatabase DeveloperCommented:
I'm with @Jeffrey: you're trying to "pivot" your data using procedural code (like a front-end developer) when the crosstab query does that for you (like a database developer):

LocName [ColumnHeader] (grouped)
ProductGroup [RowHeader] (grouped)
ProductItem [RowHeader]
ProductItem [RowHeader]...
Jeffrey CoachmanMIS LiasonCommented:
Yes,...
Also note that the "exact" output  you are specifying there is not a true Crosstab/Pivot either.
It is really a "Hybrid".

If it were me, I would create a standard Grouped Report, and call it a day.
Or create a basic Pivot (Crosstab query) and call it a day.
Or finally (if you need more flexibility), ...create this Pivot in Excel and use that.

Is this Report based on a previous Report you were getting in another program?
...Or did someone just ask you if this was "possible"?

Its just that this output is nonstandard, ...
...and while you can certainly use loops and arrays to create this output,, ...you will probably have to rework this complex code each time an aspect changes...
...too much work ,  IMHO....

JeffCoachman
ilfocorpAuthor Commented:
Ok.  You were both correct.  I just was not handling the crosstab correctly.  It's all good now.  Great Help.

Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.