Pass a parameter to nested query

[laura]

Hello,

I've got two queries, one calls another. The first query has a criteria to
select month and year from a date field. The second query calls the first
query.

I want to be able to pass a parameter (a variable) through to the first
query when I call the second query - is this possible?

My first query is called qryEmpEvent and it has a date field where I want to
be able to pass to it a Month and Year. In the Query Design window I put
into the Date field a parameter called "theMonth" (integer) and "theYear"
(integer). If I run the query itself, naturally I get a pop-up box asking me
to put the month and year in which works, but is not what I want.. I want to
be able to pass the variable straight through when I call the second query
query which calls the first and is called qryEmpEventFinal.

My VB Code looks like this... how can I pass the parameter, or variable to
the first query?

stDocName = "qryEmpEventFinal"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Thanks
Laura TD

 

[MGFoster]

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

AFAIK, you can't pass a parameter in an OpenQuery method, you need to
use a Recordset. When you open the "final" query & assign the parameter
to the final query, the query that needs the parameter(s) will get them.
E.g. (DAO):

dim db as dao.database
dim rs as dao.recordset
dim qd as dao.querydef

set db = currentdb
set qd = db.querydefs("query_name")
qd(0) = "param1"
qd(1) = "param2"

set rs = qd.openrecordset()

If the query "query_name" uses other queries that have parameters,
assigning the parameters to the "query_name" will pass the parameters to
the nested queries.

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

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

iQA/AwUBQhT0DIechKqOuFEgEQLoQwCeLthdMdCJZUvzpXtOoEzvTzFhnLcAoLw4
Gkuh7JUnlNX4sfRriwKnrIYC
=Yoif
-----END PGP SIGNATURE-----

 

[[MVP] S.Clark]

You can make a form to hold the two parameter values. In the first query,
reference the fields in the Criteria.

There are other methods, but this is the least VBA intensive.

 

[laura]

Hi, I know I can use a form, but the problem is that I am doing it in such a
way that I cannot use a form in this instance. I need to be able to pass the
parameter through the nested queries to the first one (deepest level).

Laura

 

[Van T. Dinh]

In your code, I can't see where you pass the Parameter values?

Are the Parameter values (for the first Query) embedded in the SQL of the
second Query?

Please post the SQL Strings of both Queries with brief explanation of the
Table / Field / Query names.

 

[laura]

Hello,

I am trying to build a travel database for about 200 employees. defaulting
to the
current month and year showing a list of employees as row headings and the
days of the week/month "01 Tue", "02 Wed".. etc as column headings and
inside the grid, naturally, the locations they are travelling to.

I've gone about it in the only way I know how, which might be slightly more
complicated than it needs to be.

