Count the receipt# if ..

H

hoachen

I am using 2003 version of access.
I have two tables:
Table1 (Recipt#, Item#, RcdQty, Cust#)
Table2 (Order#, OrderQty, OrderDate, Cust#, price, Item#)

What i intent to do is to eliminate the item have been refund (with the "-"
in OrderQty) then count how many recipt been issued base on unique recipt#

here is my pseudocode:

If the OrderQty AND price on table2 =="-"
Then
check the item# on table2 agaist table1 if MATCHES Continue to check

Then
check the cust# on table2 agaist table1 if MATCHES

Then
I don' want to count these recipt#

End if

How can i put this in code and where?
 
H

hoachen

Thank you so much for read my question. Here is the query result after i
joined the Cust# and the Item#

Recipt# Cust# Item# RQty OQty O# ODate OPrice
122455 11111 779127 1 1 9321510 1/2/2009 $2.00
12354 6015140 786571 1 1 9321510 1/2/2009 $9.99
158223 6015140 925373 1 1 9321510 1/2/2009 $7.05
158223 6015140 925373 1 -1 9321510 1/5/2009 -$7.05
5974473 7772523 789988 1 1 5512321 3/2/2009 $11.00
5974473 7772523 789988 1 -1 5512321 3/2/2009 -$11.00
5974473 7772523 874810 1 1 9321510 1/2/2009 $4.25

The total count on the recipt# should be 3
Should display
122455 11111 779127 1 1 9321510 1/2/2009 $2.00
12354 6015140 786571 1 1 9321510 1/2/2009 $9.99
5974473 7772523 874810 1 1 9321510 1/2/2009 $4.25

Does this make sense?
 
K

KARL DEWEY

I think this does it --
SELECT qry_hoachen.[Recipt#], qry_hoachen.[Cust#], qry_hoachen.[Item#],
Sum(qry_hoachen.RQty) AS SumOfRQty, Sum(qry_hoachen.OQty) AS SumOfOQty,
qry_hoachen.[O#], Sum(qry_hoachen.OPrice) AS SumOfOPrice, (SELECT
IIF(Sum([XX].OPrice) <>0, [XX].[ODate], "") FROM qry_hoachen AS [XX] WHERE
[XX].[Cust#] = qry_hoachen.[Cust#] AND [XX].[Item#] = qry_hoachen.[Item#]
AND [XX].[Recipt#] = qry_hoachen.[Recipt#] GROUP BY [XX].[ODate]) AS O_Date
FROM qry_hoachen
GROUP BY qry_hoachen.[Recipt#], qry_hoachen.[Cust#], qry_hoachen.[Item#],
qry_hoachen.[O#]
HAVING (((Sum(qry_hoachen.OPrice))<>0));
 
H

hoachen

Sorry, it seems too complex! and I don't understand why you do the sum on all
of them. Can you explain why you do all the sum? I need only count the
recipt# if they don't return that match the order.

KARL DEWEY said:
I think this does it --
SELECT qry_hoachen.[Recipt#], qry_hoachen.[Cust#], qry_hoachen.[Item#],
Sum(qry_hoachen.RQty) AS SumOfRQty, Sum(qry_hoachen.OQty) AS SumOfOQty,
qry_hoachen.[O#], Sum(qry_hoachen.OPrice) AS SumOfOPrice, (SELECT
IIF(Sum([XX].OPrice) <>0, [XX].[ODate], "") FROM qry_hoachen AS [XX] WHERE
[XX].[Cust#] = qry_hoachen.[Cust#] AND [XX].[Item#] = qry_hoachen.[Item#]
AND [XX].[Recipt#] = qry_hoachen.[Recipt#] GROUP BY [XX].[ODate]) AS O_Date
FROM qry_hoachen
GROUP BY qry_hoachen.[Recipt#], qry_hoachen.[Cust#], qry_hoachen.[Item#],
qry_hoachen.[O#]
HAVING (((Sum(qry_hoachen.OPrice))<>0));

--
Build a little, test a little.


hoachen said:
Thank you so much for read my question. Here is the query result after i
joined the Cust# and the Item#

Recipt# Cust# Item# RQty OQty O# ODate OPrice
122455 11111 779127 1 1 9321510 1/2/2009 $2.00
12354 6015140 786571 1 1 9321510 1/2/2009 $9.99
158223 6015140 925373 1 1 9321510 1/2/2009 $7.05
158223 6015140 925373 1 -1 9321510 1/5/2009 -$7.05
5974473 7772523 789988 1 1 5512321 3/2/2009 $11.00
5974473 7772523 789988 1 -1 5512321 3/2/2009 -$11.00
5974473 7772523 874810 1 1 9321510 1/2/2009 $4.25

The total count on the recipt# should be 3
Should display
122455 11111 779127 1 1 9321510 1/2/2009 $2.00
12354 6015140 786571 1 1 9321510 1/2/2009 $9.99
5974473 7772523 874810 1 1 9321510 1/2/2009 $4.25

Does this make sense?
Click to expand...
Click to expand...
 
K

KARL DEWEY

Can you explain why you do all the sum?
You have to sum the positive and negatives to determine the zeros.
Maybe I missed the boat but I took your sample data and the query results
match what you posted that you wanted.
It is a totals query with criteria to return records not totaling zero with
subquery to pull date for those records. A subquery is necessary as the
records that zero out may not have same date and would not allow combined
total.


--
Build a little, test a little.


hoachen said:
Sorry, it seems too complex! and I don't understand why you do the sum on all
of them. Can you explain why you do all the sum? I need only count the
recipt# if they don't return that match the order.

KARL DEWEY said:
I think this does it --
SELECT qry_hoachen.[Recipt#], qry_hoachen.[Cust#], qry_hoachen.[Item#],
Sum(qry_hoachen.RQty) AS SumOfRQty, Sum(qry_hoachen.OQty) AS SumOfOQty,
qry_hoachen.[O#], Sum(qry_hoachen.OPrice) AS SumOfOPrice, (SELECT
IIF(Sum([XX].OPrice) <>0, [XX].[ODate], "") FROM qry_hoachen AS [XX] WHERE
[XX].[Cust#] = qry_hoachen.[Cust#] AND [XX].[Item#] = qry_hoachen.[Item#]
AND [XX].[Recipt#] = qry_hoachen.[Recipt#] GROUP BY [XX].[ODate]) AS O_Date
FROM qry_hoachen
GROUP BY qry_hoachen.[Recipt#], qry_hoachen.[Cust#], qry_hoachen.[Item#],
qry_hoachen.[O#]
HAVING (((Sum(qry_hoachen.OPrice))<>0));

--
Build a little, test a little.


hoachen said:
Thank you so much for read my question. Here is the query result after i
joined the Cust# and the Item#

Recipt# Cust# Item# RQty OQty O# ODate OPrice
122455 11111 779127 1 1 9321510 1/2/2009 $2.00
12354 6015140 786571 1 1 9321510 1/2/2009 $9.99
158223 6015140 925373 1 1 9321510 1/2/2009 $7.05
158223 6015140 925373 1 -1 9321510 1/5/2009 -$7.05
5974473 7772523 789988 1 1 5512321 3/2/2009 $11.00
5974473 7772523 789988 1 -1 5512321 3/2/2009 -$11.00
5974473 7772523 874810 1 1 9321510 1/2/2009 $4.25

The total count on the recipt# should be 3
Should display
122455 11111 779127 1 1 9321510 1/2/2009 $2.00
12354 6015140 786571 1 1 9321510 1/2/2009 $9.99
5974473 7772523 874810 1 1 9321510 1/2/2009 $4.25

Does this make sense?

:

Post sample data and expected results.

--
Build a little, test a little.


:

I am using 2003 version of access.
I have two tables:
Table1 (Recipt#, Item#, RcdQty, Cust#)
Table2 (Order#, OrderQty, OrderDate, Cust#, price, Item#)

What i intent to do is to eliminate the item have been refund (with the "-"
in OrderQty) then count how many recipt been issued base on unique recipt#

here is my pseudocode:

If the OrderQty AND price on table2 =="-"
Then
check the item# on table2 agaist table1 if MATCHES Continue to check

Then
check the cust# on table2 agaist table1 if MATCHES

Then
I don' want to count these recipt#

End if

How can i put this in code and where?
Click to expand...
Click to expand...
Click to expand...
 
H

hoachen

Thanks again for your time and the explanation. I see what you are coming,
but i think i misleading and misinterprete.

I want to count the a unique receipt# and group it by month (Date base on
RDate) and not count those "-" (nagative mean return) from OrderQty where
cust# and item# matches (don't count this if mataches).

Table1 (R#, RItem#, RQty, Cust#, RDate)
Table2 (O#, OQty, ODate, Cust#, $, OItem#)

Recipt# Cust# Item# RQty OQty O ODate OPrice RDate
122455 11111 9127 1 1 1510 1/2/09 2.00 1/12/09
544856 11111 5566 1 1 1510 1/2/09 2.00 1/12/09
134221 11111 3511 1 1 2331 3/2/09 11.05 3/12/09
158223 60140 5373 1 1 1510 1/2/09 7.05
158223 60140 5373 1 -1 1510 1/5/09 -7.05 1/8/09
74473 72523 9988 1 1 2321 3/2/09 11.00
74473 72523 9988 1 -1 2321 3/2/09 -11.00 3/9/09
2365 72523 4810 1 1 1510 1/2/09 4.25 3/1/09

Output should be: (Like 60140 nothing it will not show but when it rcd
something then it will show on that month which receivd) Also, The Rdate can
not ahead of ODate.

Cust# Jan Feb March Total All
11111 2 1 3
60140
72523 1 1

I think this should be what i am looking for.
 
K

KARL DEWEY

Change HAVING (((Sum(qry_hoachen.OPrice))<>0));
to HAVING (((Sum(qry_hoachen.OPrice))=0));
then create a crosstab query to count.

--
Build a little, test a little.


KARL DEWEY said:
I think this does it --
SELECT qry_hoachen.[Recipt#], qry_hoachen.[Cust#], qry_hoachen.[Item#],
Sum(qry_hoachen.RQty) AS SumOfRQty, Sum(qry_hoachen.OQty) AS SumOfOQty,
qry_hoachen.[O#], Sum(qry_hoachen.OPrice) AS SumOfOPrice, (SELECT
IIF(Sum([XX].OPrice) <>0, [XX].[ODate], "") FROM qry_hoachen AS [XX] WHERE
[XX].[Cust#] = qry_hoachen.[Cust#] AND [XX].[Item#] = qry_hoachen.[Item#]
AND [XX].[Recipt#] = qry_hoachen.[Recipt#] GROUP BY [XX].[ODate]) AS O_Date
FROM qry_hoachen
GROUP BY qry_hoachen.[Recipt#], qry_hoachen.[Cust#], qry_hoachen.[Item#],
qry_hoachen.[O#]
HAVING (((Sum(qry_hoachen.OPrice))<>0));

--
Build a little, test a little.


hoachen said:
Thank you so much for read my question. Here is the query result after i
joined the Cust# and the Item#

Recipt# Cust# Item# RQty OQty O# ODate OPrice
122455 11111 779127 1 1 9321510 1/2/2009 $2.00
12354 6015140 786571 1 1 9321510 1/2/2009 $9.99
158223 6015140 925373 1 1 9321510 1/2/2009 $7.05
158223 6015140 925373 1 -1 9321510 1/5/2009 -$7.05
5974473 7772523 789988 1 1 5512321 3/2/2009 $11.00
5974473 7772523 789988 1 -1 5512321 3/2/2009 -$11.00
5974473 7772523 874810 1 1 9321510 1/2/2009 $4.25

The total count on the recipt# should be 3
Should display
122455 11111 779127 1 1 9321510 1/2/2009 $2.00
12354 6015140 786571 1 1 9321510 1/2/2009 $9.99
5974473 7772523 874810 1 1 9321510 1/2/2009 $4.25

Does this make sense?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

differents results from the same query execution 2
find exact match or match those only few cents off 3
Merging data in a one-to-many relationship 5
Conditional Query 1
Query multiple tables 0
"if... else" statement in MS Access 2
Count and assign 2
I want to Sync two tables (kinda like syncing a palm) 3

Top