How to design this query

Question

How to design this query

Rob Curtis 20

I wanted to find all the species shot at a location in a period before 1990. I created one query for the number of species, but when I try to put the time period in I lose the count and get everyinstances of each record. So this would jump from 908 types to thousands of instances. User's image

This query had duplicates so I have 874 with just Montrose Point. I was trying to make a query based on this query but kept having the non-exclusive problem.

George Hepworth 23,120 Reputation points Volunteer Moderator

2026-03-13T16:09:27.69+00:00

Before we attempt to offer concrete suggestions, please explain the tables in the screen shot and the relationships between them. Is [Slides 1] a table or a query? Is [animals 3] a table or a query? Is [Family List New] a table or a query?

You apparently did not enforce referential integrity between these three objects, if all three are, in fact, tables. Why not?

We can probably guess that all three must be tables, but on the chance they are not, please clarify.

Based on the assumption that they must be tables, you need to start with a subquery to get the count of each species for each location in "Slide 1" (btw. That does appear to be an odd name for a table. What does it refer to?)

SELECT Location, Species, Count(*)
FROM [Slide 1]

WHERE [Date_1] <=#12/31/1899#
GROUP BY Location, Species

That will return three fields: Location, Species, and count of records with that combination, i.e. count of species at each location, filtered for the time period you want.

With that subquery in replacing [Slide 1] in the query shown in your screenshot, you should then get the other fields you need.
Rob Curtis 20 Reputation points

2026-03-13T17:09:32.4133333+00:00

Thanks for looking at this. All three are tables. That is the design view of a query to show a count of species at Montrose Point. Each time I try to add the limiting date field in this or another query based on it, I lose the count, getting duplicate species. Slide one refers to when pictures were taken with slides. This is a database of all the kept images from my shooting, even though now it is all digital. Are From and Where new features? I don't have in this Access 2007.
George Hepworth 23,120 Reputation points Volunteer Moderator

2026-03-13T17:21:43.6666667+00:00

SELECT, FROM, WHERE, GROUP BY, ORDER BY and others are the actual SQL keywords in queries. Every query contains at least a SELECT and a FROM. Depending on what else the query needs to do, you need one or more of the other clauses.

You are, apparently, familiar only with the Design view query grid. It is a heavily formatted and structured view of the real query. It's intended to help new comers create queries without understanding the real SQL in the query. It's a very good idea to expand your knowledge by inspecting the real SQL in your queries.

SQL View shows you the actual SQL as I posted this example.

The SQL I gave you is for a subquery. Create it SQL view and save it. Open it in Datasheet view to verify it returns the records you need accurately.

Then, in the query shown, replace the table [Slide 1] with this new subquery and make sure it joins on the correct field.
Rob Curtis 20 Reputation points

2026-03-13T20:54:48.84+00:00

I don't know if I understood. I took the Montrose query and overwrote what was in the SQL view with what I copied here. I changed slide 1 to slides 1 and the date to <1/1/1990 and it found 1232 records, but I most are not from Montrose, which is in Chicago, and some are from Wisconsin. I'm not sure what the result is, or what to do to refine so just a count of Montrose species.
George Hepworth 23,120 Reputation points Volunteer Moderator

2026-03-13T21:21:31.5166667+00:00

I did not suggest replacing the SQL in your current query with what you showed in your other post.

I suggested this:

Create a NEW query. Use my suggested SQL in that query. Make sure it returns the right location, species and counts for the time period you want, pre-1900.

Save that query. Give it a meaningful name, something like LocationCounts, for example.

Now, open the original query again. It has three tables in it. Remove the [Slides 1] table.

Add the new query after removing [Slides 1]. Join the new subquery to the [animals 3] table on Species.

5 answers

Your answer

George Hepworth 23,120 Reputation points Volunteer Moderator

2026-03-13T16:09:27.69+00:00

Before we attempt to offer concrete suggestions, please explain the tables in the screen shot and the relationships between them. Is [Slides 1] a table or a query? Is [animals 3] a table or a query? Is [Family List New] a table or a query?

You apparently did not enforce referential integrity between these three objects, if all three are, in fact, tables. Why not?

We can probably guess that all three must be tables, but on the chance they are not, please clarify.