My tables:
tblEmployees - Names and Employee No.
tblEvents - Dates, locations, and Employee No. of each journey
taken
tblDays - A database that will be filled with 1 year's worth of
dates, i.e., one field, 365 records (01/01/05.. etc.) (this will increase to
having at least 2 year's worth of dates at any one time)

I created the tblDays so I could pick out whatever month the user wanted -
so that I could make a pivot table and alter them into column headings.

I then created 3 queries that are nested. The first query puts together the
tblDays,tblEvent and tblEmployee for the current month to begin with.
This is the query where I want to pick up the month's worth of days as I
definitely want my grid to show every day of the month regardless of whether
anyone travelled on that day or not - therefore I had to figure a way of
drawing in 1-28 days for Feb, 1-31 days for January, etc. It is this query
that will have the parameters.. the month and the year for the grid. I can
put in current month and current year and it works fine. But how can I pass
a parameter through to this without having it on a form, but perhaps rather
in the VB code.

The 2nd Query pivots the table so that the days become column headings -
this is fine, but I get one blank record which needs to be eliminated with
the 3rd Query.

SQL from the Query Design window as follows:

1. qryEmplEvent
SELECT tblAllDates.day, tblEvent.EventDate, tblEvent.Event, [sname] & " " &
[fname] AS fullname, Month([day]) AS Expr1, Year([day]) AS Expr2
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;

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");

3. qryEmplEventFinal
SELECT qryEmplEvent_Crosstab.*
FROM qryEmplEvent_Crosstab
WHERE (((qryEmplEvent_Crosstab.fullname)>""));

The third query eliminates the blank record which is created when I pivot
the table.. without it, I would lose some columns where there are no trips -
I need to show every day of the month.

I am hoping eventually to transport this to an ASP page which is why I need
to be able to pass the month and year parameters so people can choose
whichever they want to view, after the initial disply of current month and
year. This is why I need to be able to pass a parameter through to the first
query in VB code.

I'm still very new to Access and perhaps have gone a long way round to
produce something which should be more simple, I don't know.

Laura

 

[Van T. Dinh]

1. It seems to me the first SQL is fairly inefficient since the Month() and
the Year() function have to be executed for each Record in the tblDays (730
times each!)

It is much better to Field "Day" in this Table and use the BETWEEN ... AND
.... phrase to select the Record. Assuming you allows the user to select the
month of the current year, you can use something like:
....
WHERE [tblAllDates].[Day]
BETWEEN DateSerial(Year(Date()), [Enter Month (1-12):], 1)
AND DateSerial(Year(Date()), [Enter Month (1-12):] + 1, 0)

It will be even more efficient if you index the filed [Day] in the Table as
JET (the database engine) should be able to use the index to select the
Records.

2. I think the ORDER BY Clause in the second SQL is not correct. The
Column Headings are automatically sorted (alphabetically in this case). I
think it is more appropriate to sort by fullname, i.e.

.... ORDER BY qryEmplEvent.fullname

3. I think you can put the criteria in the 3rd Query into the second Query
and eliminate the 3rd Query. I am not sure whether a WHERE Clause or a
HAVING Clause is required here.

4. In fact, I believe (but haven't tested, though) you should be able to do
all this in one Query rather than 3. I may look complicated but if you read
and follow the Access Help topic "Create a select or crosstab query" (A2002
I am looking at) carefully, you should be able to do this in one Query.

5. If you can do 4, you can use MGFoster's method to resolve the
Parameters.

6. If you still need more than 1 Query, I would recommend using VBA code to
construct the SQL for "qryEmplEvent" and in the constructed SQL, replace the
Prameters with explicit value. You can access / modify the SQL of the Query
using the QueryDef object.

Check Access VB / DAO Help on the QueryDef object.

--
HTH
Van T. Dinh
MVP (Access)

Hello,

I am trying to build a travel database for about 200 employees. defaulting

to the
current month and year showing a list of employees as row headings and the
days of the week/month "01 Tue", "02 Wed".. etc as column headings and
inside the grid, naturally, the locations they are travelling to.

I've gone about it in the only way I know how, which might be slightly more
complicated than it needs to be.

My tables:
tblEmployees - Names and Employee No.
tblEvents - Dates, locations, and Employee No. of each journey
taken
tblDays - A database that will be filled with 1 year's worth of
dates, i.e., one field, 365 records (01/01/05.. etc.) (this will increase to
having at least 2 year's worth of dates at any one time)

I created the tblDays so I could pick out whatever month the user wanted -
so that I could make a pivot table and alter them into column headings.

I then created 3 queries that are nested. The first query puts together the
tblDays,tblEvent and tblEmployee for the current month to begin with.
This is the query where I want to pick up the month's worth of days as I
definitely want my grid to show every day of the month regardless of whether
anyone travelled on that day or not - therefore I had to figure a way of
drawing in 1-28 days for Feb, 1-31 days for January, etc. It is this query
that will have the parameters.. the month and the year for the grid. I can
put in current month and current year and it works fine. But how can I pass
a parameter through to this without having it on a form, but perhaps rather
in the VB code.

The 2nd Query pivots the table so that the days become column headings -
this is fine, but I get one blank record which needs to be eliminated with
the 3rd Query.

SQL from the Query Design window as follows:

1. qryEmplEvent
SELECT tblAllDates.day, tblEvent.EventDate, tblEvent.Event, [sname] & " " &
[fname] AS fullname, Month([day]) AS Expr1, Year([day]) AS Expr2
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;

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");

3. qryEmplEventFinal
SELECT qryEmplEvent_Crosstab.*
FROM qryEmplEvent_Crosstab
WHERE (((qryEmplEvent_Crosstab.fullname)>""));

The third query eliminates the blank record which is created when I pivot
the table.. without it, I would lose some columns where there are no trips -
I need to show every day of the month.

I am hoping eventually to transport this to an ASP page which is why I need
to be able to pass the month and year parameters so people can choose
whichever they want to view, after the initial disply of current month and
year. This is why I need to be able to pass a parameter through to the first
query in VB code.

I'm still very new to Access and perhaps have gone a long way round to
produce something which should be more simple, I don't know.

Laura

In your code, I can't see where you pass the Parameter values?

Are the Parameter values (for the first Query) embedded in the SQL of the
second Query?

Please post the SQL Strings of both Queries with brief explanation of the
Table / Field / Query names.

--
HTH
Van T. Dinh
MVP (Access)


want
to asking
me want
to