Trouble with Query

[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

 

[Guest]

Just redo the calculation in your query from the raw data and use criteria of

 

[cathywoodford]

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 -

What do you mean by "Raw" data?

 

[Guest]

What do you mean by "Raw" data?
The data in the records used to sum(subtotal).

 

[cathywoodford]

The data in the records used to sum(subtotal).
--
KARL DEWEY
Build a little - Test a little






- Show quoted text -

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

 

[cathywoodford]

The data in the records used to sum(subtotal).

- Show quoted text -

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 -

What's happening is it's doing a total for each item vs each customer.

 

[Guest]

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

What do you mean by "Raw" data?

The data in the records used to sum(subtotal).

:
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.

:
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- Hide quoted text -

- Show quoted text -

What do you mean by "Raw" data?- Hide quoted text -

- Show quoted text -

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 -

What's happening is it's doing a total for each item vs each customer.