Ignoring Duplicates In Table Field Values That Is For A Combo Box Drop Down List
Apr 13, 2014
I'm trying to create a combo box that takes its values from a field in a table, the thing is that this field has duplicates because:
* The table is a qualification look up table
* It has 3 fields: QualificationID (autonumber), Qualification(e.g. Bachelor), Programme (e.g. Science)
* E.g of duplicate values (this is not a problem as it is necessary) Bachelor of Science, Diploma of Science, Certificate of Science
What I'm trying to do is create 2 single-columned combo boxes that are a parameter for a query and it's working except the combo boxes show duplicated valuesIs there a way to get the query that is the row source for the combo box to eliminate duplicates?
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
In my form's table (tblMain), I've got a lookup field (drop-down list) that lists the primary key field from a different table (tblDiff). tblDiff includes 3 more fields. In my form for tblMain, I want to include 3 more textboxes that get filled up with these 3 fields from tblDiff when the corresponding primary key is selected in the drop-down box.
I have created a database which is used as a sign in and out machine for students. I have 2 forms a 'sign in' which has a lookup list containing all student names. as well as date and time and a 'sign out form' which uses a lookup list from the sign in form.
It collects data fine but when a student signs in and out a second time there name is duplicated in the 'sign out' drop down list. The student must select the right name out of the duplicates otherwise it wont record.
Im just wondering if there is some sort of fix to this.
Just fumbling my way around Access 2007, is the only way you can create a drop down list of number by creating a table and inserting a combo box? I am trying to create a list of hours for example:
I am using a sequence of combo boxes on a 'Continuous' form.
To reduce the possibility of error I am using a 'Value List' combo box where I use AddItem method to add the previously entered string to the combo control so that it is there for use when entering the next record.
This works fine - but what I want to ask is - how to have a routine to check that the string does not already exist in the combo box before I AddItem i.e., I don't want duplicated values in the list.....
I have a problem. I'm trying to import values to a Combo List from a table:
Row Source = SELECT Tasks.Milestones FROM Tasks;
where Task is a table and Milestones a field of that table. In this field (Milestones) there are repeated values, but I'd like to hide those duplicates when I want to chose them from the Combo List. I don't know If it is clear, if not it's because I'm not an Expert of Access :p
I have a combo box in which it's value changes according to user input. It is working but it is annoying to have to click the combo box for it's list to refresh. How to make it seemless?
I have a number of fields that hold values (scores - either 0,1 or 2) that need to be summed but in some cases the value is 99 (which is recognised by our stats program as a N/A type of anwer- This occurs when a question is optional and the user hasnt answered it. I did not want to value to be null (since its not like the user didnt answer it because they missed out on it by accident but rather they could not) and it couldnt be 0 either since 0 actually means something different in the context of the questionnaire.Lets say I have Q1 which has a value of 0, Q2 has a value of 1, Q3 has a value of 99 and Q4 has a value of 2I want to get the total of Q1+Q2+Q3+Q4 for a particular entry but if the value of any or all of the questions are 99 I want access to ignore it and just sum the rest of the values i.e. the sum should read 0+1+2+(99)=3 not 102I want to be able to include this sum function in the expression builder if possible since I am using it for a report. Can someone please help?
Is there a function that will populate a field with drop down menu based on two criteria?I want the the fields with first and last name to populate with drop down lists based on the employee code I have inputted in the form and job title from a query.
Path: looks at employee code from form > looks at specific job title from query > pulls out all first names in one field and all last names in another field with the same job title in drop down list from query
Employee Code: 100
Returns all employees' first names in first field with same job title:
Prince Tina Greg
Returns all employees' last names in second field of form with same job title:
Fey William Jones
Here's what the query looks like in datasheet view:
Code: Location # First Name Last Name Job Title Employee Code 1 John Smith Technician 100 2 Jane Doe Manager 100 2 Greg Jones Engineer 100 1 Prince William Engineer 100 1 Tina Fey Engineer 100
I've been trying to get dlookup to work, but no luck. Here's one of my formula:
The first is linked to a separate table that only contains employee codes because query I am working with has duplicates due to multiple records.The second is trying to link both the table and query together to populate only first name.how to include the second criteria, job title, to refine it more.
I'm working on a table for work which will serve as a database of bins and the products currently in those bins, as follows:
ID Bin Amt Product 1 34 25 110001 2 33 15 200005 3 32 23 110003 etc.
Basically: employee A will use a form to update "Bin 34" (ID=1), with information (Amt, Product) until it is emptied by employee B and cleared using a separate form. No new records will be made or deleted, just the adjacent fields (Amt, Product) cleared and filled in and cleared again over and over.
What I'm trying to figure out is, when employee A clicks on the combo box on his/her form to select a Bin and enter information, how can I filter what is listed in the combo box to display only those bins which have no values under Amt & Product. The real-world risk of overwriting a bins fields before it has been cleared by employee B (who physically empties the bin) can be extremely high monetarily so I am trying to reduce that risk by eliminating it from the combo box until employee B has cleared those fields.
It is essential that i use MS access to do this, what i need to do is have a drop down list on an ms access table to make sure the data in the field is exact, i could use validation if this is not possible but i would prefer this method also i dont need an answer to this but is there a way to make the key field dependant on whatever is selected on the dropdown menu? sort of use a general input mask but alter something dependant on the drop down menu to have it as specific characters, i have some experience with VB if there is a way to link vb programming with access
I have a form with 4 different search fields used to query a table and limit results: Policy#, SSN, Credit_Amt, Debit_Amt. Any matching rows based on the search criteria are returned in a subform.
My problem is how to handle any fields where the user doesn't specify a search value.
In my query, I have the Criteria set to: Field: Policy_No Criteria: =[Forms]![form name]![Policy#]
Field: SSN Criteria: =[Forms]![form name]![SSN]
and so on....
If I place my criteria on different OR lines, the query runs, but if a user specifies more than 1 criteria, I get all the data for one criteria(such as policy# matches) and all the data for the second criteria(such as all the records for a certain credit_amt, regardless of Policy #).
If I place my criteria on the same criteria line, effectively making it AND, I get no results because I assume the database is looking for any blank search field values.
So, how do I make the query an "AND" query, but actually get results by ignoring any NULL search fields? So if a user enters a Policy# and Credit_Amt, the results will be only matching rows by Policy# AND Credit_Amt, and doesn't look at SSN and Debit_Amt.
I've tried different iterations using IIF(Not IsNull(.... to no avail.
I have a form with a drop down list, when the user selects an option and press a button. I would like to add the value of the drop down list to a table as a new record.
I have a sizable list of GIS polygon shapes(~9000 data points), collected over 15 years, that I am trying to shrink down to unique ID's. Each years data has a unique label, but for many of the polygons the shape does not change from year to year, so I want to assign a unique ID that encompasses all of the years that shape existed. Some examples:
1950- Parcel A - 2 acre star same 1951- Parcel K - 2 acre star same 1952- Parcel L - 2 acre star same 1953- Parcel F - 2 acre star same
1954- Parcel J - 3 acre box same 1955- Parcel Z - 3 acre box same
Ok, simple enough. I created a unique polygon ID in a separate table, and set the first 4 records to some value, let's say AAAA. The latter 2 records I called something else, BBBB let's say. These values are used in a combobox so my data entry stooges can easily assign each duplicated polygon the same ID, so a quick query will show that polygon XXXX is comprised of so many years worth of the same value, just with a different label. Now my question...
In the above example, after 1953 the 2 acre star polygon never exists again, so I want to 'retire' that option from the combobox so it cannot be chosen in error. As it stands now, if I were to enter the following date point 1956- Parcel N - 3 acre box
I can still choose AAAA or BBBB as the unique polygon ID to equate it to. Is there some way (maybe a boolean option) to disable just a specific combobox value so it cannot be entered in new records, but STILL shows up in old records, a.k.a. is not deleted?
My computer has been updated to 2010 whilst I've been off sick (was 2003 before my accident).
I've created a main table, for devises across the company, and a combo box/selection box based on another table which holds a list of all the "Responsible" employee's aswell as another combo box/selection box for the device location.
So the person entering the information, can enter all the information for a device (torq wrench, socket set etc), who is responsible for it and which department they belong to (where to find the device).
Which all works fine
However, I'd like to create 2 queries, one to enable the user to run a report of all device's allocated with an employee or to be able to run a query for all device's stored in a particular department.
But I have been unable to set the correct query criteria, to enable to query user to be able to selection from a drop down list, which responsible person or location to pull back the correct list.
I was getting an error asking me to set the parenthesis, I have now deleted criteria for both queries, as even if I put [Enter] and type a Responsible person's name exactly as its held on the table, the report comes back blank.
Hi all, i have a 2 fields in a subform named "HRS_ABSENT" & "ABSENCE_REASON" i'm trying to create some code that will display a message if the user inputs any number into the "HRS_ABSENT" field & leaves the "ABSENCE_REASON" field empty. I want to force the user to select a ABSENCE RESON (these are 3 letter codes) from the drop down list, if they enter a number in the HRS ABSENT field. Ive tried the below code but it doesnt do anything :-(
Anybody please help me out? ------------------------------------------------------------------------- Private Sub Form_BeforeUpdate(Cancel As Integer) If HRS_ABSENT = >0 & ABSENCE_REASON = FALSE Then MsgBox "Please select an Absence reason" Cancel = True End If
I've been looking everywhere to find a better way of finding duplicates in a table and then recording and adding another value in that record together.
Let me try to explain better.
example:
I have a table that has 2 columns "Name", "DOB". I would like to find all duplicate "DOB" and add all of the "Name"'s together.
Quote:
Name DOB bob 19800201 Sam 19761211 Jim 19800201
The output I would like is to have Name = bob & Jim DOB = 19800201.
I've tried using the find duplicate wizard in access but I can't seem to group them together and just to find the duplicates it takes upwards of a minute.
Is there a way in which someone can select a table from a drop down list and then search that table for a record then move that record to another table.
For example.
User selects 'Mikey's_table' searchs for a record then move this selected record to 'Mandy's_table'
(all the tables have the same structure etc. identical apart from the name of the table and records within)
I have the list of tables that all the records will be on and the users will know which table the record is in, i basically need to know if there is an ability to search for a record over multiple tables then edit that record and move it to another Table.
I have tried to use a Union Query which works when searching but i cannot edit or move the record ...
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:
I have drop down list linked to table included "agent names" , the names appeared normally in the form but not Alphabetic (A-Z) although the table was alphabetic .
I have a subform in a form that has a Combo Box that is linked (not sure is that is the correct term) to a field called PartID in a table containing a list of Parts. The list show the Name of the Part (PartName) and other fields.
This all works great except that it allows the user to change the text in the Name of the Part (PartName) field directly from the Combo Box!!!! I really don't want this to be able to happen.I have the "Limit to List = Yes" but it still allows the user to change the value...The "Allow Value List Edits = No"...
I'm in Access 2013 and I built a form with multiple drop downs. Here is what I have in the same order of which the user must choose from......
Document "Field Observation", "Monthly Inspection", "Safety Roster" (The user must first choose the document from this drop down he is recording)
Supervisor "First & Last Name" (The user then chooses the first & last name of the specific Supervisor name he is recording the document for from this drop down)
Manager - This field automatically populates based on Supervisor. AOR - This field automatically populates based on Supervisor. Org Unit - This field automatically populates based on Supervisor.
Month "January", "February", etc. (The user then chooses the Month of which the document will need to be applied to)
I would like the form to trigger an error with "This document is already recorded for this month" when the user chooses "Monthly Inspection" OR "Safety Roster" for a specific "Supervisor" for a specific "Month" that is already recorded in "TBL_DataTracker". The TBL_DataTracker is where all of my records are stored.
Situation: 3 tables. Manufacturers, Countries & Provinces/States. 1 Form for data entry in Manufacturers.
Countries table contains ID, Country and CountryCode fields. i.e. 47, Great Britain & GB
Provinces/States table contains ID, Province/State and CountryCode. as in 1, Alaska and US.
In the form the country is easily selected from a list refering directly to the Countries table.
Problem: How to make a list in the form from which the user can simply select the province for the country that has previously been selected. And not a list with all teh provinces and states from every country in the world. (This would be a really really long list...)
