Any suggestions on how to simplify queries to identify YTD entries?

[templeowls]

I inherited a DB which holds customer complaints and it seems to be set up strangely to capture YTD complaints (i.e. to show complaints across all years from only January 1 to July 6)

I've attached a simple example DB to help. Essentially the complaints are parsed out into individual queries representing each year. The current year is just filtered down to '2022' while the other years are using DateSerial functions to identify the YTD events. Then all the individual queries are tied back together using a union query.

I was hoping there was a simpler way to do this (i.e. not requiring individual queries to be made). I ask because, with every passing year, new queries will have to be made and the current year's query's filter will need to be adjusted. Not a huge burden, but I thought it'd be worth checking to see if there's a cleaner and more automated way of doing this.

Thanks!

[Micron]

This seems like another case of interacting with queries and tables instead of using forms. Not real clear what you want but if it's always a year or date range, then form controls (textboxes) can provide the beginning and ending dates. You can even have those pop up a calendar for input. Or you can pick years (one) from a combo if you just want for one particular year. Or any myriad of things you can come up with to suit your needs. Least favourable would be a parameter prompt that prompts user for year values, but if not supplied these types of queries don't perform properly. You could try a new query by pasting this into the sql view then look at it in design view. Could even run it and see what happens after entering 2022 and then again by entering nothing.

SELECT qryAllComplaints.ID, qryAllComplaints.ComplaintNumber, qryAllComplaints.ComplaintDate, qryAllComplaints.Year
FROM qryAllComplaints
WHERE (((qryAllComplaints.Year)=[Enter Year]));

EDIT
When using all fields from a table, this is usually easier

SELECT qryAllComplaints.*
FROM qryAllComplaints
WHERE (((qryAllComplaints.Year)=[Enter Year]));

EDIT 2 - you shouldn't need the union query if you follow that advice.

[templeowls]

So basically, the union query feeds into a chart in a report. The chart's x values are the years and it basically shows how many complaints we've had this year, how many we've had last year at this time, two years ago at this time, etc.

My real goal tbh is to just cut down 11 queries (1 for each year, going back 10 years + the union query) into one if possible.

[Micron]

My real goal tbh is to just cut down 11 queries (1 for each year, going back 10 years + the union query) into one if possible.

As stated in post 2, use a form to provide options for single year and/or inputs for same. I might use a combo whose row source is based on a query that returns only year values from your table in order to pick one year. Perhaps add an "ALL" option at the top of the list so that all years can be returned if so desired. If you want to search on date ranges, you'll need either 2 combos or a start and end textbox for a date range. You might also want to use validation to ensure that user input values are valid year values and that start does not come after end chronologically speaking. It all depends on what you want to end up with, but for sure, get rid of the query per year thing.

[orange]

Perhaps you could mock up a sample chart or any graphic to show what you expect as output(s).
If you want details (eg ytd for several years), I think you'll need a query per year.

I agree with micron that a form with options to select the appropriate report/form is a fairly common approach.

[Micron]

Here's your modified db. I just cobbled together a sample to show that it can be done with zero queries in the db.
Open frmOpnComplaints - you'll see that complaints form command button is disabled, which eliminates the possibility that it can be clicked with no values entered anywhere.
Choose single year option and enter a value of "dog" and click button. Retry with 2020 value and click button again.
Choose Year range option and enter 2022 in 2nd textbox and click button again.
Try with begin year as 2024, end year as 2022 and you'll see that I didn't validate that end year comes after beginning year. There's only so much one wants to do without having had much prior feedback after feedback was sought.

Example22.zip

[xps35]

My real goal tbh is to just cut down 11 queries (1 for each year, going back 10 years + the union query) into one if possible.

SELECT * FROM tblComplaints WHERE Year([ComplaintDate])>=Year(Date())-10

[templeowls]

SELECT * FROM tblComplaints WHERE Year([ComplaintDate])>=Year(Date())-10

That didn't work. It's still showing ones that are beyond current YTD (i.e. 12/19/2020)

[Micron]

I don't know why that was posted since it simply goes back 10 years and returns everything after that, regardless of what year you run it.
The sample db was of no help?

[xps35]

That didn't work. It's still showing ones that are beyond current YTD (i.e. 12/19/2020)

As a Dutchman I am not that familiar with the term "YTD". I have delved into this and hope that my modified query now yields the desired result.

SELECT * FROM tblComplaints WHERE Format([ComplaintDate],"mmdd")<Format(Date(),"mmdd") AND Year([ComplaintDate])>=Year(Date())-10

[xps35]

I don't know why that was posted since it simply goes back 10 years and returns everything after that, regardless of what year you run it.

I don't understand how one could make a query that goes back ten years without considering the current year.
In my query I do look at the current year (WHERE Year([ComplaintDate])>=Year(Date())-10).

[Micron]

Unless I missed something, OP did not ask for a query that goes back 10 years, per se. There happens to be 10 years worth of queries at this time? So what happens after the 11th year using 10? You can't get all 11 years. Maybe I misunderstood the exact need but

to show complaints across all years

doesn't say "get only last 10 years" to me.

[xps35]

@Micron, this was in post #3

My real goal tbh is to just cut down 11 queries (1 for each year, going back 10 years + the union query) into one if possible.

[Micron]

Duly noted, and thank you.
I guess we'll have to wait and see exactly which half of the contradiction is the desired outcome, assuming it is either.

[templeowls]

Sorry, I know its odd. Please don't shoot the messenger lol. I inherited this DB and management very much likes the report that comes from this process.

So I have a DB of customer complaints dating back to 2011. I need a query to show complaints only up to today (July 11). I.e. it'd only show entries from 1.1.2011 to 7.11.2011 and 1.1.2012 to 7.11.2012 and 1.1.2013 to 7.13.2013 and so on......

Management likes this because it feeds into a very simple bar graph broken down by year. So they can look and say "oh at this point two years ago, we had x complaints as opposed to y this year"

As stated in my original post, the DB has 11 different queries to achieve this and I was really just hoping to limit this down to one just for simplicity sake. But I have no idea if that's possible.

Micron - I appreciate your sample but that's not what I'm looking for. I'm just looking for a query that is able to flow into a report. I don't need a form to filter things.