CODEWITHSUNDEEP
ms-access
edmon
edmon

Reputation: 341

Building a Access Report from a query

I have a query that has 3 columns: Payment Method, Count, Amount. When I try to create my report to bring in the data from the query, if a payment method has more than one in the count column it shows up as zero on my report for the count and the total. I am using the following in expression builder to bring in the data from the query:

for the number of the specific payments

=Sum(IIf([paymethod]="Discover",[Count],0))

for the total amount of all payments

=Sum(IIf([paymethod]="Discover",[Total],0))

The SQL behind the report

SELECT qryDailyDeposit.Count, qryDailyDeposit.Total, qryDailyDeposit.paymethod
FROM [qryTotal Deposit], qryDailyDeposit;

Upvotes: 0

Views: 6641

Answers (2)

Igor Turman
Igor Turman

Reputation: 2205

I guess your query with JOIN and Count(*) causes issues. Regardless, the following setup should guard you against unexpected results:

  • Payment Methods table:

    enter image description here

  • Payments table:

    enter image description here

  • Query:

    enter image description here

  • Query results:

    enter image description here

Now, just use the above query as datasource for your report:

  • Report datasource:

    enter image description here

  • Report preview:

    enter image description here

Upvotes: 2

Banjoe
Banjoe

Reputation: 1768

Make a new query to bind to the report:

SELECT paymethod, sum(amount) as [Amount], count(*) as [Total Payments] FROM yourTransactionTable GROUP BY paymethod ORDER BY paymethod

Once that's bound to your report you should be able to use a query wizard to build a quick report or design your own by dragging the bound fields over.

I'm guessing the reason you're getting 0 entry text boxes is that the report is going through each row returned by the query and on rows where the paymethod isn't "Discover", for example, it just outputs 0.

Upvotes: 0

Related Questions