show only records where NEXT record is different then the prior

A

ave

I have a query for historical inv with fields
Part - Date - Qty
Sample data
x - 1/1/04 - 5
x - 1/2/04 - 5
x - 1/3/04 - 5
x - 1/4/04 - 10

I would like result set to show only the records where the NEXT record has a
different QTY then the prior record

So for the above it should read:
x - 1/1/04 - 5
x - 1/4/04 - 10

Any ideas on how to accomplish this?

thx in advance
 
T

Tom Ellison

Dear Ave:

You need to use a correlated subquery to retrieve the Qty from that
previous row, if there is one. Another subquery, nested inside that,
must retrieve the date for that previous row. Finally, another
subquery allows for the cases where there is no previous row.

Not terribly simple.

SELECT *
FROM YourTable T
WHERE T.Qty <> (SELECT Qty from YourTable T1
WHERE T1.Part = T.Part AND
T1.[Date] = (SELECT MAX([Date]) FROM YourTable T2
WHERE T2.Part = T1.Part AND T2.[Date] < T.[Date]))
OR NOT EXISTS (SELECT * FROM YourTable T2
WHERE T2.Part = T.Part AND T2.[Date] < T.[Date])

I've tried this against your sample data and it seems to work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
A

ave

Thanks!!!!!
hmmmmmmm quite complicated.

so this cant be written in one step

i need to make the subquery first and then reference the sub query i wrote?

Tom Ellison said:
Dear Ave:

You need to use a correlated subquery to retrieve the Qty from that
previous row, if there is one. Another subquery, nested inside that,
must retrieve the date for that previous row. Finally, another
subquery allows for the cases where there is no previous row.

Not terribly simple.

SELECT *
FROM YourTable T
WHERE T.Qty <> (SELECT Qty from YourTable T1
WHERE T1.Part = T.Part AND
T1.[Date] = (SELECT MAX([Date]) FROM YourTable T2
WHERE T2.Part = T1.Part AND T2.[Date] < T.[Date]))
OR NOT EXISTS (SELECT * FROM YourTable T2
WHERE T2.Part = T.Part AND T2.[Date] < T.[Date])

I've tried this against your sample data and it seems to work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a query for historical inv with fields
Part - Date - Qty
Sample data
x - 1/1/04 - 5
x - 1/2/04 - 5
x - 1/3/04 - 5
x - 1/4/04 - 10

I would like result set to show only the records where the NEXT record has a
different QTY then the prior record

So for the above it should read:
x - 1/1/04 - 5
x - 1/4/04 - 10

Any ideas on how to accomplish this?

thx in advance
 
T

Tom Ellison

Dear Ave:

The query I posted does the whole thing at once.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Thanks!!!!!
hmmmmmmm quite complicated.

so this cant be written in one step

i need to make the subquery first and then reference the sub query i wrote?

Tom Ellison said:
Dear Ave:

You need to use a correlated subquery to retrieve the Qty from that
previous row, if there is one. Another subquery, nested inside that,
must retrieve the date for that previous row. Finally, another
subquery allows for the cases where there is no previous row.

Not terribly simple.

SELECT *
FROM YourTable T
WHERE T.Qty <> (SELECT Qty from YourTable T1
WHERE T1.Part = T.Part AND
T1.[Date] = (SELECT MAX([Date]) FROM YourTable T2
WHERE T2.Part = T1.Part AND T2.[Date] < T.[Date]))
OR NOT EXISTS (SELECT * FROM YourTable T2
WHERE T2.Part = T.Part AND T2.[Date] < T.[Date])

I've tried this against your sample data and it seems to work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a query for historical inv with fields
Part - Date - Qty
Sample data
x - 1/1/04 - 5
x - 1/2/04 - 5
x - 1/3/04 - 5
x - 1/4/04 - 10

I would like result set to show only the records where the NEXT record has a
different QTY then the prior record

So for the above it should read:
x - 1/1/04 - 5
x - 1/4/04 - 10

Any ideas on how to accomplish this?

thx in advance
 
A

ave

the problem i am having with this is T1 T2 tables
When I substitute the name of the table for T and try to run query i get
error:

jet db cant find input table or query InvHistory_1....make sure it exists
and that its name is spelled correctly.

that makes me think i need to create a query InvHistory_1 that this query
will refer too.

i also tried adding the table InvHistory (T) 2 x in design manager but got
same error.


Tom Ellison said:
Dear Ave:

The query I posted does the whole thing at once.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Thanks!!!!!
hmmmmmmm quite complicated.

so this cant be written in one step

i need to make the subquery first and then reference the sub query i wrote?

Tom Ellison said:
Dear Ave:

You need to use a correlated subquery to retrieve the Qty from that
previous row, if there is one. Another subquery, nested inside that,
must retrieve the date for that previous row. Finally, another
subquery allows for the cases where there is no previous row.

Not terribly simple.

SELECT *
FROM YourTable T
WHERE T.Qty <> (SELECT Qty from YourTable T1
WHERE T1.Part = T.Part AND
T1.[Date] = (SELECT MAX([Date]) FROM YourTable T2
WHERE T2.Part = T1.Part AND T2.[Date] < T.[Date]))
OR NOT EXISTS (SELECT * FROM YourTable T2
WHERE T2.Part = T.Part AND T2.[Date] < T.[Date])

