Ok so this is probably very easy but I just cannot get it to work.
I have a table (tbl_orders) with 2 fields, order number and customer. 10000 order numbers, and 300 customers

I have another table (tbl_customers) with a list of the 300 customers and a field named Number of Orders. I want the number of orders for each customer in that field.

I figured I can just use dcount and count the number of times a customer id is in the Customer field in the orders table. And take this number and add it to its corresponding customer in the customer table.

I created a update query.
Field: Number of Orders
Table: tbl_customers
Criteria: DCount("[tbl_orders].[Customer]","tbl_orders","[tbl_orders].[Customer] = [tbl_customers].[Customer]")

This gives me an error: "Unkown".

I tested it with one customer id, and it put 35 in each field that had a customer id match in the orders table:
DCount("[tbl_orders].[Customer]","tbl_orders","[tbl_orders].[Customer] = '1234' ")
This would be good by I need it to use the corresponding customer id in the customers table! I want the '1234' to be changed every time to the corresponding customer id.


Please help asap.
Any ideas what I can do would be helpful!

Hi,



What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

It's not a good idea to store the number of orders in the customers table because as soon as a customer places an order, your table is already outdated.

It's better to just use a Totals query against the Orders table to count how many orders each customer made.

Just my 2 cents...

I am using Access 2007.

I will be reimporting the data from a report every week, so it will never be outdated.
I only need the query to update the number of orders once, not keep it up to date.

Thanks
Q

qwerty1,

Why are you storing this calculated value at all, even if you're only storing it once? What do you use this value for?

Surely it makes more sense just to calculate it when you need it. Storing this value is like storing a person's birthdate and their age in two fields. You can use the birthdate to calculate the age for a given date. Even if you only need the person's age once, you still wouldn't store it redundantly in a table.

Hope this helps,

Dennis

Hi Q,


But it will be outdated as soon as another order is added. That could be within minutes or days of your update process.



Whether you use a totals query (what I recommend) or an update query (what you want), the result will be the same at the time that you use it. The only difference is that using the totals query will always be accurate and will probably run faster.

Just my 2 cents...

Yah, thanks.
I did a totals query, then used update query to put it in the correct field.
It now works great.
Thanks!

Good luck with your project.