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

gittymoe

Board Regular
Joined
Apr 23, 2005
Messages
79
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​
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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. ?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0
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... :)
 
Upvote 0
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.?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,023
Latest member
alabaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top