By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

MS Access VBA making multiple tables from one base query

P: 2
Given the following Access database items:

ID City
1 Memphis
2 Los Angeles
3 Tucson
4 Sacramento
5 Austin

ID First Last City
1 Ted Strickland Memphis
2 Jen Landry Memphis
3 Jerry Presh Los Angeles
4 Roxanne Bider Los Angeles
5 Pam Archdale Tucson
6 Jen Luxe Sacramento

I need to create a VB function that can be called which cycles
through each of the of the records in CityTable and Creates a
Table for EACH city which is populated with only the related
City records of the PeopleQuery. Each table needs to be named
the City Name. I even need a blank table for Austin which
doesn't have any records in PeopleQuery. I do NOT want to setup
multiple copies of PeopleQuery with a different City criteria.
The function should call PeopleQuery and provide a City parameter
during processing. Can someone help?!

** Email address removed **
Jun 18 '10 #1
Share this Question
Share on Google+
4 Replies

Expert 5K+
P: 8,607
Just to clarify. You wish to dynamically create a Table for each Unique City from (CityTable), then populate these Tables with only the Records pertaining to each specific City in (PeopleQuery). Is this correct? If this assumption is correct, then you would have a Table named Memphis with the following Records:
The Memphis Table:
Expand|Select|Wrap|Line Numbers
  1. ID    First     Last             City
  2. 1     Ted       Strickland       Memphis
  3. 2     Jen       Landry           Memphis
Jun 19 '10 #2

Expert Mod 15k+
P: 31,275
We can help with specific questions, but we will not provide ready-made solutions on request (any such responses would be removed anyway).

If you would like to discuss what you've already tried and your current understanding then we can help fill in the blanks of course.

I'm sure the first piece of advice any of us would give would be to warn against the approach you are indicating as it is not normalised (See Normalisation and Table structures).
Jun 19 '10 #3

P: 2
ADezii, your understanding of the question and related outcome example are both correct.

NeoPa, I realize the tables are not normalized, I am really trying to create and then later route these tables as reports to separate users/systems, and I only want to route data pertaining to them.

Another user sent me an example of a Do While Loop that I may be able to build my function around. Thanks for your help!
Jun 21 '10 #4

Expert Mod 15k+
P: 31,275
That makes sense. Let us know if you struggle with any particular aspect.
Jun 21 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.