MS Access VBA making multiple tables from one base query

[cpamerlin]

Given the following Access database items:

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

PeopleQuery:
-------------------
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 **

[ADezii]

@cpamerlin
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:

ID    First     Last             City
1     Ted       Strickland       Memphis
2     Jen       Landry           Memphis
 

------------------------------------------------------------------------

[NeoPa]

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).

[cpamerlin]

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!

[NeoPa]

That makes sense. Let us know if you struggle with any particular aspect.