Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: Getting many records summed in a query

    I am making a database to manage paper delivery orders. I have 3 tables; the customer table (primary key, Customer No); the paper table (primary key, paper name); and order table (primary key, Order No).

    The order table contains both foreign keys Customer ID and Paper Name. This has allowed a customer to have many papers delivered each day. I have successfully made some a nice form showing cutomer details with a subform showing all their paper orders and prices. On this form, I can create a totalling box summing the paper prices.

    Unfortunately I need this weekly total figure in a table/query.

    I have so far created a query with 2 fields: Customer No (from Customer Table) and a weekly cost field with the following DSum expression:

    Weekly Cost: DSum("[Paper Price]","qry_allcustomerorder","[Customer No] = [Customer No]")

    (qry_allcustomerorder is the query combining cutomer details and their orders)

    As you can no doubt see, this simply adds up all the paper prices for every order and puts it as every customer's weekly total. I cannot for the life of me work out how to set the criteria to match the Customer No in qry_allcustomerorder to the Customer No in this query, and create an individual weekly total.

    Can any one help me please?

    Sorry if I've omitted some key info, or if it's a simple problem I'm taking a really roundabout route in solving!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Does this work?

    DSum("[Paper Price]","qry_allcustomerorder","[Customer No] = " & [Customer No])
    Paul

  3. #3
    Join Date
    Mar 2009
    Posts
    2

    Thanks

    Works like a charm, thanks.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem, and welcome to the site. By the way, you may find this a good page to bookmark:

    General: DLookup Usage Samples
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •