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

Hi John,

I replied to this but it seems to have got lost. Thank you so much for your help on this query - it's really taking shape. The output of this query will be populating a map. When a map user clicks on a country, they should be able to see every Red List relating to it. Unfortunately what I'm using at the moment are locations - these can be one country, an area within a country or a region encompassing several countries.

I've tried to modify the code to show countries too:
SELECT [Red Lists].ID, [Red Lists].Location, [_lookup_Countries & Codes].[Country Name], [Red Lists].Red_List_Title, [Red Lists].Red_List_stage, [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)) LEFT JOIN [_lookup_Countries & Codes] ON [Red Lists].Location = [_lookup_Countries & Codes].[Country Name];

This gives me country names for everywhere that Location=Country Name, but blanks for those Locations that are multi-country. I need Red Lists from multi-country Locations to occur repeatedly in the table - once for each country covered by the Location, so that a user clicking on, say, India or Bangladesh will be able to see the South Asian Bats Red List associated with both countries. I can't work out how to do this! The final output should list each Red List only as many times as it appears in a country, with any multiple taxa back in one field again. The map will present a list of most recent Red Lists in each country as:
Red List Title (date) Stage
Taxa Covered

e.g.Red List of Canadian Vertebrates (2007) Being updated
Taxa Covered: Mammals, Birds, Amphibians, Reptiles, Fish

Effusive thanks for all your help, as always!

Steph

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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]
>