I have a table tblElectronic that recieves data electronically. It
contains fields: filenumber, status, date, qualifier and comments. The
qualifier field is either 100 or 101, meaning the file is confirmed or
estimated, respectively. I then join tblElectronic to another table
(tblFile) in a view. I am having trouble building the view. The field
Qualifier in tblElectronic can have the value 100 or 101.
filenumber status Date Qualifier Comments
1111 xxxx 01/01/01 100 Comments
1111 xxxx 01/01/01 101 Comments
1112 xxxy 01/01/01 101 Comments
1113 xyxy 01/01/01 100 Comments
I want to use the record where qualifier = 100 in my view, except in the
case where 101 is the only qualifier that exists, meaning it has not
been confirmed yet.
So my view should pull the rows:
filenumber status date qualifier comments
1111 xxxx 01/01/01 101 Comments
1112 xxxy 01/01/01 101 Comments
1113 xyxy 01/01/01 100 Comments
I have tried case statements but to no avail. Here is my view:
SELECT tblFile.Filenumber, tblFile.DataofFile, tblElectronic.status,
tblElectronic.date, tblElectronic.comments
FROM tblFile inner join
tblElectronic on tblFile.filenumber = tblElectronic.filenumber
where tblElectronic.qualifier = ??????
Not sure what to put here, since I want it to be where qualifier = 100
unless that doesn't exist, in which case I want it to be where qualifier
= 101.
Thanks for the help.
Rubia
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!