Parameter & Subqueries
Mar 24, 2006
Hi, I hope someone can help me out...
I've made a bunch of queries to get the data I need out of my table, they build on each other and I find that my date query is used twice. In date query I prompt for the date, when I run the percent query I get prompted twice.
How can I set this up to only prompt the user once for the date?
Here is roughly how my queries are dependent on each other
'percent query'
/
'Average query' 'date query'
/
'subtract query' 'record count query'
/
'sum query' 'date query'
View Replies
ADVERTISEMENT
May 12, 2005
I have a form with a drop down menu of people to filter a report of projects with the managers and up to 3 assistant managers. When I choose someone from the dropdown menu, I want all of their projects to come up on the report. Currently, only the projects that person is managing come up, not the ones they are assistant managing.
In my query that is powering this report, I have joined the manager_id number in table A to an id_num field in table B. To make the assistant managers come up in the report, I need to join the assist1_id, assist 2_id, and assist3_id to id_num also. When I join assist1_id to id_num, I get the following error:
The SQL statement couldn't be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in the SQL statement.
The way I would like to fix this problem is by creating subqueries in the SQL view, but I don’t know how to code it. If you know how or if you know a better way of doing this, please help! :)
View 1 Replies
View Related
Jul 15, 2005
How many subqueries can I put into one SQL statement? I'm trying to run a query that has two subqueries, but it's not working. It works when I create and reference one of the queries separately, but when I put them directly into the statement as subqueries, it gives me a syntax error. It would really save loads of time to have this in one statement. Can someone tell me what I'm doing wrong with this query or tell me if this is possible to use the AS identifier more than once in a query?
qryTwo: WORKS!
SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntityDetail.fldValue, tblEntityDetail.fldDate
FROM tblEntityDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, Max(tblEntityDetail.fldDate) AS MaxOffldDate, tblEntity.fldInactive, tblEntity.fldTypeID
FROM tblEntity LEFT JOIN tblEntityDetail ON tblEntity.fldEntityID = tblEntityDetail.fldEntityID
GROUP BY tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntity.fldInactive, tblEntity.fldTypeID
HAVING (((tblEntity.fldInactive)=False) AND ((tblEntity.fldTypeID)=2))]. AS qryOne ON (tblEntityDetail.fldDate = qryOne.MaxOffldDate) AND (tblEntityDetail.fldDetailID = qryOne.fldDetailID) AND (tblEntityDetail.fldEntityID = qryOne.fldEntityID);
qryThree with separate reference to qryTwo: WORKS!
SELECT qryTwo.fldEntityID
FROM tblDetail INNER JOIN qryTwo ON tblDetail.fldDetailID = qryTwo.fldDetailID
GROUP BY qryTwo.fldEntityID
qryThree without separate reference to qryTwo: DOESN'T WORK!
SELECT qryTwo.fldEntityID
FROM tblDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntityDetail.fldValue, tblEntityDetail.fldDate
FROM tblEntityDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, Max(tblEntityDetail.fldDate) AS MaxOffldDate, tblEntity.fldInactive, tblEntity.fldTypeID
FROM tblEntity LEFT JOIN tblEntityDetail ON tblEntity.fldEntityID = tblEntityDetail.fldEntityID
GROUP BY tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntity.fldInactive, tblEntity.fldTypeID
HAVING (((tblEntity.fldInactive)=False) AND ((tblEntity.fldTypeID)=2))]. AS qryOne ON (tblEntityDetail.fldDate = qryOne.MaxOffldDate) AND (tblEntityDetail.fldDetailID = qryOne.fldDetailID) AND (tblEntityDetail.fldEntityID = qryOne.fldEntityID)]. AS qryTwo ON tblDetail.fldDetailID = qryTwo.fldDetailID
GROUP BY qryTwo.fldEntityID
Hopefully I am wording this correctly??? If anyone can help me figure this out, I would greatly appreciate it. Thanks!!
View 6 Replies
View Related
Oct 5, 2006
Hello,
I am having a problem getting the expected results from a SQL subquery. The point of the query is to return all users and user titles that have not been entered into the database for the current month (not in tblHours). Some users have 2 titles and some just one title. Each month we enter hours worked per title so we can track labor progress. The problem lies w/ the individuals w/ 2 titles. The SQL only keys on the user id, but I need to return both user id and title. The variables called iMonth and iYear are passed from the form and the query is executed via a command button. qryTitlesPerUser contains each users title, name, and user id.
Here is the query:
strSQL = "SELECT qryTitlesPerUser.Name, qryTitlesPerUser.title FROM qryTitlesPerUser "
strSQL = strSQL & "WHERE qryTitlesPerUser.UserID AND qryTitlesPerUser.title NOT IN "
strSQL = strSQL & "(SELECT tblHours.UserID, tblHours.title FROM tblHours "
strSQL = strSQL & "WHERE tblHours.month = " & iMonth & " AND tblHours.year = " & iYear & ") "
strSQL = strSQL & "ORDER BY qryTitlesPerUser.Name"
Any help would be greatly appreciated.
Ken
View 7 Replies
View Related
Mar 8, 2008
I've looked at so many options for writing this query that I can't see the forest for the trees and hoping you kind folks will point me in the right direction.
I have a table called services that includes:
service_id
prop_id
service_year (ie: 2007, 2008)
service_date (ie: 3/3/08)
service_id is a unique ID for each service which relates to data I will need to pull from another table.
prop_id relates to a property ID in another table, not unique as multiple services against one property.
The problem that I have is that each prop_id can have more than one service_date for the same service_year. I need to be able to find the latest service_date on a prop_id and its associated service_year and service_id.
Any pointers would be greatly appreciated. BTW, using this in Access 2003 & 2007
View 14 Replies
View Related
Dec 19, 2006
Short version of question:
My research has shown that Access will not allow edits through a form that 1) has subqueries in its SELECT clause, or 2) uses aggregation such as First(). Is there any tricky way around this?
Long version of question:
I have a database form that shows an overview of orders for products (it's based on a query that pulls all current orders from the big table). I would like to handle both of these on each row:
Show where we built it last time, and
Allow the user to select where it will be built this time.
The problem is that I cannot figure a way to change the form/query such that it doesn't aggregate to find out where we built it last time, or doesn't rely on subqueries that do just that.
But I feel like there should be some way to do it--since each row displayed does rely on one and only one record in the complete table of orders. Which obviously is the record I want to update.
Is there something I can do that will accomplish this? I really do not want to have to make the user open up another form to see last time or choose this time.
Thanks in advance!!
View 3 Replies
View Related
Apr 17, 2014
Some essential background first. I have a Balances table which records balances by date. I also have an Issues table where problems are logged. There is a one-to-many relationship between Balances and Issues (i.e. each Balance can have multiple Issues). I also have a Comments table where updates for each Issue are recorded. There is again a one-to-many relationship between Issues and Comments (i.e. each Issue can have multiple Comments)
There are two key date fields in the Issues table :FlagDate (the date an Issue was flagged by a user for investigation)
ResolveDate (the date said investigation was brought to a conclusion)
There is also a date field in the Comments table :UpdatedWhen (the date any given comment was added)
So the basic flow is that an Issue gets flagged (FlagDate), then various comments are added (multiple UpdatedWhen's) and finally the Issue gets resolved (ResolveDate)
I need to incorporate a trend graph which will show the counts ofNew (i.e. new issues flagged as of each day) Cleared (i.e. issues resolved each day)
Updated (i.e. issues not yet resolved but updated each day)
Unchanged (i.e. issues not yet resolved and not updated each day)
Outstanding (i.e. all unresolved issues as of each day)
This is the SQL I've put together to get that table of information on which to base my chart :
Code:
SELECT [tblBalances].[BalanceDate] AS AsOfDate,
(SELECT COUNT([tblIssues].[IssueID])
FROM [tblIssues]
WHERE [tblIssues].[Flag] = True
AND [tblIssues].[FlagDate] = [tblBalances].[BalanceDate]) AS New,
[Code] .....
The subqueries for 'New', 'Cleared' and 'Outstanding' work perfectly; the resultant dataset gives me one record for each date in the Balance table and correctly counts the number of issues falling into each of those buckets.
The problem I have is with the 'Updated' bucket. If a flagged issue happens to be updated twice on the same day (which is perfectly acceptable), it counts this twice as well. I don't want this as I just want to know how many issues were updated on any given day - not how many updates there were.
I tried using COUNT(DISTINCT) in the 'Updated' subquery but it gives me a syntax error - on further research, I don't think it's possible to use the DISTINCT keyword in a COUNT subquery (at least not easily)
I also tried grouping by IssueID within that 'Updated' subquery but it still gives me the duplicate count within the same IssueID (and returns nulls rather than zeroes for those days where no updates occured)
I think I need to add a subquery within the subquery () to only return the latest comment as of the date in question - something along the lines of :
Code:
(SELECT TOP 1 [tblComments].[UpdatedWhen]
FROM [tblComments]
WHERE [tblComments].[IssueID] = [tblIssues].[IssueID]
AND DateValue([tblComments].[UpdatedWhen]) <= [tblBalances].[BalanceDate]
ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedWhen
But how to do this, nor if it is even feasible in Access to begin with.
View 2 Replies
View Related
Jul 12, 2005
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
View 2 Replies
View Related
Nov 9, 2006
Hi,
I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!
Here is my code (abbreviated slightly):
SELECT DISTINCTROW C1.*, C2.*
FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct]
WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);
I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.
So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!
Help! :confused:
P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.
View 2 Replies
View Related
May 28, 2005
Hi all,
Can i populate a parameter just like i populate a combo box?
I want to have a FIXED value for parameter. May I know how to do this.
Thank you
View 3 Replies
View Related
Jul 31, 2007
Hi,
I am creating a database for work and I keep getting a message that pops up when I opn my form that says "tblClientBFinfo.ClosedReason". Then there is a place to enter a value and and it says "ENTER PARAMETER VALUE." I know it is a problem with one of my tables, specifically the "closed reason" field in the table. But I odn't know why it is asking me to enter a parameter value. Could anyone offer help?
Thanks!
View 3 Replies
View Related
Jul 6, 2005
I need a new parameter in my query. When I open the query i need the parameter to be asked in a combo box or drop down box or any way to select the value from the groupo other than typing.
View 1 Replies
View Related
Jul 6, 2005
I need a new parameter in my query which enables me to select from a field in the table rather than typing .
View 1 Replies
View Related
Feb 9, 2006
I want to run a query automatically in the gotfocus event of a field within a form.
Prior to the gotfocus, a value is entered in an unbound text box on the form and I want to use that value for the maximum value in the parameters. I will set the minimum to zero.
My code errors in the where, it has no problem with the serial number but I can't seem to set the deliv_no to pull the value entered on the form.
SELECT Sum(VOLTEST.unc_del) AS SumOfunc_del
FROM VOLTEST
WHERE (((VOLTEST.SERIAL_NO)=[Forms]![largeVolume1]![serial]) AND ((VOLTEST.DELIV_NO)>0 And (VOLTEST.DELIV_NO)<=([Forms![largeVolume1]![No_del])));
I figure this is a syntax problem. Can you guys see where I have gone wrong?
I need the value to write to the table, not the form when this runs.
Thanks, GG
View 1 Replies
View Related
Aug 10, 2006
In my chart I have a yes/no field that I would like to set up a query to for. I want the user to be able to input yes or no and then the data be displayed. Whenever I enter yes or no when the prompt comes up, I get an error saying the expression is typed incorrectly or the data is too complex. How do I set it up so the user can select yes or no in some way? thanks
View 5 Replies
View Related
Feb 1, 2005
Hi all.I have a form that links to another form using the following macro "[homerid]=[Forms]![Homer Input]![homerid]" so when I click on form 2 the homerid from form 1 is automatically displayed. However on my switchboard I have a direct link to my Form 2 but when I click on this i get a box come up which is titled "enter parameter value" and then the subtitle is "[homerid]=[Forms]![Homer Input]![homerid]", this is fine, but I would like to change the subtitle to something i write myself such as "please enter homerid". Is their a way of doing this? Thanks.
View 2 Replies
View Related
Sep 22, 2006
Hi,
I have been programing SQL for almost 3 years now but, am a newbie with access. I have just added a field to a form used by employees to enter time and am getting a parameter request when the user clicks Add Entry. I placed a msgBox before the insert line and noticed the field data (type memo) is being sent by the insert query as an integer (without quotes). Any ideas how I might get the insert to pass the Comments field as a string?
Thanks,
~Leo
Private Sub AddTimesheet()
On Error GoTo Err_AddTimesheet
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
'
Set db = CurrentDb()
'
strSQL = "SELECT * FROM tblTimeCard "
strSQL = strSQL & " WHERE [EmpID] = " & Me.cboEmp
strSQL = strSQL & " AND [Category] = " & Me.cboCategory
strSQL = strSQL & " AND [Week] = " & Me.cboWeek & ";"
'
Set rst = db.OpenRecordset( _
strSQL, dbOpenDynaset, dbReadOnly)
'
With rst
If Not .EOF Then
MsgBox ("Time sheet for this employee, category, and week already exists. Please click the record at the bottom of the screen to edit.")
Exit Sub
End If
End With
strSQL = "INSERT INTO tblTimeCard (EmpId, Category, Week, Hours, Comments, CreateDt, CreateID) SELECT "
If IsNull(Me.cboEmp) Then
strSQL = strSQL & "null, "
Else
strSQL = strSQL & Trim(Me.cboEmp) & ", "
End If
If IsNull(Me.cboCategory) Then
strSQL = strSQL & "null, "
Else
strSQL = strSQL & Trim(Me.cboCategory) & ", "
End If
If IsNull(Me.cboWeek) Then
strSQL = strSQL & "null, "
Else
strSQL = strSQL & Trim(Me.cboWeek) & ", "
End If
If IsNull(Me.txtHrs) Then
strSQL = strSQL & "null, "
Else
strSQL = strSQL & Trim(Me.txtHrs) & ", "
End If
If IsNull(Me.txtComments) Then
strSQL = strSQL & "null, "
Else
strSQL = strSQL & Trim(Me.txtComments) & ", "
End If
strSQL = strSQL & "'" & Now() & "', "
strSQL = strSQL & "'" & Trim(Me.txtLogon) & "' "
'Before Insert
MsgBox (strSQL)
DoCmd.RunSQL strSQL
'After Insert
Me.Refresh
Exit_AddTimesheet:
Exit Sub
Err_AddTimesheet:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_AddTimesheet
End Sub
View 5 Replies
View Related
Oct 29, 2004
Hello,
I do not know what I did but now when I open my database it asks me over and over again to "enter parameter value" and it won't let me open the database like normal. Please help!
Katie
View 3 Replies
View Related
Jun 15, 2005
Set Start & End Dates
I have a list box that lists the 52 week numbers of the year
week 1 being 26-03-2005 to 01-04-2005
week 2 being 02-04-2005 to 08-04-2005....etc.
My aim
By choosing a week no all the records within that date will be returned
I am trying to do away with having to type the start and end date each time a search is carried out
I need to be able to click on the week no and from that the results are shown. perhaps in another list box or something
Any thoughts on how to make this sort of thing work?
thanks
View 9 Replies
View Related
Jul 27, 2005
Hello,
I am designing a query as i have been advised in the following Quote:
Doc ManNow, you write a couple of queries. As a parameter to the queries, choose a date that will be your archiving cutoff or split point. Usually the last day of some month or quarter. Doesn't matter as long as you are consistent about it. The technical cutoff will be, in effect, 23:59:59 of the chosen date. The first record to be KEPT will be 00:00:00 of the next day in sequence.
Query #1 - Compute stock on hand as of your cutoff date. Create a single stock-on-hand transaction with the cutoff date. Put it in a temporary table until you need it. This will be a two-layer query. I.e. a query of a query.
Query #1A - a summation query grouped by item number for all transactions earlier than the archiving date so you can get the "inventory of X at archiving date"
Query #1B - an append query that draws from the summation query and feeds it to the temp table. The stock-on-hand record will have the archiving date
.
I am able to get "inventory of X at archiving date" and append the results to a temporary table but I can't see how to append the "archiving Date" data itself to the same table as the "Archiving date" is entered via parameter prompt? Any ideas?
View 2 Replies
View Related
Oct 25, 2005
Hi!
When I run a querry I have a parametr querry like this:
Between[FirstDate] and [LastDate]
I want to "catch" the FirstDate and LastDate from the parameter querry and place them in the head of the Report based on the same querry.
How do I do this????
View 1 Replies
View Related
Aug 18, 2006
Hi there
When using parameters from one form to the next I normally hide the form and then reference the parameter textboxes in the next form to the hidden form. Is there a better way of doing this as I saw threads here mentioning passing a parameter. How do I do this? Thanks!
Dave
View 3 Replies
View Related
Aug 23, 2006
How can you control SQL statement parameters with VBA? The SQL statement will not accept a variable.:confused:
View 1 Replies
View Related
Feb 28, 2008
I have a bunch of queries put in 1 macro. Of course, some queries have the same parameter [Enter current monthend] so when I run a macro, there are few popups asking to [Enter current monthend]. It’s annoying for users to put in the same parameter 2 or 3 times for a report. Is there a way to avoid it?
I tried to make [Enter current monthend] as a new_field so the 2nd query could pick up new_field instead of [Enter current monthend] again but that gave me an error b/c the new_field automatically has ‘binary’ datatype instead of ‘date’ datatype. How can I fix this?
Thanks.
View 1 Replies
View Related
May 12, 2005
ok, im new to access, so please bear with me!
i ma building a database for my boss, and everything was going somewhat smoothly until i got to parameters in my queries...
i have a date parameter in one query that reads, "enter date to view"
that worked fine, but now, whenever i make another query, even if it uses different parameters, when i run the query, the "enter date to view" appears, followed by the real paramater...
how do i correct this?!?!
thanks!
View 4 Replies
View Related
Jun 8, 2005
I have a form and it is related with a query and by clicking ok I got the required results in Access 97. But now I have converted to Access 2002 and whenever i enter values in the form and click ok I get a Dialog Box "Enter Dialog Box". I read MKB article but it did not help me at all. Please help me out I am not getting any idea at all to solve this problem?
View 6 Replies
View Related
