GroupBy query for aligning cash transactions

[swas]

Hello,



I am trying to create a GroupBy query with Sum() and having dramas getting my head around things.

Scenario:

Laundromat, machines, based on cash. Cash cleared say weekly (Could be more or less often).

1 clear = coins + notes, all great. A single clear for the week is say $47 coins + 100 notes = $147 for the clear, for a given date / machine of say 8 Mar 2017. This is stored in a table tblCashClearances.

Introduce credit card (New technology) - now I have the maybe same $147 cash (Coin + note) sales for the week, and a bunch of card transactions in between. In other words, if the last a cash clearance was done on 1 Mar 2017, then the next on 8 Mar 2017, I may have card transactions for:
27 Feb 2017 2.11pm for $7.60,
2 Mar 2017 11:57am for $5.20,
3 Mar 2017 2:15pm for $6.80,
7 Mar 2017 3:45pm for $2.90
and so on. These card transactions are imported into my db to a tblCardTransactions table from csv files from the card supplier.

What I need to see, is the total sales per CashClearance - this should include the once off coin + notes, plus any CardTransactions since the last cash clear.

With the above numbers, I should have

Machine ClearDate Coin Notes Card Total
XYZ 8 Mar 2017 $47.00 $100.00 $14.90 $164.90

I start off looking at a GroupBy query with a Sum(CardTransactions.TransactionAmount) per CashClearance, then get bogged in the detail. I am currently trying to create a UNION query first (All card + cash), then GroupBy on this query.

I am generally ok on queries, it seems getting stuck on where to start here rather than the query detail (I think...).


Any comments appreciated.


Thanks in advance

swas

[Micron]

Sometimes the problem with a Totals query is that grouping is an issue. If you don't have a way to create a group wherein all the values you require belong to that group, then it's pretty much impossible to do it in one query. So we often end up creating multiple queries to feed a totals query. However, in your case it seems to me that this situation is what reports are for. Quite easy to group data in a report and do running sums over a group. You probably need a group over cash clear date or some other identifier like CashClearID.

[swas]

Thanks for the comment Micron, and sorry for being a bit vague on detail.

Here is a small subset of data, from a union query of tblCashClearances table (Where a single record per clear has coin + notes), and tblCardTransactions table which has the card sales accross multiple dates in between.

CashCleranceID	Machine	EventDateTime	CashBox	CashNotes	Card
596932	GRANTAFX1	01-Apr-15	$21.80	$60.00	$0.00
597397	GRANTAFX1	08-Apr-15	$10.10	$15.00	$0.00
600045	GRANTAFX1	24-Apr-15	$5.60	$210.00	$0.00
	GRANTAFX1	24-Apr-15 2:15:00 AM	$0.00	$0.00	$3.40
	GRANTAFX1	24-Apr-15 3:30:00 AM	$0.00	$0.00	$6.40
	GRANTAFX1	26-Apr-15 3:02:00 AM	$0.00	$0.00	$5.50
	GRANTAFX1	27-Apr-15	$0.00	$0.00	$3.40
598630	GRANTAFX1	29-Apr-15	$17.60	$30.00	$0.00
	GRANTAFX1	29-Apr-15	$0.00	$0.00	$25.70
	GRANTAFX1	30-Apr-15 2:15:00 AM	$0.00	$0.00	$5.60
	GRANTAFX1	01-May-15	$0.00	$0.00	$9.50

Where there is a CashClearanceID it means this was a cash clearance (Obviously). What I need to do is to include any Card sales with this same record, before the next CashClearanceID.

Here are the results sought from the above data:

CashCleranceID	Machine	EventDateTime	CashBox	CashNotes	Card
596932	GRANTAFX1	01-Apr-15	$21.80	$60.00	$0.00
597397	GRANTAFX1	08-Apr-15	$10.10	$15.00	$0.00
600045	GRANTAFX1	24-Apr-15	$5.60	$210.00	$18.70
598630	GRANTAFX1	29-Apr-15	$17.60	$30.00	$40.80

So the card sales are summed per CashClearanceID. But I can't group by CashClearanceID otherwise the null entries simply show the total for all Card sales.

My best guess forward is to use a subquery which looks up the previous CashClearanceID for a card date, then group by CashClearanceID (Along the lines you suggest). But I find subqueries to be very slow, especially when on larger transaction type tables.


Thoughts appreciated.

Thanks

swas

[swas]

Sorry - small correction to the above -

Card sales should be totalled forward to the next CashClearanceID, not backwards to the prior CashClearanceID.

I don't think it matters for the purpose of the discussion.


Thanks
swas

[Bob Fitz]

Do you not get what you want if you "Group By" your date field

[Bob Fitz]

Do you not get what you want if you "Group By" your date field

On second thoughts that won't work because of the time element sown in some records. You will need to create a "calculated" column in the query which only returns the actual date element (perhaps something like: "Left([EventDateTime],9)" without quotes) and group on that column.
EDIT
Better still might be to use Len([EventDateTime]) just in case your date format changes to something with more or less than 9 characters.

[swas]

Bob,

Thanks for the thoughts.

A GroupBy on the date field wouldn't help unless the Card dates were the same as a CashClearance date. Otherwise each Card transaction date would return a total.

Using your thoughts, I could use a sub query returning the first [CashClearanceID] [EventDateTime] Date after each Card [EventDateTime], then group on this.

Maybe sub query then group on the CashClearanceID is a better one, to avoid date and date / time issues.


Thanks

swas

[Micron]

I would try the subquery route. I've used them before on tables with literally millions of records using ODBC linked tables over a network. Wasn't lightning fast, but I'd say the results were very acceptable - maybe 15 seconds or so.

[swas]

Thanks for the comments.

A subquery is unuseably slow. Takes 30+ seconds for ~250 test card records (tblCashClearances has ~130k records), running on a laptop (Front and back end).

I don't know if some indexing or other optimisations might help. Or just avoid the sub query solution...


Thanks

swas