Parameter Queries using dates

[Guest]

How do you set up a parameter query that will sort all study certificates
awarded to a student on a quarterly basis i.e 01/01/2005 to 31/03/2005 for
example, but will also ignore all certificates awarded after the first
certificate. So, if the first certificate awarded is dated 01/10/2004, it
will count in the quarter 01/10/2004 to 31/12/2004, but if the student gets
further certificates in the current quarter, these are ignored. Only first
certificates awarded are picked up by the query. I have a date awarded field
and a certificate level field (1,2,3,4). Students can get certificates in any
level order.

 

[MGFoster]

How do you set up a parameter query that will sort all study certificates
awarded to a student on a quarterly basis i.e 01/01/2005 to 31/03/2005 for
example, but will also ignore all certificates awarded after the first
certificate. So, if the first certificate awarded is dated 01/10/2004, it
will count in the quarter 01/10/2004 to 31/12/2004, but if the student gets
further certificates in the current quarter, these are ignored. Only first
certificates awarded are picked up by the query. I have a date awarded field
and a certificate level field (1,2,3,4). Students can get certificates in any
level order.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your criteria seems to say "Get the record of the first certificate
awarded per student. Sort the output by the award date's quarter."

Maybe this:

SELECT DatePart("q",award_date) As Qtr, student_id, certificate
FROM table_name as t
WHERE award_date = (SELECT MIN(award_date) FROM table_name
WHERE student_id = t.student_id
AND certificate IS NOT NULL)
ORDER BY 1

ORDER BY 1 means sort the 1st column in the SELECT clause in ascending
order.

certificate IS NOT NULL means a certificate number has been entered.
This assumes that the column 'certificate' has a numeric data type.

Substitute your column/table names where necessary.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi+K14echKqOuFEgEQJr6QCdGq2HOl2LyaGDft49hChhBdb92BQAoNiX
jqHTr+oSdmyK3Fl2mc+qekoj
=vU/B
-----END PGP SIGNATURE-----

 

[Guest]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your criteria seems to say "Get the record of the first certificate
awarded per student. Sort the output by the award date's quarter."

Maybe this:

SELECT DatePart("q",award_date) As Qtr, student_id, certificate
FROM table_name as t
WHERE award_date = (SELECT MIN(award_date) FROM table_name
WHERE student_id = t.student_id
AND certificate IS NOT NULL)
ORDER BY 1

ORDER BY 1 means sort the 1st column in the SELECT clause in ascending
order.

certificate IS NOT NULL means a certificate number has been entered.
This assumes that the column 'certificate' has a numeric data type.

Substitute your column/table names where necessary.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi+K14echKqOuFEgEQJr6QCdGq2HOl2LyaGDft49hChhBdb92BQAoNiX
jqHTr+oSdmyK3Fl2mc+qekoj
=vU/B
-----END PGP SIGNATURE-----
Thanks for the above, however, I am inexperienced in statements as above, so perhaps a more in depth description of the problem is needed. The Query I want to run will display records from 3 linked tables, Tb_Customers, Tb_Courses and Tb_Bookings. Fields selected for display from each table as follows:-

Tb_Customers: Surname, Forename and Customer ID(PK)
Tb_Courses: Course Number(PK), Course Title and Level
Tb_ Bookings: Date of Award(DD/MM/YYYY)
As you correctly surmised the query is Get the record of the first
certificate awarded per student. Sort the output by the award date's quarter.
A report will then be generated from this Query. This database is for a
learning centre funded by European EEC bodies who are only interested in how
well the centre is utilised by the local community, so only want to know when
an individual achieves their very first certificate.

 

[MGFoster]

:



Tb_Customers: Surname, Forename and Customer ID(PK)
Tb_Courses: Course Number(PK), Course Title and Level
Tb_ Bookings: Date of Award(DD/MM/YYYY)
As you correctly surmised the query is Get the record of the first
certificate awarded per student. Sort the output by the award date's quarter.
A report will then be generated from this Query. This database is for a
learning centre funded by European EEC bodies who are only interested in how
well the centre is utilised by the local community, so only want to know when
an individual achieves their very first certificate.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't indicate which columns are the "joining" columns between the
tables. I'll assume it is the [Customer ID]. But, logically, there
shouldn't be a Customer ID in the tb_Courses table. Therefore, I can't
put that table in the query. If you want me to do that you'll have to
supply all the columns of each table & indicate which are the "joining"
columns (the columns that relate one table to the other).

Try this - put it in a query's SQL view.

SELECT DatePart("q",B.[Date of Award]) As Qtr,
C.Surname, C.Forename, C.[Customer ID]

FROM (tb_Customers As C INNER JOIN tb_Bookings AS B
ON C.[Customer ID] = B.[Customer ID]

WHERE B.[Date of Award] =
(SELECT MIN([Date of Award])
FROM tb_Bookings
WHERE [Customer ID] = B.[Customer ID]
AND [Date of Award] IS NOT NULL)

ORDER BY 1

I'm using [Date of Award} IS NOT NULL to determine if the customer has
been awarded a certificate.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjCPuYechKqOuFEgEQKc2ACeKNNi2ScbCdLiVSpf7Ty2hqGAsXMAn0TA
Yj2OET6kElna9MQYzE0lufPe
=b7rQ
-----END PGP SIGNATURE-----