I've tried this against your sample data and it seems to work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 28 Oct 2004 20:17:03 GMT, "ave"

I have a query for historical inv with fields
Part - Date - Qty
Sample data
x - 1/1/04 - 5
x - 1/2/04 - 5
x - 1/3/04 - 5
x - 1/4/04 - 10

I would like result set to show only the records where the NEXT record has a
different QTY then the prior record

So for the above it should read:
x - 1/1/04 - 5
x - 1/4/04 - 10

Any ideas on how to accomplish this?

thx in advance
 
T

Tom Ellison

Dear Ave:

Leave the T1 and T2 as they are and substitute the name of your table
where it says "YourTable". Do not append _1 or anything like that.

Sorry if this wasn't clear. If you still have trouble, please post
back the whole SQL with which you're having trouble so I can correct
it. If I had known the actual name of your table, I would have used
it and we wouldn't be having this trouble. Sorry if I missed it
somehow. Otherwise, it's best to give such details if it avoids such
problems. Just meant to be a friendly tip, you know?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


the problem i am having with this is T1 T2 tables
When I substitute the name of the table for T and try to run query i get
error:

jet db cant find input table or query InvHistory_1....make sure it exists
and that its name is spelled correctly.

that makes me think i need to create a query InvHistory_1 that this query
will refer too.

i also tried adding the table InvHistory (T) 2 x in design manager but got
same error.


Tom Ellison said:
Dear Ave:

The query I posted does the whole thing at once.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Thanks!!!!!
hmmmmmmm quite complicated.

so this cant be written in one step

i need to make the subquery first and then reference the sub query i wrote?

Dear Ave:

You need to use a correlated subquery to retrieve the Qty from that
previous row, if there is one. Another subquery, nested inside that,
must retrieve the date for that previous row. Finally, another
subquery allows for the cases where there is no previous row.

Not terribly simple.

SELECT *
FROM YourTable T
WHERE T.Qty <> (SELECT Qty from YourTable T1
WHERE T1.Part = T.Part AND
T1.[Date] = (SELECT MAX([Date]) FROM YourTable T2
WHERE T2.Part = T1.Part AND T2.[Date] < T.[Date]))
OR NOT EXISTS (SELECT * FROM YourTable T2
WHERE T2.Part = T.Part AND T2.[Date] < T.[Date])

I've tried this against your sample data and it seems to work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 28 Oct 2004 20:17:03 GMT, "ave"

I have a query for historical inv with fields
Part - Date - Qty
Sample data
x - 1/1/04 - 5
x - 1/2/04 - 5
x - 1/3/04 - 5
x - 1/4/04 - 10

I would like result set to show only the records where the NEXT record
has a
different QTY then the prior record

So for the above it should read:
x - 1/1/04 - 5
x - 1/4/04 - 10

Any ideas on how to accomplish this?

thx in advance
 
A

ave

Excellent this worked. Your a genious! : )

Tom Ellison said:
Dear Ave:

Leave the T1 and T2 as they are and substitute the name of your table
where it says "YourTable". Do not append _1 or anything like that.

Sorry if this wasn't clear. If you still have trouble, please post
back the whole SQL with which you're having trouble so I can correct
it. If I had known the actual name of your table, I would have used
it and we wouldn't be having this trouble. Sorry if I missed it
somehow. Otherwise, it's best to give such details if it avoids such
problems. Just meant to be a friendly tip, you know?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


the problem i am having with this is T1 T2 tables
When I substitute the name of the table for T and try to run query i get
error:

jet db cant find input table or query InvHistory_1....make sure it exists
and that its name is spelled correctly.

that makes me think i need to create a query InvHistory_1 that this query
will refer too.

i also tried adding the table InvHistory (T) 2 x in design manager but got
same error.


Tom Ellison said:
Dear Ave:

The query I posted does the whole thing at once.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 28 Oct 2004 21:31:52 GMT, "ave"

Thanks!!!!!
hmmmmmmm quite complicated.

so this cant be written in one step

i need to make the subquery first and then reference the sub query i wrote?

Dear Ave:

You need to use a correlated subquery to retrieve the Qty from that
previous row, if there is one. Another subquery, nested inside that,
must retrieve the date for that previous row. Finally, another
subquery allows for the cases where there is no previous row.

Not terribly simple.

SELECT *
FROM YourTable T
WHERE T.Qty <> (SELECT Qty from YourTable T1
WHERE T1.Part = T.Part AND
T1.[Date] = (SELECT MAX([Date]) FROM YourTable T2
WHERE T2.Part = T1.Part AND T2.[Date] < T.[Date]))
OR NOT EXISTS (SELECT * FROM YourTable T2
WHERE T2.Part = T.Part AND T2.[Date] < T.[Date])

I've tried this against your sample data and it seems to work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 28 Oct 2004 20:17:03 GMT, "ave"

I have a query for historical inv with fields
Part - Date - Qty
Sample data
x - 1/1/04 - 5
x - 1/2/04 - 5
x - 1/3/04 - 5
x - 1/4/04 - 10

I would like result set to show only the records where the NEXT record
has a
different QTY then the prior record

So for the above it should read:
x - 1/1/04 - 5
x - 1/4/04 - 10

Any ideas on how to accomplish this?

thx in advance
 

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

Top