IIF Statement Help

[Mike]

Hello MVP's
Please help with my problem
Below is my SQL and Example
SELECT PDetail.TIME_STAMP, PDetail.RECEIPT_NUM, PDetail.DETAIL_FOR_QTY,
PDetail.PLU, Plu.PLU_DESC, Plu.MANUF
FROM PDetail INNER JOIN Plu ON PDetail.PLU = Plu.PLU_NUM
GROUP BY PDetail.TIME_STAMP, PDetail.RECEIPT_NUM, PDetail.DETAIL_FOR_QTY,
PDetail.PLU, Plu.PLU_DESC, Plu.MANUF, PDetail.DEPT, PDetail.DETAIL_CLASS
HAVING (((PDetail.TIME_STAMP)>#4/1/2007#) AND ((Plu.MANUF)="RJ REYNOLDS")
AND ((PDetail.DEPT)=200 Or (PDetail.DEPT)=230 Or (PDetail.DEPT)=280) AND
((PDetail.DETAIL_CLASS)=1))
ORDER BY PDetail.TIME_STAMP;

How can I say:
IF SUM of DETAIL_FOR_QTY For RECEIPT_NUM Is = 5 In the example below
I need to see RECEIPT_NUM 1 ,3 ,5
TIME_STAMP | RECEIPT_NUM | DETAIL_FOR_QTY | PLU | PLU_DESC | MANUF
4/1/2007 1 1
4/1/2007 1 1
4/1/2007 1 1
4/1/2007 1 1
4/1/2007 1 1
4/1/2007 2 1
4/1/2007 2 1
4/1/2007 2 1
4/1/2007 3 3
4/1/2007 3 2
4/1/2007 4 4
4/1/2007 5 5

 

[John W. Vinson]

Hello MVP's
Please help with my problem
Below is my SQL and Example
SELECT PDetail.TIME_STAMP, PDetail.RECEIPT_NUM, PDetail.DETAIL_FOR_QTY,
PDetail.PLU, Plu.PLU_DESC, Plu.MANUF
FROM PDetail INNER JOIN Plu ON PDetail.PLU = Plu.PLU_NUM
GROUP BY PDetail.TIME_STAMP, PDetail.RECEIPT_NUM, PDetail.DETAIL_FOR_QTY,
PDetail.PLU, Plu.PLU_DESC, Plu.MANUF, PDetail.DEPT, PDetail.DETAIL_CLASS
HAVING (((PDetail.TIME_STAMP)>#4/1/2007#) AND ((Plu.MANUF)="RJ REYNOLDS")
AND ((PDetail.DEPT)=200 Or (PDetail.DEPT)=230 Or (PDetail.DEPT)=280) AND
((PDetail.DETAIL_CLASS)=1))
ORDER BY PDetail.TIME_STAMP;

How can I say:
IF SUM of DETAIL_FOR_QTY For RECEIPT_NUM Is = 5 In the example below
I need to see RECEIPT_NUM 1 ,3 ,5
TIME_STAMP | RECEIPT_NUM | DETAIL_FOR_QTY | PLU | PLU_DESC | MANUF
4/1/2007 1 1
4/1/2007 1 1
4/1/2007 1 1
4/1/2007 1 1
4/1/2007 1 1
4/1/2007 2 1
4/1/2007 2 1
4/1/2007 2 1
4/1/2007 3 3
4/1/2007 3 2
4/1/2007 4 4
4/1/2007 5 5

I'm not at ALL sure I understand the question, but here's a possible hint. You
can pass criteria to a totals query in *either* the WHERE clause or the HAVING
clause. WHERE filters the records first, prior to doing the calculations;
HAVING lets you apply criteria to totals, counts, and other calculated values.
The query grid makes it easy to use HAVING criteria - it's the default - but
it's really more efficient to use the WHERE pseudo-operator on the TOTALS line
for criteria such as the ones you're using; and use the default HAVING on only
the totals line.

Do you want to see all the detail lines, but only for those records where the
sum is equal to 5? In your example you're not showing PLU or PLU_DESC or
MANUF; do you need them, or just the RECEIPT_NUM?

John W. Vinson [MVP]

 

[Mike]

John,
First The PLU|PLU_DESC|MANUF
I didn't write them in because of lack of space
I just would like to see records if the DETAIL_FOR_QTY for the receipt is =>5

Looking at the little Example I gave you See RECEIPT_NUM 1 There are a Total
of
are 5 (1)'s (I want to see)
Looking at RECEIPT_NUM 2 There are a Total of 3 (1)'s (I don't need to see)
Looking at RECEIPT_NUM 3 There is 1 (3) And 1 (2) for a total of 5 for
RECEIPT_NUM3
(I need to see)

I just would like to see records if the DETAIL_FOR_QTY for the receipt is =>5

 

[John W. Vinson]

John,
First The PLU|PLU_DESC|MANUF
I didn't write them in because of lack of space
I just would like to see records if the DETAIL_FOR_QTY for the receipt is =>5

Looking at the little Example I gave you See RECEIPT_NUM 1 There are a Total
of
are 5 (1)'s (I want to see)
Looking at RECEIPT_NUM 2 There are a Total of 3 (1)'s (I don't need to see)
Looking at RECEIPT_NUM 3 There is 1 (3) And 1 (2) for a total of 5 for
RECEIPT_NUM3
(I need to see)

I just would like to see records if the DETAIL_FOR_QTY for the receipt is =>5

OK, it sounds like a) you don't need a totals query at all, since you want to
see each individual record; and b) you'll need a Subquery or a DSum() function
as a criterion. If the data in this query can be read-only try the subquery:

SELECT PDetail.TIME_STAMP, PDetail.RECEIPT_NUM, PDetail.DETAIL_FOR_QTY,
PDetail.PLU, Plu.PLU_DESC, Plu.MANUF
FROM PDetail INNER JOIN Plu ON PDetail.PLU = Plu.PLU_NUM
WHERE (((PDetail.TIME_STAMP)>#4/1/2007#) AND ((Plu.MANUF)="RJ REYNOLDS")
AND ((PDetail.DEPT) IN (200, 230, 280)) AND
((PDetail.DETAIL_CLASS)=1)) AND (SELECT Sum([DETAIL_FOR_QTY]) FROM PDetail AS
XCount WHERE XCount.PLU = Plu.PLU_NUM) = 5
ORDER BY PDetail.TIME_STAMP;


John W. Vinson [MVP]