Access Report Needs to Use Query

[LarryRobert]

I have a report which lists orders. The orders are listed at the line item order. Therefore, there may be multiple line item records for a single order, all carrying the same order number. What I want to do is display the count of orders on the report. I have a query that does this, but have been unsuccessful in getting this to work in the report. I have tried the count function, but cannot find out how to make it only count the number of unique order numbers vs. number of records.

Any help would be greatly appreciated.

 

[lupins46]

I assume all items for the same order are listed together.
Introduce a group on Order number and say that you want a group header. In the group header put a textbox and set its properties to Controlsource : =1 and Running Sum value to: Over All.
Lets call this textbox txtKnt.

In the report footer add a textbox whose controlsource is =txtKnt.

You can make txtKnt invisible if you want and you can close up the control to zero height and then close the group header to zero height if you don't want the orders separated by a space.

 

[LarryRobert]

Lupins46, Thank you for your response. It looks like this is going to work if I can figure out one issue. When I set the textbox name in the footer to =txtknt, the report is not recognizing the reference.

 

[lupins46]

Check the name (property) of the running sum textbox.

 

[hobman]

You can use the count function but if you are trying to display the total count in the footer, you will not be able to calculate it directly into the footer. You must use the count function in a new textbox(txtCount) that you can create in the detail section and make that text box's visible to "No". Then you can create a new text box in the footer and give it a value of =[txtCount].

hobman

 

[LarryRobert]

Thanks to all who responded. I was finally able to get this to work. For some reason, the way I had to reference the field was report!txtcount in the controlsource.