Avatar of SteveL13
SteveL13
Flag for United States of America asked on

How create a complicated query

I'm trying to figure out how to create a query in query designer,  The data will come from a table named tblPractices.  I will have a form for the user to enter a start date and an end date, and select a Zone (one of the fields in the table) via a combobox on the form.  So the form will have 3 fields... txtStartDate, txtEndDate, and cboZone.

Once the three fields have been filled out I will have a command button that when clicked, will open the query.  The query is to present the count of "VisitDate" which is another field in the table in the date range entered.


But here's the catch...  If the user enters 1/1/2017 through 12/31/2017, I want the count to display BUT ALSO display the count of records from the previous year... 1/1/2016 through 12/31/2016.  In other words an annual count for both based on the Zone selected.

But if the user enters 10/1/2017 through 12/31/2017, I want the count to display BUT ALSO display the count of records from the previous year... 10/1/2016 through 12/31/2016.  In other words an quarterly count for both based on the Zone selected.


But if the user enters 11/1/2017 through 11/30/2017, I want the count to display BUT ALSO display the count of records from the previous year... 11/1/2016 through 11/30/2016.  In other words an monthly count for both based on the Zone selected.


How in the world do I develop this query?

(I hope I've explained this well)
Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23