subquery question; should be easy for you

[BodiKlamph]

Hello, thx for reading.

shopping for groceries will be a perfect analogy for my needs.

What I want is to get the total bill (sum of all items) for shoppers in
any given day, but only for the people with ketchup or cereal on their
receipt.

however, unlike a real grocery receipt, i don't store a "total" field
in the table, only an amount field that i will later sum.

so, in pseudo sql:

select sum([amount]) from [details] where any [item] like "*cereal*" or
"*ketchup*" and date=#1/11/05#

it's the "any" part of the query that has got me stumped. I can create
a subquery that searches for the desired items, but I can't figure out
how to sum each persons bill based on finding 1 or more of those items.

possible?

thx

 

[Brendan Reynolds]

SELECT Sum(Details.Amount) AS SumOfAmount
FROM Details
WHERE (((Details.ShopperID) In (select shopperid from details where item
like "*cerial*" or item like "*ketchup*" and datebought = #1 November
2005#)));

 

[BodiKlamph]

thx, that was a good starting point, but that gives me the some of the
whole table, in other word all of the grocery bills.

I want to know the sum of each persons bill independantly

* bill 1
- item = ketchup, price = $1.00
- item = cereal, price = $2.00
- item = tuna, price = $3.00
* bill 2
- item = tuna, price = $3.00
- item = cereal, price = $2.00
* bill 3
- item = tuna, price = $3.00

my query would return:

bill 1 = $6.00
bill 2 = $5.00

bill 3 would not return a row because it doesn't have ketchup or cereal
in it. I need, for each record in the parent table, to return the sum
of items bought (detail record) if, and only if, they match the given
creteria.

 

[BodiKlamph]

I figured it out.

I first created a sub query grouped by bill using your IN sub query to
filter even more

I then used a main query to attach all the other fields I needed.

thx