Selecting Lowest Values In A Query?
Feb 23, 2006
Hi all - I have a table that contains Client ID, Supplier Name, Order Number, Order Date, and Price.
Example -
Client ID -- Supplier Name -- Order Number -- Order Date -- Price
1111111 Supp A 222222 1/2/06 1,000
1111111 Supp B 222222 1/2/06 2,000
1111111 Supp C 222222 1/2/06 4,000
1111111 Supp D 222222 1/2/06 500
1111111 Supp E 222222 1/2/06 1,200
I need to determine the 2 lowest prices per order number. In this case that would be Supp D - 500, and Supp A - 1000.
I am using the Top function and sorting the price in ascending order to get the lowest two prices. The problem is that it selecting the lowest 2 prices of all the orders on the table, not each individual order. I tried grouping on the other fields, but still came up with incorrect results.
Any ideas? Thanks!
B
View Replies
ADVERTISEMENT
May 24, 2006
Hello there...
I have a data set as follows:
Product CompanyA CompanyB CompanyC .... CompanyZ
aaa 2.3 2.4 2.5
bbb 3.4 - 3.1
and so on...
The value represents the price of products...
I want to make a query that will show me results in following heads..
Product MinimumPrice CompanyName
aaa 2.3 CompanyA
bbb 3.1 CompanyC
How can I proceed??
Looking for some help
Regards,
View 1 Replies
View Related
Apr 20, 2006
Right.
What I want to do is use a value obtained in the form (obtained from a list-box selection) to select a record in a table, then return one of the fields to a text-box on the form.
All the values coming from the list-box are unique, and the value returned is a simple integer.
Is there any way to do this reasonably simply?
View 5 Replies
View Related
Dec 22, 2005
Hi all! I have a tough problem I was hoping I could get some help with:
I would like to create a recordset based on the "middle" 50% of the data. I need to chop 25% off the top of the data, AND 25% off the bottom of the data.
Any ideas?
View 3 Replies
View Related
Sep 13, 2006
I'm teaching this term, and have put my grades and roster in Access. Each grading opportunity is a separate field.
Students will have to take 3 exams (Exam1, Exam2, and Exam3); however, their grade is based only on the 2 highest exam grades. There are other grades...FinalPaper, Attendance, Paper1, Paper2... the total of the grades is 600 points.
I can do the other calculations... what I do not know how to do is to select the two highest test values from the 3 Exam fields. I coudl do it manually, but I really prefer to simply enter grades, and then have the final grade calculated.
Thanks.
Gayle Ann
View 3 Replies
View Related
Apr 16, 2014
I am working on a school database, today I am working for fee collection form and I am facing trouble.In attachment At the top right area the hidden field I would like here changes according to selecting Fee Type under Fee Detail. I want to achieve this,
If feetype is ABF then Hidden field = 1500110
If feetype is ASF then Hidden field = 1500120
If feetype is EXF then Hidden field = 1500130
If feetype is MTF then Hidden field = 1500140
If feetype is RBF then Hidden field = 1500150
How I can perform this ?
Table structure is
(tblstudents) Contain students data
(tblFee) Contain, Student Id linked with student table, month, year, amount, date, narratives and FeeType that looup values from FeeType tbl, this hidden field also in tbl fee.
The codes that are 1500100,1500120,1500130
These are necessary for GL entries that's why I must have it on my form. I had as well apply method of Default value but in that case I only present only a single value, but I want that in hidden field values take changes according to the fee type.
View 3 Replies
View Related
Aug 5, 2004
I have searched all the forms but could'nt find any answer which would help me , I have created a listbox , it works fine till the moment i set its 'Multi select' property to SIMPLE or EXTENDED , the minute i do that i get no results in my query , could someone please help me with this , i really really need to get this workin!! I have also uploaded my database so u could try it yourself...
View 11 Replies
View Related
Jun 4, 2015
I have a form that is a datasheet. it contains multiple records. One of the fields in that datasheet is a combo box. I would like to make it so when I select a value from the combo box it changes all the values of the other records in that column/field to what was chosen from that combo box. is that possible?
View 4 Replies
View Related
Dec 29, 2013
I am trying to use a list-control on a form to let the user select multiple values. I have understood that this requires some VBA-code to step through the selections in the list, since the "multivalue-selection" is set to "Extended".
When I try to execute the code I have (found and have tried to adjust), then I get the error message "Object required". The "ListCount"-paramater always only results in a ZERO-value, when i step through the code:
Function cmdOpenQuery_Click()
On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
[code]....
View 9 Replies
View Related
Jan 16, 2008
hi,
I have a table that contains itemcodes and prices. An example of the records would be:
<Item Code> <Price>
Product001 £34.56
Product001 £49.23
Product001 £23.22
Product001 £98.43
Product002 £12.45
Product002 £54.34
Product002 £25.51
Product002 £76.84
Product002 £14.97
I would like to run a query that returns the lowest value price for each Item code. So in this example it would return:
<Item Code> <Price>
Product001 £23.22
Product002 £12.45
Does anyone know the criteria code in the query design view for this problem?
I know it sounds silly, but there are unique codes in another field, i just need to query against these fields.
Thank you for your help.
Dean
View 3 Replies
View Related
Oct 24, 2006
Let's say I have a table with the following data:
Customer:
----------
name | city | salary
john| Boston | 14000
billy | Boston | 32000
sam | Boston | 12000
jj | Dallas | 6000
greg | Dallas | 8000
josh | LA | 1200
ally | LA | 600000
bill | LA | 12000
bob | LA | 9800
how can i list the city with the lowest average salary?
I need to find the average salary of each city(should be 3), then choose the lowest from those 3 averages.
Can anyone help me?
View 6 Replies
View Related
Feb 2, 2008
I'm using a filter on the form load of one of my forms that potentially would have 3000 records. The idea is to speed the process up a little.
I'm using:
If IsNull(Forms!frmProjectMain!ProjectID) = False Then
DoCmd.GoToRecord , , acFirst
end if
Me.FilterOn = True
Which works fine. However, I'd rather it was the ID with the lowest numeric value that was displayed and filtered rather than the last record chosen. i.e. 1 instead of a random number.
Does anyone know how i'd go about writing code that would tell access to go to the lowest ID to filter?
thanks for any help I receive here
View 5 Replies
View Related
Nov 3, 2006
Hello,
I have searched through the forum, but cannot find a suitable answer to my question, so here I am.
I wonder if you could help me?
I have a list of Customers each of which has a Customer ID then I have a list of accounts which may have more than one customer ID.
For this senerio I would like to only show the record that has the lowest customer ID.
I.E. If one account has two customers take the lowest customer ID.
Does anyone know how to do this in SQL or VBA?
Thanks for all your help.
View 2 Replies
View Related
Mar 13, 2008
I have a table that has a list of order information, there are multipule records per order that have information about when the order was processed. There is a day tied to the process time of each record, I need to filter out the duplicate records and get one record for an order, but that one record must be the first process time entry for that order. Anyone know how to make this query? Let me know if this doesnt make sense.
View 4 Replies
View Related
Mar 17, 2014
I have a relatively complex update that I need to perform on a table from a form. I have a system that has "games" and "game copies". The game is simply a name of a particular game and the copy is something that has a stock number but a foreign key of the game catalogue number. This means I have several catalogue numbers that are the same in the GameCopy table.
The problem is that I have to reserve a game - not a game copy. When I have made a game available (it has been returned) I have to indicate this in the reservation table. I could have many different reservations for the same game so I need to only update the oldest reservation and indicate that a game copy is available now.
Summary:
One "Game"
Several "GameCopy" using "Game" as a foreign key
Reservation table with possibly several reservations for the same Game - not GameCopy(s)
In other words I have an "Available" field in the reservation table and a "date reserved". I need to create an "Available" (Date()) entry for the oldest DateReserved entry on that reservation table. I could have done it as a boolean but I decided to use a date instead for logging purposes.
Would I use some kind of "Once only" action to make sure that only one of the reservation entries are updated? I really do not know how to proceed with this.
Obviously if I simply:
UPDATE Reservation SET Reservation.Available = Date()
WHERE Reservation.CatalogueNo=Forms![Current Reservation].CatalogueNo;
...then it will update all of them. I believe there must be a bit more SQL I have to add or something else maybe.
View 5 Replies
View Related
Sep 4, 2006
I must admit I am a "newby" to Access but I have bought my Access 2000 bible, as I am running Access 2000, and attempted to create my desired database. I have linked to 2 tables that I download on a daily basis. This is my inventory from two different suppliers. I then have created two queries that filters each of these files to only show positive quantity items, filters out item specifics, etc.What I want to do is join the 2 queries with their data already filtered. Both of the files from 2 different suppliers contain some of the same data. Once joined if there is an item that is the same I want to delete the higher cost item and only show the lower cost item, I also want to show the rest of the items that do not match. It is easy to identify the same items as every item contains a 12 digit identifier called a upc.I can create a UNION ALL query that shows every item from the two queries. However, I am unsure as to how I go about deleting the higher cost item and only showing the lower cost item if the item is the same. On a side note after I get the results desired I will then be createing an append to query and append all this information to another file that is then uploaded to another system.I will try to attach some sample data that I am using from the two queries I want to join, this data is only a sample of a much bigger file.Thank you and any and all comments or suggestions is much appreciated.
View 3 Replies
View Related
Jan 22, 2006
Hi, I'm a complete novice to Access and wonder if anyone can help me with this one please? I have a column in a query that is the output from a barcode scanner called WhenScanned from a table tblScans. The data in the column is in the format of the date and time for example: 20/01/2006 21:30:00 I want to be able to interrogate the data in the query by selecting a range of a date(s) and times for example from 20/01/2006 05:30:00 to 20/01/2006 13:29:00, date and time range from 13:30:00 to 21:29:00, date and time range from 21:30:00 to next day 05:29:00. I can achieve this by typing the required range into the criteria row of the query column in design view e.g. >=#20/01/2006 13:30:00# And <=#20/01/2006 21:29:00# for each range but I want to make it more user friendly so that a user can select type in the appropriate date and select the time range from a drop down list or something without having to edit the query using syntax. Thanks in anticipation.
View 2 Replies
View Related
Jul 31, 2007
Sure its simple to do, I just don't know! lol.
I want to create a query, where the user can choose from 3 products to choose from (from the same table, under the same field name) that they can search for to find which customers r buying it.
Ideas....?
View 6 Replies
View Related
Jul 13, 2006
I have a problem I should be able to solve, but I can't think my way around it.
I need to automate a series of tasks to perform the following:
1. The user clicks a button on a menu form and arrive at a select records form.
2. The user makes choices regarding the contents of certain fields (blank, not blank, equals a text string, etc.) and presses OK.
3. On the OK click, the fields are applied to a query, the results of which are exported in an excel spreadsheet.
I've been doing step 2 and 3 by hand in that I alter the selections in the design view of the query and save it. I then run a macro that deletes the data in the current table, runs the append query with the new criteria, and creates an excel spreadsheet of the new data.
However, I now have to make this functionality available to a non-tech person, thus the need to create a GUI menu that can launch a form on which the user can select options and with a single click, launch the macro.
I've tried adapting select forms that I've used with forms and reports using the doCmd.openquery, but they will only work for select or crosstabs, not appends. I considered having the form create the query and then qrite the append query off that, but I can't seem to get that to work.
Can anyone give me some direction on where I can take this?
Thanks.
Susan
View 1 Replies
View Related
May 17, 2007
Hi all
I am trying to get my head around some of the more advanced “features” of access..
At the minute I am trying to change the criteria of a query from selecting a value from within a combo box.. I believe I have got the syntax correct, but I keep getting no results returned..
I am using:
[forms]![frmMovies]![combo2] as the criteria where frmMovies is the name of the form, and combo2 the name of the combo box.. I have set combo2’s source as the table containing the movie information “tblMovies”
Before you ask I am not trying to setup my own movie store, more apply the theory to a bigger DB that I am working on :-p
Hopefully you can help, as I have now been scouring the forums for 2 days and am getting no where..
Thanks in advance
Noel
View 2 Replies
View Related
Jan 24, 2014
I have a query that is used to capture all the fields that can be possibly used.When run & exported to excel the user then simply deletes the columns with the fields that they don't require.We do this so we don't have to keep creating queries over and over again when you just use one that does all but then delete what you don't need afterwards.
Any way of using a form with tick boxes for each of these fields so when they user ticks the fields they require reporting on then only those fields will be returned in the query.
View 1 Replies
View Related
Jul 22, 2015
I have a combo box that has three items in it. Name, ID_Num, Date
I need to use ID_Num and Date to filter the results of query. I can use one or the other but not both. I have tried ListIndex but apperently am writing it wrong in the expression builder.
[Forms]![TableName]![Combo0].
[ListIndex]????????????
I have tried =0, =1, =2 after the
[ListIndex] but it doesn't work.
How do I use two items from combobox in a query?
View 2 Replies
View Related
Oct 11, 2006
I have a value list combobox (cboShift) on my form with selections as "1st Shift";"2nd Shift";"3rd Shift" and a subform that should filter my records based on the shift selected on the mainform.
My data fields in my query are [TimeStart], [Time Stop], [other fields].
I would like that e.g. when "1st Shift" is selected, it should filter my records based on the [timeStart] that falls within the range, and so on.
I really don't know how to handle this, even to set the time range parameter for this...
I have written this function for a start. To continue, i ask for your help or guide. Please help????
Function fShiftWorked(strTimeStart As DateTime)
Dim strOperatorStart As String
strOperatorStart = FormatDateTime(([tblTimeLog]![timeStart]), vbLongTime)
If strOperatorStart >= #8:00:00 AM# And strOperatorStart < #5:00:00 PM# Then
strTimeStart = "1st Shift"
ElseIf strOperatorStart >= #5:00:00 PM# And strOperatorStart < #12:00:00 AM# Then
strTimeStart = "2nd Shift"
ElseIf strOperatorStart >= #12:00:00 AM# And strOperatorStart < #8:00:00 AM# Then
strTimeStart = "3rd Shift"
Else
strTimeStart = "2nd Shift"
End If
End Function
View 6 Replies
View Related
Oct 17, 2007
I have an interesting report query I am working on. Is it possible to have a query that shows different fields for individual records depending on the content of the record? Let me see if I can explain. Let's say that I have a Status field that can be "Complete", "Active", or "Planned". And lets say also in that table I have Field1, Field2, and Field3. For each record, I don't want to include all three fields (Field1, Field2, Field3), but only one of these fields depending on what is in the Status field.
So, it would be something like:
Select Status, (if Status = "Complete", select Field1), (if Status = "Planned", select Field2), (if Status = "Active", select field3) FROM table;
So, the query would result in 2 fields for each record: the status field, and one of the three other fields depending on what is in the status field for that record.
Thank you kindly for your help.
View 2 Replies
View Related
Jun 3, 2015
I have a table of prices for commodity contracts, with my first field showing the dates the prices are from and the subsequent fields relating to the individual contracts (contract A, contract B, contract C, etc.)
I want to run a query that allows a date range to be selected, and a contract to be selected.
the first part I am pretty sure i know how to do (Between [Enter Period Start:] And [Enter Period End:]), but its selecting which contract i want this range to apply to that I am not sure how to do.
Can this be done in the same query? or would i have to do something like create a separate query for each contract and then use a form with a selection box that chooses which query to run?
View 7 Replies
View Related
Jan 13, 2014
I am looking for a way to generate a list of all fields within a table, have the end-user select which fields he/she wants to include, and then run the query. I am trying to create this within a form for a nice, easy to use GUI.
I am using Access 2010 on Windows. The fields I need them to select from are in one table, however there are many lookup (tblkp) tables related.
View 2 Replies
View Related
