creating access reports with multiple tables

[scissorsmc]

Hey all,
I am attempting to create an access report. I have 19 tables built by client. In each of these tables i have a list of names, (last name, first name) with the last name as my primary key, and other information. i would like the report to list the name and the clients that they belong to. unfortunately i cannot get the report to do as i like. anyone have any suggestions???

Thanks,
scissorsmc

 

[dhookom]

How about you provide a little more information? I am not a fan of using Last Name as a primary key. I would think this would soon create serious issues.

Your final answer might involve a union query but I can't be sure without more information about your current tables and desired display in the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[scissorsmc]

sorry, i figured that using last name would suit my needs perfectly and that is my reasoning behind that. more details... 19 tables with names, emails, notes, and updates. each table is for a different client. i need to figure out how to say John Doe and have it search all 19 tables and pull what client they belong to, what their email is, their notes and updates. John Doe might be affliated with 15 of the 19 clients (tables) if you get what i am saying.

thank you for the help.

scissorsmc

 

[dhookom]

You can possibly use a union query

SELECT "Joe" as Client, *
FROM tblJoeClients
UNION ALL
SELECT "Sally", *
FROM tblSallyClients
UNION ALL
SELECT "Bob", *
FROM tblBobClients
...etc...;

You could then search the union query for specific individuals or whatever. If 19 UNIONs ends up being too complex, you may need to append individual records into a master table.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]