Trouble with Query

  • Thread starter Thread starter cathywoodford
  • Start date Start date
C

cathywoodford

Hey. I've got trouble with a query. I'm trying to create a report
based on if a customer has spent more than $250.00 in sales...the
trouble is the total sales is not stored anywhere. On another report
it's just a text box that says =sum(subtotal). Is there anyway for me
to reference this in another query?

Cathy
 
Just redo the calculation in your query from the raw data and use criteria of>250.

--
KARL DEWEY
Build a little - Test a little






- Show quoted text -
Click to expand...

What do you mean by "Raw" data?
 
The data in the records used to sum(subtotal).
--
KARL DEWEY
Build a little - Test a little






- Show quoted text -
Click to expand...

I've tried that but I still can't get it to work. I have one customer
who buys something for $20 and another item for $40 (2 separate
records) so in total their Total Sale should be $60 but I can only get
it to display $60 if I type int eh textbox.

I have one query as follows:
SELECT Transactions.OrderID, Items.ItemDescr, Items.Price,
Transactions.Quantity, Items!Price*Transactions!Quantity AS SubTotal,
Customers.CustID, Customers.LName, Customers.FName, Transactions.
[Item#], Sum(items!Price*transactions!Quantity) AS Total
FROM Items INNER JOIN (Customers INNER JOIN Transactions ON
Customers.CustID = Transactions.CustID) ON Items.[Item#] =
Transactions.[Item#]
GROUP BY Transactions.OrderID, Items.ItemDescr, Items.Price,
Transactions.Quantity, Items!Price*Transactions!Quantity,
Customers.CustID, Customers.LName, Customers.FName, Transactions.
[Item#];

The TOTAL column in this query shows the same as the subtotal - so
it's not adding the customers 2 lines together. That's why my >250
won't work.

Cathy
 
The data in the records used to sum(subtotal).
Click to expand...
- Show quoted text -
Click to expand...

I've tried that but I still can't get it to work. I have one customer
who buys something for $20 and another item for $40 (2 separate
records) so in total their Total Sale should be $60 but I can only get
it to display $60 if I type int eh textbox.

I have one query as follows:
SELECT Transactions.OrderID, Items.ItemDescr, Items.Price,
Transactions.Quantity, Items!Price*Transactions!Quantity AS SubTotal,
Customers.CustID, Customers.LName, Customers.FName, Transactions.
[Item#], Sum(items!Price*transactions!Quantity) AS Total
FROM Items INNER JOIN (Customers INNER JOIN Transactions ON
Customers.CustID = Transactions.CustID) ON Items.[Item#] =
Transactions.[Item#]
GROUP BY Transactions.OrderID, Items.ItemDescr, Items.Price,
Transactions.Quantity, Items!Price*Transactions!Quantity,
Customers.CustID, Customers.LName, Customers.FName, Transactions.
[Item#];

The TOTAL column in this query shows the same as the subtotal - so
it's not adding the customers 2 lines together. That's why my >250
won't work.

Cathy- Hide quoted text -

- Show quoted text -
Click to expand...

What's happening is it's doing a total for each item vs each customer.
 
You are grouping on OrderID so it totals at that level. You can not have a
rollup sum and details in the same query unless you use a subquery or total
in a different query and join at the same level.
--
KARL DEWEY
Build a little - Test a little


[email protected] said:
What do you mean by "Raw" data?
Click to expand...
The data in the records used to sum(subtotal).
Click to expand...
:
On Oct 4, 2:51 pm, KARL DEWEY <[email protected]>
wrote:
Just redo the calculation in your query from the raw data and use criteria of>250.
Click to expand...
:
Hey. I've got trouble with a query. I'm trying to create a report
based on if a customer has spent more than $250.00 in sales...the
trouble is the total sales is not stored anywhere. On another report
it's just a text box that says =sum(subtotal). Is there anyway for me
to reference this in another query?
Click to expand...
Cathy- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
What do you mean by "Raw" data?- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

I've tried that but I still can't get it to work. I have one customer
who buys something for $20 and another item for $40 (2 separate
records) so in total their Total Sale should be $60 but I can only get
it to display $60 if I type int eh textbox.

I have one query as follows:
SELECT Transactions.OrderID, Items.ItemDescr, Items.Price,
Transactions.Quantity, Items!Price*Transactions!Quantity AS SubTotal,
Customers.CustID, Customers.LName, Customers.FName, Transactions.
[Item#], Sum(items!Price*transactions!Quantity) AS Total
FROM Items INNER JOIN (Customers INNER JOIN Transactions ON
Customers.CustID = Transactions.CustID) ON Items.[Item#] =
Transactions.[Item#]
GROUP BY Transactions.OrderID, Items.ItemDescr, Items.Price,
Transactions.Quantity, Items!Price*Transactions!Quantity,
Customers.CustID, Customers.LName, Customers.FName, Transactions.
[Item#];

The TOTAL column in this query shows the same as the subtotal - so
it's not adding the customers 2 lines together. That's why my >250
won't work.

Cathy- Hide quoted text -

- Show quoted text -
Click to expand...

What's happening is it's doing a total for each item vs each customer.
Click to expand...
 
Back
Top