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, "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, 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]
> >
>
Tidak ada komentar:
Posting Komentar