Results 1 to 7 of 7
  1. #1
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192

    Combine Underlying queries

    I am using a Database for the purchase of seed cotton, its processing in various factories and storage of pressed cotton bales in various Warehouses.


    Daily Report has to be sent to Corporate Office for the status of bales shifted to Warehouses. In the Report the days shifting to warehouse from factory and progressive bales shifted upto that date has to reflect.
    At present 2 different reports are sent, one for the days and the other for the progressive.
    I want to combine both as a single report.
    Below is the underlying queries. Is it possible to combine these 2 queries by way of subquery?


    SQL FOR QUERY 1 (For the days shifting)
    SELECT BsShiftingQry.SeasonID, BsShiftingQry.CenterID, BsShiftingQry.CenterName, BsShiftingQry.FactoryID, BsShiftingQry.FactoryName, BsShiftingQry.WarehouseID, BsShiftingQry.WHandLocation, Sum(BsShiftingQry.NoOfBs) AS DaysBalesShifted, BsShiftingQry.ShiftDtNo
    FROM BsShiftingQry
    GROUP BY BsShiftingQry.SeasonID, BsShiftingQry.CenterID, BsShiftingQry.CenterName, BsShiftingQry.FactoryID, BsShiftingQry.FactoryName, BsShiftingQry.WarehouseID, BsShiftingQry.WHandLocation, BsShiftingQry.ShiftDtNo
    HAVING (((BsShiftingQry.ShiftDtNo)=DateNo(Date()-1)));


    SQL FOR QUERY 2 (For Progressive shifting)
    SELECT BsShiftingQry.SeasonID, BsShiftingQry.CenterID, BsShiftingQry.CenterName, BsShiftingQry.FactoryID, BsShiftingQry.FactoryName, BsShiftingQry.WarehouseID, BsShiftingQry.WHandLocation, Sum(BsShiftingQry.NoOfBs) AS BalesShiftedProgressive
    FROM BsShiftingQry
    WHERE (((BsShiftingQry.ShiftDtNo)<=DateNo(Date()-1)))
    GROUP BY BsShiftingQry.SeasonID, BsShiftingQry.CenterID, BsShiftingQry.CenterName, BsShiftingQry.FactoryID, BsShiftingQry.FactoryName, BsShiftingQry.WarehouseID, BsShiftingQry.WHandLocation;
    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Union query.
    select * from query1
    union
    select * from query2


    i would also add the query name label in each
    Days shift and Prog shift.

  3. #3
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Thanks a lot ranman, it succeeded. This idea didn't occur to me. Thanks once again

    Alex

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Are you SURE you really need both queries for the report. Query 2 will include all the data from query 1 if you include the field BsShiftingQry.ShiftDtNo.

    If you do use a union query, do bear in mind you will need to add the ShiftDtNo field (or a dummy equivalent) anyway as the number of fields must be identical
    In addition a union query is read only though that probably won't matter if its just for the report

    BTW WHERE is likely to be faster than HAVING if you have a lot of records ...but you then won't see the field
    ShiftDtNo
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Thanks isladogs. I included an equivalent. That is not relevant for the report.

  6. #6
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    I always convert the Date to number, because sometimes Date format gives problem.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Using something like DateNo shouldn't be necessary.
    In my experience, dates only cause issues in VBA code where the local date format isn't the US mm/dd/yyyy. Explicitly converting dates for VBA SQL statements solves that.
    It isn't necessary to do so using a query as date handling is managed automatically.

    Anyway, I still see no reason for using a union query in this case
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 1
    Last Post: 08-29-2014, 04:15 PM
  2. How do I combine these two queries?
    By racefan91 in forum Queries
    Replies: 18
    Last Post: 10-19-2013, 12:54 PM
  3. Combine three queries into one
    By Ray67 in forum Queries
    Replies: 8
    Last Post: 06-13-2012, 11:23 AM
  4. Combine Queries
    By Steven.Allman in forum Queries
    Replies: 9
    Last Post: 08-30-2010, 12:13 PM
  5. Combine 3 Queries
    By Logix in forum Queries
    Replies: 1
    Last Post: 06-10-2010, 06:03 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