Computing total sales with a query

  • Thread starter Thread starter cdrennon
  • Start date Start date
C

cdrennon

I need to generate a query which will list total sales by customer. I
have the first and last names from a Customer table, price from an
Inventory table, and quantity from a Sales table. In query design I
have lastname, firstname, and totalsales. For total sales I have
entered totalsales:=[Quantity]*[Price].

This will list the customer and the individual items*quantity, but
there is one line for every item purchased. I need the customer and
the total sales to show up as one line.

When i try sum([Quantity]*[Price]) for totalsales I get an error "You
tried to execute a query that does not include the specified
expression 'LastName' as part of an aggregate function."

Thanks
 
You have to change the query to an Aggregate query. Look for the symbol in
the query design menu that looks like a M rolled over on its left side (click
that symbol).

In the query grid, you will now see a row labeled "Total" and it should have
the term "Group By" entered under each of the columns. Go to your TotalSales
column and change this to Sum.

Now run your query.
 
You have to change the query to an Aggregate query.  Look for the symbol in
the query design menu that looks like a M rolled over on its left side (click
that symbol).

In the query grid, you will now see a row labeled "Total" and it should have
the term "Group By" entered under each of the columns.  Go to your TotalSales
column and change this to Sum.

Now run your query.

----
HTH
Dale

cdrennon said:
I need to generate a query which will list total sales by customer. I
have the first and last names from a Customer table, price from an
Inventory table, and quantity from a Sales table. In query design I
have lastname, firstname, and totalsales. For total sales I have
entered totalsales:=[Quantity]*[Price].
Click to expand...
This will list the customer and the individual items*quantity, but
there is one line for every item purchased. I need the customer and
the total sales to show up as one line.
Click to expand...
When i try sum([Quantity]*[Price]) for totalsales I get an error "You
tried to execute a query that does not include the specified
expression 'LastName' as part of an aggregate function."
Click to expand...
Thanks
Click to expand...
Click to expand...

That was exactly the info I needed. Thank you very much Dale!
 
Back
Top