Select record in queries not equal to the previous record

[Guest]

I am trying to build a query that will select records only if it is not equal to the previous record. For example: I have the following table that I only want to count the DownCdoe records if it is not equal to the prevoius record. i.e. I want to count 110 (ID 2) and 110 (ID 4), but not 110 (ID 5 & 6) because I know that it represents the same data... the same would be true for DownCode 109 records
ID DownCod
1 10
2 11
3 10
4 11
5 11
6 11
7 10
8 10
9 11
10 10
11 10

Any help would be appreciated

Thansk

 

[Tom Ellison]

Dear Nick:

In order to do this, there must be a very strict definition of what is
the "previous record". I see you have arranged your example in
ascending ID order. I will assume ID is a unique value. However, I
will not assume they will always be in consecutive order. There
usually CAN be deletions, you know, and that screws it up. So, the
previous record will usually, but not always be the one with ID - 1.
What the previous record will ALWAYS be (if there is a previous
record, as there is no previous record to the first record) is the
maximum value if ID less than the current record. That is guaranteed
to work.

So no, the query work, a little at a time. First, let's try to show
the DownCode of the previous record:

SELECT ID, DownCode,
(SELECT DownCode FROM YourTable
WHERE ID = (SELECT MAX(ID) FROM YourTable T1
WHERE T1.ID < T.ID) PrevDownCode
FROM YourTable T
ORDER BY ID

If you're running MSDE for your back end, this should be good. Jet
can't handle it in most cases, however. So you must use extra steps:

SELECT ID, DownCode,
(SELECT MAX(ID) FROM YourTableT1
WHERE T1.ID < T.ID) AS PrevID
FROM YourTable T
ORDER BY ID

Save the above query, I'll call it Q1 for reference, but you should
probably use a more descriptive name.

Now:

SELECT Q.ID, Q.DownCode, T.DownCode
FROM Q1 Q
INNER JOIN YourTable T ON T.ID = Q.PrevID
ORDER BY ID

Now you're back to the point the first query would have done, if only
it could work in Jet.

Finally, you want to filter the results, not show the previous value.
But it is a good thing to see that it's actually finding the correct
previous values as you build it. That's good step-wise testing to see
the previous ID first, then the previous DownCode, then finally use
it. So, to wrap up:

SELECT Q.ID, Q.DownCode
FROM Q1 Q
INNER JOIN YourTable T ON T.ID = Q.PrevID
WHERE Q.DownCode <> T.DownCode
ORDER BY ID

Hope I didn't screw anything up. That's a fair bit to do without
testing, ya know! Please let me know!

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

 

[Guest]

Tom,

I tried your recomendation and I keep getting the following error message:

You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field. (Error 3306)

Here are the three queries that I wrote:
DownCodQ
(SELECT ID, DownCode, (SELECT MAX(ID) FROM DownCode WHERE DownCode.ID < DownCode.ID) AS PrevID FROM Downcode ORDER BY ID)

DownCodQ1
(SELECT DownCodQ.ID, DownCodQ.DownCode, DownCode.DownCode FROM DownCodQ INNER JOIN DownCode ON DownCode.ID = DownCodQ.PrevID ORDER BY ID)

DownCodQ2
(SELECT DownCodQ1.ID, DownCodQ1.DownCode FROM DownCodQ DownCodQ1 INNER JOIN DownCode ON DownCode.ID = DownCodQ1.PrevID WHERE DownCodQ1.DownCode <> DownCode.DownCode ORDER BY ID)

What's next?

Thanks for the help!

 

[Tom Ellison]

Dear Nick:

The first query I wrote had ALIASes which you have omitted. That will
keep it from working properly. Don't leave anything out!

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

 

[Guest]

Tom

I'm just not getting it! I Added the aliases and I still get the same error message

My email address is (e-mail address removed) if you would like to send me an example access file. Or, I can send you what I have for your review

Let me know and I appreciate your help… this is killing me

Nic

 

[Tom Ellison]

Dear Nick:

It may take up to 2 days, but if you email me with the database
attached, I'll try to make it work. Zip the database, and it must fit
under 1 MB. If necessary, import only the necessary data into another
database to keep the size down. OK?

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

 

[Guest]

The database is on its' way. Thanks!

 

[Rufus T. Firefly]

Greetings,

Here's any easy way to visualize the problem
(and solve it.

This is being solved with the RAC utility for S2k.
No complex sql coding necessary.Just real simple
stuff

You can simply rank runs of DownCode.

create table #Nick([ID] int primary key,DownCode int)
go
insert #Nick values(1,109)
insert #Nick values(2,110)
insert #Nick values(3,109)
insert #Nick values(4,110)
insert #Nick values(5,110)
insert #Nick values(6,110)
insert #Nick values(7,109)
insert #Nick values(8,109)
insert #Nick values(9,110)
insert #Nick values(10,109)
insert #Nick values(11,109)

We create 2 ranks/counters.The DCrank counter increments
every time there is a new DownCode based on the
ordering of ID.If the next DownCode is the same
as the previous one the counter stays the same.
This counter thus identifies all the different runs
of DownCode.Within a run of DownCode another counter (DCnter)
ranks the values from 1 to N.


Exec Rac
@transform='_dummy_',
@rows='DownCode & [ID]',
@rowsort='[ID]', -- Sort DownCode by [ID] to keep
-- their sequence (sort order).
@pvtcol='Report Mode',
@from='#Nick',@grand_totals='n',@rowbreak='n',
-- Define runs of DownCode values based on ID sort order.
@rowindicators='DownCode{DCrank}',
-- Number from 1 to N the same value DownCodes in each run.
@rowcounters='DownCode{DCnter}',
@counterdatatype='int',
@defaultexceptions='dumy'

DownCode ID DCrank DCnter
-------- ---- ----------- -----------
109 1 1 1
110 2 2 1
109 3 3 1
110 4 4 1
110 5 4 2
110 6 4 3
109 7 5 1
109 8 5 2
110 9 6 1
109 10 7 1
109 11 7 2

Check out DCrank and DCnter.

I am trying to build a query that will select records
only if it is not equal to the previous record

This translates to picking the records where Dcnter=1.
(DCrank) is not necessary for the solution).

Here is the solution.

Exec Rac
@transform='_dummy_',
@rows='DownCode & [ID]',@rowsort='[ID]',
@pvtcol='Report Mode',
@from='#Nick',@grand_totals='n',@rowbreak='n',
@rowcounters='DownCode{DCnter}',
@counterdatatype='int',
@select='select [ID],DownCode
from rac
where DCnter=1
order by 1*DownCode,1*[ID]'

ID DownCode
---- --------
1 109
3 109
7 109
10 109
2 110
4 110
9 110

Maybe someone wants to translate this to Access code.

For S2k check out RAC v2.2 and QALite @
www.rac4sql.net

 

[Guest]

Tom

The query is providing the desired results. The only problem is that it is very slow. When I run it on my actual data (about 1,700 records) and try to count the indices of 109 and 110, it takes about an hour to run. If I run the query without the count function, it takes less than a minute. I have also tried to run it as a “make a table queryâ€, but that takes over an hour as well. Any ideas on why it is so slow

Nic