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