adding a parameter to a query

[vicky464]

I have a query that uses a DMIN function to find the first time a company (org) registered and to capture that date. A subsequent query groups and counts all the data by months and displays the total. I have a report based on this and another similar query which finds the number of students.

How do I add a parameter to the query that will allow someone to select 2010 results vs 2011 and so on?

My two queries are based on a table, but I tried creating a simple query with all the fields of the table and using the parameter option in the query criteria. That didn't work. I understand it may be related to the DMIN function. Some of the tips I am seeing online point to creating a form for date input and linking the report to the form, but I am not sure how to go about this.

Alternately, and I might prefer this solution if it is easier for a novice, I wouldn't be opposed to hard coding the query/report to only show 2011 data. I would then create one for 2010 and one for 2011.

Here is the code for the first query

SELECT DISTINCT [tblCombined_Enrollment].studentorg, tblCombined_Enrollment.[EnrollDate]
FROM tblCombined_Enrollment
WHERE (((tblCombined_Enrollment.[EnrollDate])=DMin("EnrollDate","tblCombined_Enrollment","[studentorg] = '" & [studentorg] & "'"));




and the second:
SELECT Format([EnrollDate],"mm yyyy",0,0) AS [Month Period], Count([Firms Serviced].StudentOrg) AS [Firms Serviced]
FROM qryUniqueOrgCombined AS [Firms Serviced]
GROUP BY Format([EnrollDate],"mm yyyy",0,0)
HAVING (((Format([EnrollDate],"mm yyyy",0,0))<>False))
ORDER BY Format([EnrollDate],"mm yyyy",0,0);


Thank you so much for your help.
Vicky

[jzwp11]

Having separate queries where the only thing different is filtering by year is a waste of your time. You will have to create one for not only 2010 and 2011 but every year thereafter.

If you are trying to find the earliest enrolldate for an organization assuming that there are many records for each organization in the table, then this would be the appropriate query:


SELECT [tblCombined_Enrollment].studentorg, MIN(tblCombined_Enrollment.[EnrollDate]) as EarliestDate
FROM tblCombined_Enrollment
GROUP BY [tblCombined_Enrollment].studentorg


Could you please explain in a little more detail what type of data is stored in tblCombined_Enrollment? Can you please provide the field list for this table?

[vicky464]

This looks great, and it doesn't take as long to run as my query did I don't think, so it may be a bit more efficient. What about grouping by months, and then prompting for years? Any thoughts?

The fields in my tblCombinedEnrollment are - StudentOrg, Email, EnrollDate, TopLevelOrg, CurrentStatus, EventStatus.

Thanks a lot!

[jzwp11]

This looks great, and it doesn't take as long to run as my query did I don't think, so it may be a bit more efficient. What about grouping by months, and then prompting for years? Any thoughts?

Is the above a separate issue than the query that finds the min(enrolldate) for an organization?

If so, you have a couple of options. The easiest approach is to create a query that just gets the records of interest and include the parameter in the WHERE clause, then use another query to do the grouping

query to the records of interest

SELECT StudentOrg, Email, EnrollDate, TopLevelOrg, CurrentStatus, EventStatus
FROM tblCombinedEnrollment
WHERE year(enrolldate)= [Enter your year of interest]

The [Enter your year of interest] is the parameter.

As an alternative, you can use a form to supply the parameter and adjust the query to reference the control (textbox) on a form. You can use a button to execute the query (the command button wizard can be used to create everything you need)

SELECT StudentOrg, Email, EnrollDate, TopLevelOrg, CurrentStatus, EventStatus
FROM tblCombinedEnrollment
WHERE year(enrolldate)= forms!YourFormName!NameOfControlOnTheForm

Now for grouping, you can use the above query to supply the records you want to group. For example purposes, lets say we gave the above query a name called qryStudentDetail the grouping query might look like this

SELECT SELECT Format([EnrollDate],"mm yyyy",0,0) AS [Month Period], Count(qryStudentDetail.StudentOrg) AS [Firms Serviced]
FROM qryStudentDetail
GROUP BY Format([EnrollDate],"mm yyyy",0,0)

[vicky464]

Thanks this was terrific. It didn't solve it 100% and forced me to play with the SQL to get it to work, which was a great learning experience. I am very grateful! How do I mark the tread solved?

[jzwp11]

I believe there is a tool in the frame (upper right corner I think) of your post that you can use to mark a thread as solved.

Good luck with your project.