473,322 Members | 1,352 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 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!
Jul 20 '05 #1
2 1349
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" <ru******@yahoo.com> wrote in message
news:40**********************@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!

Jul 20 '05 #2
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!
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: dave | last post by:
I first started using HCW.exe to compile .rtf filew created with MS Word a couple of weeks ago. I used the file | new menu then selected New project in the dialog box and everything worked as...
9
by: sk | last post by:
I have an applicaton in which I collect data for different parameters for a set of devices. The data are entered into a single table, each set of name, value pairs time-stamped and associated with...
0
by: Tree menu using XML | last post by:
I have one XML file that has nodes and sub node and each and every node has the attribute call visible if its value is true then diplay this node else don't display thid node, but this condition i...
3
by: Neil Hindry | last post by:
I wonder if you can help me. I have setup an address-book database in Access XP. I have the first name & surname as separate fields. As I wanted to sort my database by surname and then by first...
2
by: Jon | last post by:
I am writing an MDI app that uses a document manager class to keep track of opened child windows. I want the user to be able to close a child window, but then re-open the window from the "Window"...
53
by: Hexman | last post by:
Hello All, I'd like your comments on the code below. The sub does exactly what I want it to do but I don't feel that it is solid as all. It seems like I'm using some VB6 code, .Net2003 code,...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
22
by: Amali | last post by:
I'm newdie in c programming. this is my first project in programming. I have to write a program for a airline reservation. this is what i have done yet. but when it runs it shows the number of...
3
by: Alami | last post by:
I'm newdie in c programming. this is my first project in programming. I have to write a program for a airline reservation. this is what i have done yet. but when it runs it shows the number of...
6
by: priyajohal | last post by:
#include<fstream.h> #include<process.h> #include<stdlib.h> #include<conio.h> #include<string.h> #include<dos.h> #include<ctype.h> #include<stdio.h> void setup() void help();
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.