L
laura
Hi, this is a little complicated... I am working on creating an ASP page
which uses an Access database (on the internet). In Access I created 3
nested queries to get the result I need, using the query design window. It
all works fine and I can even run the query (queries) from my ASP page.
The problem that I am having is that I need to filter the records in the
first query to a particular month and year that will be selected by the user
on the Internet.
How can I pass a parameter to the first query? i.e.,
Query 3 looks at Query 2 (Query 3 is the one that I run on the ASP page)
Query 2 looks at Query 1
Query 1 has the date field where I want to select a particular month and
year from the date field. I want it to default originally to current month
and year, which is easy if I put criteria =month(date) and = year(date), but
when the user then wants to look at different months/years.. how can I then
pass this to the query? I don't want to prompt them for the month and year -
these will be chosen from drop down selection boxes Jan-Dec and a list of
years.. 2005, 2006, 2007 etc.. It is these selections that I want to pass to
Query 1.
The second query, Query 2, is a Crosstab query... I will put the codes of
each at the bottom of this email.
Two questions..
1. Can I create a nested in line query which includes the Crosstab query
and/or
2. Can I somehow pass a parameter from my ASP code back to the first query,
Query 1 so that I can filter the dates.
SQL code from the Query Design Window:-
Query 1 qryEmplEvent
SELECT [tblAllDates].[day], [tblEmployee].[Fname], [tblEvent].[EventDate],
[tblEvent].[Event], [sname] & " " & [fname] AS fullname
FROM tblAllDates LEFT JOIN (tblEvent LEFT JOIN tblEmployee ON
[tblEvent].[EmployeeID]=[tblEmployee].[EmployeeID]) ON
[tblAllDates].[day]=[tblEvent].[EventDate]
WHERE (((Month([day]))=Month(Date())) AND ((Year([day]))=Year(Date())))
ORDER BY [tblAllDates].[day];
Query 2 qryEmplEvent_Crosstab
TRANSFORM First(qryEmplEvent.Event) AS FirstOfEvent
SELECT qryEmplEvent.fullname
FROM qryEmplEvent
GROUP BY qryEmplEvent.fullname
ORDER BY Format([day],"dd") & " " & Format([day],"ddd")
PIVOT Format([day],"dd") & " " & Format([day],"ddd");
Query 3 qryEmpEventFinal - this one simply eliminates a blank line
SELECT qryEmplEvent_Crosstab.*
FROM qryEmplEvent_Crosstab
WHERE (((qryEmplEvent_Crosstab.fullname)>""));
Laura TD
which uses an Access database (on the internet). In Access I created 3
nested queries to get the result I need, using the query design window. It
all works fine and I can even run the query (queries) from my ASP page.
The problem that I am having is that I need to filter the records in the
first query to a particular month and year that will be selected by the user
on the Internet.
How can I pass a parameter to the first query? i.e.,
Query 3 looks at Query 2 (Query 3 is the one that I run on the ASP page)
Query 2 looks at Query 1
Query 1 has the date field where I want to select a particular month and
year from the date field. I want it to default originally to current month
and year, which is easy if I put criteria =month(date) and = year(date), but
when the user then wants to look at different months/years.. how can I then
pass this to the query? I don't want to prompt them for the month and year -
these will be chosen from drop down selection boxes Jan-Dec and a list of
years.. 2005, 2006, 2007 etc.. It is these selections that I want to pass to
Query 1.
The second query, Query 2, is a Crosstab query... I will put the codes of
each at the bottom of this email.
Two questions..
1. Can I create a nested in line query which includes the Crosstab query
and/or
2. Can I somehow pass a parameter from my ASP code back to the first query,
Query 1 so that I can filter the dates.
SQL code from the Query Design Window:-
Query 1 qryEmplEvent
SELECT [tblAllDates].[day], [tblEmployee].[Fname], [tblEvent].[EventDate],
[tblEvent].[Event], [sname] & " " & [fname] AS fullname
FROM tblAllDates LEFT JOIN (tblEvent LEFT JOIN tblEmployee ON
[tblEvent].[EmployeeID]=[tblEmployee].[EmployeeID]) ON
[tblAllDates].[day]=[tblEvent].[EventDate]
WHERE (((Month([day]))=Month(Date())) AND ((Year([day]))=Year(Date())))
ORDER BY [tblAllDates].[day];
Query 2 qryEmplEvent_Crosstab
TRANSFORM First(qryEmplEvent.Event) AS FirstOfEvent
SELECT qryEmplEvent.fullname
FROM qryEmplEvent
GROUP BY qryEmplEvent.fullname
ORDER BY Format([day],"dd") & " " & Format([day],"ddd")
PIVOT Format([day],"dd") & " " & Format([day],"ddd");
Query 3 qryEmpEventFinal - this one simply eliminates a blank line
SELECT qryEmplEvent_Crosstab.*
FROM qryEmplEvent_Crosstab
WHERE (((qryEmplEvent_Crosstab.fullname)>""));
Laura TD