Help with a View

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 statusDate Qualifier Comments
1111xxxx01/01/01 100Comments
1111xxxx01/01/01 101Comments
1112xxxy01/01/01 101Comments
1113xyxy01/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:
filenumberstatusdate qualifiercomments
1111xxxx01/01/01 101Comments
1112xxxy01/01/01 101Comments
1113xyxy01/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!Hi

It is always better to post DDL ( CREATE TABLE statements etc..) and example
data (as INSERT statements) with the expected output from that data. This
will remove any ambiguities from your descriptions and helps everyone try
out their replies. There is no definition for tblFile which does not help!!

It is not clear if you want one or all records. But this may help

SELECT F.Filenumber, F.DataofFile, E.status, E.date, E.comments, E.qualifier
FROM tblFile F
join tblElectronic E on F.filenumber = E.filenumber
WHERE E.qualifier = 100
OR ( E.qualifier = 101
AND NOT EXISTS ( SELECT 1 FROM tblElectronic T
WHERE T.filenumber = E.filenumber
AND T.qualifier = 100 )
)

John

"Rubia 078" <rubia078@.yahoo.com> wrote in message
news:40ec7b0c$0$16462$c397aba@.news.newsgroups.ws.. .
> 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!|||John,

Thank you, I believe that will work. I won't be able to test it until
tomorrow, but it would appear to be what I needed.

Thank you!

Rubia

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!