Multiple Queries in a report...

[dymas]

Ok to explain the entire problem it goes like this..

I am building a report to count the amount of customers that have had a transaction in the last X amount of days (the criteria for the query is that they enter in an amount of days and the query brings up records in the last X amount of days made by those customers) In the report based on this single query you get a list of all the customers and their transactions but if a customer has multiple transactions in the period it will obviously show both of them uner the customers name. I want to be able to count the number of customers that have made transaction but a simple count function in that report will count each transaction.

Solution? I made a query to filter the names down to no duplicates and another query based on the second query to count the second query... Now I am trying to get the number spit out by that third query into the first report... I have tried to use dlookup and I haven't gotten it to work. Any idea on how I can get information from a different query to show up in the report? Or perhaps a different way than making three queries? THanks

Dymas

 

[dhookom]

You can sort/group by customer with a group header section (which could be very small). Add a text box to the customer group header:
[tt][blue]
Name: txtCustCount
Control Source: =1
Running Sum: Over All
Visible: No
[/blue][/tt]
Then add a text box in the Report Footer section:
[tt][blue]
Name: txtCustomers
Control Source: =txtCustCount
Visible: Yes
[/blue][/tt]


Duane
Hook'D on Access
MS Access MVP

 

[dymas]

Is that just the steps i should take or is that entering it into vba or SQL?

 

[dymas]

Well i entered that into the report word for word in the properties sheet and I still get a pop up asking for a parameter value for txtcustcount

 

[dymas]

nevermind my stupidity i got it to work thank you so much for your help.

 

[dhookom]

There is no code. This solution works.

What section did you add the txtCustomers in? Did you put it in the Page Footer rather than the Report Footer?


Duane
Hook'D on Access
MS Access MVP