Create multiple tables from one table based on change in field name

gittymoe

Board Regular
Joined
Apr 23, 2005
Messages
67
I am trying to create a VBA loop in access that would take the below data from Table A and create new tables based on a change in General Category. We run this quarterly so we would want to delete the data in the "New Tables" created each quarter and populate with the most recent quarter's data

General CategoryCurrent>30 days>60 days>90 days
Category A
$101,297​
$117​
$3,978​
$178​
Category A
$117,104​
$0​
$0​
$0​
Category A
$441,581​
$0​
$0​
$0​
Category A
$102,671​
$0​
$0​
$0​
Category A
$185,991​
$0​
$0​
$0​
Category A
$0​
$0​
$225​
$210​
Category B
$32,035​
$0​
$0​
$0​
Category B
$9,664​
$0​
$4,029​
$0​
Category B
$41,391​
$0​
$0​
$0​
Category B
$43,173​
$0​
$0​
$0​
Category B
$23,566​
$0​
$32,139​
$0​
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
701
Two loops.
Outer loop until EOF
Inner Loop until stored GeneralCategory <> current GeneralCategory, in which case start new table and save new GeneralCategory

This only addrresses your question as I believe your structure is incorrect as all that should be calculated, not stored.?
What defines one CategoryA against the next CategoryA ? :confused:

Another way would be to use a Distinct query as a recordset to determine the individual category values and a query to select each in turn to new tables. ?
 

gittymoe

Board Regular
Joined
Apr 23, 2005
Messages
67
If I understand your question, the change in CategoryA to CategoryB is the change I am trying to execute off of. There is no calculation per se'. If a change from one category to another then take all the records associated with that category and create a new table. This could be up to 10-12 categories at a time over hundreds of records

Below is what I am working from (i picked up from google)and if I understand it correctly, this will do what I need but I am getting a compile syntax error on the highlighted text below.

Sub NewTables()
Function yourFunctionName()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("MT_9817 Quarterly Certificate")
Set rs2 = db.OpenRecordset("YourNewTableName")

rs1.MoveFirst
Do Until rs1.EOF
If rs1![General Category] = <GeneralCategory> Then
rs2.AddNew
rs2![General Category] = rs1![General Category]
rs2.Update
End If
Loop

rs.Close
Set rs = Nothing
Set db = Nothing
End Function
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
701
Probably something more along the lines of

VBA Code:
Sub NewTables()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strCategory as String, strTable as String

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("MT_9817 Quarterly Certificate")


rs1.MoveFirst
Do Until rs1.EOF

         strCategory = rs1!Category
        Set rs2 = db.OpenRecordset(strCategory)

    Do until  strCategory <> rs1!Category
        rs2.AddNew
        rs2![General Category] = rs1![General Category]
        'what other fields are being updated?
        rs2.Update
        rs1.MoveNext
    Loop
     rs2.Close

Loop

rs1.Close

Set rs1 = Nothing
Set rs2= Nothing
Set db = Nothing
End Sub
Also the correctly tables will probably need to exist before you write to them. Not something I have done this way.

HTH
 

gittymoe

Board Regular
Joined
Apr 23, 2005
Messages
67
Thanks, when you say need the correct tables to need to exist before you write to them, is Set rs2 = db.OpenRecordset(strCategory) the table that you are referring too. Sorry just learning VBA and probably a pretty basic question.

Thanks again,
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
259
Office Version
365, 2016
Platform
Windows
Do you actually need to create new tables or are you just wanting to view the filtered data? If the latter, you can do this without code just using a regular query and a form with a dropdown (showing a DISTINCT list of the categories) whose form field is referenced as a criterion in the WHERE clause of the query.

To run you select the category, hit some kind of 'submit' button and the records for that category are displayed.

If you need the tables, ignore me... :)
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
701
Thanks, when you say need the correct tables to need to exist before you write to them, is Set rs2 = db.OpenRecordset(strCategory) the table that you are referring too. Sorry just learning VBA and probably a pretty basic question.

Thanks again,
Yes, as all you would be doing is writing to the table.?

Copying the structure manually in Access would be enough.? I am asssuming that the table name is the same as the Category value.?
 

gittymoe

Board Regular
Joined
Apr 23, 2005
Messages
67
Ok, when I run the VBA posted by welshgasman, I receive an error this item not found in this collection on....strCategory = rs1!Category. I then bracketed [rs1!Category] but still not correct.
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
701
Ok, when I run the VBA posted by welshgasman, I receive an error this item not found in this collection on....strCategory = rs1!Category. I then bracketed [rs1!Category] but still not correct.
It has to be a fieldname in your recordset?
I made a mistake. :(

Code should be

Code:
strCategory = rs1![General Category]
and obviously change any reference to the fieldname Category to be the same?

BTW spaces in fieldnames not such a great idea, as you have to bracket them [].

HTH
 

Forum statistics

Threads
1,082,717
Messages
5,367,171
Members
400,946
Latest member
GiMan

Some videos you may like

This Week's Hot Topics

Top