Results 1 to 8 of 8
  1. #1
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318

    Union Query to create Report?? Create two Reports??


    I have two tables "Tbl_Building_Information" and "Tbl_Schedule". What I am trying to accomplish is:
    1. To make sure that every Active building in the Tbl_Building_Information table is also in the Tbl_Schedule table
    2. To make sure that every building that is in the Tbl_Schedule table has the Current field check but only once per building.
    3. To create a Report that will a) tell me if there is a Building that is not Scheduled and b) let me know if there are Buildings in the Schedule table missing a Current status or have duplicate Current status.

    I feel like somehow there has to be a union query involved but I can't put my mind around how to begin this process. Any help is appreciated. I have attached a sample database.
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am not seeing need for UNION.

    1. Consider

    INSERT INTO Tbl_Schedule(PK_Bldg_Num) SELECT Tbl_Building_Information.Bldg_Number
    FROM Tbl_Building_Information LEFT JOIN Tbl_Schedule ON Tbl_Building_Information.[Bldg_Number] = Tbl_Schedule.[PK_Bldg_Num]
    WHERE (((Tbl_Schedule.PK_Bldg_Num) Is Null));

    I had to remove the PK assignments from Tbl_Schedule to make that work.

    This works with the PK's:
    INSERT INTO Tbl_Schedule(PK_Bldg_Num, PK_Schedule_FY) SELECT Tbl_Building_Information.Bldg_Number, "FY16" AS FY
    FROM Tbl_Building_Information LEFT JOIN Tbl_Schedule ON Tbl_Building_Information.[Bldg_Number] = Tbl_Schedule.[PK_Bldg_Num]
    WHERE (((Tbl_Schedule.PK_Bldg_Num) Is Null));

    Still looking at 2 and 3.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    Wouldn't doing an INSERT change the data in the table? Isn't that an Append Query? I don't want to change any of the table's data.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I thought 'make sure that every Active building in the Tbl_Building_Information table is also in the Tbl_Schedule table' meant you want the record in table. If you just want to have a look-see then build a Find Unmatched - the nested SELECT subquery in my examples.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    I guess to clearify I want to append the data manually. I just need want a report to tell me which ones need to be added.

  6. #6
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    OK that was way to simple. I should be ashamed.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Just to see stats, not change anything:

    2. count Current = Yes regardless of FY
    SELECT Tbl_Schedule.PK_Bldg_Num, Sum(IIf([Current]=-1,1,0)) AS CountCheck
    FROM Tbl_Schedule
    GROUP BY Tbl_Schedule.PK_Bldg_Num;

    3. a. does Find Unmatched query show that?
    b. does item 2 query show that?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    That works fine for what I need. Thanks so much for your help.

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

Similar Threads

  1. Replies: 1
    Last Post: 10-22-2012, 05:52 PM
  2. cREATE REPORT BASED ON TWO ONATHER REPORTS
    By BorisGomel in forum Access
    Replies: 0
    Last Post: 07-12-2012, 03:00 PM
  3. Replies: 5
    Last Post: 03-13-2012, 08:24 PM
  4. Create CrossTable Query report
    By ysrini in forum Reports
    Replies: 1
    Last Post: 02-19-2010, 11:20 AM
  5. Create table out of union query
    By DKruse1969 in forum Queries
    Replies: 2
    Last Post: 08-28-2009, 09:55 AM

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