Getting Record Count on Form

[paxdak]

I have a table of Customers which has CustomerName, Address, etc.
Another table of Orders that has OrderNumber, Order Date, Salesperson,
CustomerName.
And a third table of OrderDetails that has OrderNumber, Product, Category,
Price.

In a form for customers (currently based on Customers Table), how can I
show a total of each Category for all Orders that a specific Customer has
bought? On the form, I have a textbox for each category: Small and Large.
How do I get the form to show the number of Small and Large into the right
textbox?

I think I have all the relationships correct [Customers to Orders (1 to
many) and Orders to OrderDetails (1 to many)], because I can do a Total
Query that is grouped by CustomerName and Category and gives the correct
totals. But for the life of me a can't get it into the form.

 

[Guest]

Try to read about using DCount() method
hope this can add more help

 

[Brett Collings [429338]]

There's a few tools available.
- If you have Grouping say on category in your Report (highly
recommended), then you have the option of using the Sum([MyFieldName])
in the Group Footer.
- There is also the ability to do a "Running Sum" (see Help) for order
items if they are all in the same group.
- If all the items are in a single list but are of different
Categories, as you've already been advised, check out the DCount()
functin in Help

Brett

I have a table of Customers which has CustomerName, Address, etc.
Another table of Orders that has OrderNumber, Order Date, Salesperson,
CustomerName.
And a third table of OrderDetails that has OrderNumber, Product, Category,
Price.

In a form for customers (currently based on Customers Table), how can I
show a total of each Category for all Orders that a specific Customer has
bought? On the form, I have a textbox for each category: Small and Large.
How do I get the form to show the number of Small and Large into the right
textbox?

I think I have all the relationships correct [Customers to Orders (1 to
many) and Orders to OrderDetails (1 to many)], because I can do a Total
Query that is grouped by CustomerName and Category and gives the correct
totals. But for the life of me a can't get it into the form.

Cheers,
Brett