Queries :: SELECT TOP 3 Returning More Records
Mar 24, 2015
My statement below is current returning 4 records. Two of the records have the same GBPAmount value.
RequestID is the Primary Key
Code:
SELECT TOP 3 RequestID, GBPAmount, Currency, RequestDate
FROM PayRequest
WHERE (((Currency)="CAD Canadian Dollar") AND ((RequestDate)>#11/16/2014#))
ORDER BY GBPAmount;
Code:
RequestID GBPAmount Currency RequestDate
10207 8.17 CAD Canadian Dollar 03/02/2015
9874 33.82 CAD Canadian Dollar 20/01/2015
11327 109.58 CAD Canadian Dollar 23/02/2015
10495 109.58 CAD Canadian Dollar 05/02/2015
View Replies
ADVERTISEMENT
Mar 30, 2006
Hello, I am sure this is quite simple, however I cannot figure it out. I have a query that shows tblPartNumber and tblAverage. The average is calculated in the query by taking the total minutes to assemble the parts, divided by the number of parts completed. What I am trying to do is run a query that will show me the most efficient (lowest average), time for each Partnumber. Ideally the query would show me something like this;
Part # Average
1234 .72
1234 .86
1234 .94
etc...
I searched on here and it seemed like the Top 10 is used, but in my application that ommits all of the other partnumbers because it only shows ten of the records for the first partnumber. I only want to see the five most efficient averages. Any help is greatly appreciated.
Thanks, Tim
View 3 Replies
View Related
Sep 18, 2014
I am building a select query which is grabbing data from multiple tables with items being linked by a unique field "Certificate_ID".
I have created joins between table A and Table B, and Table A and Table C, linking both by Certificate_ID
All have the join property set to select all Records from table A, but only those from Table B and Table C where the joined fields are equal.
Table A has 5000 records. Am I correct to assume that my query should only return a max of 5000 records as well? When I select Certificate_ID from A and another column from B it only gives me the 5000 unique records. When I add in a column from Table C it is however returning something like 7500 records, with several being duplicates with the same data in every column.
Why it is choosing to duplicate records and give me more than I want. I am sure I am overlooking something simple.
View 1 Replies
View Related
Feb 17, 2015
I have a sub report that is based on a query. The Where clause of my SQL is giving me a bit of hang up. What I am attempting to do is return the records that are the items used to test products we test. The ID's of the records are gathered in a public function. The function is called GetCal Equipment. This returns all of the requested numbers just fine. For example ID numbers 4, 112, 124, 138, 232, 338 are returned when I call the function. Any number of records can be returned at any point. This is the unique numbers used to identify the records. I thought by having the function return the numbers the records would come up. However when I use that in my where statement, none of the records are returned. The whole SQL is as follows:
Code:
SELECT CalibratedEquipmentListTable.ID, CalibratedEquipmentListTable.Manufacturer, CalibratedEquipmentListTable.ModelNo, CalibratedEquipmentListTable.Description, CalibratedEquipmentListTable.SerNo, CalibratedEquipmentListTable.LastCal, CalibratedEquipmentListTable.CalDue
FROM CalibratedEquipmentListTable
WHERE CalibratedEquipmentListTable.ID In (GetCalEquipment());
how to get the query for the report to return the records.
View 10 Replies
View Related
Mar 10, 2015
I have a query that I set up to return multiple records. The number of the records is in that text box. I have tried to set the where clause to the text box but for some reason I don't get any of the records I want returned. I am using a Where... IN statement. I have tried to put the numbers in single quotes and no quotes and I have had no luck at all. If I do individual records they will return, just not when I try and do more then one at a time. This query is a part of something more, but since it doesn't return anything, Here is my sql statement
Code:
SELECT CalibratedEquipmentListTable.ID, CalibratedEquipmentListTable.Manufacturer, CalibratedEquipmentListTable.ModelNo, CalibratedEquipmentListTable.Description, CalibratedEquipmentListTable.SerNo, CalibratedEquipmentListTable.LastCal, CalibratedEquipmentListTable.CalDue
FROM CalibratedEquipmentListTable
WHERE CalibratedEquipmentListTable.ID In ([Forms]![ReportForm]![Text41]);
View 6 Replies
View Related
Aug 7, 2015
I'm running a query from two related tables in the database.the table relationships and the query design. Instead of doing what I want it to do/what I think I'm asking it to - which is show the sum total number of weeks on the program for each student - it shows me the number of weeks for that student just for that claim, not the total for the student overall. I've added some 'dummy data' (2 records in the claim table relating to 1 student), and the query then returns the student twice in the results.
View 1 Replies
View Related
Oct 1, 2014
I have a simple nested query that is not working as expected. My inner query returns 102 records but when I run with outer query I only get 96 records. Below is my query, I don't really want to pull the same fields from both tables but I was doing to test. The values that are missing are those that don't exist with the monthenddate 8/31/2014 - a left join should fix that but doesn't seem to be working ..
Code:
Select distinct a.entity, a.gl_account,a.profit_center,[Open Items_1].profit_center,[Open Items_1].gl_account,[Open Items_1].entity
from(
SELECT DISTINCT [Open Items].entity, [Open Items].gl_account, [Open Items].profit_center
FROM [Open Items]
)a
left outer JOIN [Open Items] AS [Open Items_1] ON
(a.profit_center = [Open Items_1].profit_center) AND (a.gl_account = [Open Items_1].gl_account) AND (a.entity = [Open Items_1].entity)
Where ([Open Items_1].MonthEndDate=#8/31/2014#)
View 1 Replies
View Related
Oct 11, 2014
I have created a query that is designed to return a count of how many records there are in various tables. There are 10 expressions in all, so when it is run I am expecting to see one row of data with each field populated with the number of records.
It does do this, but the data is repeated over line after line (see the attached picture)
View 4 Replies
View Related
Nov 29, 2007
Hello!
My problem is the following:
My select works fine when I select 3 fields, but trying to select more than three, when I add two fields more, it doesn't give me the last two fields, being these two fields added lattely in the table...
I mean... I added two fields more to the table one it was created, and the script executed.
After that, if I try select * from table, the recordset does not have this two fields... only the old ones...
Any idea?
View 1 Replies
View Related
Nov 28, 2007
Hello!
I've good a very very strange problem...
I do an insert of a new row into the table TRA. That insert creates an id for the new row.
Right after that I do a select of the new row, trying to get the new id created.
Well, the insert is working fine, but the select sometimes gives me values, sometimes not.
How is it possible?? I've tryied closing the database and opening it again, but is not working anyway...
Any idea??
My code right now is the following: (closing the database, setting recordset to nothing... and still not working properly)
sql = "insert into tra (semana, empleadoid) values('" & semana & "'," & empleadoId & "); "
DoCmd.RunSQL (sql)
db.Close
Set db = OpenDatabase(database)
sql = ""
Set rcset = Nothing
sql = "select * from TRA where (semana='" & semana & "' and empleadoId =" & empleadoId & "; "
View 10 Replies
View Related
Sep 29, 2005
I have a problem that I haven't seen discussed here yet. (I know this is not good practice, but we're doing this for transfer to another application that wants and accepts data this way)
1) I have a multi select list box.
2) I have code that saves all the values in a semi colon delimeted list in a single field in the DB. (As I said, the application that this data is going to (and from) wants and accepts data in this way)
3) My problem is this. When I re-open my form, how do I get the values that are stored in the database to be highlighted in the multi select list box. I have code similar to this running, but still can't figure it out.
Dim lst() As String
rst.Open sSQL, cnn, adOpenStatic, adLockOptimistic
lst = Split(rst(0), vbCrLf, -1) 'Split the recordset into an array
For i = 0 To List96.ListCount
For j = 0 To UBound(lst) 'lopp until the upper bound of the array
'If the item in the list box = the item in the array then highlight the item in the list box
If List96.ItemData(i) = lst(j) Then
List96.ItemsSelected(i) = True
End If
Next j
Next i
In a nutshell, that's the problem. My list box contains a list of about 5 or 6 things, the user can select any number of them, and I store it in a single field in the DB semi colon delimited. However, I don't know how to return that field to my list box with the selected values highlighted. IE if the list contains A, B, C, D and the user selected A, C the field in the DB looks like this A;C. When I open the form again, I want to highlight A and C in my list box since those were the fields the user selected.
What happens is A;C gets passed to another application (which wants it that way). However, in that other application those values may or may not change, and then they will be passed back to me. (IE the other application may change those values to A;B;D and pass it back to me (which I accept no problem). However, when the user opens the form I want A B and D to be highlighted in the multi select list box
Any help is much appreciated
Robbo
View 4 Replies
View Related
Jan 13, 2006
I am trying to select all projects with the keyword "sett" in them. About a thousand rows with this keyword exist but why isn't my query returning any data? :confused:
Here's an example of the data "STPT-SETT-NY-EQ Legacy"
Now i want to return all rows with "SETT".
What am i doing wrong here please :)
SELECT projectList.*
FROM projectList
WHERE (((projectList.projectName) Like "%SETT%"));
this doesn't work either. No records are returned when query is run:
SELECT projectList.*
FROM projectList
WHERE (((projectList.projectName)="%SETT%"));
View 2 Replies
View Related
Sep 12, 2012
I have a db with 2 tables, one containing basic info FirstName, Surname, YearGroup, and another table with other data. I imported a list of names into the first table, firstname and surname only were imported, other field was left empty, other table was also left empty. I made a simple select query to pull all records from both tables and i get no results at all. None of the imported names show on the query results. what I am missing?
View 2 Replies
View Related
May 2, 2014
Why is strCreditCheck returning the actual query string and not the result? Of course, this is not working.
Code:
Private Sub b_PrintWorkOrder_Click()
Me.Requery
Dim strCreditCheck As String
Dim strCompany_id As Integer
Dim stlinkCriteria As String
[code]....
View 6 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
Mar 12, 2015
I would like to select records based on Minimum values of specific vendor.
Example:
MasterItemID Price PriceDate VendorID
1 2 2/5/14 30
1 3 2/5/14 31
1 7 2/5/14 32
2 3 2/5/14 31
2 2 2/5/14 32
So wants the all columns where price is minimum
Example: output required
MasterItemID Price PriceDate VendorID
1 2 2/5/14 30
2 2 2/5/14 32
View 6 Replies
View Related
Mar 10, 2014
I have a large database filled with customer records. Some customers come one time. Other customers come 50 times at year. I want to find all customers that have records that are less than 30 days apart so I can ultimately see the types of products they buy. How do I write the query?
I attached a picture of a sample database. The 30 day (+/-) field doesn't exist but I would like the query results to build it.
View 11 Replies
View Related
Sep 26, 2013
I am trying to get the average of a select group of records within a query. It appears the davg function should give me what I need, however my query returns no results. Here is a sample of my data.
Item Cost Basis Group Cost
1HF20812 1HF208 6.17
1HF20816 1HF208 8.63
1HF20820 1HF208 9.44
Here is the davg string I am trying to use.
Group: davg("Cost","Cost Basis Group")
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
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
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
Aug 9, 2014
I am fairly new to Access 2013 but am trying to create a query that will select random records from three totally unrelated tables and display the results together as if one table -- think video slot machine wheels. Each table has two fields - ID which is the primary key and NAME. The data in the tables are names of states, names of colors, and types of animals. Each table has a different number of records. My end result is a table that selects X number of random records from each table and displays them side by side like this:
Desired Result:
Animal Color State
cat red Ohio
dog blue Texas
fox green Iowa
I have been able to create three individual queries that will pull X number of random records by using:
SELECT TOP 10 Animals.[ID], Animals.[Name] FROM Animals ORDER BY Rnd(-(100000*[ID])*Time());
SELECT TOP 10 Colors.[ID], Colors.[Name] FROM Colors ORDER BY Rnd(-(100000*[ID])*Time());
SELECT TOP 10 States.[ID], States.[LongName] FROM States ORDER BY Rnd(-(100000*[ID])*Time());
Using the three queries above I get three separate lists. how to make one query that will randomly pull from all three tables and make the display above?
View 14 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
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
May 7, 2013
So I have a table with around 20,000 records and there is a field which holds a phone number for each individual within the organization, I want to run a select query that counts how many are not in the correct format
The format is 10 digits and it must start with a 0
Using Access 2010
View 2 Replies
View Related
Jun 26, 2014
I can not solve this problem, my Access knoledge is mediocre.
I have the following table from imported data:
RecNr IPaddres DateandTime
1 178.00.000.01 20-11-2013 21:47:21
2 178.00.000.01 20-11-2013 21:47:59
3 178.00.000.01 20-11-2013 21:48:35
4 178.00.000.01 22-11-2013 20:44:59
5 178.00.000.02 22-12-2013 19:47:59
6 178.00.000.02 22-12-2013 20:47:59
7 178.00.000.03 01-02-2014 12:47:59
8 178.00.000.03 01-02-2014 12:48:30
9 178.00.000.03 01-03-2014 11:47:59
etc
I want to make a query that results in displaying records that have the same IP-address where the difference in the date/time stamp is within 1 minute. When the date/time stamp is > 1 minute the record can be deleted from the table.
View 6 Replies
View Related
