wlwebb
asked on
Access - Query Cumulative for day by TransType if No TransType is Subseq Shift
I am trying to create a Query to Sum information for each Shift in a Business Day and show the Cumulative Amount for each Day for any TransTypeID. If a Day's subsequent Shift doesn't have a Transaction for the TransTypeID that an earlier shift has, I still need it to show that Cumulative for the Day info.
The Cumulative by Day needs to report by TransTypeID =5 or null. (Null so that if there aren't any Transactions in a Day I'll have a record with a zero for forms and reports.
If ShiftSeq number 1 of the Day has a TransTypeID = 5 but ShiftSeq 2 or 3 of the day doesn't, then I can't get it to show a record for that ShiftSeq showing the cumulative for the day....
Any help?? or suggestion...
Attached is a stripped down version
Cumulative-TransType-By-Day.accdb
The Cumulative by Day needs to report by TransTypeID =5 or null. (Null so that if there aren't any Transactions in a Day I'll have a record with a zero for forms and reports.
If ShiftSeq number 1 of the Day has a TransTypeID = 5 but ShiftSeq 2 or 3 of the day doesn't, then I can't get it to show a record for that ShiftSeq showing the cumulative for the day....
Any help?? or suggestion...
Attached is a stripped down version
Cumulative-TransType-By-Day.accdb
exactly, what do you need?
ASKER
Trying to get a query that accumulates [Amt] (ie cumulative for the day reported for each shift) from qry_X for each [Shift] and BusDay AND IF there is a [TransTypeNbr] in a [BusDay] in a preceeding [DayShiftSeqByType] that the [Shift] Shows that accumulated amount for that [TransTypeNbr]
ASKER
The code I have to Sum the Amt by Shift and BusDay is:
HOWEVER,
If a shift has a TransType 5 early in the day and later in the day the next shift(s) don't have a TransType 5 then there's not a cumulative TransType 5 for that Shift and I need there to be that shows how much had been accumulated for any shift prior during that day.
SELECT qry_X.ShiftID, qry_X.BusDay, qry_X.ShiftTypeID, qry_X.DayShiftSeqByType, qry_X.TransTypeNbr, Sum(DSum("Amt","qry_X","ShiftID<=" & [ShiftID] & " AND BusDay=#" & [BusDay] & "#")) AS CumulAmt
FROM qry_X
GROUP BY qry_X.ShiftID, qry_X.BusDay, qry_X.ShiftTypeID, qry_X.DayShiftSeqByType, qry_X.TransTypeNbr;
HOWEVER,
If a shift has a TransType 5 early in the day and later in the day the next shift(s) don't have a TransType 5 then there's not a cumulative TransType 5 for that Shift and I need there to be that shows how much had been accumulated for any shift prior during that day.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
PatHartman.......
Sort of there....... However if First Shift of the Day has a TransTypeNbr but a subsequent Shift for the Day doesn't it shows the cumulative amt but the TransTypeNbr shows {None} and I'd need it to show whatever any prior TransTypeNbr for the Day would have been....
Sort of there....... However if First Shift of the Day has a TransTypeNbr but a subsequent Shift for the Day doesn't it shows the cumulative amt but the TransTypeNbr shows {None} and I'd need it to show whatever any prior TransTypeNbr for the Day would have been....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
join to force a Cartesian product............... ?????????????? But I think I know what you're saying.........
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Pat...
Thanks... I am attempting that.......and that is what I'm trying to do so I think I understand how you've explained.... I'll be back......
Thanks... I am attempting that.......and that is what I'm trying to do so I think I understand how you've explained.... I'll be back......
ASKER
So this was the code for the Cartesian Product
SELECT tblCtl_Shifts.ShiftID, tblCtl_Shifts.BusDay, sCtl_TransType.TransTypeID , sCtl_TransType.TransType
FROM tblCtl_Shifts, sCtl_TransType;
That gave me every Shift and Every Possible TransTypeID
Then my code for the Cumulative Query is:
That works........... But for no more records than I have it takes a long time to run..... See any problems????
I Joined the Cartesian with the qry_x on ShiftID, BusDay and TransTypeID Also tried it with just the Qry_X and no joins and no Cartesian qry in that Cumulative query.... runs about the same
Just in case... following is Qry_X code
SELECT tblCtl_Shifts.ShiftID, tblCtl_Shifts.BusDay, sCtl_TransType.TransTypeID
FROM tblCtl_Shifts, sCtl_TransType;
That gave me every Shift and Every Possible TransTypeID
Then my code for the Cumulative Query is:
SELECT qry_X.ShiftID, qry_X.BusDay, qry_X.TransTypeID, Val(nz(DSum("Amt","qry_X","ShiftID<=" & [qry_x].[ShiftID] & " AND TransTypeID=" & [qry_x].[TransTypeID] & " AND BusDay=#" & [qry_x].[BusDay] & "#"),0)) AS CumulAmt
FROM qry_X
GROUP BY qry_X.ShiftID, qry_X.BusDay, qry_X.TransTypeID, Val(nz(DSum("Amt","qry_X","ShiftID<=" & [qry_x].[ShiftID] & " AND TransTypeID=" & [qry_x].[TransTypeID] & " AND BusDay=#" & [qry_x].[BusDay] & "#"),0));
That works........... But for no more records than I have it takes a long time to run..... See any problems????
I Joined the Cartesian with the qry_x on ShiftID, BusDay and TransTypeID Also tried it with just the Qry_X and no joins and no Cartesian qry in that Cumulative query.... runs about the same
Just in case... following is Qry_X code
SELECT qry_AllTransTypeAllShifts.ShiftID, qry_AllTransTypeAllShifts.BusDay, qry_AllTransTypeAllShifts.TransTypeID, Val(nz(Sum([LineItemNoTx]),0)) AS Amt, Val(nz(Sum([CSTax]),0)) AS TTx
FROM ((qry_AllTransTypeAllShifts LEFT JOIN (tbl_OtherPdOuts RIGHT JOIN tbl_CashFundActivityNoDenom ON tbl_OtherPdOuts.CashFdActNoDenomID = tbl_CashFundActivityNoDenom.CashFdActNoDenomID) ON (qry_AllTransTypeAllShifts.TransTypeID = tbl_CashFundActivityNoDenom.TransTypeID) AND (qry_AllTransTypeAllShifts.ShiftID = tbl_CashFundActivityNoDenom.ShiftID)) INNER JOIN sCtl_TransType ON qry_AllTransTypeAllShifts.TransTypeID = sCtl_TransType.TransTypeID) LEFT JOIN tbl_OtherPdOutsDetails ON tbl_OtherPdOuts.OtherPdOutsID = tbl_OtherPdOutsDetails.OtherPdOutsID
GROUP BY qry_AllTransTypeAllShifts.ShiftID, qry_AllTransTypeAllShifts.BusDay, qry_AllTransTypeAllShifts.TransTypeID;
ASKER
Realized that you had answered the question and that the Performance question should be a separate issue. Thank you for the help