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

#1 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 223
  • View blog
  • Posts: 1,035
  • 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  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4438
  • View blog
  • Posts: 12,309
  • 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

Was This Post Helpful? 0
  • +
  • -

#3 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 223
  • View blog
  • Posts: 1,035
  • 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:

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.
Was This Post Helpful? 0
  • +
  • -

#4 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 223
  • View blog
  • Posts: 1,035
  • 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:

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?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1