Table generation based on criteria in a table

[clickaccess]

Hello experts,

I need to have tables generated based on the key field in another table.

For example the only field in the color table has these values: BLACK, RED, PURPLE, WHITE.

The main table has on the average about 50,000 records, and in the color field the above mentioned values exist. I need to extract all the records for the colors seperately and save them in a table with preassigned table names.

color table was derived from the main table using unique values query, so the color table varries month to month.

Thanks
Click

Email Service Free from Banner ads.

http://www.SacMail.com

 

[Ed2020]

If I understand you correctly you don't need to do this in VBA. It can be achieved with a set of Make Table queries. Sample SQL below for the colour RED:

SELECT tblColour.* INTO tblRED FROM tblColour WHERE (((tblColour.Colour)="RED"));

Just repeat the SQL, changing the INTO and WHERE statements, to create a set of four Make Table queries (one for each colour).

Or have I isunderstood your requirements....?

Ed Metcalfe

Please do not feed the trolls.....

 

[clickaccess]

Thanks for responding, but, there is no telling if the RED is in the main table, there could be any other color than the one I mention above, further more I have seen as much as 25 different colors in that table. This needs to be automated, and yes, VB is perhaps the best way.


Thanks
Click

Email Service Free from Banner ads.

http://www.SacMail.com

 

[Ed2020]

Something like this....?

Public Sub MakeColourTables()
Dim ThisDB As DAO.Database
Dim rstCriteria As DAO.Recordset
Dim strSQL As String

Set ThisDB = CurrentDb()
Set rstCriteria = ThisDB.OpenRecordset("SELECT DISTINCT tblColour.Colour FROM tblColour;", dbOpenSnapshot)

With rstCriteria
Do While Not .EOF
strSQL = "SELECT tblColour.* INTO tbl" & !Colour & " FROM tblColour WHERE (((tblColour.Colour)=" & """" & !Colour & """" & "));"
ThisDB.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With

rstCriteria.Close
ThisDB.Close
Set rstCriteria = Nothing
Set ThisDB = Nothing
End Sub

Ed Metcalfe.

Please do not feed the trolls.....

 

[clickaccess]

Thanks Ed,

It worked beautifully! I had to create 40 such tables manually from excel in Dec. Now with a little refinement this will reduce time and increase accuracy.

Thanks a bunch
Click

Email Service Free from Banner ads.

http://www.SacMail.com

 

[clickaccess]

Request an enhansment to the table naming.

How do I attach the number of records in the table name?

Like in the above example:

tblRED_59, where 59 is the number of records in that table.

Thanks
Click

Email Service Free from Banner ads.

http://www.SacMail.com

 

[Ed2020]

Click,

This should do the job:


Public Sub MakeColourTables()
Dim ThisDB As DAO.Database
Dim rstCriteria As DAO.Recordset
Dim rstRecCount As DAO.Recordset
Dim strMakeTableSQL As String
Dim strRecCountSQL As String

Set ThisDB = CurrentDb()
Set rstCriteria = ThisDB.OpenRecordset("SELECT DISTINCT tblColour.Colour FROM tblColour;", dbOpenSnapshot)

With rstCriteria
Do While Not .EOF
strRecCountSQL = "SELECT * FROM tblColour WHERE Colour=" & """" & !Colour & """" & ";"
Set rstRecCount = ThisDB.OpenRecordset(strRecCountSQL, dbOpenSnapshot)
rstRecCount.MoveLast
strMakeTableSQL = "SELECT tblColour.* INTO tbl" & !Colour & "_" & rstRecCount.RecordCount & " FROM tblColour WHERE (((tblColour.Colour)=" & """" & !Colour & """" & "));"
ThisDB.Execute strMakeTableSQL, dbFailOnError
.MoveNext
Loop
End With

rstCriteria.Close
rstRecCount.Close
ThisDB.Close
Set rstCriteria = Nothing
Set rstRecCount = Nothing
Set ThisDB = Nothing
End Sub


Ed Metcalfe

Please do not feed the trolls.....

 

[clickaccess]

Ed, This is awesome, works like a charm.

Thanks
Click

Email Service Free from Banner ads.

http://www.SacMail.com