Parameter query - nested queries

[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.

I could copy all three SQL statements into my ASP page and use two
variables, but I am not sure how to build nested queries doing that.

Thanks
Laura TD

 

[[MVP] S.Clark]

Here is an example of nesting queries in inline SQL
Select * From (Select * from (Select * from table name where [criteria])
where [criteria]) where [criteria])

You'll have to play with each of the criteria(or omit if not needed) to get
the right results.

 

[laura]

Many thanks, do you think it is the best option in the circumstances? I
realise it's difficult to tell without actually seeing the application, but
it's going to be a very big select statement (largely copied from the query
window) - I hope there is no limit to the size of a query. I think it will
give me the result I need - will give it a try - thank you.

Laura TD

Here is an example of nesting queries in inline SQL
Select * From (Select * from (Select * from table name where [criteria])
where [criteria]) where [criteria])

You'll have to play with each of the criteria(or omit if not needed) to
get the right results.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

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.

I could copy all three SQL statements into my ASP page and use two
variables, but I am not sure how to build nested queries doing that.

Thanks
Laura TD

 

[laura]

Hi,

OK.. I'm having a go at this.. but as I said, it seems a little complicated.
Maybe it would help if I added the code to this Post. The problem is that I
am using a Crosstab query as Query 2 (and I just cannot figure out how to
word this in the nesting query).

The last query (Query 3) just eliminates a blank line. I am working on a
Travel and Absence database - people enter their EmployeeID, date they are
away and location - fairly simple, but I wanted to display it on a grid with
the days of the month (one month at a time) at the top and the names of the
people on the left and the locations visited in the grid.

As I said, the whole thing works fine if I call Query 3 from my ASP page,
but the problem is in being able to use whatever month and year required in
the first query, Query 1. Am I going to be able to use the CrossTab query in
an inline SQL statement? Also, I'm not referring to tables, but to
queries... I am a little lost.


The SQL. Query 3 calls Query 2. Query 2 calls Query 1.

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

Here is an example of nesting queries in inline SQL
Select * From (Select * from (Select * from table name where [criteria])
where [criteria]) where [criteria])

You'll have to play with each of the criteria(or omit if not needed) to
get the right results.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

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.

I could copy all three SQL statements into my ASP page and use two
variables, but I am not sure how to build nested queries doing that.

Thanks
Laura TD