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@yahoogroups.com] On Behalf Of sweet_n_heady
Sent: Wednesday, June 27, 2012 4:50 PM
To: MS_Access_Professionals@yahoogroups.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> , "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> , 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@yahoogroups.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>
> > 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> , 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@yahoogroups.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>
> > > 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> , 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@yahoogroups.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>
> > > > 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]
Tidak ada komentar:
Posting Komentar