Select query

[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

 

[KARL DEWEY]

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]

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));

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]

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"))
);

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.

Well it follows the first rule of software development... "There's never time to

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":

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.

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!

Thanks for the tutorial. I grew up using flat files, and have only relatively
recently begun using databases.

 

[MikeR]

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)

Ahhh. So the first is a saved query, right?

 

[MikeR]

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!

Thanks again. Fast AND accurate.