If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Nested in-line Query
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Parameter query - nested queries | laura | Running & Setting Up Queries | 3 | February 10th, 2005 04:09 PM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM | |
nested pass thru query problem | Tcs | Running & Setting Up Queries | 1 | August 18th, 2004 08:38 PM |
Nested Query only returning lowest number | Nine Doors | Running & Setting Up Queries | 5 | July 22nd, 2004 05:56 PM |