Based on the assumption that they must be tables, you need to start with a subquery to get the count of each species for each location in "Slide 1" (btw. That does appear to be an odd name for a table. What does it refer to?)

SELECT Location, Species, Count(*)
FROM [Slide 1]

WHERE [Date_1] <=#12/31/1899#
GROUP BY Location, Species

That will return three fields: Location, Species, and count of records with that combination, i.e. count of species at each location, filtered for the time period you want.

With that subquery in replacing [Slide 1] in the query shown in your screenshot, you should then get the other fields you need.
Rob Curtis 20 Reputation points

2026-03-13T17:09:32.4133333+00:00

Thanks for looking at this. All three are tables. That is the design view of a query to show a count of species at Montrose Point. Each time I try to add the limiting date field in this or another query based on it, I lose the count, getting duplicate species. Slide one refers to when pictures were taken with slides. This is a database of all the kept images from my shooting, even though now it is all digital. Are From and Where new features? I don't have in this Access 2007.
George Hepworth 23,120 Reputation points Volunteer Moderator

2026-03-13T17:21:43.6666667+00:00

SELECT, FROM, WHERE, GROUP BY, ORDER BY and others are the actual SQL keywords in queries. Every query contains at least a SELECT and a FROM. Depending on what else the query needs to do, you need one or more of the other clauses.

You are, apparently, familiar only with the Design view query grid. It is a heavily formatted and structured view of the real query. It's intended to help new comers create queries without understanding the real SQL in the query. It's a very good idea to expand your knowledge by inspecting the real SQL in your queries.

SQL View shows you the actual SQL as I posted this example.

The SQL I gave you is for a subquery. Create it SQL view and save it. Open it in Datasheet view to verify it returns the records you need accurately.

Then, in the query shown, replace the table [Slide 1] with this new subquery and make sure it joins on the correct field.
Rob Curtis 20 Reputation points

2026-03-13T20:54:48.84+00:00

I don't know if I understood. I took the Montrose query and overwrote what was in the SQL view with what I copied here. I changed slide 1 to slides 1 and the date to <1/1/1990 and it found 1232 records, but I most are not from Montrose, which is in Chicago, and some are from Wisconsin. I'm not sure what the result is, or what to do to refine so just a count of Montrose species.
George Hepworth 23,120 Reputation points Volunteer Moderator

2026-03-13T21:21:31.5166667+00:00

I did not suggest replacing the SQL in your current query with what you showed in your other post.

I suggested this:

Create a NEW query. Use my suggested SQL in that query. Make sure it returns the right location, species and counts for the time period you want, pre-1900.

Save that query. Give it a meaningful name, something like LocationCounts, for example.

Now, open the original query again. It has three tables in it. Remove the [Slides 1] table.

Add the new query after removing [Slides 1]. Join the new subquery to the [animals 3] table on Species.

Answer 1

Use a totals (aggregate) query that groups only by the fields that define a unique species at a location, and put the date restriction in the criteria for the date field.

In Design view of the query:

Add the table that contains the shots (Slides 1) and any lookup tables that define species.
Add these fields to the grid:
- LOCATION (from Slides 1)
- SPECIES (from Slides 1 or the related table that uniquely identifies a species)
- DATE_1 (the date the shot was taken)
On the Design tab, click Totals so the Total row appears.
Set the Total row as follows:
- LOCATION: Group By
- SPECIES: Group By
- DATE_1: Where
In the Criteria row under DATE_1 enter the date condition, for example:
- <= #12/31/1989# (all shots before 1990)
Add a new column to count distinct species per location:
- Field: SpeciesCount: SPECIES
- Total: Count

This design:

Filters records to only those at Montrose (keep LOCATION criteria like Like "Montrose*" if needed).
Restricts records to dates before 1990 via the Where clause on DATE_1.
Groups by LOCATION and SPECIES so each species at that location is counted once.
Uses Count to return the number of species, not every instance.

If duplicates still appear, add additional grouping fields that distinguish species (for example, SUBJECT or an identifier) or use a separate totals query first to get unique species per location, then base a second query on that totals query to count the rows.

References:

Answer 2

Rob Curtis 20

The AI can't see that I already made such a query in the pictures. Before the date, there are 874 different species. With the date, that drops to 118 records but there are duplicates of the species. How to set that up to get rid of the extras I'm still stuck.

