Report or Query, How and What?

Question

Hello,

I have multiple tables that all have the same fields and I was curious how to do the relationships or how to set up reports or queries to have all the information from each table come together. I keep them separate because they are each based on different events but they are measured in similar ways. I want to be able to create a report of which items are completed, on track, and so on based on a status field that they all have.

Sorry for the simple questions or confusion I am very new to access and stumbling my way through.

Answer 1

Hi,

You could create a UNION query for your reports or dump your records into a temp table first.

However, tracking data for multiple events shouldn't stop you from storing all the records into one table. You could simply add a field/column to designate the event you're tracking for each record and use queries to filter out each event.

Just my 2 cents...

Answer 2

Thank you! I might look into creating one table to help with that. Just sensitive data that I didn't want to accidently get changed for the wrong one.

Answer 3

Your table design is not correct. It should probably be one table with an additional field to indicate the event.

But that said, you can do a UNION query to get records from all tables. Just make sure the fields are in the right order for each SELECT statement.

SELECT LastName, FirstName
FROM Customers1
UNION
SELECT LastName, FirstName
FROM Customers2
UNION
SELECT LastName, FirstName
FROM Customers3

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 4

Hi,

If you had all the records for all events in one table, you can use a menu to select which event to update and then open a report filtered to the selected event. Then, there should be less chance of updating the wrong event. It's pretty much the same as making sure to select the correct table for the event you want to update right now. Instead, in this case, you just select the correct filter for a single table.

Just my 2 cents...

Answer 5

Sorry, DBguy. I wrote this and then sat on it as I forgot to hit Send. Anyway, great minds think alike, no? <grin>

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 6

Hi Bill,

No worries. I do the same thing sometimes. We may think alike only because I have learnt a lot from you.

Cheers!