# Report Total Logic Building

Page 1 of 1

## 3 Replies - 2146 Views - Last Post: 11 July 2012 - 08:34 AM

### #1 AnalyticLunatic

• D.I.C Lover

Reputation: 226
• Posts: 1,052
• Joined: 25-June 12

# Report Total Logic Building

Posted 10 July 2012 - 09:23 AM

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

Is This A Good Question/Topic? 0

## Replies To: Report Total Logic Building

### #2 Martyr2

• Programming Theoretician

Reputation: 4923
• Posts: 13,312
• Joined: 18-April 07

## Re: Report Total Logic Building

Posted 10 July 2012 - 11:13 AM

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

### #3 AnalyticLunatic

• D.I.C Lover

Reputation: 226
• Posts: 1,052
• Joined: 25-June 12

## Re: Report Total Logic Building

Posted 10 July 2012 - 12:16 PM

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:

-dtmDay (This is where I need the by Day break-down)
=[chrReceiptType] & " Permits"
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.

### #4 AnalyticLunatic

• D.I.C Lover

Reputation: 226
• Posts: 1,052
• Joined: 25-June 12

## Re: Report Total Logic Building

Posted 11 July 2012 - 08:34 AM

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:

'Miscellaneous Labels
-[dtmDayIn]
[="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:

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: