none
Report or Query, How and What? RRS feed

  • 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.

    Tuesday, August 28, 2018 3:57 PM

Answers

  • 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...

    • Marked as answer by courtneyca Tuesday, August 28, 2018 4:40 PM
    Tuesday, August 28, 2018 4:00 PM
  • 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

    • Marked as answer by courtneyca Tuesday, August 28, 2018 4:41 PM
    Tuesday, August 28, 2018 4:11 PM
  • 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...

    • Marked as answer by courtneyca Tuesday, August 28, 2018 4:40 PM
    Tuesday, August 28, 2018 4:13 PM

All replies

  • 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...

    • Marked as answer by courtneyca Tuesday, August 28, 2018 4:40 PM
    Tuesday, August 28, 2018 4:00 PM
  • 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.
    Tuesday, August 28, 2018 4:08 PM
  • 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

    • Marked as answer by courtneyca Tuesday, August 28, 2018 4:41 PM
    Tuesday, August 28, 2018 4:11 PM
  • 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...

    • Marked as answer by courtneyca Tuesday, August 28, 2018 4:40 PM
    Tuesday, August 28, 2018 4:13 PM
  • 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

    Tuesday, August 28, 2018 5:39 PM
  • Hi Bill,

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

    Cheers!

    Tuesday, August 28, 2018 5:47 PM