How do you guys handle duplicates?

[Dnallov]

Hi Guys



My DB (see here) is coming along nicely (ssanfu naming rules implemented - the problem from that thread was solved via a simple query with relationships).

I now have a question on how I should handle duplicates. My DB will need to handle them on several different tables, one I have come across straight away is the following...

> I'm adding new 'directors' to my table as and when I add new 'companies' to my DB. There will be some 'companies' that share 'directors', but as I add these directors to the DB, it will create two different records (but with the same name & DOB), whereas I want to check what companies belong to the individual 'director'.

I hope that makes sense!

How would you guys handle the above scenario?

[ItsMe]

I handle duplicates by not allowing them in the database. A common way to describe this is to implement constraints. There are different ways to apply constraints. For instance, you can define rules at the table level while in Design View of the table. Each column will have various properties that can be adjusted to do things, like not allow duplicates when indexed.

Almost always, you can find many ways to manage a single constraint. You might choose to use VBA or use a property of a column or control.

Sometimes a composite key is a solution to manage duplicates when a single key will have duplicates.
https://www.youtube.com/watch?v=uKwLIvV_S9Q

[Dnallov]

That's great, just what I was looking for .

A quick follow question...

I have a table within a form; for each company, I can add a list of directors. The problem is, now I have that index set up, every time I add a new director already in the 'Directors' it (as I would like it to do) flags up as a duplicate record. What I want it to do is automatically assign it to the pre-existing Director; how would I go about achieving this?

Many Thanks

[ItsMe]

I know I am not following perfectly. So I will say that it is likely you will need to consider your Business Rules are dictating a Many to Many Relationship. In these circumstances, a Junction Table can support the necessary referential integrity. Sometimes these tables are referred to as Lookup Tables or Bridge Tables/Relations.

It is basically as follows. You have two entities and three tables. The first two tables represent each of the Entities. The third table bridges the other two by storing their PK's as Foreign Keys. Thus, 'bridging' the two entities.

It seems I have uploaded a many to many example before. I am attaching that example here. Although, I am unsure what is inside. I will check it in a minute to determine if it is relevant.

[ItsMe]

OK, the example I uploaded is not a perfect example because its purpose is to illustrate a different and more complex Business Rule.

The three tables that represent the Many to many are tblLots, tblProductions, and tblHasLots. The two entities are represented by tblLots and tblProductions. The junction table is tblHasLots.

[ssanfu]

I now have a question on how I should handle duplicates. My DB will need to handle them on several different tables, one I have come across straight away is the following...

> I'm adding new 'directors' to my table as and when I add new 'companies' to my DB. There will be some 'companies' that share 'directors', but as I add these directors to the DB, it will create two different records (but with the same name & DOB),

I am confused...
Why would there be duplicates? There should be only one unique director name with a DOB in tblDirectors, just as there would only be one unique company name in tblCompanies
Since one director can be associated with many companies, there could/would be multiple records in the junction table jctCompanyDirector.
So exactly which tables have the duplicates??

<snip> whereas I want to check what companies belong to the individual 'director'.

for a report, I would use a query like

SELECT tblDirectors.DirLastname, tblDirectors.DirFirstName, tblCompanies.Companyname, jctCompanyDirector.Active
FROM tblDirectors INNER JOIN (tblCompanies INNER JOIN jctCompanyDirector ON tblCompanies.Company_PK = jctCompanyDirector.Company_FK) ON tblDirectors.Director_PK = jctCompanyDirector.Director_FK
WHERE (((jctCompanyDirector.Active)=True))
ORDER BY tblDirectors.DirLastname, tblDirectors.DirFirstName;

Use the report sort/grouping.....


Or I would use a main form/sub form arraignment.
Main form record source (in continuous form view) would be a query "qryDirectors":

SELECT tblDirectors.Director_PK, tblDirectors.DirLastname, tblDirectors.DirFirstName, tblDirectors.DOB
FROM tblDirectors
ORDER BY tblDirectors.DirLastname, tblDirectors.DirFirstName;

The subform (in the footer) record source (in continuous form view) would be like "qrysfCompany":

SELECT tblCompanies.Companyname, jctCompanyDirector.Active, jctCompanyDirector.Director_FK
FROM tblCompanies INNER JOIN jctCompanyDirector ON tblCompanies.Company_PK = jctCompanyDirector.Company_FK;

Don't forget to set the linking fields between the main form/ sub form.
Access might complain about both form being in continuous form view but just ignore the warning and set both forms to continuous form view.