result sort

V

VilMarci

Hi,

This is the result of my query:

Key, OrderNumber, Possible sn, Real sn
39, 1,123, 123
39, 1, 456, 456
40, 1, 123, 123
40, 1, 456, 456

How can I filter this to retreive this recordset?
39, 1, 123, 123
40, 1, 456, 456

or
39, 1, 456, 456
40, 1, 123, 123

I don't user the key really, the main is the order number in the second
coloumn.

The serials don't always match, this is why I need this whole stuff.

Can somebody help?
Many thanks,
Marton
 
M

Michel Walsh

Hi,


*if* you wish to *not* get the previous values, at all, then


SELECT a.*
FROM myTable As a
WHERE a.PossibleSn NOT IN( SELECT b.PossibleSn
FROM myTable As b
WHERE b.key< a.key)


or


SELECT a.*
FROM myTable As a
WHERE NOT EXISTS( SELECT *
FROM myTable As b
WHERE b.key < a.key
AND ( a.PossibleSN=b.PossibleSN
OR a.RealSN=b.RealSN
) )




Hoping it may help,
Vanderghast, Access MVP
 
V

VilMarci

Hi,

Thanks,
Almost working.
Maybe I didn't adapt correctly, but I still have dups.

The two source table look like:

Table1
key,orderNumber,PossibleSN
1,11,123
2,11,
3,12,345

Table2
OrderNumber,SentSerial
11,123
11,321
12,345

So what I need is a result like:
key,orderNumber,PossibleSN,SentSerial
1,11,123,123
2,11,,321
3,12,345

Is there any hope?
Thanks,
Marton
 
M

Michel Walsh

Hi,

The initial problem was involving just one table. You now have two, so some
kind of join will be required, and not having a well defined relation
between the two tables, it may be preferable to use recordsets.


Hoping it may help,
Vanderghast, Access MVP
 
M

Michel Walsh

Hi,


You open the two tables as recordsets, in VBA code, and through MoveNext
and FindFirst/FindNext, make the required matching between the two tables.

Vanderghast, Access MVP
 
V

VilMarci

Thank you,

Now I turned to the macro group.
I'm not afraid of VBA, but this is a bit over my skills :(

Marton
 
Top