Query To List Max Values
Oct 1, 2006
I have a Select query that lists, among other things, two fields:
ID_Clients
EndDate
Each entry in ID_Clients may be listed several times
I want to generate a Select query based on the above query that lists each value of ID_Clients just once, by selecting the record for that ID_Clients that has the maximum value in the field EndDate.
So, sample source data
ID_Clients......EndDate
AAA.............2005-04-30
BBB............. 2005-06-30
AAA.............2006-04-30
BBB............. 2004-06-30
CCC.............2006-09-30
Desired end result:
ID_Clients......EndDate
AAA.............2005-06-30
BBB.............2006-04-30
CCC.............2006-09-30
Any ideas how I should achieve this would be appreciated.
Thanks
View Replies
ADVERTISEMENT
Jun 12, 2007
Quote: Originally Posted by ansentry Have a look at the attached sample.
Hi John,
I am new to this site. I can't seem to find where the attachment being referred to is. I have a similar question. Basically, I have this data:
Parcel No. ......Soil Type......Area
001-001 flathead 75
001-001 whitefish 33
001-001 kiwanis 45
001-002 kiwanis 22
001-002 flathead 33
001-002 chamokane 74
002-005 flathead 20
002-005 kiwanis 53
002-005 chamokane 27
The desired result is:
Parcel No. Soil Type Area
001-001 flathead 75
001-002 chamokane 74
002-005 kiwanis 53
Can you please help me? Thanks.
View 8 Replies
View Related
Mar 26, 2015
I have a list in a form where the user can select multiple values. The list "Projekte" is based on the table "Projekte".
I want to be able after the user selects multiple values from the list to use them in a query to show the respectful records. For example if the user selects Project 1 and Project 2. I want to show the records where there are either Project one or Project 2. Is that possible without VB as I have no knowledge of VB.
View 1 Replies
View Related
Oct 1, 2013
One Manifest can have many line items. One line item can only have 1 designation. Each manifest may have many line item with same designations.
For example:
Manifest #0001
Line Item 1: N
Line Item 2: H
Line Item 3: U
Line Item 4: N
Line Item 5: P
Line Item 6: H
Table set-up
CurrentCY
CurrentCYIDPK
WasteCategoryIDFK
LineItemInformation [Line Item 1, Line Item 2, etc]
ManifestDataIDFK
ManifestData
ManifestDataIDPK
ManifestNumber [0001]
WasteCategory
WasteCategoryIDPK
WasteCategory [H, N, U, etc]
I'd like to query the line items for each manifest so the end result has the manifest number (Manifest #0001) in a field and the designations (N, H, P, U) in fields on a report.
View 14 Replies
View Related
Sep 11, 2005
Hi Friends,
I m new to access, i want to get the Project_name into "form!project_name " from the project table when user select project_id in the form. Any idea will be appreciated.
Best Regards,
Saliuzzama
View 1 Replies
View Related
Mar 1, 2006
need help., have a form, have put a combo box to display values from another table, works ok, but when a type a new value, it displays 'not in list', so how to add the value to the table without opening the other table or any other suggestion, should i select values in combo box from a query...
:eek:
View 3 Replies
View Related
Oct 6, 2006
I have a list box box which contains a ProNo and ProName.
I want to be able to select a value in the list box and pass it to another list box in the form so i can pick the projects i want to print reports on.
Is this possible or is there another way of going about this problem.
View 1 Replies
View Related
Oct 18, 2006
Anyone know how to get the total value from a field in a list box please?
http://johnviki.com/images/listbox.jpg
I have text boxes on the main form above the list box & can't work out how to get the totals in the text boxes above like I've typed them in the sample above
Any ideas?
Thanks
View 9 Replies
View Related
Nov 14, 2006
Hi All,
I have a list box and I populate it with records from a "Select" query. I am using VBScript to do this. Here are my codes:
gr1_list.RowSource="Select Criteria from CriteriaList WHERE Criteria='" & cvalue & "';"
My "CriteriaList" table has two columns; Criteria and CriteriaDescriptions.
The question is, what if in my list box I want to be able to see both 'Criteria' and 'CriteriaDescription?' How would I code this?
Thank you.
View 4 Replies
View Related
Feb 7, 2007
Hi!
I have a tables.
One is called: products => prdouctID,productName, ProductPrice
I created a dropdown list.
To read in the values of productName.
I wanted to have a textbox / label which will update the productPrice.
If i select productName as "Pirates", the textbox/label will show $50.00
If i select productName as "Who let them out?", the textbox/label will show $80.00
I have managed to do the dropdown list but cant seem to figure out the label / textbox.
Please advice?
Cheers!
Darence
View 2 Replies
View Related
Jan 11, 2006
Hi all,
I was thinking/hoping of using a list box on my form to store multiple values, I haven't been able to find a way of storing any value so far so not sure how easy it will be?
How can this be achieved or is it just easier to use several check boxes (approx 8)
Thanks
View 3 Replies
View Related
Dec 31, 2013
I want to validate the value of the list in the lookup in the table and create a list of the values that no match the values of the list..
View 3 Replies
View Related
Jan 2, 2014
An affiliate sent us a table of email addresses, one per record. We need to find which ones already exist in our master table. Our master table contains an email field but it may contain MULTIPLE email addresses separated by semicolons. How do we create a query (or queries) which tell us which email addresses already exist somewhere in our master table?
View 7 Replies
View Related
Feb 12, 2014
I have two tables, TBL_Students and TBL_Email. Each of these tables have a field called Category, which allows multiple values from a list.
Is there a way to return records from TBL_Students where at least one value in TBL_Students!Category = at least one value in TBL_Email!Category.
E.g. If Student A has categories Maths, Physics and Computing;Student B has categories Maths, English and History;Student C has categories Physics and Geography
I would expect:
when TBL_Email!Category = Maths,Physics : Students A,B,C to be returned
when TBL_Email!Category = Maths : Students A,B to be returned
when TBL_Email!Category = Physics,Geography : Students A,C to be returned
View 2 Replies
View Related
Jan 24, 2005
Hi,
I have a table of records, with one field of the records a combobox populated by a select query based onanother table.
My problem ism that it doesn't seem to recognise any of the values as on the list, though deleting one character and then replacing it results in the value being accepted with no problems.
This is a database I have inherited (Cheers predecessor!), the table is populated with a few thousand records and scrolling through this table results in an error message on this field for every record and is making displaying data on this form a nightmare! Anyone know of the cause or of a quick fix?
I don't know the history of this table unfortunately.
Cheers in advance
John
View 6 Replies
View Related
Jan 12, 2005
I hope the title actually conveys what I'd like to do.
I want to assign records on a subform to a group header on a form. For example, I have groups A, B, C, and D and I want the records on a subform to be assigned to groups A, B, and D. (The number of groups and their names will change so I can't simply use an "A," "B," "C" option box.) My idea is to have a Multi-Select List Box on the main form and choose all the groups to which the records on the subform need to belong. So I'd select the groups, enter the records on the subform, then create a another record on the master form and assign records to another group.
Is it possible to make this happen without a lot of programming?
View 2 Replies
View Related
Jun 27, 2012
I have a table with a few Listbox options. I want one of the columns' values to be determined by the value chosen in another field. How do I do this?
Example: In column A I choose value ABC from the dropdown. I want column B to now offer values in that list of DEF, GHI, JKL. If I chose XYZ in column A instead, I'd want the column B list to show MNO, PQR, STU. Etc.
View 6 Replies
View Related
Jun 30, 2014
I was able to create a very user-friendly pop-up search form : {URL] .....
I would like to take it a step further... Now, I would like to grab the selected record ID from within one of the list box columns and use it in my SQL statement (to copy the record into a new one). Is there any way to reference a highlighted record value within a list box recordset directly and/or any workarounds?
I know one would be to grab the ID and insert it into the text box, just don't know what the coding references would be for this.
View 4 Replies
View Related
Sep 25, 2014
I have a combo box which gets its values from sql server using a query which is called "get_query_reason", which works fine. Now I want to update combo box values based on a user selection, st string. Have written the code, but does not work:
Dim qDef As QueryDef
Dim Query As String
Dim st As String
Dim rs As Recordset
st = "SOV"
Set qDef = CurrentDb.QueryDefs("get_query_reason")
[Code] ....
View 2 Replies
View Related
May 22, 2014
I have an Employee's Development Objective main form , which has a subform that contains a combobox that lists all 'Job Titles' and 'Job Objectives' within the company, say such as:
Code:
IT Manager, Create Spreadsheets
IT Manager, Manage Staff
SQL DBA, Create Databases
SQL DBA, Create Spreadsheets
Managing Director, Manage Finance
Managing Director, Manage Staff
How do I get the subform combo box to ONLY display the Job Titles and Job Objectives specific to the Employees known Job Title? The combobox Row Source is presently:
Code:
SELECT qryJobObjectiveDetails.jobObjID, qryJobObjectiveDetails.jobTitle, qryJobObjectiveDetails.objective
FROM qryJobObjectiveDetails
ORDER BY [jobTitle], [objective];
The main form has a jobID number specific to the Employees job title.
View 4 Replies
View Related
Jun 8, 2014
I have a Table with a short text field, field size: 20. The row source has been set up ie "Item 1", "Item 2", "Item 3", etc. Allow multiple values IS allowed.Once I drop a control on a form (list box) to represent the aforementioned field, I realize I can put a checkmark in many items. Why? I add up the total length of the items text at 40-50 characters, yet the field size is only 20. To check my sanity, I open the Table in Datasheet view and lo, I can see all the Items spelled out, in clear violation of field size constraints
View 5 Replies
View Related
Mar 28, 2014
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
View 5 Replies
View Related
Nov 11, 2005
OK - I'm a bit of a novice at access and the answer to this may be very simple but any help would be much appreciated.
I have a problem getting a Combo box field in a table to link to another table and input a relevant value in another field of the same table. Here is what I've got:
Table 1 - Client
Fields - CLIENT ID, CLIENT NAME
Table 2 - Client Rates (The rates charged to the client for 3 specific items that do not change)
Fields - CLIENT ID, SC, LX, SX. (These last three are the codes for the items and the values in the field are the currency values for the items as they are charged to the specific client)
Thanks
Table 3 - Job Details
Fields - JOB ID (Autonumber for the Job), CLIENT ID (Who the job is for), ITEM (This is a combo box that selects between the items i.e. SC, LX and SX), RATE (I want this to recognise the item chosen in the previous field and display the corresponding rate for that item and for that client - This is my problem!) :mad:
I will attach the database as I currnetly have it so that you can see what I'm talking about. As I say - any help is muchly appreciated
Thanks muchley
View 1 Replies
View Related
Nov 2, 2007
Hi
I am trying to compare data in two different tables and list the data that does not match. Not sure if this is possible. Details below
Table 1
xyz - A1 - Do not use
ABC - A4 - Use
Table 2
123 - A1
The result of the query should show me
A4 (as this does not appear in table 2
I am not too familiar with Null values as there will be many null values that i want to ignore.
Any help will be appreciated.
View 4 Replies
View Related
Jun 4, 2013
Is there a way to order the values in a multi-select list box:
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 appears in the listbox as:
1,10,11,12,13,14,15,16,17,18,19,20,2,3,4,5,6,7,8,9 . Is there a way to have it appear as it does originally?
View 1 Replies
View Related
Feb 11, 2015
I created a form with subforms that are all linked to their own tables. Values that are entered in to the form (i.e. just numbers or names) all save into the tables as I would expect; HOWEVER, all of my value lists (0,1,2 or 1-5) do not. I can change the values in the forms and as long as I am toggling between them they appear in the form but they are not populating the tables.
Other details. They are defined as numbers in the property table, I did change them to a value list in the form properties and did assign values to the property list.
I looked through some other threads that seemed similar but the solutions didn't apply. Because the straight up data entry saves, I feel like it is something specific to me creating those value lists...
View 2 Replies
View Related
