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

Steph-

It's late here in Paris. I'll look at this again in the morning - unless
someone else has jumped in.

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: Wednesday, June 27, 2012 7:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Using a GROUP BY clause in a query of a
multi-valued field

Hi John,

I mis-phrased my questions. To be a bit clearer:

The first: I know the Red Lists are showing once for each taxon. So we've split
out the taxa to see which is the latest Red List for each - can we pull them
back together to just show one Red List again now we've discovered which Red
Lists we want? So hard to explain clearly! But basically I don't want my map to
show a list like this:

The Red List of Brazilian vertebrates (2007) Covers - Mammals
The Red List of Brazilian vertebrates (2007) Covers - Birds

I want it to look like this:
The Red List of Brazilian vertebrates (2007) Covers - Mammals, Birds

So I need my table to show each Red List once only.

Unless...and this is the second question: Some of my Red Lists cover several
countries. e.g. South Asia is Bangladesh, Bhutan, India, the Maldives etc. I
want my map to show any Red List for South Asia in every country in South Asia.
So for Red Lists in many countries, the Red List should show up once for every
country it is for. I suppose that I want the opposite of what I'm asking for
'taxa'.

So essentially my question was 'what is the most recent Red List for each Taxon
in each Location?' but the results I want are 'Show me the Red Lists from my
question that occur in this Country'.

I'm not sure I'm doing a good job of explaining myself clearly and I realise I'm
very reliant on your help - I hope I'm making some sense.

Many thanks,

Steph

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@...>
wrote:
>
> Steph-
>
>
>
> If a publication covers multiple Taxa and is the latest publication for a
> particular Taxon and Location, then yes, it will show up multiple times - once
> for each Taxon. You can see the individual Taxon by changing your query to
> this:
>
>
>
> SELECT [Red Lists].ID, Location.[Parent Country], [Red Lists].Location,
> [Red Lists].Red_List_Title, [Red Lists].Red_List_stage, [Red
> Lists].Publication_Year,
> [Red Lists].Taxa.Value
>
> FROM ([Red Lists] INNER JOIN qryMaxLocTaxonYear
> ON ([Red Lists].Location = qryMaxLocTaxonYear.Location)
> AND ([Red Lists].Taxa.Value = qryMaxLocTaxonYear.[Red Lists].Taxa.Value)
> AND ([Red Lists].Publication_Year = qryMaxLocTaxonYear.MaxOfPublication_Year))

> LEFT JOIN Location ON [Red Lists].Location = Location.[Location Name];
>
>
>
> When you show the individual Taxon, it's clear they aren't duplicates.
>
>
>
> See how much trouble multi-value fields get you in to?
>
>
>
> 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%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of sweet_n_heady
> Sent: Wednesday, June 27, 2012 4:50 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
>
>
>
>
>
> Oops, sorry - realised I was using the wrong field...
>
> I've modified the SQL to look like this:
> SELECT [Red Lists].ID, Location.[Parent Country], [Red Lists].Location, [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 Location ON [Red
> Lists].Location=Location.[Location Name];
>
> What I'm now getting is multi-value fields for country (which is how I set it
up
> to be :S) but I need the countries separated out (in a similar way to how the
> taxa were in your initial suggested query). I'm also getting apparent
duplicates
> of Red Lists in the final query - if a Red List covers 5 taxa it's showing up
5
> times (similar to the initial problem I had with my data entry form!) How do I
> fix this?
>
> Thank you,
>
> Steph
>
> --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "sweet_n_heady"
> <stephanie.landymore@> wrote:
> >
> > 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
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
sweet_n_heady
> > > Sent: Tuesday, June 26, 2012 3:44 PM
> > > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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,
> > >
> > > 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>
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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>
> <mailto:MS_Access_Professionals%40yahoogroups.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>
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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>
> <mailto:MS_Access_Professionals%40yahoogroups.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]
> > >
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

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