troubleshooting Question

How create a complicated query

Avatar of SteveL13
SteveL13Flag for United States of America asked on
Microsoft Access
2 Comments2 Solutions76 ViewsLast Modified:
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)
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros