SQL on report

[SpookiePower]

It is a long time since I last used access and are now starting to refresh it.

My question --
I have a report showing some data about a customer. Now I would like to be able to show in this report, how many items the customer has on stock. I have created a SQL like this -

SELECT COUNT (items) AS itemsonstock
FROM table
WHERE customernumber = 10

But in this example, the result will allways be from the customer number 10 !!
How do I do it, so that the result will be from the current customer, showing on the report ?

 

[Fozi]

In the report creat a group heading and footer for Customers.
Drop the Items into the Detail section
In the footer section create an unbound field anc enter =Count([itemref])


This should show the number of items per customer. Obviously "itemref" should be set to the correct field name

 

[SpookiePower]

In the report creat a group heading and footer for Customers.
Drop the Items into the Detail section
In the footer section create an unbound field anc enter =Count([itemref])

This should show the number of items per customer. Obviously "itemref" should be set to the correct field name

Drop the Items... Do you mean from the Field list ?

Where do I put the SQL that counts how many items on stock ?

 

[Fozi]

Yeah....place the items field within the Detail Section of your report

In the group footer create and unbound field and within it's control source type in the SQL