Solved Find same names in different depts?

[Gasman]

Hi all,
In need of some help please.

I have my very first Access database, that generates the reports at www.bibby-gazette.co.uk, emulating a Gazette we used to get twice a year, when we were at sea.

The whole DB hinges on a links table that literally does that, it links to
Dates
Ships
Rank
Crew

plus an autonumber ID

I have now the need to add a Dept to the crew table, as I have discovered that I have the same ID & Name, allocated to different departments. An example is Ashcroft, A who is in the DB as a Chef and also Electrical Officer.
Not a mistake, just that two people had the same surname and initials.

So I want to identify where I have such 'dupes' and add the name again and allocate to another dept and then add relevant records for the new crew member.

I have managed to create a query that groups ID, Surname with Initials and Dept, taken from a newly created tblDept and using their rank from the links table.

The query for that is

SELECT Crew.ID, Trim([Surname]) & " " & [Initials] AS Name, Rank.Dept
FROM Dates RIGHT JOIN (Ship RIGHT JOIN (Crew RIGHT JOIN (Rank LEFT JOIN Links ON Rank.ID = Links.Rank) ON Crew.ID = Links.Crew) ON Ship.ID = Links.Ship) ON Dates.ID = Links.Date
GROUP BY Crew.ID, Trim([Surname]) & " " & [Initials], Rank.Dept
ORDER BY Trim([Surname]) & " " & [Initials];

This shows output like

As you can see 2381 Ashcroft is in E for Engineering and also S for Service.
I am trying to find all occurrences like these, but have drawn a blank. I have tried various groupbys to no avail.
I intend to turn this query into an update query to update the crew table with a value for my newly created Dept field in that table, but for now, just trying to identify the names I need to add again, but as a different dept.

I know I should be able to do this, but for some strange reason drawn a blank, so a hint/solution would be nice.

DB is available is needed. There is no confidential data in the DB.
It is larger than needed as I have all sorts of queries etc that you create on your first couple of DBs.

TIA

 

[jdraw]

Post a copy of database.

An example is Ashcroft, A who is in the DB as a Chef and also Electrical Officer.
Not a mistake, just that two people had the same surname and initials.

But unique id, right?
Seems, if crew can be in >1 dept, you need deptTbl???

 

[arnelgp]

can you Count on CrewID and just return if the Count > 1.

 

[Gasman]

No, a person would only be in one dept. That is my problem. I was keying in names, so when I had Ashcroft, A, it came up again., I just used that entry However, that *might* have been a different person, depending on their dept.
I have now created a dept table, but had a dept code (D,E,R,S) in the Rank table already to define what rank appeared in what dept.

Even now I have created a tblDept, I am going to use the one character code rather that the table ID, just to save one extra lookup.
The query in question is qryURank

 

[Gasman]

can you Count on CrewID and just return if the Count > 1.

Hi arnel,
That is what I thought, but it still shows record not satisfying that criteria.
Hence my post.
Please try. Perhaps I have missed something simple, but the count does not work for me, even when I use qryURank as source for another query.
I use @MajP 's FAYT method to check. That is frmFAYT

 

[Pat Hartman]

Using the wizards just create a find duplicates query. You can only pick one field so pick last name. Then when the wizard is done, you can modify the query to include the first name also. Make sure to update the join.

 

[Gasman]

Using the wizards just create a find duplicates query. You can only pick one field so pick last name. Then when the wizard is done, you can modify the query to include the first name also. Make sure to update the join.

Pat,
I only want duplicates that have different depts?

 

[Pat Hartman]

Remove the ID from your first query so you get just the name and the department. Then find duplicates on that query. Then you go back to the table to pick up the ID. You have to join on both name and dept. You can do some of this with subqueries.

 

[Gasman]

That appears to have done it Pat. Thank You!
ID is same as (or should be the same as) Name, so either appear to work.

365 to add and amend.

Thanks again.

 

[Pat Hartman]

You're welcome? Not sure how this would work with the ID. You are looking for duplicates of a name. The IDs of the two John Smiths are different and so would not be duplicated.

 

[Gasman]

No, if I had a John Smith, then when I was typing the name into the combo, it would come up as found, if already entered previously.
Problem is, might not be the correct John Smith.

I was aware of the issue at the time, but thought it would not matter as a name is a name.
Where it falls down now, is if they exist on two different ships for the same date, or in two different depts.

I now need to differentiate between each.

 

[Pat Hartman]

I guess I don't understand the problem. You can NEVER rely on name to be unique. Even in small sets of data, you can have people with the same name. That is why you always use an ID as the PK to an employee table and take extra care when entering data to ensure that the person doesn't already exist. Perhaps, hw was an employee a few years ago and has been rehired. You would want to reactivate his old record rather than creating a new one.

But, I don't need to understand the problem. If you think my suggestion fixed it, then we can move on