Results 1 to 7 of 7
  1. #1
    WhiskyLima is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2013
    Location
    UK
    Posts
    32

    Multiple sets of date

    Hi guys, I'm really trying but I can't wrap my brain around this one. I have two sets of data in separate tables (actually SharePoint lists), both lists of events, but both from different suppliers.

    Supplier Table A:

    • Event 1 - 21/03/14
    • Event 2 - 23/03/14


    Supplier Table B:

    • Event 3 - 15/06/14
    • Event 4 - 03/04/14


    And so on..

    Now what I'm trying to do is to create a sequential report base on both sets of data. Lets say for argument sake I'm querying the year '2014' I was hoping to make a report that would look like this:

    Supplier's Events:
    Event ----- Date

    • Event 1 - 21/03/14
    • Event 2 - 23/03/14
    • Event 3 - 15/06/14
    • Event 4 - 03/04/14


    Is this possible? I really would appreciate some help on this, maybe if someone knows a way to do this with or even without reports, but I do need to be able to print out the result and it needs to be pretty!



    Cheers Guys!

  2. #2
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    1) Build a UNION query to combine the tables in question.
    2) Build a query based on the UNION query and sort.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    lfpm062010's suggestion is what I'd suggest to, just be aware that you won't get a 'design view' for a union query. To put a union query together build the layout you want first, then, assuming all the fields for both sets of data are identical, start building on your original query.

    i.e. Let's assume your tables are table1 and table2

    Get one portion of your query working first:

    Code:
    SELECT Event, EventDate FROM Table1
    then start adding additional data

    Code:
    SELECT Event, EventDate FROM Table1
    UNION ALL
    SELECT Event, EventDate FROM Table2
    When I build union queries I always keep a copy of the 'base' query so that I can cut and paste the SQL into the union query if I modify it, and only modify a table name to make it work again.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,852
    Just to clarify Union and UNION ALL
    Union will select the records from each table. UNION ALL will include duplicates if they exist; Union will not include duplicates.

    Good luck with your project.

  5. #5
    WhiskyLima is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2013
    Location
    UK
    Posts
    32
    Hey guys and thank you all for your input, sounds like I have some learning to do but this was the point in the right direction I needed. I'm probably going to have to make a number of changes to the most recent table I have created as the data does not have the same names in both tables. Would I have to have the entire table the same in both? I only want to use a few fields from each form, also will this have any impact on the original information from my original event list - I don't want the data to be permanently joined I just want to be able to report both sets of data together.

    Anyway now that I have the building blocks, I can look up union queries and start learning what I need. Thank you all for our valued input!

  6. #6
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Quote Originally Posted by WhiskyLima View Post
    I'm probably going to have to make a number of changes to the most recent table I have created as the data does not have the same names in both tables. Would I have to have the entire table the same in both? I only want to use a few fields from each form
    The column names of the union will be taken from the first query. The names of the second query will be ignored. The only important thing is that the column count, and the column types are the same. So no need to change any names in the base tables. You can even change the names only for the union query by adding aliases to the columns in the first query.

    Quote Originally Posted by WhiskyLima View Post
    I don't want the data to be permanently joined I just want to be able to report both sets of data together.
    The union query is only a view on the underlying tables. There is no change on your data in the tables as with any other normal select query. Every time the query is executed it will do the union of the base tables, but everything else is the same as before.e

  7. #7
    WhiskyLima is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2013
    Location
    UK
    Posts
    32
    Perfect thank you for clarification of that. It sounds like this is the way to go! Many Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-04-2013, 03:45 PM
  2. Crosstab => Multiple Sets of Values
    By Minimalist in forum Queries
    Replies: 1
    Last Post: 01-07-2013, 01:17 PM
  3. Replies: 8
    Last Post: 11-19-2012, 09:22 AM
  4. Multiple Sets of the Same Data on 1 Report
    By rmikulas in forum Reports
    Replies: 1
    Last Post: 08-07-2012, 10:09 AM
  5. Update Query - Multiple SETS and WHERE conditions
    By jasonbarnes in forum Queries
    Replies: 26
    Last Post: 12-15-2010, 01:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums