Show Identical Information from multiple tables and create more identical tables

[PurpleGod]

Hey I was wondering if you have any suggestions. I'v been stuck on this for a while.

Here's what I have so far:

5 tables: Each table has 10 feilds, Name, money, date1, pay1, date2, pay2 Ect

*The same person can come up in multiple tables*

Example:

Table 1 could have 3 people each with their own Name, Money, date1, pay1 ect
Table 2 could have the same 3 people, but with different Money, date1, pay1 ect

My question is, how do I create a form, in which I am able to search for a name, and It shows me the information about that person from both tables.
So I should have:

Person1 - Money, date1, Pay1 (From table 1)


- Money, date1, Pay1 (From table 2)
- Money, date1, Pay1 (From table 3)

The tricky part is, I need to make more tables with Money, date1, Pay1, In the future so I need to be able to add a new table, with the same fields as the other ones, and have to show when I search for that persons name.

[Perceptus]

You could create a Union Query of the 2 tables. And then use that query as the datasource.

[CJ_London]

Sounds like your data is not normalised. You have only provided a brief description and from that it would be expected that you would have one table with an additional column which is the equivalent of the table name. You will often find those new to Access will use one table for each year for example - a kind of excel way of doing it - when in fact you can simply determine the year from the paydate

[Missinglinq]

As Ajax suggested, having five Tables with identical Fields undoubtedly means that your data is not normalized! As he said, you simply need an additional Field to differentiate between the Records.

Linq ;0)>