asp or sql solution? 1

[travmak]

in my table I have two fields: partnumber and employeenum

I am trying to make a report that displays the partnumber where employeenum = '' no problem I got that part. the problem I have is this:

partnumber employeenum
1111 1
1112
1112 2
1113
1114 3

my page at this time gives me a recordset of:

partnumber employeenum
1112
1113

however 1112 has a later entery with an employeenum so I don't want that one, just 1113 because it doesn't have an employeenum.

my sql is currently:

sql_unaccounted = "select * from tbl_Inventory where employeenum = '' order by partnumber"

please help

 

[FesterSXS]

If you only want one instance of each partnumber where it doesn't have an employeenumber associated with it then you could use the DISTINCT keyword.

sql_unaccounted = "select DISTINCT partnumber from tbl_Inventory where employeenum = '' order by partnumber"

I 'think' this will work

regards

Tony

http://www.phoenix-paints.co.uk/portfolio

 

[travmak]

It didn't work. I'm only getting one instance now (because their are not any douplicate numbers where employeenum = ''.)

 

[sandallj]

Try something like:

sql_unaccounted = "select * from tbl_Inventory where employeenum = '' and partnumber not in (select partnumber from tbl_Inventory where employeenum != '') order by partnumber"

 

[travmak]

I think that was it, thank you