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!