DB Design To Eliminate Duplicates While Avoiding Compound Key Fields
Nov 10, 2013
I'm getting back into Access after retiring and about 10 years of isolation (from Access). I have 2010 version and know that one should avoid duplicate entries and a way to do this is make those fields unique key fields. I have a Customers table and have bounced back between CusID (AutoNumber) and Compound Keys (CusFName and CusLName) as the key fields. The compound keys prevent duplicates but become very hard to work with later in code and expressions. The CusID is preferable from that standpoint, but can't prevent inadvertent entry of duplicate names.
View Replies
ADVERTISEMENT
Sep 25, 2007
Hello,
I have a problem with one of my tables and don't know where to start to fix it. This is where I stand: I have a table with 5 columns, there is no single primary key. I have duplicate entries of 4 of the 5 columns and a count number as the last column. I need to eliminate the duplicates of the combination of the 4 columns but add the count of the duplicates. Let me illustrate for you to understand better:
this is the original table:
col1 col2 col3 col4 col5
A B C D 1
A B C D 5
A B C D 2
W X Y Z 3
W X Y Z 5
W X Y Z 4
I need to obtain this in a new table:
col1 col2 col3 col4 col5
A B C D 8
W X Y Z 12
Oh, and keep in mind that the original table has almost 6 million rows that will probably get reduced to about 500 000 after this process.
Any help would be appreciated. Thanks
View 6 Replies
View Related
Dec 7, 2007
Hi All,
I have an MS Access table that currently links into word as a mail merge. Great!
Sample data as follows:
Tutor Student
Mr A Donald Duck
Mr A Mickey Mouse
Mr A Minnie Mouse
Mrs B Joe Bloggs
I want to produce a mail document that allows me to address each tutor separately, along with the respective student names in a grid.
Now I've setup the document, but when I view each document it produces the list of all students for each tutor.
e.g.
Dear Mr A
Your students are:
Donald Duck
Mickey Mouse
Minnie Mouse
-------------------
Dear Mrs B
Your Students are:
Joe Bloggs
Is this at all possible?
Thanks:)
View 1 Replies
View Related
Feb 27, 2015
I am using Access 2013 and have created a music inventory database.I have a question about formatting some text in a table. One of my tables has the following fields:
tblArtist
Artist ID
DVD Number
Artist Last Name
Artist First Name
Artist Full Name
In my form, I want the artist's name to appear like this:
Mozart, Wolfgang
Dylan, Bob
Beatles
Aerosmith
I know how to format the table to do this, and I know how to construct the form for this. To format the name, in the tblArtist table, I use the following expression in the Artist Full Name field:[Artist Last Name] & ", " & [Artist First Name]
If an artist has only one name, such as Aerosmith, I enter it in the Artist Last Name column. Artists such as The Beatles or The Rolling Stones, I just omit "The."When a one-name artist's full name appears in the form, there is a comma after their name.
Is there a simple way to eliminate the comma if there is no text in the Artist First Name column? If so, what is the simplest, easiest, most practical novice-friendly way to do this?
View 1 Replies
View Related
Apr 6, 2006
Basically i have a mortgage application system which im running a query on to see which applications have been completed in the last week or period up to now ie. last week, last month, last 3 months.
basically each application or CaseID has one or more applicants. The majority of them have two applicants. A few have one.
Problem is when i run this query which shows me: - CaseID, CLIENTNAME, LENDER, PRODUCT, MORTGAGE RATE, LOAN AMOUNT -
it brings up these fields but i only want one occurence of each CaseID????
CaseID Forenames Surnames PRODUCT RATEDateCompletedLoan required
2821NothandoDube71116.6920/01/2004£63k
2821Sipho Dube 711 16.69 20/01/2004 £63k
2909Hobbly Chise 726 15.69 31/01/2002 £164k
Get the picture? Both Sipho and Nothando are applicants on the same mortgage but i only want to show one name. Anybody know how to help me out???
Ive taken up this existing system, so i think there's possibly a normalisation problem thats causing this. But i need a quick fix for now rather than redesign the whole system.
View 2 Replies
View Related
Apr 17, 2013
I have created a database which holds architectural drawings for my company. The drawings have a number (dwgNumber), a name (dwgTitle) and a revision (dwgRevision). Each number should only have one title but cannot be unique as they can have several revisions. I am looking for a way to allow the users of the database to only enter a drawing title for a drawing number once. This is because if more than one person is accessing the front end at a time, they may use the same drawing number for different drawings.
For Example:
----THIS IS FINE
Number: A001 Title: Drawing 1 Revision: P1
Number: A001 Title: Drawing 1 Revision: P2
----THIS IS NOT OK
Number: A001 Title: Drawing 1 Revision: P1
Number: A001 Title: Drawing 2 Revision: C1
Basically, each number can only have one title assigned to it.
View 1 Replies
View Related
Oct 15, 2004
Is there any way I can stop the same permutation of three field values being duplicated in a table.
eg
a - b - c is ok
a - b - d is ok
b - c - e is ok
a - e - c is ok
a - b - c ......duplicate
View 2 Replies
View Related
Apr 17, 2013
I have created a database which holds architectural drawings for my company. The drawings have a number (dwgNumber), a name (dwgTitle) and a revision (dwgRevision). Each number should only have one title but cannot be unique as they can have several revisions. I am looking for a way to allow the users of the database to only enter a drawing title for a drawing number once. This is because if more than one person is accessing the front end at a time, they may use the same drawing number for different drawings.
For Example:
----THIS IS FINE
Number: A001 Title: Drawing 1 Revision: P1
Number: A001 Title: Drawing 1 Revision: P2
----THIS IS NOT OK
Number: A001 Title: Drawing 1 Revision: P1
Number: A001 Title: Drawing 2 Revision: C1
Basically, each number can only have one title assigned to it.
View 8 Replies
View Related
May 16, 2005
Sorry for the question's wording.
Here is what bugs me:
- I have one table tblRefctc with the followings fields :
LCtcRef,../..,LRefServ0, LrefServ1,..LrefServ16.
Where all those Lref are long; LCtcRef being the primary key. This table describe a contact from different branches (account, etc..) so while each branch has the relevant information about this contact, they know that that branch too is in contact with the same person.
- I have this other table with describes Events, TblIdxEvt (meeting, whatever) :
LRefidx,IdxEvt,IdxCtc
where IdxCtc is filled with LCtcRef once an event is created.
What I must check for is : For the same IdxEvt, should another branch makes an appointement for LCtcRef (i), I must check if one of the LRefServ0 to LRefServ16 are not already added in the event table.
At this time, I am stuck with making functions which test the existence of the LRefServ(i) (i ranging from 0 to 16) in tblIdxEvt, but I wonder if there is a simpler way to do it with SQL (which, as you could guess, Im not good at).
Thanks in advance for any pointers.
View 2 Replies
View Related
Jul 31, 2007
Hi everyone,
I am working on a database to manage newsletter subscriptions. Each subscriber record has the option of having up to four email addresses registered to his/her name.
Is there a way to check for duplicate email addresses in the entire database? It would have to compare all values in all four fields of all records.
Any ideas on how to implement such a thing? I'm clueless...
Thanks!
View 2 Replies
View Related
Jan 30, 2008
Hello,
In access 2003 i have an external Oracle table named EXT-COM_EXTRA_DATA_FIELDS
in this table I have the following fields: COM_DATA_KEY, COM_DATA_FIELD_NO, COM_DATA_VALUE
From this I have created a query named: LOOKUP-BARCODES-10 this looks at the above table by filtering the
the records on the field: COM_DATA_FIELD_NO where this =10.
Also I have created a second query named: LOOKUP-BARCODES-20 this looks at the above table by filtering the records on the
field: COM_DATA_FIELD_NO where this =20
Below is sample data in the table:
COM_DATA_KEY; COM_DATA_FIELD_NO; COM_DATA_VALUE
009A/s 10 03010303165001
009A/s 20 04350309115002
010B/P 10 05150909116001
010B/P 20 06150909116002
011C/X 10 07281809217003
011C/X 20 N/A
The problem is i would like to combine the numbers in the COM_DATA_VALUE field for the same product (ie the same COM_DATA_KEY
record) on the same rows without any duplicate records as shown below:
COM_DATA_KEY; COM_DATA_VALUE; COM_DATA_VALUE (2)
009A/s 03010303165001 04350309115002
010B/P 05150909116001 06150909116002
011C/X 07281809217003 N/A
As you can see from above some records only have data in one COM_DATA_VALUE (like 011C/x) where COM_DATA_FIELD_NO = 10 but no
data for the same product where the COM_DATA_VALUE = 20.
Im thinking maybe i can create a new join query that looks at the above two query’s and combines the values in the COM_DATA_VALUE for
the same product onto the same record row but im not sure how to go about this without creating duplicates & its been some time since ive done joined queries. Any help would be great?
ive attached a cut down version of the database with the table imported from oracle rather than being linked to oracle if this helps.
Thanks
Kevin
View 2 Replies
View Related
Jan 31, 2007
Howdy all,
I'm attempting to set up a junction table.
The help for junctions says to make a compound key in the junction table, but I don't understand the intructions for this. Do they mean make all three of ID's the junction table PK's or is there a literal "Make a Compound Key"
button somewhere?
Currently, the table has an AutoIncrementing field as the PK and the two FK's copied in from the Primaries (converted from Auto to Number). As a result, I can only make one-to-one relationships to the junction table.
Please let me know what it is I am missing here.
Thanks
View 4 Replies
View Related
Nov 19, 2013
I have a large table with many fields and many rows. There is no primary key. I'll call one field ParentPN, and another field ChildPN. There are many other fields as well. I want to identify all rows where BOTH the ParentPN and ChildPN occur more than once. I know how to create a query to identify duplicates of ONE field in the table, but not two. I can solve this with VBA: I will read the two fields of interest in the first row, then compare both values with every other row. If it finds another row with BOTH ParentPN and ChildPN identical with the first, that's a "hit". Then, repeat with all the other rows. I could find ways to make this run faster, but I was wondering if there are any build in functions to accomplish this. I looked at the Find Duplicates query builder, and all I see is I can select ONE field to search for dupes, not two.
View 14 Replies
View Related
Oct 12, 2012
I am trying to modify an inventory management database. I want to prevent duplicate entries to specific fields in centralized table. The table is called Work Stations. Its function will be to track various computer equipment by a specific work station name. Each of the items will have a unique asset tag.
The primary Key for Work Stations is WS-ID. The fields I am trying to prevent duplicate entries in are WS-Computer, WS-Docking Station, WS-Monitor_1, WS-Monitor_2, and WS-Switch. Each of those fields are primary keys in 4 different tables that conation more detailed information about item. The exception is WS-monitor_1 and WS-Monitor_2 have a one-to-many relationship with Mon-Asset Tag in the monitors table.
The goal I am trying to accomplish is when a work station ID is created or modified duplicate entries are prevented to those fields listed above. The computer, monitors, docking station, and switch fields in the work station table may contain data or may be null.
I have tried to set the Indexed Option to Indexed (No duplicates) for each of the fields and I have also tried setting Yes to Ignore Nulls in the index option on the table design tab for the individual fields. Both options have returned the same error stating the changes would create duplicate values, in the index, primary key, or relationship.I have not created form for this table yet as I was trying to get no duplicates option to work first. I have verified the data and the only duplicating fields are the Null fields
View 6 Replies
View Related
Sep 28, 2015
I have 4 or 5 tables. Most of the fields are exactly the name but they all have at least 1 to possibly 5 or six fields that are not in the other table. Additionally there are some duplicates within the individual tables as well as across tables.
i.e.
I have a
Student Table - with all the info on the student as well as a column called student that identifies them as such however it does not have the columns parent, donor, appeal, designation.....
Parent Table - with all the info on the student as well as a column called parent that identifies them as such however it does not have the columns student, donor, appeal, designation.....
Donor Table - with all the info on the student as well as a column called donor that identifies them as such however it does not have the columns student, parent, appeal, designation.....
Appeal Table - with all the info on the student as well as a column called appeal that identifies them as such however it does not have the columns student, parent, donor, designation.....
-A person can be within one of these tables more than once but with all the same information.
-A person can also fall into all of these parameters so they could be on every table with the same information in addition to the missing columns,=.
Question 1 : what is the best way to dedupe and delete the individual tables (they all have account numbers)
Question 2: I was thinking create a new table with all the columns available, however how do i dedupe across tables while populating the additional columns from each?
View 9 Replies
View Related
May 26, 2015
In these three formulas for the Ceil tutorial found here:
[URL]
one of the formulas is:
=IIf(IsNull([RoomNumber]),"",DLookUp("RoomType","Rooms","RoomNumber= '" & [RoomNumber] & "'"))
If one were interested in BedType or Rate then simply substitute that term for RoomType.
Now I am somewhat familiar with how DLookUp works. In other words get RoomType from a table Rooms where there is a given RoomNumber.
Where I get confused is the last clause:
"RoomNumber = '" & [RoomNumber] & ""
I realize it is a compound statement hence the &, but it uses two &s and more.
View 7 Replies
View Related
Dec 23, 2014
I've been playing around with a new database design and ran into a possible 'error' that I would like to avoid.
It's going to be a payroll database to store time codes for hours spent working on specific projects. I have been struggling on how to put this together to fit with what we've been doing for years and I think I hit a few breakthroughs this morning.
However I want to avoid this error of possible duplication of entry.
Simple table set up - primary key is just a running integer; Employee ID; and Week Ending Date.
I can have multiple week ending dates for a specific employee; but I want to avoid having the same employee with the same week ending date. I cannot set up either field as being unique.
Quick run of data that would be in this table:
Code:
1 ABC 11/21/2014
2 ABC 11/27/2014
3 ABC 12/07/2014
4 DEF 11/21/2014
5 DEF 11/27/2014
6 DEF 12/07/2014
7 ABC 11/27/2014
in this example, when the last row is entered I need to get a popup or some warning that this time has already been entered.
View 13 Replies
View Related
Mar 3, 2008
I am trying to enforce referential integrity on tables where a compound key is implemented.
I want to enforce integrity on the DETAIL table so that it can only use an SOR_ID from the SOR table that has a corresponding PhaseID in the HEADER table. Here is my current diagram:
http://i234.photobucket.com/albums/ee261/winshent/IT/Relationships2.jpg
The only solution i can think of at the moment is to build two queries. One which concatenates SOR.PhaseID & SOR.SOR_ID, and another which concatenates HEADER.PhaseID & DETAIL.SOR_ID... and then create a relationship between the two queries.
There must be a much nicer way of doing this though?
View 10 Replies
View Related
Jul 5, 2006
In a form named PRODUCTION there are 3 text box for invoice data: xtype, xserie, xnumber
I need valid this data from the INVOICE table, where its key is a compound key (TPINV+SERINV+NUMINV)
I tried with:
Private Sub xnumber_AfterUpdate()
Dim searchinv as string
searchinv=Dlookup("*","[Invoice]", [tpinv]="& Forms![production].[xtype] and [xserie]= &Forms![production].[xnumber] and "&Forms![xnumber]
If searchinv ="" then
MsgBox ("Invoice doesn't exist'")
Cancel = True
End If
End Sub
... but it doesnt work.
View 1 Replies
View Related
Oct 15, 2013
How to prevent duplicates on the combination of two fields - text & numeric?
I'm currently using the code below that warns users when the combination of two fields have already been used. (Combination of the TWO fields has to always be unique so if used again will warn the user)
Works well when both fields are numeric but fails when the JobDetails field is changed to text in the main table (tblPPMPLanner)
Code:
Option Compare Database
Option Explicit
Private Function IsDuplicateRecord() As Boolean
On Error Resume Next
Dim PreviousRecordID As Long
IsDuplicateRecord = False
[Code] ....
The field that should be a text field is called "JobDetails"
View 5 Replies
View Related
Nov 2, 2014
I have a client database that has recently had multiple duplicate entries. I need to reduce or negate this erroneous activity. I have a client table where I record amongst others, the following;
key
[christian_name]
[family_name]
[dob]
......
I believe that to prevent duplicate entrie via form I have created an additional field called "unique" given it as a unique index which I want to have populated with the joined fields first_name & last_name & dob (IE johndoe01/01/90), and then as user enters a new client it wont allow a duplicate.
However I need to fill all the existing customers (3600+) with the relevant joined existing data. If I create an expression I can cajoin the fields in a select query but when I try to make an update query the same syntax comes up with empty fields.
select query sql that worked to show field ...
SELECT divers.christian_name, divers.family_name, divers.dob, [christian_name] & [family_name] & [dob] AS Expr1
FROM divers;
update query that was empty ..
UPDATE divers SET divers.[unique] = [christian_name] & [family_name] & [dob];
View 4 Replies
View Related
Mar 27, 2014
When you create a column chart and then change one of the series to a line, how can you then apply formatting to that line? I've attached a stripped down database showing what I'm trying to do. Series 1 shows individual monthly values (percentages) and series 2 shows the target which is 80% for each month.
The problem is that although I can apply formatting to the column series, I can't see any way of selecting the line series so I can set the formatting I want. I'm using Access 2003, on Win XP Professional, in case that's important.
View 3 Replies
View Related
May 2, 2014
How to determine if a field within a table is a primary key or part of a compound key using ADO?
View 1 Replies
View Related
Nov 22, 2014
I'm trying to create a compound interest form using loops and the following formula:
TotalVariable = TotalVariable + (TotalVariable * (RateVariable / 100 / 12))
I'm not sure whats wrong with my code and it's not working!
View 14 Replies
View Related
Aug 15, 2013
I'm trying to get a total based a combination of criteria. Here's my issue:
If ([Vendor Billed Amount] > 650 $ and $ [Excess Fee Approved] = "Yes") then
[Payment to Vendor] = [Vendor Billed Amount]
else
If ([Vendor Billed Amount] < 650 $ and $ [Excess Fee Approved] <> "Yes") then
[Payment to Vendor] = [Vendor Billed Amount]
else
endif
endif
I think I'm missing an argument with the nested If statement.
View 3 Replies
View Related
May 11, 2006
I am designing a form for users to write letters in which they give “reasons” for denying a particular service. It is a State-required form letter and there are 7 “reasons” to deny. The user usually chooses just one “reason” and then types in (free-style) the text for the specific rationale (Memo field). Sometimes, however, they need to choose more than one “reason” so I have to design the table to accommodate up to 7 “reasons”.
For the underlying table I could create 7 fields, one for each reason, but this seems like a very inefficient use of disk space as most records would always have 6 blank fields.
Anyone have an idea for a table(s) design? I know this calls for some kind of dynamic procedure or pehaps a separate table to store the “reasons” but I am stuck and my brain is froze.
Thanks in advance for any and all replies!!
View 1 Replies
View Related
