a SELECT question?

[Geoff Cox]

Hello,

I have records containing fields called say field(n) where n=1 to 4,
with the following values:

field1 field2 field3 field4
record1 a
record2 b a
record3 c d a
record4 e f g a
record5 h i a k

How do I write a query in which I select only those records in which
the field(n) with the highest number value is a? ie I get records 1 to
4 but not record 5.

Cheers

Geoff

 

[John W. Vinson]

Hello,

I have records containing fields called say field(n) where n=1 to 4,
with the following values:

field1 field2 field3 field4
record1 a
record2 b a
record3 c d a
record4 e f g a
record5 h i a k

How do I write a query in which I select only those records in which
the field(n) with the highest number value is a?

With considerable difficulty... since your table structure is incorrectly
normalized. I'd say that you have a many to many relationship between Records
and Letters (whatever your field1 - field4 might be). A better design might be
a one to many relationship from the table of Records to a table with fields
RecordNo (a foreign key to the records table), Level, and Letter (or whatever
fieldname you like); records might be

record1 1 a
record2 1 b
record2 2 a
record3 1 c
record3 2 b
record3 3 a

A query like

SELECT recordNo, Level, Letter
FROM tablename
WHERE Letter = a
AND Level = (SELECT Max(Level) FROM tablename AS X
WHERE X.recordNo = tablename.recordNo)

would work.

With your current table you'll need to test for every possible combination of
field1 through field4 being NULL... let's see if I can do this...

SELECT * from yourtable
WHERE ([Field1] = "a" AND [field2] IS NULL AND [field3] IS NULL AND [field4]
IS NULL)
OR ([Field2] = "a" AND [field3] IS NULL AND [field4] IS NULL]
OR ([field3] = "a" AND [field4] IS NULL
OR ([field4] = "a");


John W. Vinson [MVP]

 

[Geoff Cox]

Hello,

I have records containing fields called say field(n) where n=1 to 4,
with the following values:

field1 field2 field3 field4
record1 a
record2 b a
record3 c d a
record4 e f g a
record5 h i a k

How do I write a query in which I select only those records in which
the field(n) with the highest number value is a?

With considerable difficulty... since your table structure is incorrectly
normalized. I'd say that you have a many to many relationship between Records
and Letters (whatever your field1 - field4 might be). A better design might be
a one to many relationship from the table of Records to a table with fields
RecordNo (a foreign key to the records table), Level, and Letter (or whatever
fieldname you like); records might be

record1 1 a
record2 1 b
record2 2 a
record3 1 c
record3 2 b
record3 3 a

A query like

SELECT recordNo, Level, Letter
FROM tablename
WHERE Letter = a
AND Level = (SELECT Max(Level) FROM tablename AS X
WHERE X.recordNo = tablename.recordNo)

would work.

With your current table you'll need to test for every possible combination of
field1 through field4 being NULL... let's see if I can do this...

SELECT * from yourtable
WHERE ([Field1] = "a" AND [field2] IS NULL AND [field3] IS NULL AND [field4]
IS NULL)
OR ([Field2] = "a" AND [field3] IS NULL AND [field4] IS NULL]
OR ([field3] = "a" AND [field4] IS NULL
OR ([field4] = "a");


John W. Vinson [MVP]

John,

Thanks again for your help. 'am giving them a try!

Cheers

Geoff