Queries :: Select Records With Minimum Value
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 Replies
ADVERTISEMENT
Mar 23, 2006
I'm trying to get my query to display only the minimum value within my query results. I have the following fields, CustomerID, OrderID, ProductID, WarehouseID, Freight Cost.
My current query will return results that show me the locations and freight cost of each product to my customers. My intention is for the query to only return records that has the lowest freight cost as there may be several warehouses with identical products but with varying freight costs due to location to customer.
I've tried to apply the MIN function on Freight Cost but it will only sort the records in ascending order instead of only displaying the record with lowest frieght cost.
Thanks in advance for your help.
View 1 Replies
View Related
Feb 24, 2008
hi all,
I've got one question regarding dates.
I've already make the form to show the count of records by using query according to the start and end date selected by the user.
What I want is when I open the form, it should show the results of count between the minimum and maximum dates inside the data table. It means I want to show the count result without date specification that is the total count from all records of the whole table .
Can anyone help me how can I make it?
thanks a lot in advance.
View 2 Replies
View Related
Dec 6, 2013
I have a normalised table containing the following fields:
<Unique ID> <Fund Name> <Date> <Return>
The data are time series denoting the monthly performance of investment funds. Funds can have any number of observations (e.g. March 1997 to June 2005). In addition, some funds can have performance gaps.
I would like to extract those funds that have a continuous performance history of 120 consecutive months between a specific start and end date. As an exmaple, the start date is July 1990 and end date June 2010:
- Fund A: performance history July 1995 to June 2005 (= 120 consecutive observations) INCLUDE
- Fund B: performance history July 1995 to June 2007 (> 120 consecutive observations) INCLUDE
- Fund C: performance history July 1995 to June 2000 (< 120 consecutive observations) EXCLUDE
- Fund D: performance history July 1995 to and June 2000 August 2000 to June 2007 (> 120 observations but gaps) EXCLUDE
- Fund E: performance history July 1985 to June 1995 (= 120 consecutive observations but only 60 after start date) EXCLUDE
how to build a query around this? Perhaps this needs implementation in VBA. Ideally, I would like to be able to select the start and end date dynamically and then run the query accordingly.
View 2 Replies
View Related
Jan 16, 2014
How to find the Minimum value of a column. This is my SQL:
SELECT *
FROM tblFinalMaster
WHERE (((tblFinalMaster.OperatingDay)=[forms]![frmEnterParameters]![Combo13]) AND ((tblFinalMaster.[Checkout Order])=([Forms]![frm01ATFTieLineCheckout]![Checkout Order])+2) AND ((tblFinalMaster.[From BA_REPORT])=[forms]![frmEnterParameters]![Combo5]) AND ((tblFinalMaster.[TO BA_REPORT])=[Forms]![frmEnterParameters]![Combo3]));
The part in blue, instead of pulling a +2, I want the Minimum value. Seems simple enough.
View 5 Replies
View Related
Aug 26, 2013
I am trying to set up a calculated field in one of my form querries, using expression builder.
In one of my source tables I have 4 date fields called Inspection date 1 to 4
I need the lowest date among those inspectinos which are in the future (next inspection) If all 4 dates are in the past or Nulls, the function should return the current date. The logic of the expression could be:
MIN(MAX(D1,Date()),MAX(D2,Date()),MAX(D3,Date()),M AX(D4,Date()))
How can I do this in expression builder?
The built-in functions DMin and Dmax work with single field arrays, witch would be perfect if the database were properly designed, but now I dont have the power to change this.
View 1 Replies
View Related
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 3 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 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
Mar 21, 2014
Expressions in Access have given me some trouble before. Mainly due to inexperience. I hardly ever work with them. What I am trying to do is make the default value of a form textbox control the minimum value of a field A in a table A. The datatype of Field A is Date.
So far I've got:
=Min([table A].[field A])
In the Default Value of the form's property sheet, but this just returns a blank value. I've had a look in the table and there is no value that is blank in field A.
View 5 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
Jan 29, 2015
I have a database with an import process which normalises incoming data and appends to various tables. No issues with that. I also have a function within that process which counts the number of new entries for a summary popup when the process has completed.This works by querying the staging table, prior to the append, into a recordset and using the .RecordCount to increment the count (multiple files can be imported at once so this effectively provides a running count, per file, to give a total for the whole import)
I thought it was working fine but this morning I noticed that the count which appeared on the popup was 1 greater than the number of actual new records. I checked the source files and noticed that, for whatever reason, there was a duplicate entry in there. So I presume that's why the count was out by 1.
There's no integrity issue in the main tables as the composite primary keys ensure that duplication shouldn't be a problem. Indeed, the record in question, duplicated in the source, appears only once in the main table post-import. So not too worried about that.
However, I need the count in the popup to be accurate (it tells the users how many new entries require further investugation). And what's puzzling me is that I use DISTINCT in the query, which I would have thought should eliminate any potential dupes in the recordset and thus provide the correct count. It seems it doesn't?
Code:
Public lngNewBalancesTBI As Long ' Defined in a separate module...
-------
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
[code]....
Why the dupe, which is still present in the staging table, also makes it over to the recordset, even though I'm using DISTINCT?
View 14 Replies
View Related
Jan 4, 2014
WinXPPro Sp3
Access 2007
After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.
Code:
SELECT Unique_No, Table_Name, List_Order FROM My_Table
WHERE Table_Name = 'Titles'
AND List_Order IN (3,1,15,4,5,12,7,2)
ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)
Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus
List_Order
3
1
15
5
4
12
2
7
View 3 Replies
View Related
May 1, 2013
Is it possible to run a basic select query to pull records based on multiple strings entered by a user?
I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.
I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.
But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above
SQL statement (Like) is below :
SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));
View 10 Replies
View Related
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 3 Replies
View Related
Jun 15, 2013
In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code:
Set objDialog = Application.FileDialog(4)
With objDialog
.AllowMultiSelect = False
.Title = "Please select a File"
.InitialFilename = "C:"
.Show
If .SelectedItems.Count = 0 Then
MsgBox ("Action Cancelled")
Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
View 1 Replies
View Related
Jan 28, 2005
Hello,
I have a table which has repeated IDs, I want to find the start of the ID then mark it as Y in the next field, the remaining repeated IDs should have blanks in the next field, how do I do this, thanks for any help
View 3 Replies
View Related
Aug 9, 2007
hi
i try to build an access form .
i have "big" table that fill in the name of the product , the quantity , and a minimum quantity.
the field : min quantity give us the quantity that is the minimum of the product.
i have a "minimum" table that has the list of the product and the minimum quantity.
i want to see the minimum quantity of a product , when i type the product (in the form if it possiable , if not - in a report)
eran
View 2 Replies
View Related
