sequence

[Guest]

TaI need to add a sequence number to a table.

Most likely this will break normalization rules, BUT that's okay for this
applet.

I am comparing data in two tables. Matching criteria is (say) 5 fields of
10. Tricky part is that there is NO unique identifier in the data.

For simplicity sake, I'll say I'm comparing 2 tables with one field each.

Here are possible records
T1 T2
-------------------------------- -----------------------------------
A A
A A
B A
B B
C B
C D
D D
D D

Basically what I need to do is have a one for one match, and then show (from
each table) what falls out.

I was thinking if I could add a sequence number, by 'key' I could then do
inner joins to find and remove matches..

So My tables would look like this

T1 T2
'key' Sequence 'key' Sequence
-------------------------------- -----------------------------------
A 1 A
1
A 2 A
2
B 1 A
3
B 2 B
1
C 1 B
2
C 2 D
1
D 1 D
2
D 2 D
3


The data is brought into the db by using a linked table and append queries..
So I can 'add' the sequence # to the actual table...

I've got it 'working' now by downloading to Excel and putting the data side
by side, but would prefer doing it in access..

Thanks
Tom

 

[Gary Walter]

Hi Tom,

I think you are still going to need a distinct pk
to get the "Rank" you need.

I might suggest adding an autonumber field (say "TID")
to the table(s) that you append the data into.

Then, if you have also added a "Sequence" field,
run an update query like:

UPDATE Atable
SET Sequence =
DCount("*","Atable","[T1]='" & Atable.T1 & "'[TID]<" & Atable.TID) +1;

good luck,

gary

 

[Gary Walter]

UPDATE Atable
SET Sequence =
DCount("*","Atable","[T1]='" & Atable.T1 & "' AND [TID]<" & Atable.TID) +1;