Results 1 to 2 of 2
  1. #1
    qwerty1 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    5

    Issue with update query and dcount as criteria! Easy need help!

    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!

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    Except under very special circumstances, you would not store a calculated value in a table, but just do the calculation when needed. You can display the count in queries, forms or reports.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-14-2012, 10:31 AM
  2. Easy DLookup Issue
    By Niki in forum Access
    Replies: 7
    Last Post: 05-25-2011, 03:00 PM
  3. multiple dcount update query
    By slothnet in forum Programming
    Replies: 5
    Last Post: 08-24-2010, 03:44 PM
  4. Mask for dcount criteria
    By akbigcat86 in forum Programming
    Replies: 3
    Last Post: 08-09-2010, 11:00 AM
  5. Replies: 2
    Last Post: 04-16-2010, 09:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums