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, 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@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