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@yahoogroups.com] On Behalf Of sweet_n_heady
Sent: Friday, June 22, 2012 1:25 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,
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, 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@yahoogroups.com] On Behalf Of sweet_n_heady
> Sent: Thursday, June 21, 2012 4:35 PM
> To: MS_Access_Professionals@yahoogroups.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
>
Tidak ada komentar:
Posting Komentar