Hi,
I have two tables, one with attendance data recorded by day (mm/dd/yyyy) and another with certificates with a starting and an ending date (also mm/dd/yyyy).
i.e.
Attendance Data Table:
UserID -------- Date -------- Data
1 -------- 1/1/2020 -------- 1
1 -------- 1/2/2020 -------- 2
1 -------- 1/3/2020 -------- 1
1 -------- 1/4/2020 -------- 1
2 -------- 1/1/2020 -------- 1
2 -------- 1/2/2020 -------- 2
2 -------- 1/3/2020 -------- 2
2 -------- 1/4/2020 -------- 1
Certificate Table:
UserID --- Start -------------- End -------------- Cost
1 -------- 12/1/2019 -------- 1/31/2020 -------- $1.00
1 -------- 2/1/2020 -------- 5/31/2020 -------- $2.00
2 -------- 11/1/2019 -------- 4/15/2020 -------- $1.50
I am trying to build a query that uses both tables to search for results between the 1st and last day of a selected month. My thoughts are to have the user enter a month and year, and then build a 'between dates' criteria, but how do I build the criteria using the user entered data?
My existing crosstab query pulls the year from the daily attendance data using 'Year: Format([AttndDate],"yyyy")', monthly using 'Month: Format([AttndDate],"mm")' which I use as rows and 'Format([AttndDate],"d")' which I use as columns with the Data in the columns. This part is working well for me!
My end result that I think I should have is along the lines of ask the user the search parameters of [Year?] [Month?] which will pull the correct data from my crosstab table and also build a query that is 'Between [Month?]/01/[Year?] and [Month?]/31/[Year?]' for my certificates table, but I am so lost in how to build this query correctly!
My questions are:
How do I take user entered data of month, year (and convert that into mm/dd/yyyy) to build a dated criteria using it? How can I make sure the first and last day is correct as not every month ends on the 31st?
Many thanks,