Adding Access DB Columns Using VBA

ilfocorp
ilfocorp used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
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
Database Developer
Top Expert 2014
Commented:
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.

Author

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()
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Russell FoxDatabase Developer
Top Expert 2014

Commented:
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 Liason
Most Valuable Expert 2012

Commented:
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

Author

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

Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial