Return records based on calculated field

J

John G

Hi,

I have a query that returns the following records:

BinID txtRecNumber RSum
1 5 2100
1 52 0
2 148 1200
2 201 769
2 203 0
2 290 1500
45 89 78
45 92 0
45 185 1350
45 243 150
45 251 0
45 257 33
45 287 673

Results should be:
BinID txtRecNumber RSum
2 290 1500
45 257 33
45 287 673

"txtRecNumber" is the Record Number in Ascending Order and "RSum" (running
Sum) is a calculated field. I need to return all the most recent records
which RSum is above the most recent "0".
I have tried the following SQL to return just the records above
the most recent "0" but it returns the Max(txtRecNumber) which of course is
290. I want to return records 290, 257 and 287.

SELECT BinID, TxtRecNumber, RSum
FROM YourQuery
WHERE txtRecNumber >
(SELECT Max(txtRecNumber)
FROM YourQuery
WHERE RSum = 0)

Thanks in advance.
John G
 
M

Marshall Barton

John said:
I have a query that returns the following records:

BinID txtRecNumber RSum
1 5 2100
1 52 0
2 148 1200
2 201 769
2 203 0
2 290 1500
45 89 78
45 92 0
45 185 1350
45 243 150
45 251 0
45 257 33
45 287 673

Results should be:
BinID txtRecNumber RSum
2 290 1500
45 257 33
45 287 673

"txtRecNumber" is the Record Number in Ascending Order and "RSum" (running
Sum) is a calculated field. I need to return all the most recent records
which RSum is above the most recent "0".
I have tried the following SQL to return just the records above
the most recent "0" but it returns the Max(txtRecNumber) which of course is
290. I want to return records 290, 257 and 287.

SELECT BinID, TxtRecNumber, RSum
FROM YourQuery
WHERE txtRecNumber >
(SELECT Max(txtRecNumber)
FROM YourQuery
WHERE RSum = 0)
Click to expand...


SELECT T.BinID, T.TxtRecNumber, T.RSum
FROM YourQuery As T
WHERE T.txtRecNumber >
(SELECT Max(X.txtRecNumber)
FROM YourQuery As X
WHERE X.RSum = 0
AND X.BinID = T.BinID)
 
J

John G

Thanks for the information Marshall, but now I realize I have another
problem. If there is no "0" in the RSum, the BinID and its data doesn't show
in the results. In other words if product is placed in a bin (BinID) and the
bin is never emptied there is no "0" balance. How do edit your SQL to
consider bins that do not have a "0" balance in the RSum (running sum)?
 
M

Marshall Barton

I suspect there is more than one way to do that, but see if
this is close to what you want:

SELECT T.BinID, T.TxtRecNumber, T.RSum
FROM YourQuery As T
WHERE T.txtRecNumber >
(SELECT Max(X.txtRecNumber)
FROM YourQuery As X
WHERE X.RSum = 0
AND X.BinID = T.BinID)
UNION ALL
SELECT T.BinID, T.TxtRecNumber, T.RSum
FROM YourQuery As T
WHERE 0 < ALL (SELECT T.RSum
FROM YourQuery As Y
WHERE Y.BinID = T.BinID)
ORDER BY BinID, TxtRecNumber
 
J

John G

Thanks Marshall but this doesn't return the proper number of records either.
It returns 598 records when it should return 20. Lots of duplicates and the
RSum values are much greater than they should be. What do you suppose is
wrong?
John G
 
M

Marshall Barton

John said:
Thanks Marshall but this doesn't return the proper number of records either.
It returns 598 records when it should return 20. Lots of duplicates and the
RSum values are much greater than they should be. What do you suppose is
wrong?
Click to expand...

Try it without the typo I made in the second sub select
where I had a T instead of a Y

SELECT T.BinID, T.TxtRecNumber, T.RSum
FROM YourQuery As T
WHERE T.txtRecNumber >
(SELECT Max(X.txtRecNumber)
FROM YourQuery As X
WHERE X.RSum = 0
AND X.BinID = T.BinID)
UNION ALL
SELECT T.BinID, T.TxtRecNumber, T.RSum
FROM YourQuery As T
WHERE 0 < ALL (SELECT Y.RSum
FROM YourQuery As Y
WHERE Y.BinID = T.BinID)
ORDER BY BinID, TxtRecNumber
 
J

John G

Marshall,
This edited SQL is also returning 598 records. There is only 561 records in
the table that is being queried. This is returning duplicates for some
BinID's and there are some negative RSum values. I checked my query that has
the RSum values and it appears correct. I also noticed that this SQL as well
as the first one you gave me seems to take a while to run. What am I doing
wrong?
 
M

Marshall Barton

John said:
This edited SQL is also returning 598 records. There is only 561 records in
the table that is being queried. This is returning duplicates for some
BinID's and there are some negative RSum values. I checked my query that has
the RSum values and it appears correct. I also noticed that this SQL as well
as the first one you gave me seems to take a while to run. What am I doing
wrong?
Click to expand...


I must have a blind spot about something here. Could you
reduce the data to a small sample that demonstrates the
problem? If you can post the sample data and the results my
last query produces? Also post a Copy/Paste of the query as
you now have it.

I will try to recreate the table and experiment with the
query.
 
J

John G

Marshall,
Here is some table data you requested. Hopefully the data lines up in the
correct columns.
Sample Table Data
BinID Date AmountIn AmountOut txtRecNumber
16 08/15/07 1500 0 1
16 10/02/07 2900 0 2
16 06/03/08 0 300 3
16 06/03/08 0 2900 4
16 06/19/08 0 1200 5
16 09/23/08 4250 0 6
16 09/24/08 1200 0 7
16 09/24/08 2050 0 8
16 03/17/09 0 1200 9
16 04/05/09 0 1200 10
16 04/05/09 0 1200 11
18 08/07/07 300 0 1
18 08/08/07 1950 0 2
18 08/09/07 5400 0 3
18 01/31/08 0 1300 4
18 02/04/08 0 1900 5
18 02/20/08 0 3450 6
18 03/03/08 0 1000 7
18 08/12/08 1050 0 8
18 08/13/08 2850 0 9
18 08/18/08 2600 0 10
21 08/03/05 1800 0 1
21 09/30/06 0 1800 2
21 08/09/07 2400 0 3
21 04/10/08 0 1750 4
21 04/18/08 0 650 5
21 04/18/08 3500 0 6

Here is the results of the first query (qInv) that I run to get the RSum
(running sum) before running your latest SQL.

Results of qInv (first query I run)
BinID txtRecNumber RSum AmountIn AmountOut
16 1 1500 1500 0
16 2 4400 2900 0
16 3 4100 0 300
16 4 1200 0 2900
16 5 0 0 1200
16 6 4250 4250 0
16 7 5450 1200 0
16 8 7500 2050 0
16 9 6300 0 1200
16 10 5100 0 1200
16 11 3900 0 1200
18 1 300 300 0
18 2 2250 1950 0
18 3 7650 5400 0
18 4 6350 0 1300
18 5 4450 0 1900
18 6 1000 0 3450
18 7 0 0 1000
18 8 1050 1050 0
18 9 3900 2850 0
18 10 6500 2600 0
21 1 1800 1800 0
21 2 0 0 1800
21 3 2400 2400 0
21 4 650 0 1750

And here are the results of your latest SQL

BinID txtRecNumber RSum
16 1 1500
16 2 2900
16 3 -300
16 4 -2900
16 6 4250
16 6 4250
16 7 1200
16 8 2050
16 8 2050
16 9 -1200
16 10 -1200
16 11 -1200
18 1 300
18 2 1950
18 3 5400
18 4 -1300
18 5 -1900
18 6 -3450
18 7 -1000
18 8 1050
18 8 1050
18 9 2850
18 9 2850
18 10 2600
18 10 2600
21 1 8800
21 2 5200
21 3 9400
21 4 5250
21 5 6350
21 6 7000
21 6 10500

This is the SQL (your latest) that I'm using to return the above results.

SELECT T.BinID, T.txtRecNumber, T.RSum
FROM qInv As T
WHERE T.txtRecNumber >
(SELECT Max(X.txtRecNumber)
FROM qInv As X
WHERE X.RSum = 0
AND X.BinID = T.BinID)
UNION ALL SELECT T.BinID, T.txtRecNumber, T.RSum
FROM qInv As T
WHERE 0 < ALL (SELECT Y.RSum
FROM qInv As Y
WHERE Y.BinID = T.BinID)
ORDER BY BinID, T.txtRecNumber;

Results should be a total of 11 records as follows:
BinID txtRecNumber RSum
16 6 4250 16 7 5450
16 8 7500 16 9 6300
16 10 5100 16 11 3900
18 8 1050 18 9 3900
18 10 6500
21 3 2400 21 4 650

If you have some time, see what you can do with this.
Thanks Marshall! I really appreciate what you have done.

John G
 
M

Marshall Barton

John said:
Here is some table data you requested. Hopefully the data lines up in the
correct columns.
Sample Table Data
BinID Date AmountIn AmountOut txtRecNumber
16 08/15/07 1500 0 1
16 10/02/07 2900 0 2
16 06/03/08 0 300 3
16 06/03/08 0 2900 4
16 06/19/08 0 1200 5
16 09/23/08 4250 0 6
16 09/24/08 1200 0 7
16 09/24/08 2050 0 8
16 03/17/09 0 1200 9
16 04/05/09 0 1200 10
16 04/05/09 0 1200 11
18 08/07/07 300 0 1
18 08/08/07 1950 0 2
18 08/09/07 5400 0 3
18 01/31/08 0 1300 4
18 02/04/08 0 1900 5
18 02/20/08 0 3450 6
18 03/03/08 0 1000 7
18 08/12/08 1050 0 8
18 08/13/08 2850 0 9
18 08/18/08 2600 0 10
21 08/03/05 1800 0 1
21 09/30/06 0 1800 2
21 08/09/07 2400 0 3
21 04/10/08 0 1750 4
21 04/18/08 0 650 5
21 04/18/08 3500 0 6

Here is the results of the first query (qInv) that I run to get the RSum
(running sum) before running your latest SQL.

Results of qInv (first query I run)
BinID txtRecNumber RSum AmountIn AmountOut
16 1 1500 1500 0
16 2 4400 2900 0
16 3 4100 0 300
16 4 1200 0 2900
16 5 0 0 1200
16 6 4250 4250 0
16 7 5450 1200 0
16 8 7500 2050 0
16 9 6300 0 1200
16 10 5100 0 1200
16 11 3900 0 1200
18 1 300 300 0
18 2 2250 1950 0
18 3 7650 5400 0
18 4 6350 0 1300
18 5 4450 0 1900
18 6 1000 0 3450
18 7 0 0 1000
18 8 1050 1050 0
18 9 3900 2850 0
18 10 6500 2600 0
21 1 1800 1800 0
21 2 0 0 1800
21 3 2400 2400 0
21 4 650 0 1750

And here are the results of your latest SQL

BinID txtRecNumber RSum
16 1 1500
16 2 2900
16 3 -300
16 4 -2900
16 6 4250
16 6 4250
16 7 1200
16 8 2050
16 8 2050
16 9 -1200
16 10 -1200
16 11 -1200
18 1 300
18 2 1950
18 3 5400
18 4 -1300
18 5 -1900
18 6 -3450
18 7 -1000
18 8 1050
18 8 1050
18 9 2850
18 9 2850
18 10 2600
18 10 2600
21 1 8800
21 2 5200
21 3 9400
21 4 5250
21 5 6350
21 6 7000
21 6 10500

This is the SQL (your latest) that I'm using to return the above results.

SELECT T.BinID, T.txtRecNumber, T.RSum
FROM qInv As T
WHERE T.txtRecNumber >
(SELECT Max(X.txtRecNumber)
FROM qInv As X
WHERE X.RSum = 0
AND X.BinID = T.BinID)
UNION ALL SELECT T.BinID, T.txtRecNumber, T.RSum
FROM qInv As T
WHERE 0 < ALL (SELECT Y.RSum
FROM qInv As Y
WHERE Y.BinID = T.BinID)
ORDER BY BinID, T.txtRecNumber;

Results should be a total of 11 records as follows:
BinID txtRecNumber RSum
16 6 4250 16 7 5450
16 8 7500 16 9 6300
16 10 5100 16 11 3900
18 8 1050 18 9 3900
18 10 6500
21 3 2400 21 4 650
Click to expand...


You only have 6 records there, but I see how there should be
11.

The above example records do not have a bin without a 0 RSum
so my test used your qInv data plus a made up bin with no
Rsum=0 records.

I then tested a Copy/Paste of your query with this result:

BinID txtRecNumber RSum
1 1 1
1 2 5
1 3 2
16 6 4250
16 7 5450
16 8 7500
16 9 6300
16 10 5100
16 11 3900
18 8 1050
18 9 3900
18 10 6500
21 3 2400
21 4 650

I don't know what to say about the results you are seeing.
The only difference between what you are doing and what I
did is that I started with qInv as a table instead of your
running sum query.

I would not expect it, but it is conceivable that basing the
final query on a complex query is confusing things. All I
can suggest is that you use your running sum query as a make
table query and use the test table as the final query's
source table to see if that makes a difference.
 
J

John G

Thanks so much for your time Marshall.
I ended up doing as you suggested. I am now using the qInv (running sum
query) as a make table query then I'm using that table as the final query's
source table. It is now returning the desired results. I wonder why it
returns incorrect data when using qInv as the source. Because of course, the
table made by qInv contains the exact same data that qInv returns. Like you
stated, maybe Access just gets mixed up as to what it is suppose to be
doing?
The final query runs a lot faster now!
Thanks again Marshal for taking the time to help me solve this issue.
John G
 
M

Marshall Barton

John said:
Thanks so much for your time Marshall.
I ended up doing as you suggested. I am now using the qInv (running sum
query) as a make table query then I'm using that table as the final query's
source table. It is now returning the desired results. I wonder why it
returns incorrect data when using qInv as the source. Because of course, the
table made by qInv contains the exact same data that qInv returns. Like you
stated, maybe Access just gets mixed up as to what it is suppose to be
doing?
The final query runs a lot faster now!
Thanks again Marshal for taking the time to help me solve this issue.
Click to expand...


I was afraid that would be the case. Unfortunately that was
more of a diagnostic test than a solution. There is still
some more work to do before you can wrap this up.

All I can say about the problem is that it is probably some
kind of bug in the query optimizer where it recognizes one
query is based on another query and tries to combine them
into a single, more efficient, query. All the subqueries
are most likely what blew its mind, but the optimizer
apparently isn't smart enough to know when it is in over its
head and back out of that technique.

A serious caution about using make table queries. Because
of the obvious issue of bloat and the not so obvious issue
of creating and removing major objects, you need to be very
concerned about using temporary tables. The big questions
are where you put them and how you deal with the space they
consume. I try really, really hard to avoid using a temp
table, but if there is no other way to deal with a
situation, then I make sure to put the temp table in a temp
database so it can't mess with my production databases.

Generally it is less hassle to precreate the temp table (if
needed, use a Delete query to empty it) and then use an
Append query instead of a Make Table query.

See http://www.granite.ab.ca/access/temptables.htm
for a good disussion on this topic. (I am one of the ones
that uses a master temp db and work on a copy of it.)
 

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

How to return records based on a calculated field 2
Macro Error 6
left join not returning enough records 3
Return Query values based on calculated field 1
multiple ranking (sorting), based on diff. criteria 0
how to get previous calculated field 1
Entourage 2004 forces windowserver restart when inputting text 2
show records based on calculated field 2

Top