How to make 'First' select just the first case

[Dick Vernon]

In a table of agriultural pests I have fields for Genus and Species. I have removed Common Name (e.g. yellow stinkbug) to another table because many pests have > one common name. In a query with both tables I have a concatenated field of [Genus]&" "&[Species] and another of common name, but I do not want five records for a pest with five common names - just the first will do. So I set the query to include the Totals (Group by) row, and set the [Genus]&" "&[Species] field to 'First'. But I still get all records, one for each common name.

1. How do I get it give me just the first?

2. Separately, is there a way I could get the several common names of one pest to appear on one row? (I still need a fix for Question 1)

Guidance will be much appreciated.

Dick Vernon

 

[Pat Hartman]

1. First() should do it. Post your query and we'll see if we can see what is wrong. You are probably selecting too many columns from the many-side table.
2. Look up this article in the microsoft kb - 141624 - ACC How to Concatenate a List of Items from a Many-Side Table

 

[Dick Vernon]

Pat, thanks for quick response. Here is the SQL version - will that do? Dick

SELECT DISTINCTROW tblSpecies.[Host ID], First((Trim([Genus] & " " & [Species]))) AS Host, tblSpecies_Common_Names.[Common name]
FROM tblSpecies LEFT JOIN tblSpecies_Common_Names ON tblSpecies.[Species ID] = tblSpecies_Common_Names.[Species ID]
GROUP BY tblSpecies.[Host ID], tblSpecies_Common_Names.[Common name]
ORDER BY First((Trim([Genus] & " " & [Species])));

1. First() should do it. Post your query and we'll see if we can see what is wrong. You are probably selecting too many columns from the many-side table.
2. Look up this article in the microsoft kb - 141624 - ACC How to Concatenate a List of Items from a Many-Side Table

 

[Dick Vernon]

Pat,

Further to the above, I have tried variants and am pleased to say the following works - it presents only the first case of each pest with several common names. Not sure if the SQL view is what is needed but let me know if another view ie a capture image of the grid, or a small mdb c/w tables is better. Do you know if my second question above is possible?

Regards, Dick

SELECT DISTINCTROW tblSpecies.[Species ID], First(Trim([Genus] & " " & [Species] & " " & [Subspecies] & " " & [Authority] & " " & [Common name])) AS Host
FROM tblSpecies LEFT JOIN tblSpecies_Common_Names ON tblSpecies.[Species ID] = tblSpecies_Common_Names.[Species ID]
GROUP BY tblSpecies.[Species ID], tblSpecies.[Pest or Host or both]
HAVING (((tblSpecies.[Pest or Host or both])="h" Or (tblSpecies.[Pest or Host or both])="hp"))
ORDER BY First(Trim([Genus] & " " & [Species] & " " & [Subspecies] & " " & [Authority] & " " & [Common name]));

 

[Pat Hartman]

You need to remove "DISTINCTROW". You probably also need to get rid of [Common name] or select the first instance of it also.

SELECT tblSpecies.[Host ID], First((Trim([Genus] & " " & [Species]))) AS Host, First(tblSpecies_Common_Names.[Common name], As CommonName
FROM tblSpecies LEFT JOIN tblSpecies_Common_Names ON tblSpecies.[Species ID] = tblSpecies_Common_Names.[Species ID]
GROUP BY tblSpecies.[Host ID]
ORDER BY First((Trim([Genus] & " " & [Species])));

The DISTINCTROW is overriding the Group By. DISTINCTROW says to return individual rows and Group By says to group them. You can get away with DISTINCTROW in the second query because of the Having clause and because the second query also includes several more fields within the First() function so they will not be causing additional rows to be selected.

To come up with the simplest correct solution you need to understand what DISTINCTROW, Group By, and First() actually do. Try reading help. You may have to use the table of contents. Open help from the Database Container window (rather than the VBA window). Change to the Contents tab and look for SQL. You'll have to drill down but you should find explainations.