Select query

  • Thread starter Thread starter MikeR
  • Start date Start date
M

MikeR

I have 2 tables in an Access DB. In the ID table an ID may have 1,2 or 3 rows.
The g columns may be either NULL, an F, or a P. I want to select IDs where none
of the rows for that ID contains an F, and find the name for that ID in another
table (sName) which contains IDs and names
ID g1 g2 g3 g4 sem
-----------------------
AA P 1
AA F P 2
AA P 3
BB P 1
CC P 1
CC P 2
CC 3
DD 1

ID stuName
----------------------
AA Bill
BB Joe
CC Mac
DD Mazie

Result set desired:
BB Joe
CC Mac
DD Mazie

TIA, Mike
 
Use two queries. The first named MikeR_F.
SELECT MikeR.ID, MikeR.G1, MikeR.G2, MikeR.G3, MikeR.G4
FROM MikeR
WHERE (((MikeR.G1)="F")) OR (((MikeR.G2)="F")) OR (((MikeR.G3)="F")) OR
(((MikeR.G4)="F"));

SELECT MikeR_Name.ID, MikeR_Name.NAME
FROM MikeR_Name INNER JOIN (MikeR LEFT JOIN MikeR_F ON MikeR.ID =
MikeR_F.ID) ON MikeR_Name.ID = MikeR.ID
GROUP BY MikeR_Name.ID, MikeR_Name.NAME, MikeR_F.ID
HAVING (((MikeR_F.ID) Is Null));
 
MikeR said:
I have 2 tables in an Access DB. In the ID table an ID may have 1,2 or 3
rows. The g columns may be either NULL, an F, or a P. I want to select
IDs where none of the rows for that ID contains an F, and find the name
for that ID in another table (sName) which contains IDs and names
ID g1 g2 g3 g4 sem
-----------------------
AA P 1
AA F P 2
AA P 3
BB P 1
CC P 1
CC P 2
CC 3
DD 1

ID stuName
----------------------
AA Bill
BB Joe
CC Mac
DD Mazie

Result set desired:
BB Joe
CC Mac
DD Mazie

TIA, Mike
Click to expand...

Assuming the first table is STU_G and the second table is STU_NAMES:

SELECT STU_NAMES.ID, STU_NAMES.STUNAME
FROM STU_NAMES
WHERE STU_NAMES.ID NOT IN
(SELECT STU_G.ID
FROM STU_G
WHERE (((STU_G.G1)="F")) OR (((STU_G.G2)="F")) OR (((STU_G.G3)="F")) OR
(((STU_G.G4)="F"))
);

I would be remiss in my duty to the relational database community if I
did not point out that your first table breaks a cardinal rule of
design: Do not store repeating groups in a table. Such "spreadsheet"
design is difficult to work with in a relational database.

If you are able, restructure the first table so each row represents
exactly one fact. In your situation a fact appears to be one ID, one
"G", and a value for "SEM":

table STU_G_NORMALIZED
======================
PK AUTONUMBER (*)
ID TEXT (+)
G_INDEX NUMBER (+)
SEM NUMBER (+)
G_VALUE TEXT

(*) Suggested as a primary key. Alternatively you could create a unique
index based on the three attributes marked (+). This is done to ensure
duplicate rows are not created.

The data in this new table would look like this:
PK ID G_INDEX SEM G_VALUE
---------------------------------
1 AA 1 1 P
2 AA 1 2 F
3 AA 2 2 P
4 AA 4 3 P
5 BB 1 1 P

....Etc. Notice how you don't need to create "blank" spots.

The new query would look something like

SELECT STU_NAMES.ID, STU_NAMES.STUNAME
FROM STU_NAMES
WHERE STU_NAMES.ID NOT IN
(SELECT STU_G_NORMALIZED.ID
FROM STU_G_NORMALIZED
WHERE STU_G_NORMALIZED.G_VALUE = "F"
);

Notice how much easier it is to find the Fs.

If you plan to use this database for a while, and especially if you plan
to create new ones, it will be well worth your time and effort to study
the topic of "database normalization" (Google with quotes for plenty of
good references).

Hope this helps!
 
KARL said:
Use two queries. The first named MikeR_F.
SELECT MikeR.ID, MikeR.G1, MikeR.G2, MikeR.G3, MikeR.G4
FROM MikeR
WHERE (((MikeR.G1)="F")) OR (((MikeR.G2)="F")) OR (((MikeR.G3)="F")) OR
(((MikeR.G4)="F"));

SELECT MikeR_Name.ID, MikeR_Name.NAME
FROM MikeR_Name INNER JOIN (MikeR LEFT JOIN MikeR_F ON MikeR.ID =
MikeR_F.ID) ON MikeR_Name.ID = MikeR.ID
GROUP BY MikeR_Name.ID, MikeR_Name.NAME, MikeR_F.ID
HAVING (((MikeR_F.ID) Is Null));
Click to expand...

Karl,
Thanks very much. I should have said I'm returning the results in a DAO
recordset. I don't know how to do that using your solution. Or even just
plugging it into the SQL view of the query wizard.
 
MikeR said:
Karl,
Thanks very much. I should have said I'm returning the results in a DAO
recordset. I don't know how to do that using your solution. Or even just
plugging it into the SQL view of the query wizard.
Click to expand...

In your VBA code, define a String variable equivalent to Karl's second
query (or the to the query I offered in my other post in this thread) and
Set RS = DBEngine(0)(0).OpenRecordet(MySQLString)
 
Smartin said:
Assuming the first table is STU_G and the second table is STU_NAMES:

SELECT STU_NAMES.ID, STU_NAMES.STUNAME
FROM STU_NAMES
WHERE STU_NAMES.ID NOT IN
(SELECT STU_G.ID
FROM STU_G
WHERE (((STU_G.G1)="F")) OR (((STU_G.G2)="F")) OR (((STU_G.G3)="F")) OR
(((STU_G.G4)="F"))
);
Click to expand...
Thanks very much! Works like a charm, but needs a small tweak (my fault, sorry).
There are ID's in the STU_Names table that are not in the STU_G table, and it
returns those also.
I would be remiss in my duty to the relational database community if I
did not point out that your first table breaks a cardinal rule of
design: Do not store repeating groups in a table. Such "spreadsheet"
design is difficult to work with in a relational database.
Click to expand...
Well it follows the first rule of software development... "There's never time to
do it right said:
If you are able, restructure the first table so each row represents
exactly one fact. In your situation a fact appears to be one ID, one
"G", and a value for "SEM":
Click to expand...
True. There's a lot of code based on the current design. I'll have to look at
restructuring it later.
table STU_G_NORMALIZED
======================
PK AUTONUMBER (*)
ID TEXT (+)
G_INDEX NUMBER (+)
SEM NUMBER (+)
G_VALUE TEXT

(*) Suggested as a primary key. Alternatively you could create a unique
index based on the three attributes marked (+). This is done to ensure
duplicate rows are not created.
Click to expand...

A row is unique on ID and sem.
The data in this new table would look like this:
PK ID G_INDEX SEM G_VALUE
---------------------------------
1 AA 1 1 P
2 AA 1 2 F
3 AA 2 2 P
4 AA 4 3 P
5 BB 1 1 P

...Etc. Notice how you don't need to create "blank" spots.

The new query would look something like

SELECT STU_NAMES.ID, STU_NAMES.STUNAME
FROM STU_NAMES
WHERE STU_NAMES.ID NOT IN
(SELECT STU_G_NORMALIZED.ID
FROM STU_G_NORMALIZED
WHERE STU_G_NORMALIZED.G_VALUE = "F"
);

Notice how much easier it is to find the Fs.

If you plan to use this database for a while, and especially if you plan
to create new ones, it will be well worth your time and effort to study
the topic of "database normalization" (Google with quotes for plenty of
good references).

Hope this helps!
Click to expand...
Thanks for the tutorial. I grew up using flat files, and have only relatively
recently begun using databases.
 
MikeR said:
Thanks very much! Works like a charm, but needs a small tweak (my fault,
sorry).
There are ID's in the STU_Names table that are not in the STU_G table,
and it returns those also.
Click to expand...

You may have already figured this out. To exclude STU_Names IDs not in
STU_G add the following just before the ;:

AND STU_NAMES.ID IN (SELECT STU_G.ID FROM STU_G)
Well it follows the first rule of software development... "There's never
time to do it right, but there's always time to do it over." <G>
Click to expand...

LOL. I'm sure many of us have been there. I know I have!
True. There's a lot of code based on the current design. I'll have to
look at restructuring it later.

A row is unique on ID and sem.
Thanks for the tutorial. I grew up using flat files, and have only
relatively recently begun using databases.
Click to expand...

Same here. Best of luck!
 
Smartin said:
In your VBA code, define a String variable equivalent to Karl's second
query (or the to the query I offered in my other post in this thread) and
Set RS = DBEngine(0)(0).OpenRecordet(MySQLString)
Click to expand...

Ahhh. So the first is a saved query, right?
 
Smartin said:
You may have already figured this out. To exclude STU_Names IDs not in
STU_G add the following just before the ;:

AND STU_NAMES.ID IN (SELECT STU_G.ID FROM STU_G) Bingo! And no I hadn't.
Same here. Best of luck!
Click to expand...
Thanks again. Fast AND accurate.
 
Back
Top