By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,936 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Grouping with detail

P: n/a
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

May 18 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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 ***
May 18 '06 #2

P: n/a
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 ***


May 18 '06 #3

P: n/a
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 ***
May 19 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.