ACCESS FEED: RE: [MS_AccessPros] Using a GROUP BY clause in a query of a multi-valued field

Steph-

I built those two queries on your tables, so they should work as it. Follow my
instructions to copy and paste into the SQL View of a new query, then switch to
Design view to help you understand what's going on.

Here's the first query again:

SELECT [Red Lists].Location, [Red Lists].Taxa.Value,

Max([Red Lists].Publication_Year) AS MaxOfPublication_Year
FROM [Red Lists]
GROUP BY [Red Lists].Location, [Red Lists].Taxa.Value;

That finds the latest publication year (MaxOfPublication_Year) for each
combination of Location and Taxonomy.

Save that query as qryMaxLocTaxonYear.

Then this second query uses that saved query:

SELECT [Red Lists].ID, [Red Lists].Red_List_Title, [Red Lists].Red_List_stage,
[Red Lists].Location, [Red Lists].Publication_Year, [Red Lists].Taxa
FROM [Red Lists] INNER JOIN qryMaxLocTaxonYear

ON ([Red Lists].Publication_Year = qryMaxLocTaxonYear.MaxOfPublication_Year)

AND ([Red Lists].Taxa.Value = qryMaxLocTaxonYear.[Red Lists].Taxa.Value)

AND ([Red Lists].Location = qryMaxLocTaxonYear.Location);

That joins your Red Lists table with the query saved above to get the complete
listing (you can add more fields that you want from Red Lists as needed) of each
entry that matches the latest year by Locacion and Taxon.

Again, copy and paste that into the SQL View of a new query and then switch to
Design View to see what's going on.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of sweet_n_heady
Sent: Tuesday, June 26, 2012 3:44 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Using a GROUP BY clause in a query of a
multi-valued field

Hi John,

Sorry for the late reply, I didn't get an alert for some reason. When I asked
about plain English it was because I don't speak SQL very well. I want to be
able to understand exactly what I'm asking the database to do and I don't
understand the Maxof() query .

As you can see from the sample I uploaded I also need to query two tables - the
'country' field is in a separate table as a given location (e.g. The Alps) may
cut across several countries. The two tables are therefore related (one to many,
Red Lists.Location on the left) and because I just can't work out exactly what
your code is doing I can't work out how to tweak it to suit my set-up. Sorry for
being so slow at this - it's all first time for me. I get how to put it into my
database, thank you for that.

Thanks,

Steph

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@...>
wrote:
>
> Steph-
>
> Start a new query, close the Show Table dialog, then click SQL View on the
> Ribbon. Copy and paste what I gave you into the SQL window, then switch to
> Design view if you like to see how it lays out. Go to Datasheet view to see
the
> result. Save the first query as qryMaxLocTaxonYear, then create the second
> query using the same procedure and using the second SQL text.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> ----------------------------
>
> From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of sweet_n_heady
> Sent: Friday, June 22, 2012 1:25 PM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: Re: [MS_AccessPros] Using a GROUP BY clause in a query of a
> multi-valued field
>
>
> Hi John,
>
> Thanks for your reply. It could well be what I want, but I'm afraid I'm having
> huge problems getting my head around this today (head cold, mental ability of
a
> wet sheep). Would you be able to translate those into 'plain English' for me?
I
> know that the multi-value field is less than ideal - the problem is that there
> are hundreds of independent red lists and no pattern in which taxa they cover.
> So one may cover amphibians and reptiles, whilst another may cover everything
> from mammals to fungi via invertebrates - there are no rules and they are
almost
> all different combinations! I couldn't work out how to deal with that without
> the multi-value field. I know I ought to have that relational table with both
> IDs for many-to-many relationships, but nothing I found helped me work out how
> to actually use them in practice! And everything is a little time-critical
now.
>
> Many thanks,
>
> Steph
>
> --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
wrote:
> >
> > Steph-
> >
> > Is this what you want?
> >
> > SELECT [Red Lists].Location, [Red Lists].Taxa.Value, Max([Red
> > Lists].Publication_Year) AS MaxOfPublication_Year
> > FROM [Red Lists]
> > GROUP BY [Red Lists].Location, [Red Lists].Taxa.Value;
> >
> > And if you want the publications that match the Location, Taxon, and Pub
Year,
> > then save the above as a query (let's call it qryMaxLocTaxonYear), and then
do
> > this:
> >
> > SELECT [Red Lists].ID, [Red Lists].Red_List_Title, [Red
Lists].Red_List_stage,
> > [Red Lists].Location, [Red Lists].Publication_Year, [Red Lists].Taxa
> > FROM [Red Lists] INNER JOIN qryMaxLocTaxonYear ON ([Red
> Lists].Publication_Year
> > = qryMaxLocTaxonYear.MaxOfPublication_Year) AND ([Red Lists].Taxa.Value =
> > qryMaxLocTaxonYear.[Red Lists].Taxa.Value) AND ([Red Lists].Location =
> > qryMaxLocTaxonYear.Location);
> >
> > As you can see from above, the trick with dealing with the individual values
> in
> > a multi-value field is to use the .Value property. Frankly, multi-value
fields
> > are a bad idea for many reasons, including this value masking. It would be
> > better to design your own relational tables to contain the multiple taxon
> > values.
> >
> > tblRedListTaxons: ID (from Red Lists), TaxonID (from the fixed _lookup_Taxon
> > table below)
> >
> > _lookup_Taxon: TaxonID, Taxon
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> > --------------------------------
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of sweet_n_heady
> > Sent: Thursday, June 21, 2012 4:35 PM
> > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: [MS_AccessPros] Using a GROUP BY clause in a query of a
multi-valued
> > field
> >
> >
> > Hello,
> >
> > I'm trying to extract a list of the most recent documents covering each of a
> > particular category in each country from my database. The categories are
> groups
> > of species, and because many of the documents cover multiple species, I've
> made
> > this field a multi-value field.
> >
> > I've been flummoxed by how to build this query, but have been trying to run
> > something that groups by Country, then by taxon (species group), and then
> > returns the maximum value from the publication year field (i.e. the most
> recent
> > year for each group). I'm hoping this will give me the most recent document
> > covering each taxon in each country. I've not yet worked out how I'll deal
> with
> > any duplicates the query throws up (i.e. if x document covers mammals, birds
&
> > fish will it display 3 times in the results?) I discovered when I tried to
run
> > my query that it isn't compatible with my multi-valued taxon field.
> >
> > Do you all think that I'm going about it the right way (very new to queries,
> > although comfortable with the principles) and is there another way I can
group
> > by taxon to return the most recent document for each one, without having to
> > change my field format? There's a sample copy of my database (including the
> > query) in the Files section under 'Assistance Needed' called NRL database
> sample
> > 21.06.2012.
> >
> > Many thanks in advance for your help, it's absolutely invaluable!
> >
> > Steph
> >
>

[Non-text portions of this message have been removed]