Passing Table as Parameter from Combobox

[Tblqryfrmrpt]

Hi everyone,

New to posting here, but these forums are great. Thanks for the information.

I've been trying to wrap my brain around this for a while and I can't figure it out. I have a table of properties. I also have a search form that works great when you search individual criteria or a combination of them (City, State, Zip, etc).

My problem is that there are certain clients that only work in a number of zip codes. This leads me to the concept of a zip group. I have created a table that only consists of a column of zip codes for Client A. I would like to create multiple tables for multiple clients that can be joined to the main table to utilize these zip groups.

However, I cannot figure out how to search by "zip group". It seems to be a pretty simple inner join based on the zip code, but the table name is variable based on the combobox selection they select for zip group. Is there an easy way to do this? I would assume that I would need another qry for Zip Group, instead of using my regular search qry.

Thanks

 

[spikepl]

Do not split similar data into separate tables just depending on some value. This will cause you pain forever after. When you get to an issue like

It seems to be a pretty simple inner join based on the zip code, but the table name is variable based on the combobox selection they select for zip group.

then all the alarms should go off: your table structure is not good.

You could have a table of zip-groups, containing the zipgroupid and the corresponding zip codes. Your one and only clients-table then needs to have a ZipGroupID for each client.

 

[Tblqryfrmrpt]

Hi Spike,

Thank you for the response. I figured that there may be an easier way to handle it because it seemed like the zip group idea wouldn't be normalized form.

I guess my problem is how to build that zipgroup table. Multiple clients may share multiple zip codes. I can't have one field with zipcode and one column field ZipGroupID since there would be duplicates. Wouldn't I have to create an intermediary table?

Thanks

 

[spikepl]

ZipGroups
-----------
ZipGroupID
Zip

Eg,

1 1111
1 2222
1 3333
2 1111
2 2222
2 4444

etc thus you have a table from which you can extract the zipcodes belonging to one zipgroup
1

 

[spikepl]

But actually, my previous suggestion would violate normalization rules.

So:
tblZipCodes
------------
ZIpCodeID
ZipCode

tblZipGroups
----------
ZipGroupID
ZipCodeID

where the same ZipGroupID is repeated for all the ZIpCodeID's belonging to the same zipgroup

so
tblClients
----------
ClientID
ZipGroupID (assuming a client has only one zip group, otherwise you'd need a junction table
.....

 

[Tblqryfrmrpt]

Great, thanks. I have this set up successfully, now I need to figure out the right criteria for the unbound form. I have a ComboBox that is pulling the Name from the Clients table, but can't successfully figure out the query syntax to filter out the zip codes after messing around with two inner joins.