Report on multiple tables

[Core]

Hi there,

I have a access database with several tables each with almost the same fields. For example: Name, date of birth, NI Number, provision etc

I want to make a report (or query) that searches each table and displays any fields it finds based on a user input NI Number. So I want this report to prompt the user for a NI number then search several tables for a NI Number and display any records it finds regardless of which table they are in.

I have tried but each time I add more than one table to a report it asks for them to be linked, then when I link them it never works.

Any suggestions?

Thanks!

 

[MSAccessRookie]

One possibility might be

What you are describing is not a normalized database:

I have a access database with several tables each with almost the same fields. For example: Name, date of birth, NI Number, provision etc

One thing that might help would be to extract common fields from your tables to an additional table (more if necessary). For example, you indicated that more than one table has names and related information.

Assuming these names represent the same people in all tables, then a new table of people and their attributes (including the name, DOB, and perhaps the NIL #) would be helpful. Additional common information could also go here.

The new table would have a new key (Person_ID perhaps?) for each person's record, and each of the original tables would only need to point to that ID. Not only does this eliminate dupliation between the tables, but it also gives you something to link the tables to gether with to satisfy the report feature.

This is just a beginning, but it should give you an idea of how to proceed

 

[boblarson]

My suggestion is to figure out why you have multiple tables with the same type of data and fix it (normalize, normalize, normalize)

http://support.microsoft.com/kb/283878
so that you don't have to do "band-aid" fixes like this.

 

[Core]

It's because all the information had to be extracted from excel worksheets. I like the idea of name, dob and ni on one table, however, would there be a way to extract this information with out having to re-input all the data again. What I mean is, how would I go about giving all the records a Person_ID that matched other records in the other tables with out manually going through each client on the database (as there are tens of thousands of them.

I understand your what your saying about how much this database sucks, I agree, I would have used table relationships if I made it but I'm dealing stuff that extremely IT-naive people set up and as its mission-critical I don't wanna go around changing worlds or anything ;p

 

[MSAccessRookie]

Try this:

Add a new column called Person_ID to each of your tables containing Person type information (as previoulsy described)

Create a new table for the person information with as many columns as you need, and a Primary Key Person_ID that is AutoNumber.

Pick one of the tables and add the person information to the new table

Update the Person_ID information in that table.

Repeat for remaining tables, making sure that records are not duplicated.

Remove redundant columns from the original tables

This description is a little bit oversimplified, but I think it gets the main point across, and I hope it helps out