Loop to create new tables with unique field values

[RR_umd]

I have a table - (Table A) that has 2 fields X and Y. I would like to write a query or script to make two new tables based on the unique values found in field X. In other words, all data where field X = 1 would be written to a new table called "1" and all data wehre field X =2 would be written to a new table called "2". This is a very simplistic example. I would like this done automatically. Any ideas?

Table A
Field X Field Y
1 a
1 b
1 c
1 d
2 a
2 b
2 c

 

[plog]

Why, what's the end game? I know you gave us simplistic example, why must these be in new tables? What's wrong with a query?

SELECT * FROM [Table A] WHERE [X]=2;

Take me a few more steps down the road. What do these tables help you do?

 

[RR_umd]

Thanks for the quick response. Some additional color: The end game is ultimately to export the tables into excel. There will be several thousand unique values in Field X. These will be available in Table A at a rate of 100-200 per week. We will have a team of 10-15 then review each one of the files individually. The goal is to have one file per unique value in X available for review. Again, a systemic approach would be very helpful. If this is still not clear, please let me know and I will provide whatever details you need. Thanks

 

[plog]

In that case I would skip the table making and go directly to the exporting. Check out the TransferSpreadsheet Method (http://msdn.microsoft.com/en-us/library/office/bb214134(v=office.12).aspx)

This will take some coding (in VBA), but its possible to make what you want. Here's the psuedo code I would use:

Read in query of table that orders it by Field X into a Recordset
Create a New spreadsheet object
Create variable to hold last value of X, set it to first record's value of X
Begin Loop through every record in recordset
---if last value of X is different than current record's value of X
------export data in spreadsheet object to actual file
------clear out spreadsheet object for next set of X value
------set variable of last value of X to current value of X
---Add record to spreadsheet object as new row
End Loop
export data in spreadsheet object to actual file (this takes care of last X value records)


Its not too complicated scripting, but it will take knowledge of VBA and a little time.

 

[RR_umd]

Thanks - the concept makes perfect sense, but unfortuantely I am not skilled in VBA, so sample code woud be great if anyone has some they woud be willing to share.

 

[plog]

Time to get skilled then. Do it incrementally. Build code to do the first line--read in a record set. Once that works write more code to spit the whole thing out to a spreadsheet. Then make it spit out to many spreadsheets.

Divide and conquer. Each line of my psuedo code would make great text for a google search--i.e. 'In Access how do I...' then my psuedo code line