criteria depending on another field

[Guest]

Hi There,

If someone could help me straighten this out, I would really really
appriciate it.

I have number data for different parameters for several stations. At each
of these stations, data was collected at different times of the year. So,
here I am trying to query my data to give me the max and min values of each
of the parameters at each of the stations. That I can figure out the problem
is I also want to include the dates of the max and min value ( in their own
individual field) in the query. What do I enter in the criteria box for
the max (min) value date? Grouping (by max or min) of the date field gives
me a date that is not related to the max or min value.

I hope my question is clear but please let me know if I need to explain more.

Thanks,
Mui

 

[Duane Hookom]

It would help if you posted about 10 records with the significant fields and
the desired output.

Do you know how to create totals queries? You can select grouping by Station
or other fields and return Min or Max values.

 

[Ed Warren]

You do it in steps.
1. get the observation max/min values

this can be in seveal forms depending on your table and queries
form1: StationID MaxPram1 MinParam1 MaxPram2 MinParam2
etc.
form2: StationID PramID Max Min
form3: StationID PramID Max
StationId PramId Min

2. use the first query tied to the data table using the station/max/min
values to get the date(s) those observation values were recorded.
form3: above gives the easy answer to this step:
build this query to find the date(s) that match the
stationID/PramId/value for the max/min values

There may be SQL experts out there that can get the answer in one step, but
I ain't one of them!

Note, when you 'group by max/min date' you are asking "please give me the
max/min date" for the group.

Ed Warren