0 comments

Answer 3

Duplication here.

|| || Query2

CountOfCountOfSLIDE_#	LOCATION	DATE_1	CountOfSUBJECT	SPECIES
1	Montrose Point	9/9/1988	1	Agelaius phoeniceus

1	Montrose Point	9/9/1988	1	Agelaius phoeniceus
1	Montrose Point	9/1/1987	1	Ammodramus leconteii
1	Montrose Point	9/29/1989	1	Ammodramus leconteii
1	Montrose Point	9/21/1988	1	Ammospiza nelsoni
1	Montrose Point	11/13/1988	1	Anas platyrhynchos
1	Montrose Point	10/1/1988	1	Asio flammeus
1	Montrose Point	9/26/1988	1	Aythya affinis
1	Montrose Point	11/13/1988	1	Aythya affinis
1	Montrose Point	10/28/1989	1	Botaurus lentiginosus
1	Montrose Point	9/23/1989	1	Calcarius lapponicus
1	Montrose Point	9/24/1989	1	Calcarius lapponicus
1	Montrose Point	10/29/1989	1	Calcarius lapponicus
1	Montrose Point	9/7/1988	1	Calidris alba
1	Montrose Point	9/8/1988	1	Calidris alba
1	Montrose Point	8/25/1989	1	Calidris alba
1	Montrose Point	9/26/1988	1	Calidris alpina
1	Montrose Point	9/8/1988	1	Calidris bairdii
1	Montrose Point	8/25/1989	1	Calidris bairdii
1	Montrose Point	9/8/1988	1	Calidris canutus
1	Montrose Point	9/9/1988	1	Calidris canutus
1	Montrose Point	9/15/1988	1	Calidris canutus
1	Montrose Point	8/1/1989	1	Calidris canutus

Answer 4

Rob Curtis 20

I must be seeming somewhat dull here, but I am trying to get it. I started a new query, and pasted in the lines in SQL (I didn't know I could start out with no tables). I made a couple of corrections, as it is slides 1 (slide 1 not found) and it is for records before 1990, not 1900.

So: SELECT [Slides 1].Location, [Slides 1].Species, Count(*) AS Expr1

FROM [Slides 1]

WHERE ((([Slides 1].[Date_1])<=#1/1/1990#))

GROUP BY [Slides 1].Location, [Slides 1].Species;

This gave one table, with 1232 results. I then used this query instead of the slides 1 table and connected. I didn't see the field slides 1 but the result, with 63 species, seems it may be right. Because of the hidden parameters, is sorts phylogenetically instead of alphabetically, as I prefer. Thanks very much for your help! User's image

User's image

George Hepworth 23,120 Reputation points Volunteer Moderator

2026-03-14T10:44:49.68+00:00

Happy to hear you have a working solution to start. The next step will be to work out how to change the date parameter in the [location counts] query if you need to do that. I sort of assumed that the date range is not variable, but if it is, you might want to think about making it dynamic.

The reason the species count field shows up as [Expr1] is the way it was defined in the subquery. That is my fault. I failed to write it out fully.

You could change it to something more meaningful. For example:

SELECT Location, Species, Count(*) AS [Number of Species at Location]

FROM [Slide 1]

WHERE [Date_1] <=#12/31/1899# GROUP BY Location, Species

Answer 5

George, thanks again for the updated suggestion, which I can't see here. I tried that and .changing some locations to explore. It is unusual that I accessed this forum from a saved link which knows my password and everything.

Yet when I go to the email, to see the latest post I have to sign in to answer, and that takes me to GoDaddy, where it doesn't accept my current password and then I have to change with the code sent, and then even after that it doesn't accept. I just went through all this days ago and had to call Godaddy as reset wasn't working and wait over a half hour for an answer and then go back and forth. Then it worked and now it didn't again, and I don't want to spend that time now.

I am going to look at the SQL more and see if I can use different parameters such as my city or state fields. One thing I don't understand is, doesn't this Access have a "save as". If I just go to close, it asks if I want to save and then will wipe out whatever was there before making changes. Copying and pasting a duplicate isn't good, because copy doesn't appear available till it's closed, but then I would lose the original.