hello,
In excel i used the following formula =IF(COUNTIF(U$2:U$900,U3)>1,"Duplicate","")on one field to find duplicate, but I cannot come up with similar formula on column fields in a table.
Any guidance?
Thank you in advance
Niri
I'm currently in the process of doing a an overhaul of the database including a table, some odd 17,500 records long.
What I need to do is to create a unique identifier from the existing part number, almost like an autonumber, but it only increments when there are duplicates. What I mean is that there would be for example, there would be five records of say, part 000135.
What I'm trying to get at is, how I would have to code to grab the existing part numbers (all formatted to be 6 digits), add an extra 2 digits to be its unique identifier, and then save it all to a Unique Identifier column, that I created.
An example of what I want is say, I have part numbers 000135 and 000136. The numbers would go up from 000135-01, 000135-02, 000135-03, 000136-01, 000136-02.... The dash is not as important as implementing the 2 digit unique identifier.
Hi All.
Is there a way to index and create a unique identifier using a combination of fields (eg. User# + Provider + Date)?
Thanks.
I have a database wit the following fields
have a database with the following Tables
tblCD/Main: CdID - autonumber, CDName - text ...anything else will be a lookup field that we will get from another table, so we will come back to this table
tblAuthorType - AuthorTypeID - autonumber, AuthorTypeName - text. author type name will have, as I see it, 3 types : Composer, Group, Artist.
tblAuthorName - AuthorNameID - autonumber, AuthorName - text, - the name of all authors, composers and artists that u will pick from later
tblecdtrackautonum-trackname track number
Each Artist will be the same for Each CD
the tblCDMain add a AuthorName - lookkup to tblAuthorName
The lookups I understand fine it's relating the identifier the same in both places. I want to have list boxes that display cd groups and cdname the cdnames related only to those groups
How do I try relating the cd name table
to the track listing on the field of cd identifier which is the same in
both places. IS the identifier the same for the primary key
Should put together a query between the two tables, and relate them on that
one field (cd id) then the query will pull the tracks which are listed for
that id and the cd names which are listed for that id.
I need help with this to figure out the identifier is it cdname and cd tracks?
Please someoen help I need a response based on tables for my database NOT ON A DATABASE FROM A BOOK WHERE I CANT SEE A CONNECTION LIKE A PLANTS DATABASE THEN I DONT KNOW IF PLANTS IS THE SAME AS FOR THAT ID. ANY HELP APPREICATED/
mikevds@optonline.net
I have a database with several tabs. On each tab is a form in datasheet view. I have a field that when clicked, will open the corresponding form in a dialog. The code used to open the dialog is: =IIf(IsNull([SalesOps_ID]),"(New)","Open") This creates an underlined Open link which, when clicked, opens the dialog.
An embedded macro is attached to this code, telling it which dialog form to open...and a bunch of other stuff I really can't explain (I'm great at stealing shamelessly from other similar projects but don't completely know what the heck I'm doing).
That said, all the other dialog forms work splendidly using this same type of embedded macro, except one very stubborn dialog form that is giving me fits! Interestingly enough, this form has a subform (all the other forms don't). The form opens, I can edit data on the main part of the form, but if I enter data in the subform, when moving to the next record, I get the error message: The identifier [Tasks].[Project ID] could not be found.
Interestingly enough, I don't have any tables, forms, queries, macros or fields that contain anything like [Tasks] or [Project ID]. If I move off the main record, then return to the record, the data in the subform does not display (however, it is in the table - just not displaying). As if that isn't interesting enough, when I open the dialog form on its own, without using the Embedded Macro from the datasheet form, all that data shows just fine and the form works like it's supposed to.I wish there was a way to simply copy all the text from the macro here, but I can't seem to figure out how in Access 2013.
hi guys, i am slowly learning access by browsing these forums and using microsoft help and downloading example databases. problem is - i have no formal training in it and am just sort of poking around. how did you guys learn? is there a good book out there to read, or did you take a course in it?
right now i am trying to develop a database of tracking product through a factory. technicians would scan the barcode for a pump before and after every process. i made it a simple table coupled with a form where they input the barcode # and a timestamp was coupled with it. later, youd be able to graph the time stamps so you can see process times, how many are in work in process, etc.
my specific questions are:
when i use the timestamp feature, it updates the time when a new record is brought up, but i want the timestamp to register when they click the add entry button. so far i have the default value =Time() for one, and =Date() for the other ...
next is, i want each workstation with this open to have its own unique identifier. for example, one program in assembly station should automatically input "assembly" for where the record was added in, how is this done?
lastly, i have a barcode scanner that operates like text input, i have an area on the form for the barcode, so when it scans it enters the number. is there a way that once the field gets 8 digits entered into it, it automatically goes to the next field on the form? i would like for users to pull the trigger and not have to hit enter again ...
a big THANK YOU for all you access gurus, i have been browsing these forums learning a lot. i hope i can achieve your level so i can help others as well. Thanks again
- Dave
i am having an issue with sql in vba using a variable for a date time field.
i know you identify date variables with #
i know you identify text variables with '
But now i have a date time field that is set to date but when i run the sql it does not find a match. i have confirmed that both rec and ercd match.
Also, when i change the table to text, i am able to match with single quote but i would like to keep the table as date if i can.
Below is the sql statement in question
isql = "SELECT * FROM Table1 WHERE from = '" & nm & "' AND rec = #" & ercd & "# AND sub = '" & esub & "'"
I'm looking into create a small unique identifier to put on top of each invoice generated by the database. (.MDB A2010)where '000' its just 3 zeros added for extra badassery, '1' is the actual ID from the database table and -2014 its the current year when the record was added...So maybe I have info from 2 fields (ID, YEAR)... but How do I add the ceros to the ID field... so its 0001 not 1 or 0135 not 135... ???.
View 5 Replies View RelatedAny way on a table that I can have a unique identifier over two fields? E.g.I have a login ID and a Domain field in a table with sample data below.
Login Domain
John Test
Jane Test
Fred Live
John Live
The login names need to be unique to the domain so the integrity cannot be breached. This has to fit into one table (although the domain is actually being pulled from a look up table so is in fact a numerical value) for ease of form creation.
I have a database in Excel that contains farmers who can be identified by a Unique identifier: MZ-01-0001. The registration details for these farmers are not always up to date. Now when I try to append the data for the farmer with MZ-01-0001; with the primary key set on the Unique identifier, Access does not want to do it since there is already a record in the system. When I remove the primary key it doubles the record, which is also not what I want. So I am wondering what I need to do to append the record while keeping the primary key intact.
View 4 Replies View RelatedI have a large table with information about different cars, called "Car Metadata". In this, each car has a unique identifier ("Car Code"), the make ("Manufacturer Name") and the model ("Short Model").
What I want to do is create a form with dropdown menus.I have a first combo box with all the makes in the Metadata table.
What I am having trouble with is that I want a second combo box underneath which would only show the models which correspond to the make selected in the first box.E.g. someone could select Ford in the top box and the second box would only show Fiesta, Focus etc.
Once it has done that I want it to be able to write the Car Codes for the records which match up to the make & model selected to a new table. Just to complicate things further, there can be multiple records with the same make and model but different Codes, I want all these multiple codes to be written to the final table.
I like to have a Auto number field in my table (tbltask) that use below codification
B10000001
Where B = Base code and 000001 = Identifier of the task.
The identifier of the task is a unique 7 digits number
Hi,
I wonder if someone can help with what must be an easy solution to this problem.
I have a table with 350,000 records, but without a unique identifier field. I just want a simple incrementing number field to become the primary key but dont know how to add this in. I cannot add an autonumber as there are two many records - it falls over.
Ive tried exporting it all to excel, but as well as being cumbersome, something went wrong and so I'm back to square one.
I think there must be a way to add an incrementing number either via a query or in VBA but I dont know how. Please can somebody provide a solution.
Thanks,
Adam.
To prevent duplicate records, I use multiple fields indexes, which worked fine until now. I learned that each "empty" fields are consider unique by Access, so not the best in an index to prevent duplicate records. I managed to have it worked using the default value property to give each "empty" records the same value.
Now my problem is that I have a date field which is optional, but I need to use it as a unique identifier in an index. I could again use a default value, but since it has to be a date, I find it confusing. I would rather have a default value of "No date specified" or "-" or even better, "". Unfortunately none of those works with date fields.
Have a table with data in it and I want to add a new field to uniquely identify each record (simple numerical sequence like 1, 2, 3, 4.....). I don't know VBA and don't know SQL.
Tried opening the table in design view, adding a new field with "autonumber" as the data type but get the following error msg: "File sharing lock count exceeded. Increase MaxLocksPerFile registry entry". The table has about 750,000 records in it.
So i have a access database with a main field that we can call vendor # (LIFNR) and another called Company code (BUKRS). There are multiple company codes under a single vendor #. Example:
LIFNR
BUKRS
0000010535
1010
0000010535
5060
0000010535
5610
0000010536
1010
0000010536
5060
0000010536
5610
What I am trying to do is create a 3rd column where i can have a unique row for each of these fields without it repeating. There is no unique identifier in this table and that is what i am trying to achieve.
How to get this one to display in a single column.
I know how to do this wiht VBA. But, this output will need to reside on a SQL Server View. So I need a SQL language solution. If it can work in MS Access Query, it won't be too difficult to test then translate to SQL Server.
Customer Table with PK Customer_ID.
There are two tables with FK Customer_ID.
1. Table Lease1 - Has 3 Fields - the form code enforces No Fields -or All Fields. The red * indicate a Required field - These 3 are entered together.
2. Table Lease2 - Has 1 field with 0 to Many records.
Goal:
The Type shows up in a single column.
Each Type shows where the data comes from (Lease Type, Surface Owner, Mineral Owner, or Hz Lease Type)
Challenge:
Lease1 table has 3 fields that need to be transformed into a single column.
Lease2 table has 1 field to be appended to the single table.
Then, there is the column that identifies where the data came from based on the column name.
We are working on an Access (2007) database that is on a SharePoint Site (2007).
Currently the form is operational, but there is one last thing that would be nice to have.
The table is "Updated Headcount" which contains "EMP_ID" which are unique numbers stored as text.
In the event a new employee is entered in the system by another user on this site we would like to prevent any duplicate "EMPID"s from being entered and saved on the SharePoint, we would also like to alert the user and prevent the data from being saved.
All data is currently bound, so once the user makes a change it is made, no submit button is required.
We are running into some difficulties in doing a dlookup from the value entered and comparing to a column in the table.
TABLE - UPDATED HEADCOUNT
COLUMN in UPDATED HEADCOUNT - EMPID
FORM CELL user will input an EMPID - newEMPID
FORM CELL used for a dlookup to compare what user has entered to what is already in the table - duplicateEMPID
So below is what we are trying to do, we are sure there are a few commands missing....
=IIF(newEMPID=dlookup([UPDATED HEADCOUNT]![EMPID]), newEMPID, "Error, EMPID already exists")
I have a table with duplicate rows. How do I de-duplicate the table so that there are no duplicates and find out how many duplicates there were in the table.
Thanks:)
Please i am trying to create a form that will keep records of perspnnel but i want a situation whereby if someone enters a last name and first name that is already in the record, the database should send a message telling the user that such a name exists.
Regards
Henry
:confused:
I have got to create a car hire db for a course i'm doing.
I have a vehicle table
I have a customer table
I have to be able to book more than one vehicle to a customer.
I tried putting vehicleID into my appointments table twice but that is wrong.
Any solution would be great.
Hi All,
I have a table that has a device column and a tag # column, I want to pull out a report that shows duplicate tag # for different devices, the tag # can only be assigned to one device, I received an excel sheet that has to be fixed, I have the sheet in an access table. Please help. For example: device 123 and device 345 have tag #abc. I can’t simply create a query to show duplicate for tag# because I can have duplicate # only if it’s the same device.
I have a form with a bunch of fields. Now their are three im concered with. CFR_EHPID,CFR_SVCDATE,CFR_PROVTAXID. If thier is a vaule in the database when the user tries to enter it again, i want a dup message to show...... SO i dont want any duplicates for those three feilds. is their a way when the user is done keying and tries to move to the next record it displays that thier is a duplicate??????. below is the code i use but its for the before update event
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.CFR_EHPID.Value
stLinkCriteria = "[CFR_EHPID]=" & "'" & SID & "'"
'Check StudentDetails table for duplicate StudentNumber
If DCount("CFR_EHPID", "CFR", stLinkCriteria) > 0 Then
'Undo duplicate entry
'Message box warning of duplication
MsgBox "Warning Student Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
'Go to record of original Student Number
End If
Set rsc = Nothing
End Sub
I currently have a table that had 30 fields, the unique fields are survey, date, and business name. I'd like to create a query that will display all of these records so that i can get rid of them. They were imported from another table that another user created. If survey, date, and business name are identical i'd like to get rid of them.
View 1 Replies View Relatedpls can someone point me in the right direction
have searched through the forums.
i have two fields Forename & surename i need to prevent duplicate records if the forename and surename match another record.
i have a table with 3 fields: ID, line_item, and pay_app.
line_item and pay_app are linked to other tables in a one-to-many.
For every pay_app, there can be up to 100 line_items. However, I only want each line item to appear once (at most) in each pay_app. I want to prevent the same line item from showing up multiple times in the same pay_app. Essentially, I want to prevent duplicate entries, but with a stipulation, if that makes sense.