sql statement help

[tziviak2]

if I have a table with 4 fields (a1,a2,a3,a4) how do I get the max value from the 4 fields (ex. if a1 is 2/1/03 and a2 is 2/5/03 I want the latest day 2/5/03 to come up-for a certain record?

 

[PeasNCarrots]

You have to use a totals Query

Example
SELECT Max(Table1.Date) AS MaxDate
FROM Table1;

 

[lespaul]

it appears that your database isn't normalized. If you had a single date field for each record, then PeasNCarrots' solution would work, but since you have four fields in each record that need to be compared, I would first write a query that normalizes your data:

SELECT id_Field, "Field1", Field1 FROM tblName
UNION
SELECT id_Field, "Field2", Field2 FROM tblName
UNION
SELECT id_field, "Field3", Field3 FROM tblName
UNION
SELECT id_Field, "Field4", Field4 FROM tblName

save this query as qryNormalizedtblName

then you can use PeasNCarrots query to get the max date for each ID field.

HTH

leslie

 

[tziviak2]

leslie, thanks for the response(peasNCarrots too.).the database is normalized-it's just that for each record id I have 4 dates-that have n/t to do with each other-but are relevent to the recordID. but I'll have to do your code-in order to get all the dates in one column.
now, can I do this code in a module (I'm handling with each recordID individually) or will it be too complicated -and I should just call the qrys?

 

[lespaul]

you can just use queries. Save the normalized query then query the saved query just like you would a table.

leslie

 

[cmmrfrds]

I have a query where I did what you are asking. It is on the order of what lespaul suggested.

SELECT Max(ut.fld) AS MaxOffld, ut.ID
FROM testtable AS A INNER JOIN [SELECT TestTable.ID, TestTable.field1 as fld
FROM TestTable
Union
SELECT TestTable.ID, TestTable.field2
FROM TestTable
Union
SELECT TestTable.ID, TestTable.field3
FROM TestTable
Union
SELECT TestTable.ID,TestTable.field4
FROM TestTable
]. AS ut ON A.ID = ut.ID
GROUP BY ut.ID;

 

[mordja]

Hi,

You could do that or you could check

a1 > a2^a3^a4 -> a1 else
a2 > a3^a4 -> a2 else
a3 > a4 -> a3 else a4

ie


SELECT IIf([Table1]![Field1]>[Table1]![Field2] And [Table1]![Field1]>[Table1]![Field3] And [Table1]![Field1]>[Table1]![Field4],[Table1]![Field1],IIf([Table1]![Field2]>[Table1]![Field3] And [Table1]![Field2]>[Table1]![Field4],[Table1]![Field2],IIf([Table1]![Field3]>[Table1]![Field4],[Table1]![Field3],[Table1]![Field4]))) AS MaxDate
FROM Table1;


Works fine.

Mordja

 

[tziviak2]

Mordja,
thanks-I am working with your code-I just have a prob.-s/ records don't have all the fields filled in-so it's having a hard-time comparing it to a null -any suggestions besides for adding in the code "or isnull"?

 

[mordja]

tziviak2,

You could bracket each item in each comparison with a Nz statement - Nz([Field],Replacement). Nz checks if a value is null and replaces it with a value of your choice usually 0. In your case you might opt for an ancient date.

Ie Nz([Table1]![Field1],1/1/11)

I tested it and it works fine. Kinda messy as you now have 12 Nz Statements but it works.

Mordja

 

[mordja]

tziviak2,

It might just be easier to update all blank dates in your table to a date that you recognise as a dummy/blank date ie 01/01/1000. Null values could continue to cause you future problems. In my database which I update monthly I always run an update query to remove any nulls. It makes it a lot easier when it comes to querying the database.

Mordja

 

[tziviak2]

thanks-I didn't use the nz-but I entered into every condition "or isNull(field)"

thanks to everybody for your help