Grouping with detail

[UHelix]

Hi,

I'm not really even sure how to phrase this question, so here is a
sample of exactly what I want to do. In the following data set I would
like to select the entry from the group of samples 1 that has the
lowest DOpt. Do the same for the group of samples 2. I would then like
to get the average for the CFU/100ml values of the two samples together
but only for the two values that correspond to the lowest DOpt from
each of the sample sets. Basically I can group based on one field to
determine the record I want to use, but by grouping I loose the detail
of that record. Any suggestions are greatly appreciated.
Location Sample Volume ml Count CFU/100ml DOpt
BL 1 0.1 1 1000 29
BL 1 1 13 1300 17
BL 1 10 75 750 45
BL 2 0.1 2 2000 28
BL 2 1 18 1800 12
BL 2 10 93 930 63

[Rich P]

You can use a Union Query for this type of operation

Select * from tbl5 where DOpt In (select min(DOpt) from tbl5 where
sample = 1)
Union All
Select * from tbl5 where DOpt In (select min(DOpt) from tbl5 where
sample = 2)

To get to the Union Query design window - go to Query Design, then go to
the Query Menu/go to Sql Specific/select Union. This will bring up the
union query design window (just a sql window - no grids here). You can
paste the code above. Note: I used a table that I named tbl5 with the
fields that you specified in your post

Location, Sample, Volumn_ml, Count, CFU/100ml, DOpt

and I used your values. The code above will select the entire row for
min(DOpt) for sample 1 and the entire row for min(DOpt) for sample 2 and
will display both rows in the query result when you run the Union Query.
Also, the symbol for a union query in Access is �‡ the infinity symbol
(larger though) in the Database window for the Query Tab.

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[UHelix]

Thanks Rich,

I have oversimplified my problem somewhat by only including a small
sample of data. The actual data has multiple locations and the sample
number may be common for different locations. Something more like this.

Location Sublocation Date Subtype Sample Volume Count CFU_100ml Dopt
RG R1 5/8/2006 C 1 1 1 100 29
RG R1 5/8/2006 C 1 10 0 0 30
RG R1 5/8/2006 C 1 150 48 32 18
RG R1 5/8/2006 C 2 1 1 100 29
RG R1 5/8/2006 C 2 10 2 20 28
RG R1 5/8/2006 C 2 150 43 28.66666667 13
BL 51E 5/8/2006 A 1 50 0 0 30
BL 51E 5/8/2006 A 1 150 3 2 27
BL 51E 5/8/2006 A 2 50 2 4 28
BL 51E 5/8/2006 A 2 150 6 4 24

So I want the CFU_100ml value for the lowest DOpt in each group where
Location, Sublocation, date, Subtype are unique. I can't select the min
based on sample=1 since sample =1 for multiple locations.

Steve

Rich P wrote:

You can use a Union Query for this type of operation

Select * from tbl5 where DOpt In (select min(DOpt) from tbl5 where
sample = 1)
Union All
Select * from tbl5 where DOpt In (select min(DOpt) from tbl5 where
sample = 2)

To get to the Union Query design window - go to Query Design, then go to
the Query Menu/go to Sql Specific/select Union. This will bring up the
union query design window (just a sql window - no grids here). You can
paste the code above. Note: I used a table that I named tbl5 with the
fields that you specified in your post

Location, Sample, Volumn_ml, Count, CFU/100ml, DOpt

and I used your values. The code above will select the entire row for
min(DOpt) for sample 1 and the entire row for min(DOpt) for sample 2 and
will display both rows in the query result when you run the Union Query.
Also, the symbol for a union query in Access is �‡ the infinity symbol
(larger though) in the Database window for the Query Tab.

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

You can use a group by query to narrow down the groupings

Here is what the query would look like

select location, sublocation, cfu_100ml, min(DoPT) from yourTbl Group By
location, sublocation, cfu_100ml

Now you don't even need the union query. Think of group by like
grouping subgroups. The more groups you add, the narrower the spread of
data per grouping.

Rich

*** Sent via Developersdex http://www.developersdex.com ***