Queries :: Select Query To Gather Results Of Other Select Queries
May 11, 2014
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
View Replies
ADVERTISEMENT
Jun 20, 2013
I have a simple select query on a SQL table from Access. The query is:
SELECT tbl_Orders.OrderID, tbl_Orders.Approved
FROM tbl_Orders
WHERE (((tbl_Orders.Approved)=0) AND ((tbl_Orders.Completed)<>0))
ORDER BY tbl_Orders.OrderID;
The strange thing is that sometimes it pulls 34 results, and sometimes 38. From what I can tell, it should be pulling all 38.
What can I do to make sure it gets all the records?
View 2 Replies
View Related
Feb 5, 2015
I have a question where I need to effectively invert the results of a select query.
I have multiple tables in the database, but the 2 I'm looking at are TBL.Trip and TBL.TripDiary
A trip is logged in the trip table, and then an operator logs a diary entry against the trip. One of the options that the operator selects is when they receive an email back from the traveller, this is logged as a diary entry.
I need the query to look at the DIARY_Action field and select all records in the TBL.Trip that DO NOT CONTAIN an entry for Diary Action "4".
I can produce a select query that gives me the results to see all trips that HAVE had a response, with a simple IS LIKE "4" query in the action field.
If I change this to IS NOT LIKE "4", I get the results that I need, but duplication due to the various other "diary" entries in the table".
I simply need the inverse of the IS LIKE query but cannot see how to remove the duplicates?
View 5 Replies
View Related
Sep 23, 2014
Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
View 1 Replies
View Related
Mar 20, 2013
are you able to specify the location of another database that you want to select data from?
For example: I want to do something like this:
SELECT [field1], [field2] from C:DesktopFolderMyOtherDatabase.mdbTableName
View 4 Replies
View Related
Jul 10, 2013
Im trying to pass a list box variable in a select query. I understand you cannot pass a variable directly but have to pass it through a function. I may be wrong in this, but whatever I do I cannot get it to work. Here's my code:-
Public Sub GetEquipment()
List387.RowSourceType = "Table/Query"
List387.RowSource = "SELECT findequipstr() FROM Equipment"
End Sub
Public Function findequipstr() As String
If IsNull(List371.Value) Then GoTo function_end
findequipstr = List371.Value
function_end:
End Function
If I MsgBox(findequipstr()) within my Getequipment function, the variable is messaged,
View 3 Replies
View Related
Jul 28, 2014
After you "group by", is there a way to have the next field presented without a sum or avg etc.. if I know there is only ever one value to present?
View 1 Replies
View Related
May 3, 2014
I'm having a problem get a query to select all of the records it should be.
When I filter the source table (200_STANDARDIZED NRGL) to show the data I want to see (PC2 = 6000; GAAP = 02; CGL = 0950, 2735, 2736, 3500 and 3501; STD VENDOR NAME = blanks), I get 33 records.
NOTE: Had to take the PC2 records that were not '6000' out of the dB I've attached in order to be able to send dB bu the PC2 filter is needed in the complete dB.
When I create a select query to the do the same thing, I get either:3 records (when I set STD VEND NAME to Like '') or 30 records (when I set STD VEND NAME to NOT Like '*' ) I've attached the dB - Query 1 is the subject of bullet 1 above and Query 2 is the subject of bullet 2 above.
BTW, the three records that show up in Query 1 are the same records missing from Query 2.
View 3 Replies
View Related
Aug 14, 2015
I Want to Create an Query to Find Different Values in A Table/ Query
Like
Month Year Name School Post
Jan 2012 Ankur School 1 Post 1
Feb 2012 Ankur School 1 Post 1
Mar 2012 Ankur School 2 Post 1
Apr 2012 Ankur School 2 Post 2
May 2012 Ankur School 2 Post 2
June 2012 Ankur School 3 Post 2
Now i want in result of Query is only like
Month Year Name School Post
Jan 2012 Ankur School 1 Post 1 (Starting Ledger)
Mar 2012 Ankur School 2 Post 1 (Change in School)
Apr 2012 Ankur School 2 Post 2 ( Change in Post)
June 2012 Ankur School 3 Post 2 (Change in School)
View 1 Replies
View Related
Nov 15, 2013
How can set criteria in select query as Textbox1 or textbox 2 ...textbox 1 and textbox 2 are field on different forms.
View 4 Replies
View Related
Jun 5, 2013
I have form with a button on it that launches a parameter-based Select query (which served as the source for a report). I didn't have any validation measure in place, so if the User supplied a bogus value, a blank report was generated. While not technically an error, it would seem more polished to generate a warning message if the User supplies a bad query value and prompt them to re-enter.
Having read other posts along these lines, I've added an unbound text box to the form which the User fills in first before clicking the button. When the button is clicked, it executes code that uses the DCount function to make sure the text box value is in the source table, and if it is then it runs the parameter-based Select query. My question is how to pass the value in the text box to the query as a parameter. Below is a sanitized version of the code that I've generated so far.
Private Sub SingleItemRptB_Click()
If DCount("[FieldName]", "[Table]", "[FieldName]=[TextBoxValue]") = 0 Then
MsgBox "Item not in database. Please check value and re-enter."
Else
DoCmd.OpenQuery "SingleItemQ", acViewNormal, acReadOnly
End If
End Sub
View 4 Replies
View Related
Nov 27, 2013
I need to give the user the ability to select either all of a table say
[tblStores].StoreNo
or only those stores where [tblStores].StoreNo Is Null
I cant for the life of me get my query to accept Null or Is Not Null in the parameter box.
What do I need to do.The end game is to produce a subform to allow users to maintain tblStores
eg
tblStore
StoreNo | StoreNm
000001 | StoreA
<Empty>| StoreB
000003 | StoreC
Null returns
Store B
Is Not Null returns
StoreA
StoreB
StoreC
View 2 Replies
View Related
Apr 4, 2013
I have a Sales Table with below fields, i might have not set it up in the best way possible.
Consumer, Consumer_ID, SaleDate,Prod_Sl#,Prod_Type,Sale Amount
1. I need to update the sales price for each item sold based on category of Product Type, as we are tracking the product with Serial Number.
For an instance if 2 items of Category1 with Prod_Sl# as Sl1 and Sl2 and
2 items of Category 2 with Prod_Sl# as Sl3 and Sl4 are sold.
I need to update the sales price amount for these.
2. I want to accomplish this using a query.
View 1 Replies
View Related
Dec 24, 2013
How would you write a Select Query to select the most recent dates?
Select OrderDates
From Orders
Where >=Date()
No good if recent date is older than todays date!
View 3 Replies
View Related
Jun 5, 2014
I have a update statement as follows
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [PP TBL] SET [PP TBL].[GTIN] = '" & [UPC QRY]![PALLET GTIN] & "' " & _
"WHERE ((([PP TBL].[PP ID])='" & [Forms]![PP Edit FRM]![ID] & "'));"
DoCmd.SetWarnings True
Access is telling me it can't find the record and from what I have tested it seems to be the Update line, not the where line. I am basing the set portion as equals a query - could this be causing the problem? Or can code be based on a query?
View 4 Replies
View Related
Sep 19, 2013
I have a Union query as follows;
SELECT ALL *
FROM 1st_Lives
UNION SELECT ALL *
FROM 2nd_Lives;
1st Lives has 465,414 records and 2nd Lives has 151,852 records.
When I run the query I only get 604,976 records instead of 617,266 (I basically just want to add the two data items together).
View 1 Replies
View Related
May 21, 2013
I'm trying to create a query inside VBA code.
the problem is that my query is a select query and therefore I can't use RunSQL
I tried to work around it withbut had no luck... this is the code:
strSql = "SELECT '" & Me.number & "' ,Karin.[subject] " & "From Karin " & "WHERE '" & Me.number & "'" = done
View 12 Replies
View Related
Feb 5, 2014
I'm having a bit of a situation with a select query that I set up.
I have two tables, both with a list of serial ID's.
One is table field is formatted like this:
"B0340 13 453423 X"
The other is formatted like this:
"=B03401345342300"
I wanted a select query that could show me the records in a universal serial like this:
"034013453423"
This leaves out the "spaces", the "B" and the " X". (Couldn't find a way to make Access generate a new field with the serial ID correctly without a query).
So I used the "Mid" expression in a select query and it worked, it showed me the first table in the correct format.I wrote the same "Mid" expression for the other table and it worked too.But, when I ask for the results for both tables in the same query, it shows me much more records than I have(all duplicates). I figured out that when in table one I have 20 records, in table two I have 5 then the query shows 120 results (20 x 5).
This is the code in SQL view:
Code:
SELECT MainDatabase.[Transfusie Eenheid + Specification], (Mid([MainDatabase].[Transfusie Eenheid + Specification], 2, 4) & [MainDatabase].[Transfusie Eenheid + Specification], 7, 2) & [MainDatabase].[Transfusie Eenheid + Specification], 10, 6)) AS [BloedeenheidID MD], ScannedForms.BloedEenheidID, Mid[ScannedForms].[BloedeenheidID], 3, 12) AS [BloedEenheidID SF]
FROM MainDatabase, ScannedForms;
View 4 Replies
View Related
Jun 21, 2013
I need to select the second word from a product description. Here are two examples;
2156015 Dunlop SP-30
1756514 Goodyear Sport
I found the correct code on another website to do this in Excel;
=MID([field], FIND(" ",[field],1)+1, FIND(" ",[field],FIND(" ",[field],1)+1)-(FIND(" ",[field],FIND(" ",[field],1))))
It works a treat. In order for it to work in Access you apparently change FIND to Instr.
Now this worked for the person on the other forum but when I try it, it doesn't and I get the #Error returned.
Here is what I am using. Description is obviously the field I am selecting from;
TEST: Mid([Description],InStr(" ",[Description],1)+1,InStr(" ",[Description],InStr(" ",[Description],1)+1)-(InStr(" ",[Description],InStr(" ",[Description],1))))
View 9 Replies
View Related
Sep 19, 2014
I have built a database that shows the purchase of items that have serial numbers, and so are unique. The database shows the purchase oe each item, and subsequent sale, including "Date In" and Date Out".
Each "item" however has a unique transaction reference (Stock No.) I would like to be able to show what items are currently in stock, and therein lies my problem.I am able to use a select count to find all the instances where the stock number there are two stock numbers (ie In and then Out), but have been unable to find a way to filter the records in a query, to show the stock currently held.
only Single instances of the "Stock No." appear in a table, as that would show the current stock held.I have tried numerous ways to achieve this but I have reached a dead end. I am not experienced in writing with SQL.
View 14 Replies
View Related
Mar 17, 2013
I'm struggling with the correct syntax for this, but ideally what I want to achieve is:
SELECT (SELECT Count(Status) FROM P_T WHERE 'criteria are met') / (SELECT Count(Status) FROM P_T WHERE 'different criteria are met')
They are both fairly simplistic select statements, so I imagine this is not that difficult to achieve, but I'm not sure how to structure the query.
View 6 Replies
View Related
Jan 12, 2015
I created a form that has 3 multiple drop-down selections that is supposed to select records from [TableName_1] based on those selections. I initially created the query using the query designer with actual "hard coded" selections to make sure it worked. When it gave me the expected records, I changed the criteria from the "hard coded" option to the input form's control reference [Forms]![FormName]![ComboName]. I did this one at a time and tested after each change.
After the first change, I got exactly what I expected.
After the second change, it seemed okay. (note: I put the criteria on the first criteria line in the query designer, not on the second line)
After the third change, I get the dreaded: The expression is typed incorrectly or is too complex to be evaluated....
Here is the SQL:
SELECT M_Lending_Institution.InstitutionName, M_Lending_Institution.GeoRegionID, M_Lending_Institution.SpecialtyID, M_Lending_Institution.SBA
FROM (SELECT M_Lending_Institution.InstitutionName, M_Lending_Institution.GeoRegionID, M_Lending_Institution.SpecialtyID, M_Lending_Institution.SBA FROM M_Lending_Institution) AS LenderSearchQuery
WHERE (((LenderSearchQuery.GeoRegionID.Value)=[Forms]![LoanSearch]![CmbPrefGeo]) AND ((LenderSearchQuery.SpecialtyID)=[Forms]![LoanSearch]![CmbSpecialtyArea]) AND ((LenderSearchQuery.SBA)=[Forms]![LoanSearch]![CmbSBA]));
And once this is sorted out, yes I want to be able to allow for all records to be returned if any of those fields is NULL. I've read a LOT of posts around this subject, but I can't seem to find anything that answers my question without jumping into advanced VBA code.
View 14 Replies
View Related
Mar 1, 2015
I have three tables. TableA contains certain order information, such as the username of the employee, type of order, alias number, etc. TableB contains phone numbers, username of the employee, etc. TableC contains supervisor names and alias numbers.I didn't make these tables but that's what I'm working with. I connected TableA to TableB with the username of the employee, and then TableA to TableC using the alias numbers.
I have a report that summarizes how many orders of each type there are. It shows the supervisor, employee username, alias numbers and then each type of order with the amount of orders of each type under the headers.My boss wants to be able to click on the amount of that type of order and have it show him the details for those orders. This is where my problem is.
I was able to use an Inner Join type of query that allows me to filter the data by order type (however, I have to make a new query for each order type since I'm not too experienced on this). The problem is that I cannot find a way to have a query filter the data by order type AND employee username, so that it only displays the orders taken by a certain employee and it only contains one order type at a time, depending on which order type you clicked.
View 14 Replies
View Related
Mar 26, 2015
how to do to return a text for each row (as field value) when a table field contains "1" as value ?
for example i have a table named "products" with a field/column called "promotion". Sometime a product is promotional, so in this case, the "promo" column holds "1" as value.
during a select on products table, how can i do to return "in promotion" (e.g.) if the column "promo" holds "1" for a product ?
View 1 Replies
View Related
Dec 14, 2013
While I am not new to Access, I am not well versed in its abilities as far as combo boxes go. What I have is a Form where a combo box allows you to pick from a table records 'record ID #' in order to fill in the data of that record to the rest of the form.What I want to do is use a query to select specific records from this table and allow the combo box to show only these 'selected Record IDs' for user selection.
View 1 Replies
View Related
Jul 10, 2014
I need to find the frequency of client's appointment activity after their last appointment in a 4 month window.
My question is:
(1) Is my SQL going to give me the last time a client was in the office within the 4 month window?
(2) What will my SQL need to like to count frequency of appointments before and after window for each client?
(3) Can I do (2) in the same SQL shown above or will I need to make a new SQL statement?
Code:
SELECT tblTest.Client, Last(tblTest.AppointmentDate) AS LastOfAppointmentDate INTO tblTest
FROM tblTest
GROUP BY tblTest.Client
HAVING (((Last(tblTest.AppointmentDate))<#4/30/2014#))
ORDER BY Last(tblTest.AppointmentDate);
Details
Date Window I need to select the clients last appointment: Jan 1, 2014 - April 30, 201
Once the last appointment has been determined for each client in that window I need to count the frequency of appointments before and after that date.
View 14 Replies
View Related