Creating A Variable That Stores A Whole Subquery
Oct 7, 2013
Does access have WITH subquery, For example creating a variable that stores a whole subquery such as:
Code:
WITH variableTEST (a, b, c, d, e) as
(SELECT a1, a2, a3, max(ab), count(*) as nbids
FROM A x natural join B y
WHERE x.something > y.something
GROUP by a1, a2, a3)
WITH variableTWO (f, g, h, i, j, k, j) as
(SELECT A.bidder, W.a, W.b, W.c, W.d, W.e, A.something
FROM variableTEST W natural join B A
WHERE W.sprice = B.bidprice)
SELECT h FROM variableTWO WHERE now - j <7
Can it do something like this, if not what alternative do they have to do something similar? Using WITH and subquery and variable, and storing variables and then later use it again in another subquery, can it do something like this in access SQL?
View Replies
ADVERTISEMENT
Dec 3, 2013
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,
[code]....
View 6 Replies
View Related
Nov 2, 2006
Hello,
I need to create a global varibale for parameter query, only once I can change that value and run instead of everytime entering it, that way I won't enter the wrong value, is there a way to do that...
Thanks
View 4 Replies
View Related
Oct 9, 2006
first time poster, please be kind.
I am currently updating our supply stores database in work. When an item becomes unserviceable we need to attach a form to the item before sending it away. i would like the data entered into the 'unserviceable' table within my database to be transposed onto an excel spreadsheet (see attachment) and then automatically printed out with the data in the relevant boxes. Can anyone tell me how this is done please? My knowledge of access is rather limited and I am teaching myself as I go along.
View 1 Replies
View Related
Sep 18, 2005
Hella all,
I'm new to this forum :) so I say hello to all of you :)
I have a little problem..
I have a table wich stores information wich comes from a combobox.
The problem is that it saves the combobox ID instead of the text.
This is the row source of the combobox
SELECT rapporteur.ID, rapporteur.rapporteur FROM rapporteur;
Could somebody tell me how I can store the rapporteur.rapporteur in a other table instead of the rapporteur.ID?
thx a ton in advance
View 7 Replies
View Related
Sep 18, 2005
Hi, I'm new here also. I'm afraid I can't help with your question, but I have 2 questions of my own.
1) how do you start a new thread (as you have done?)
2) in my database I have thousands of contacts. How can I add an alphabetical index tab along the right edge such as in Outlook for one-click access to different parts of the database?
Thanks and I hope you can help!
Gary
View 2 Replies
View Related
May 23, 2006
Hi,
I am using group options and they store a number instead of the vaules you typed in, is there anyway of making a group option that will store values and not numbers?
View 9 Replies
View Related
Oct 6, 2006
I have a form which is associated with a table. When I put some values in the fields which are connected to the database and try to close the form, it gives me an error that "Can't insert record the primary key might be duplicated"...! and then it closes.
Does the form store a record in the table while closing??
If so is there a way to prevent it??
Please reply..
View 3 Replies
View Related
May 24, 2006
Hi,
im after a option group that stores multiple values, the only way, long winded as it will be is to create a table with all the values then have tickboxes for all of them.
Is there a simpler cleaner way?
View 1 Replies
View Related
Dec 2, 2013
I am trying to count how many plans a retail chain has over its stores.
So
WeekNo | StoreNo | Product |PlanNo
1 | 10001 | Dog | A
1 | 10001 | Cat | B
1 | 20008 | Dog | B
1 | 30005 | Dog | B
Query/report to show
Product | PlanNo A | PlanNo B
Dog | 1 | 2
Cat | 0 | 1
Numbers in Query/report are a count of Stores selling the product
One of the issues is that the data is duplicated over the weeks, I'm thinking to use max on week number so It only looks at the last available data. And how to write the query.
Currently I'm doing this
PlanA: Sum(IIf([PlanNo]="A",1,0)
PlanB: Sum(IIf([PlanNo]="B",1,0)
.
.
.
But it looks at the whole of the table for all the weeks...
View 14 Replies
View Related
Apr 16, 2013
I am creating a 2 level report to confirm an order. Main report already created, runs successfully called as subform/subreport under "OrderDetails" form. Linked to master using Order.ID. There are two versions of the confirmation report that have different layouts for different program types.
The hangup comes when I try to add a "Class Dates" subreport. It lists dates of individual classes and Skip dates. I have created the subreport as "srClassDates". When I add it to the main report, it lists the records. However, when I try to link it to the Main report, an error message box appears with the "object variable or With block variable not set".
I have tried rebuilding both the main and subreports, rebuilt the query, have not found anything that changes the result.
Linker has been working successfully on other subforms. Report with groupings works fine, but I need data from 2 tables both linked to order.id.
View 2 Replies
View Related
Jul 8, 2013
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
View 14 Replies
View Related
Oct 3, 2014
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer
LR = 5
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wbRef = xlApp.Workbooks.Add
With wbRef
wbRef.Activate
.Worksheets("Sheet1").Activate
With ActiveSheet
.Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes
End With
end With
View 3 Replies
View Related
Feb 12, 2007
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();
View 1 Replies
View Related
Aug 20, 2014
I look at a lot of files to see when they were last updated. I wanted to write a generic procedure to manage that so ..
Code:
Public fDate As Variant
Public vField As String
Public vFile As String
'GTSdata
vField = "txt_gts_data"
[Code] ....
What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.
i.e. me. txt_gts_data = fDate
What actually happens is the variable vfield gets updated from "txt_gts_data" to 19/08/2014 then later code falls over because the fieldname is lost .
Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)
How do I say update the contents of the variable, not the variable itself?
View 7 Replies
View Related
Apr 15, 2015
Runtime error '91'
Running Access 2010.
I have two reports running off of the same crosstab query. I copied one report to make the second report, then modified the second report to change the background of column fields satisfying certain conditions. These lines of code were added to the Detail_Format section, in color below. The report with the extra code lines does not error--the original report errors.
Code:
Option Compare Database
' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 11
[Code] .....
If I say OK (rather than debug) after the error message, I can then click the button for the report again and it runs without complaint. And, as I said, the report with the added code never errors.
View 4 Replies
View Related
Nov 28, 2006
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.
Regards
Henson
View 3 Replies
View Related
May 15, 2007
Hi Chaps,
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.
Thanks in advance,
Taz
View 1 Replies
View Related
Jun 11, 2007
Hello,
I've got two questions:
1) Can you use a parameter in a subquery?
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?
Any ideas?
Thanks,
Scott
View 1 Replies
View Related
Nov 23, 2006
Hi,
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)
Thanks
PH
View 1 Replies
View Related
Jun 6, 2005
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
View 1 Replies
View Related
Oct 4, 2006
Hi,
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));
View 5 Replies
View Related
Jan 28, 2007
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.
View 3 Replies
View Related
May 22, 2007
hi all,
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?
thank you,
mj
View 2 Replies
View Related
Oct 25, 2007
Hi there,
THE GIVENS:
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
STEPID.....ROJECTID....TOTALCOSTS
1.............100.............1100
2.............100.............1200
3.............100...............900
4.............100.............1300
1.............200...............700
2.............200.............1400
and so on...
THE PROBLEM:
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.
Thanks
Jaime,
View 9 Replies
View Related
Apr 21, 2008
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
View 1 Replies
View Related