Multiple tables with same fields

[Emmz]

I have a database with 5 different tables that were imported from Excel.
Each table has the same fields, but different data. All tables have the same
exact primay keys.
How can a run a query for all 5 tables?

Example: Table one is apples, table 2 is oranges, table 3 is grapes, and so
on. I just want to find the information on apples and grapes with the same
primary key.

?????

 

[Michel Walsh]

With the same primary key VALUE? using a join seems a possible solution:


SELECT table1.*, table2.*
FROM table1 INNER JOIN table2
ON table1.primaryKeyFieldName = table2.primaryKeyFieldName



Switch in design view to add additional tables, if required, adding the same
'join' between the newly added table and one of the existing table.

Sure, that imposes that the primary key value to be present in both (all
implied) tables.


Hoping it may help,
Vanderghast, Access MVP

 

[John]

I have a client with a similar type of layout. They wanted all their
tax data in tabes based on months. They then wanted to be able to put
all the data back together in a single query on command

What I used was a union query

Search Access help for "Combine data in fields from two or more tables
using a union query"

Im not saying this will help your situation but it is an option

John
MS Access Programmer
'Code Monkey'

<Note>

1) You must be in sql view to create a union query. Query By Design
wont let you.
2) Using your Apples and Oranges example the following would give you
everything. (Make sure there is only 1 semi-colin at the end and not
after each from statement. Newbie mistake I always make in access
union queries)

SELECT [Apples].*
FROM [Apples]
Union ALL SELECT [Oranges].*
FROM [Oranges];

3) If you use specifc fields instead of * then each select statement
in the union query must have the Same Number of fields and In the same
order and have compatiable data types.

4) "Union ALL" Returns duplicate records if any exist
5) "Union" without the "ALL" Does not return duplicate records.
6) With my cient tax tables I have noticed performance issues.
Expecially in my case where they are combining data for the whole
year(12 tables) and they try to sort any given field.

<End Note>