Solved

Access - Query Cumulative for day by TransType if No TransType is Subseq Shift

Posted on 2014-12-02
11
109 Views
Last Modified: 2014-12-02
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
0
Comment
Question by:wlwebb
  • 7
  • 3
11 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40476807
exactly, what do you need?
0
 

Author Comment

by:wlwebb
ID: 40476848
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]
0
 

Author Comment

by:wlwebb
ID: 40476916
The code I have to Sum the Amt by Shift and BusDay is:

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;

Open in new window


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.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 40476959
Add the shifts definition table to the query.  Use a right join to join to qry_X.  That will return all shifts plus any related data.
0
 

Author Comment

by:wlwebb
ID: 40476980
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....
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 40477072
If you want to show all possible trans types, then you need a table of trans types with a left join.  So now you're up to two.  shift * trans type.  Do that with a query that uses no join to force a Cartesian product.  Then join that query with a left join to this query.
0
 

Author Comment

by:wlwebb
ID: 40477087
join to force a Cartesian product............... ??????????????  But I think I know what you're saying.........
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40477123
You have two pieces of data that you want to force to occur in the recordset whether they occur naturally or not.  To combine the two you need to create a query that includes both tables but no join line so that each row in the first table is match to every row in the second so given the two sets:
set 1
a
b
c
set 2
1
2
3
4
you end up with
a 1
a 2
a 3
a 4
b 1
b 2
b 3
b 4
c 1
c 2
c 3
c 4
The resultset is a Cartesian Product and you join it to the posted query on the two fields using left joins to force all those rows to always appear in the recordset whether there is a matching record or not.
0
 

Author Comment

by:wlwebb
ID: 40477140
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......
0
 

Author Comment

by:wlwebb
ID: 40477247
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:
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));

Open in new window


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;

Open in new window

0
 

Author Closing Comment

by:wlwebb
ID: 40477551
Realized that you had answered the question and that the Performance question should be a separate issue.  Thank you for the help
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now