I have written a subquery that works fine alone, but it returns -1 when with query.
The subquery is supposed to return a total of type currency.
Any help/suggestions will be very much appreciated.
SELECT tblSite.Name, tblPhase.Phase_No, tblVariation_Order.Customer_No, Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;) AS Expr1
FROM (tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No = tblVariation_Order.Customer_No) INNER JOIN (tblSite INNER JOIN (tblPhase INNER JOIN tblHouse ON tblPhase.Phase_No = tblHouse.Phase_No) ON tblSite.Site_No = tblPhase.Site_No) ON tblCustomer.Customer_No = tblHouse.Customer_No
WHERE (((Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;))=True));
We are developing a complaints tool. Each completed complaint needs to be signed off by 3 leads and I'm hoping to display the progress of this in a form. Obtaining the first is simple and I was able to do that relatively quickly. The subsequence ones are now giving me a headache as it doesn't seem I can reference the initial subquery field in the others.Here's what I have so far:
Code: SELECT COMPLAINT_TBL.COMPLAINTID, COMPLAINT_TBL.CASENUMBER, COMPLAINT_TBL.COMPLAINTANTFORENAME, COMPLAINT_TBL.COMPLAINTANTSURNAME, BUSINESSUNIT_TBL.BUSINESSUNIT, COMPLAINT_TBL.FINALRESPONSEDATE, (SELECT TOP 1 [SIGNOFF_TBL].[SIGNOFFDATE] FROM [SIGNOFF_TBL] WHERE [SIGNOFF_TBL].[COMPLAINTID] = COMPLAINT_TBL.COMPLAINTID ORDER BY [SIGNOFF_TBL].[SIGNOFFDATE] DESC) AS FIRSTSIGNOFF,
I have a database with sensitive data. I dont want users to change the data, so i used a snapshot query. However when i use this query in another query, the result is an updateable query.
I have a table of accounts and a table of rates. There is a one-to-many relationship between them (i.e. each account can have multiple rates, each with their own - unique - effective date)
I'm trying to build a query which will show me all of the accounts in the accounts table and the most recent rate (based on the effective date) for each of those accounts.
This is as far as I've gotten with the SQL :
Code: SELECT [tblAccounts].[AccountID], [tblAccounts].[AccountNumber], [tblAccounts].[AccountName], [LatestRate].[IntRate], [LatestRate].[EffectiveDate] FROM [tblAccounts] LEFT JOIN (SELECT TOP 1 [tblRates].[AccountID], [tblRates].[IntRate], [tblRates].[EffectiveDate] FROM [tblRates] ORDER BY [tblRates].[EffectiveDate] DESC) AS LatestRate ON [tblAccounts].[AccountID] = [LatestRate].[AccountID]
But this can't work because the [LatestRate] subquery can only ever return one record (i.e. the most recent rate across all of the accounts)
I need the most recent rate for each of the accounts in the main query
(FYI - I use an outer join as it is possible for no rate to be available in the rates table for a given account, in which case I want to return the null value rather than omit the account from the resulting dataset...)
I've got a number of different append and delete queries running on command on one of my forms, in which it makes a copy of all of the data on that form and included subforms, copies them into another table, and then deletes all of the data from that record. When this runs, I get an error saying "record deleted" and then another error message. Now it is deleting the records and it is moving them, however when i go onto one of the subforms, all the fields in the subform show "Deleted#" until i go back to the previous record or forward to the next, then they clear, but everytime it brings up the other error message it says "end or Debug" which I don't want it doing.
or by putting its sql into a string and executing using CurrentProject.Connection.Execute strSQL
i get an error message saying too few paramters - expected one
i'm fairly used to these messages which mean that in the query is a non existent or misspelt field and i find them by running the query in the query viewer and letting access highlight the missing / misspelt field
the weird part is that when i run this query as a stored object by clicking on its icon i get no error and the query does the job it is supposed to do
does anyonw have any idea why this situation could occur?
I have a query that checks a table where there's a field that only has numbers from 0 to 100 (a grade), let's call that field "average" (note, the values 0 to 100 are actual numeric values, not percentages)
here's the problem:
when I filter the query using a parameter like <[value] on the average field, the query does show the expected records that have an average value that is less than the value that I input when prompted... except that it also includes the records on which the average field is 100 ... ... for some uknown reason.
to clarify:it won't show anything over the imput value, it just shows anything under the value I imput (good) and anything that has an average of 100
when I hard code the value for the parameter say <65 the query gives me the results expected (anything less than 65 in the average field) without including records with average equal to 100
some details:
the average field has this code: Average: CInt(Nz([Grade]))
I have a query: qryNoSurgery with linked tables tblSurgery(many) to tblPatients(one) where tblSurgery!SurgeryDate criteria = Is Null. When I run the query by itself, it works perfectly. I get all the tblPatients data where SurgeryDate field is null.
I created a command button that opens a form, frmPatients, If I enter qryNoSurgery as the filter argument in the embedded macro and run it, I get a Msgbox which wants me to enter a parameter for qryNoSurgery!SugergyDate.
I have a Report that uses a query as it's record source. I have the query ordered by a field, which works as expected in the table view of the query. In the report, however, the ORDER BY clause does not seem to carry through. The field is not sorted Ascending. What's going on?
When running a query in Access 2013 or 2010 we get an ODBC call failed. However when we run just the form, which the query connects to, it works just fine.
Can anyone tell me what is wrong with this complex query? It works fine without the highlighted subquery. The subquery works fine on its own. But they don't work together.
I think that I might not be able to call multiple results from a subquery but I'm not sure about that. Does any one know???
Any help will be much appreciated! Thanks! select k.site_id, s.name, t.date_opened,
(select concat(c.name_first,' ', c.name_last) as User from sitepack_approval sa left join sitepack_approval_users spa on sa.approval_id=spa.approval_id left join contacts c on spa.user_id=c.contact_id where sa.site_id=k.site_id and spa.timestamp is null order by spa.level limit 1) as pending_approver,
(select sat.name from sitepack_approval sa left join sitepack_approval_types sat on sa.approval_type_id=sat.approval_type_id where sa.site_id=k.site_id order by sa.approval_id desc limit 1) as sitepack_type,
(select t.site_id, D.name AS Division, R.name AS Region, M.name AS Market, concat(C1.name_first, " ",C1.name_last) AS RD, concat(C2.name_first," ",C2.name_last) AS DD FROM stores as t LEFT JOIN regions as M ON t.region_id = M.region_id LEFT JOIN regions AS R ON M.parent_region_id = R.region_id LEFT JOIN regions AS D ON R.parent_region_id = D.region_id LEFT JOIN contacts AS C1 ON R.contact_id = C1.contact_id LEFT JOIN contacts AS C2 ON D.contact_id = C2.contact_id) as site, Divsn, Reg, Mkt, RDs, DDs,
concat(cd.name_first,' ',cd.name_last) as 'DRE'
FROM sitepacks k LEFT JOIN sites s on k.site_id=s.site_id LEFT JOIN stores t on k.site_id=t.site_id LEFT JOIN contacts cd on k.dre_contact_id=cd.contact_id WHERE status_id=(select status_id from sitepack_status where description='Approving' and sitepack_type <> 'Renewal') and t.status_code <> 'DEAD' and t.date_opened > now();
Hello I have had a bit of a stumbling block with a subquery for a Daily production report and I was hoping someone could advise me.
An initial query presents the information as the table below.
Date ProdNoFirstOfSerialNoLastOfSerialNo Qty 13/11/2006004006P064600254P06460028128 13/11/2006004006P064600282P06460030019 13/11/2006004105P064600301P0646003033 13/11/2006004006P064600304P06460031714 13/11/2006004006P064600318P06460034427
However what I need to have is the table actually look like this,
Date ProdNoFirstOfSerialNoLastOfSerialNo Qty 13/11/2006004006P064600254P06460030047 13/11/2006004105P064600301P0646003033 13/11/2006004006P064600304P06460034441
I had thought to try and make another query identify the change of product number to then set the first and last serialNo's but I have no idea on how to write the statments necessary. If anyone could offer some advice it would be very much appreciated.
I haven't used subqueries before, so I am not 100% on the best way to approach this.
The Scenario:
I have tables;
tblJobDetails tblMissingKit tblSiteDeficiencies
JobID is the primary key for tblJobDetails. tblMissingKit and tblSiteDeficiencies are both tables containing (funnily enough) Missing Kit and Site Deficiences on the Job's (JobIDs) in tblJobDetails. The problem is, there can be more than 1 amount of missing Kit per JobID and more than 1 site deficiency per JobID.
In an ideal world, when I run this query, I would only get 1 record per JobID, but in a 'MissingKit' field, there would be a list of the missing kit for that site (new lines, comma seperated - doesn't matter) and then the same with Site Deficiencies.
Not sure if this is even doable, but thought I'd ask the people who know.
2) Can the parameter value be the value from another field in the query?
In a nutshell, I want to write a query that returns these fields about some marbles:
Color of Marble Marble Stat1 Marble Stat2 Marble Stat3 Number of Marbles of this Color
i.e. desired query results
Blue Stat1 Stat2 Stat3 24 Red Stat1 Stat2 Stat3 12 Green Stat1 Stat2 Stat3 38
The query is fine except for the last field, Number of Marbles of this Color. This field is a subquery with a parameter that is the color of the marble. So if I run the subquery on its own and pass 'blue' as the parameter, it returns 24.
Here is the SQL for the subquery, qry_Count_Marbles:
SELECT Count([Marble_ID]) AS [Count] FROM tbl_Marble_Info WHERE (((tbl_Marble_Info.COLOR)=[Marble_Color]));
Marble_Color is a parameter, and If I enter 'blue' it returns 24.
In my main query, I am trying to pass the parameter for the subquery as the result of the first field. It's not working, but here is my SQL:
SELECT tbl_Marble_Guidelines.COLOR, tbl_Marble_Guidelines.LOW, tbl_Marble_Guidelines.HIGH, tbl_Marble_Guidelines.FUND, qry_Count_Marbles.Count(tbl_Marble_Guidelines.COLO R) FROM tbl_Marble_Guidelines, qry_Count_Marbles
I'd like the main query to not prompt the user for the parameter, but simply grab the parameter value from the value of the first field. Is what I'm trying to do possible? Is there a way to pass a SQL parameter within the SQL itself, and not prompt the user?
I am trying modify my forms so that I can display data like a tree view. I have a mainform and a subform. The source of the subform is a datasheet view of a query which has children linked to it. I have attached a snapshot view to this post. How do I access a field in the child in the subform (it is not a subsub form - it is a subquery within a subform)? (Field need to be accessed highlighted in black in th snapshot)
Ok at the moment, my sql statement works. However the return results is not arranged according to the earliest time. I've set DATATYPE for my Time as TEXT instead of DATE/TIME. This is because of other limitations in my forms.
Ok, that aside. This is how my SQL looks like:
Code:Select DISTINCT Time from Sample where Lot_No = 'BB55555' AND Product_Code='1234567'
Is there any way where by I can include Sample_id so that I can sort the Results using Order by Sample_id ASC?
i tried this but the DISTINCT doesnt work anymore.
Code:Select DISTINCT Time, Sample_id from Sample where Lot_No = 'BB55555' AND Product_Code='1234567' ORDER BY Sample_id ASC
well here's my problem, i've been working on this homework for about 3 days i got most of my queries work except for the 2 that use nested or subqueries, i have 2 tables http://img251.imageshack.us/img251/4623/untitledko5.png everytime i run the query i get this http://img251.imageshack.us/img251/7645/untitled2rq0.png i want the query to show me the costumers that have not made any orders, basically it should show me ID #6 no order,
when i use SELECT Costumer.ID, Costumer.fname, Costumer.lname FROM Costumer INNER JOIN [Order] ON Costumer.ID = Order.tblCostumer_ID WHERE (((Costumer.ID) Not In ([Order].[ID])));
it returns the above pic of a blank query
this is what my POS book says i should type:
SELECT Costumer.ID, Costumer.fname, Costumer.lname FROM Costumer WHERE ID NOT IN (SELECT ID FROM Order);
but everytime i try that i get a syntax error after the NOT IN.
i don't know what else to try if anyone could help me out it would be greatly appriciated.
i normally just go into design view in my query to change the format type, but some of my subqueries are quite large, so i'm getting 'the expression exceeds the 1,024 character limit for the query design grid.
i figured i could just multiply the subquery by 100, but the results are still calculated out to about ten decimal places.
so... how do i format a subquery directly in sql to be a percentage with two decimal places?
The following query works fine: SELECT tblProjectSteps.ProjectID, tblProjectSteps.StepID,..., NZ(DSum("Total","tblPayments","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0)+ NZ(DSum("Total","tblReceivables","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0)+ NZ(DSum("Total","tblBankPayments","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0) AS TotalCosts FROM tblProjectSteps INNER JOIN tblSteps ON tblProjectSteps.StepID = tblSteps.StepID;
The 3 summed tables (tblPayments,tblReceivables,tblBankPayments) do all of course have a foreign key for ProjectID (from tblProjects) and StepID (from tblSteps) and the output would be something like
For performance issues I would want to eliminate the DSum part, i.e get me a TotalCosts field by other means . I tried several constructions with multiple queries and also subqueries but to no avail. Any suggestions from the query Cracks her would be greatly appreciated.
Hi, we are currently working on a project to strip down one of our SQL Server based applications to an Access 2007 based solution, and I met this problem: the query just doesn't work on the new Access mdb. Can anyone take a look and give some help? Thanks a lot.
UPDATE A SET MaxPrice= B.MaxPrice FROM Summary A INNER JOIN (SELECT Name, MAX(DailyPrice) AS MaxPrice FROM Data GROUP BY NAME) B ON a.Name = B.Name
Is it possible to use a subquery as a default value property setting?
I need to use a value from a table that is not the focus table of the form, I have tried to identify it with a subquery but it returns a #name? error every time. I hate those!
the following query works pretty much well: SELECT teamid, teamname, (select count(winlose) from winlose where team.teamid=winlose.teamid and winlose='win') as win, (select count(winlose) from winlose where team.teamid=winlose.teamid and winlose='lose') as lose, (win*2)+(lose*1) as pts FROM team ORDER BY teamname
but the problem arises when i want to sort the report based on the "pts" which is the result of calculated two subqueries ("win" and "lose"), because i can't use the "ORDER BY pts" as access cannot order records by alias. can anybody find another sql solution to this?
theoritically i can just put all the records in an array and sort it by pts field, but sorting multidimensional with numerical and text array in asp kinda flakey at best, so i hope i can find the solution in the sql. thank you all in advance.
Code: (SELECT SUM(Cash) FROM qryAccountSetup AS T2 WHERE T2.ClientID = [qryAccountSetup].ClientID AND T2.MonthlyDate < [qryAccountSetup].MonthlyDate) AS PriorCash,
How do you change this to allow multiple values. I looked up subqueries online and it said to nest a second sub for the where clause....
Code: (SELECT (Cash) FROM qryAccountSetup AS T2 WHERE (Select (SELECT MAX(T3.MonthlyDate) FROM qryAccountSetup AS T3 WHERE T3.ClientID = qryAccountSetup.ClientID AND T3.MonthlyDate < qryAccountSetup.MonthlyDate)) AS PriorCash, But i keep getting error.
What i am trying to do is get the previous cash value.... For now, a good indicator is MonthlyDate, as only one date per client will exist. Something can only happen once in a day....IE.
ClientID..MonthlyDAte 1...........03/01/2013 1...........31/01/2013 1...........01/02/2013 1...........and so on.
There can be multiple dates, however the clientID would not match then.... Does this make sense? There is also a PositionID... This is an autonumber field in its respective table.. I have that so i can use it for domain purposes in the future.
This db I'm working on tracks the latest revisions of drawings. Multiple drawings fall under a work package, and multiple revisions of a drawing are present (listed 1, 2, 3, ...). I want to pull information about the latest revision of a drawing, including the drawing number, the tracking ID from the tbl_wkpkg_dwg_rev table, and the corresponding dwgTitle from tbl_dwg. The tables are joined through the field wkpkg.
I have my select statement partially working; it works on one table. This is what it returns: dwg1 3 id1 dwg2 1 id2 dwg3 5 id3
Here is the statement:
SELECT r.dwg, r.revision, r.trackingID FROM tbl_wkpkg_dwg_rev r, (SELECT MAX(revision) as maxrev, dwg FROM tbl_wkpkg_dwg_rev GROUP BY dwg) MaxResults WHERE r.dwg = MaxResults.dwg AND r.revision = MaxResults.maxrev;
So I would like to join it to tbl_dwg and get: dwg1 3 id1 DrawingTitle1 wkpkg1 dwg2 1 id2 DrawingTitle2 wkpkg1 dwg3 5 id3 DrawingTitle3 wkpkg2
However, when I try and join it with tbl_dwg to get the other information, it returns this: dwg1 3 id1 DrawingTitle1 wkpkg1 dwg1 3 id1 DrawingTitle1 wkpkg1 dwg1 3 id1 DrawingTitle1 wkpkg1 dwg1 3 id1 DrawingTitle1 wkpkg1 dwg2 1 id2 DrawingTitle2 wkpkg1 dwg2 1 id2 DrawingTitle2 wkpkg1 dwg2 1 id2 DrawingTitle2 wkpkg1 dwg2 1 id2 DrawingTitle2 wkpkg1 dwg3 5 id3 DrawingTitle3 wkpkg2 dwg3 5 id3 DrawingTitle3 wkpkg2 dwg3 5 id3 DrawingTitle3 wkpkg2 dwg3 5 id3 DrawingTitle3 wkpkg2 dwg3 5 id3 DrawingTitle3 wkpkg2
I tried using SELECT DISTINCT, with no luck. I'm out of ideas, so any help would be appreciated! :confused:
