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
|
|||
|
|||
Parameter query - nested queries
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 |
#2
|
|||
|
|||
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 "laura" wrote in message ... 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 |
#3
|
|||
|
|||
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 "[MVP] S.Clark" wrote in message ... 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 "laura" wrote in message ... 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 |
#4
|
|||
|
|||
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 "[MVP] S.Clark" wrote in message ... 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 "laura" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to use a Access Query that as a parameter into Excel database query | Karen Middleton | Setting Up & Running Reports | 1 | December 13th, 2004 07:54 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Too Few Parameters error Mail Merge Access Parameter Query | Tony_VBACoder | Mailmerge | 3 | September 14th, 2004 12:15 PM |