Seven tables is a bad idea! Consider using seven identical forms except for
LocationID. Make the LocationID field on the forms hidden and on each form
set the default value for LocationID appropriate for the location where the
form is being used. With this setup, anytime someone enters data on their
form, Access will automatically assign the appropriate LocationID for the
location where the data is being entered.

BTW, if you need to outsource any Access, Excel or Word work, I provide help
for a very modest fee. Just contact me at .

Steve



"CorporateQAinTX" wrote in
message ...
I'm posting this in the design group because I'm actually trying to think
of
different ways to design this thing. I say thing because it's become more
of
a monster then a dbase. And one quick note, please don't comment on my
naming
conventions. I'm not a professional nor will I ever expect to be one. But
I
am far more capable then anyone else in my company and figure I will
continue
to be the closest thing to an MVP that they'll have without outsourcing.
No
offense to anyone. : )

On to the problem. I've got 7 facilities that have to keep up with "bad"
product produced. I've got the table design built and implemented at one
facility already. It's working great, but my original plan was to use one
table and have the users select a location which would store a location
ID#
in the main table. I could then use another form attached to a query that
would search the main table based on the location chosen and some other
variables available. Well, come to find out, not everyone can use a mouse
correctly. I can't trust that the user will choose the correct location.
So
my plan was to make 7 identical tables and just set the input form to
change
it's source table based on which location they choose on the Main Menu. My
issue is that I can't use my queries or reports anymore. Is it possible to
query the same field over multiple tables. For example, every table has
the
TicketNum field. Can I search for a specific TicketNum across 7 tables? Or
is
this just a bad design all together? I can code the form to change the
source
table based on which location button is chosen, but I can't do the same
for
the queries. It has to search all tables for the same thing.

I'm pretty sure I'm not making the best sense, but any suggestions would
be
helpful. And if I need to clarify something, please let me know.

Garrett