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

[gittymoe]

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 Category	Current	>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]

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 ?

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]

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]

Probably something more along the lines of

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]

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]

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]

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]

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]

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

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