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.
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.
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.
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.
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.
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...
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?
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
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.
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?
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.
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.
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.
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.
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.
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?
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..
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.
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.
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
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.
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?
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.
