[AnalyticLunatic]

Hello, I need some assistance with Expression Builder. I have a Report that breaks down some data by day. For each day on each line I display the Shift ('intShiftOut' from tblVisitors) and I need next to it to display the number of transactions which were done on that shift and at the end the Total amount transacted.

EXAMPLE

DATA:
intShiftOut-curPaidatExit
1-----------2.00
1-----------5.00
1-----------2.00
1-----------5.00

SHOULD DISPLAY ON REPORT:
July 10th 2012
Shift-------Transactions---------Total
1-----------4--------------------$14.00

What expression do I use to correctly figure out the number of Transactions that were done per shift and the Total amount processed?

Any help is greatly appreciated! I haven't used Access in quite awhile and got tagged-in for some small updates

[Martyr2]

Create a query like so...

SELECT intShiftOut AS Shift, Count(*) AS Transactions, Sum(curPaidatExit) AS Total FROM tblVisitors GROUP BY intShiftOut;

This should get you what you were looking for. Here we are grouping by the shift and for transactions we are showing the count of each item and then the sum of the pay as total.

To better understand what I have done here, you can read up on the "GROUP BY" clause of SQL.


Now this is for grouping by shift. If a date needs to come into play, you can group by day and by shift. I will leave that part up to you if you need to.

This post has been edited by Martyr2: 10 July 2012 - 11:15 AM

[AnalyticLunatic]

That does indeed make sense and I thank you Martyr2!

My biggest problem is getting it into what I need for the report. I've been given a Report for another purpose that is in the format I need, but I need to change a few field values to how they are receiving data.

Do you know of an easy way to get the aforementioned values without using it all in one query? Below I have outlined the visual layout of the Report, I sincerely I apologize for not being able to explain more clearly, but I cannot upload a photo...

On the report in Design View I have:

Report Header
Page Header
dtmDayIn Header
-dtmDay (This is where I need the by Day break-down)
chrReceiptType Header
=[chrReceiptType] & " Permits"
intShiftPaid Header
Detail
[Shift] [#OfTransactions For this Shift] [Total $ For all Transactions on this shift]
intShiftPaid Footer
chrReceiptType Footer
dtmDayIn Footer
Daily Total: [Sum of All Transactions][Sum of all $ Transacted for this Day]
Page Footer
[="Run Date: " & Format(Now(),"Long Date")] [="Page " & [Page] & " of " & [Pages]]
Report Footer
Total Received to Date: [Sum of all $ Transacted for Report]

All the Data comes direct from the tblVisitors.

[AnalyticLunatic]

Ok, now I am CLOSE to where I want to be.

My Report is grouping by 'dtmDayIn' Then by 'intShiftOut'. Below is the set-up in Design View:

Report Header
Page Header
'Miscellaneous Labels
dtmDayIn Header
-[dtmDayIn]
intShiftOut Header
[="Visitor Permits"]
Detail
[intShiftOut] [=Count([curPaidatExit])] [curPaidatExit]
intShiftOut Footer
dtmDayIn Footer
[="Daily Total:"] [=Count([curPaidatExit])][=Sum([curPaidatExit])]
Page Footer
[="Run Date: " & Format(Now(),"Long Date")] [="Page " & [Page] & " of " & [Pages]]
Report Footer
Total Received to Date: [=Sum([curPaidatExit])]

When I run the report I am getting on my Earliest Date:

__________Shift____________Permits______________Received
Thursday, August 04, 2011
Visitor Permits
___________________________1561
___________________________1561
___________________________1561
___________________________1561
Visitor Permits
__________1________________1561_________________$2.00
__________1________________1561_________________$2.00
__________1________________1561_________________$8.00
__________1________________1561_________________$2.00
Visitor Permits
__________2________________1561_________________$2.00
__________2________________1561_________________$2.00
__________2________________1561_________________$2.00
__________2________________1561_________________$2.00
__________2________________1561_________________$2.00
__________2________________1561_________________$2.00
__________2________________1561_________________$2.00
__________2________________1561_________________$5.00
__________2________________1561_________________$5.00
__________2________________1561_________________$2.00
__________2________________1561_________________$2.00
__________2________________1561_________________$2.00
__________2________________1561_________________$2.00
__________2________________1561_________________$2.00
__________2________________1561_________________$2.00
__________2________________1561_________________$5.00
Daily Total:________________20_______________$55.00

What I NEED to see is:

__________Shift____________Permits______________Received
Thursday, August 04, 2011
Visitor Permits
__________1________________4____________________$14.00
Visitor Permits
__________2________________16___________________$41.00
Daily Total:______________20_________________$55.00

Would any kind D.I.C.'rs have an idea as to how to remedy my issue?