Creating a query from multiple identical tables

[Guest]

I am trying to create a query from multiple tables with the same number of
columns and same headings to pull all records from each table with a matching
value in one of the fields. This database holds all the information for each
job we have in progress with each table representing one of our customers.
I'd like to know how I can create a query to pull form each customers table
all jobs with the status of "Dispatched". I was also wondering since I plan
to recreate the database in a while if there is a better way to organise the
records in order to make something like this easier to do. Is this a poor
database design? Please let me know in your post if you need more information
to help me I'll post it right away.

 

[Rick B]

This type of question has been asked several times in the last couple of
days. Your database structure is flawed. You don't create identical
tables.

Put all of your data into ONE table and add a "ClientNum" field.

Then, you can pull data for one client, several clients, one type of job for
one client, one type of job for multiple clients, all data for all clients,
etc.

Hope that helps.

 

[Michel Walsh]

Hi,


At first, I thought you have something like one table per year (just for
illustration). It would be easier to merge these tables into just one, where
a field, [year], would be added (again, just for illustration):


SELECT 1999 As [year], f1, f2, f3, f4 FROM year1999table
UNION ALL
SELECT 2000, f1, f2, f3, f4 FROM year2000table
UNION ALL
....
SELECT 2006, f1, f2, f3, f4 FROM year2006table


is a query that would bring the data together, but without any index, that
would be slow. That is why making it a table would be better.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks for the prompt response this is what I suspected. I am new to access
and have only designed 1 other database so my analysis skills are limited.
This is unfortunatly how it was originally designed by someone no longer
employed here (go figure). So to take your suggestion a little farther you'd
suggest storing all jobs in 1 table and accessing individual or groups of
jobs through queries and forms right? Will this still be the best way even if
we have say 5,000 jobs? Also is there any way around my original problem
without rewriting our current DB?