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开发者_JAVA技巧)) 

The SQL behind the report

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

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

• Payment Methods table:

  

• Payments table:

  

• Query:

  

• Query results:

  

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

• Report datasource:

  

• Report preview:

  

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.