Query not working - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Query not working

Colin Smyth

3 years ago

I have a database of birds I have seen. I have a two queries - one which lists all the birds I have seen in a calendar year and so some birds are listed multiple times. I have a second query which by using the group by set to "last" only shows which birds were seen for the year. I will attached queries shortly. So come 2023, i changed the dates on the query which just shows the birds seen on at least one occasion, but it is only selecting the last bird seen and not all the individual birds that have been seen so far for 2023. Not sure why it worked for 2022 but doesn't for 2023. Any advice would be great.

	Colin Smyth OP @Reply 3 years ago

	Colin Smyth OP @Reply 3 years ago

A Glenn Yesner @Reply

3 years ago

In the picture for 2022 query I notice the table names end with _1 and in the query for 2023 there is no _1, is it possible you don't have all the data you want in the tables for the 2023 query? Perhaps some 2023 data is in the tables with the _1? I should think you would load all your data in the same tables and use "Like" to get your dates then you could easily choose whatever time period you desired without ever changing your query criteria.

	Kevin Robertson @Reply 3 years ago
Do you have a separate Query for each year? For 10, 15, 20 years of data are you going to make 10, 15, 20 queries? Try to build your query so you only need one rather then many. See this video: Reset New Year

Colin Smyth OP @Reply

3 years ago

Hi Kevin. Thank you for directing me to the Reset New Year. As a result, most of my issues have been resolved. I do have one other question. In my database I have a form that shows all the new birds I have seen for the first time for a particular year. That field is called NewFor and when I enter my data, if it is a new bird, I type in the year that the bird was seen for the first time. So in the query attached - how can I create a criteria for the NewFor that is based on the answer to the parameter which asked to type in the year?

	Colin Smyth OP @Reply 3 years ago

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View