result sort

  • Thread starter Thread starter VilMarci
  • Start date Start date
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
 
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
 
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
 
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
 
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
 
Thank you,

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

Marton
 
Back
Top