Avatar of SteveL13
SteveL13Flag 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
SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo