Re: Pass a parameter to nested query

From: laura (replyto_at_group.com)
Date: 02/20/05 

Date: Sun, 20 Feb 2005 09:21:55 -0000

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" <VanThien.Dinh@discussions.microsoft.com> wrote in message
news:u8jqgEiFFHA.560@TK2MSFTNGP15.phx.gbl...
> 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)
>
>
> "laura" <replyto@group.com> wrote in message
> news:edLtmFQFFHA.1524@TK2MSFTNGP09.phx.gbl...
>> 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
>>
>>
>>
>
>

Relevant Pages

  • Re: The Microsoft Jet database engine does not recognize...
    ... In the first query, ... Choose Parameters on the Query menu. ... Access opens a dialog. ... (tblGeoLoc INNER JOIN (tblLocPicker INNER ...
    (microsoft.public.access.queries)
  • Re: Database-Query and AutoFill??????
    ... first query, so it gets data from both source tables. ... rightmost one - leave no gaps) with formula, which will retrieve according ... In data range properties for first query, ... > values of coloumn 1 and 2. ...
    (microsoft.public.excel.misc)
  • Re: Repost - help to merge 2 queries
    ... this is what the final query looks like: ... The restriction on naming is due to the fact that Access SQL won't allow ... I want to run a single query that has inner joins across 2 tables:2 fields ... The first query pulls back the data I need: ...
    (microsoft.public.access.queries)
  • RE: Need advice on speeding query up
    ... Month functions actually make an index on that column worthless plus have to ... into the next (a Crosstab query), and then the second query is fed to a ... The first query is called qryCustomerAggregate, ...
    (microsoft.public.access.queries)
  • Re: Are Linq-SQL methods commutative
    ... Take make no sense without ordering, LINQ to SQL will create an order ... The first query: ... Will return the ordered set, while the second query: ... The reason for this is that in the first query, ...
    (microsoft.public.dotnet.languages.csharp)