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)
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Steve,

Create one query to return the total count for the previous year.   Use the DateAdd() function to offset the start/end dates by one year.

Then construct a second query for the current year and add the first query as a "table".  Join tblPractices and the prior year query on the zone.

Jim.
Dale FyeOwner, Developing Solutions LLCCommented:
So, do you want this in one row, or two?  I think I would build this SQL string in vba in the Click Event of a button

Dim strSQL as string
Dim strStartDate as string, strStartPrev as string
Dim strEndDate as string,, strEndPrev as string

strStartDate = "#" & me.txtStartDate & "#"
strStartPrev = "#" & DateSerial(Year(me.txtStartDate) - 1, Month(me.txtStartDate), Day(me.txtStartDate)) & "#"
strEndDate = "#" & me.txtEndDate & "#"
strEndPrev = "#" & DateSerial(Year(me.txtEndDate) - 1, Month(me.txtEndDate), Day(me.txtEndDate)) & "#"

strSQL = "SELECT SUM(IIF([VisitDate] >= " & strStartPrev & " AND [VisitDate] <=" & strEndPrev & ", 1, 0) as Previous, " _
                          & "SUM(IIF([VisitDate] >= " & strStartDate & " AND [VisitDate] <=" & strEndDate & ", 1, 0) as Current " _
             & "FROM yourTable "
currentdb.querydefs("qryCountVisits").sQL = strsql
docmd.openquery "qryCountVisits"

Open in new window

Or, instead of opening the query, you could use DLOOKUP() to retrieve the values:

DLOOKUP("Previous", "qryCountVisits")
DLOOKUP("Current", "qryCountVisits")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.