UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Unique Records, Access 2016    
 
   
jude.nugent
post Jul 23 2019, 07:26 PM
Post#1



Posts: 2
Joined: 21-July 19



Hello all. I am a relatively new(ish) user of Access. I am building a database for a school project. I have written a query that will show me Nurses that have a State License, Life Support Certification, or both. What I am trying to do is create another query off the first that will show me only those nurses that have a unique record. In other words, I don't want the BOTH people (those that return two records), I only want the nurses that have one OR the other (those that return only one record). I have tried changing the Unique Values and Unique Record settings in the property sheet without success. Any suggestions would be much appreciated.
Go to the top of the page
 
RJD
post Jul 23 2019, 07:40 PM
Post#2


UtterAccess VIP
Posts: 9,942
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

QUOTE
I don't want the BOTH people (those that return two records)

This seems the clue to your solution.

If you do a Totals query (GROUP BY) from the query you mentioned, grouping on the name only in one field, counting the records grouped (such as with the person's ID or name) and using a criteria of 1, and showing the Max of the license/certification field, that should give you a list of the nurses with only one certification and what that certification is.

But, of course, we know nothing of your total db, so you will have to adjust from this...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
jude.nugent
post Jul 23 2019, 08:52 PM
Post#3



Posts: 2
Joined: 21-July 19



RJD,

Thanks for the help. It didn't really work the way I wanted, but as I was screwing around with it, I found a Find Duplicates query in the Query wizard.

In (SELECT [EmployeeLastName] FROM [1_StateAndLifeSupportCert] As Tmp GROUP BY [EmployeeLastName] HAVING Count(*)>1 )

I was just able to change the >1 to = 1. So you got me on the right path. Thank you again.
Go to the top of the page
 
RJD
post Jul 23 2019, 10:44 PM
Post#4


UtterAccess VIP
Posts: 9,942
Joined: 25-October 10
From: Gulf South USA


You are welcome. Glad you got that figured out. Good work.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
BruceM
post Jul 24 2019, 06:34 AM
Post#5


UtterAccess VIP
Posts: 7,923
Joined: 24-May 10
From: Downeast Maine


Are State License and Life Support Certification in a related table, or are they both fields in the same record? If the latter, consider that if you add another license or certificate you will need to alter the design of the table, queries that use the table, and forms and reports that draw their data from the table.
Go to the top of the page
 
dale.fye
post Jul 24 2019, 07:04 AM
Post#6



Posts: 148
Joined: 28-March 18
From: Virginia


Just so you understand, using a last name is not a really good way to identify unique records (Jane Smith, John Smith).

Each of your tables should include a primary key (PK) which uniquely identifies each employee. The easiest way to accomplish this is to add an Emp_ID field (autonumber) to each table, although purists would argue that is a "surrogate key", not a "natural key". Natural keys for an employees table would probably include Last_Name, First_Name, MI, DOB (since we never store SSN's if we are at all concerned with data security).

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
BruceM
post Jul 24 2019, 07:33 AM
Post#7


UtterAccess VIP
Posts: 7,923
Joined: 24-May 10
From: Downeast Maine


There are still those who insist on natural keys, but I believe they are missing the point, which is that the surrogate key only represents a unique record, it is not (or should not be, in any case) what makes the record unique. I think most developers accept surrogate keys as representation of a record that is unique even without the surrogate key.

This is not to dispute your point about last names, with which I agree completely.
Go to the top of the page
 
nvogel
post Jul 25 2019, 07:08 AM
Post#8



Posts: 977
Joined: 26-January 14
From: London, UK


Anyone who cares about accuracy and data integrity cares about natural keys. After all, they are the keys that matter to the people who own and use the data. In the case of personally identifiable information modern data protection laws have just doubly underlined the importance of natural keys (the keys used in the business domain). Natural keys can be numbers of course, and often they are values that are assigned to people and things for some particular purpose - like a login name or an account number for example.

As Bruce rightly says, a surrogate key serves a different function and the natural key is just as important whether you also have a surrogate or not - it is more important than the surrogate in fact. People's names don't generally make good keys for obvious reasons.

This post has been edited by nvogel: Jul 25 2019, 07:28 AM
Go to the top of the page
 
BruceM
post Jul 25 2019, 08:13 AM
Post#9


UtterAccess VIP
Posts: 7,923
Joined: 24-May 10
From: Downeast Maine


I make a distinction between a unique index and a natural key. Every record needs a unique constraint (maybe there is an exception somewhere, but that's not the point). Once that constraint is established, a surrogate key represents the data. IMHO it is not relevant whether the number being used has real-world applicability such as account number or employee number.

Things like employee numbers can change (new system, new ownership, etc.). In situations such as a church or club membership database there is no equivalent of an employee number. The way I see it, there is no good argument against a surrogate key provided the uniqueness of each record can be established without it.
Go to the top of the page
 
nvogel
post Jul 25 2019, 08:55 AM
Post#10



Posts: 977
Joined: 26-January 14
From: London, UK


Hi Bruce, I do agree with all you have said. However, I think it's worth pointing out a difference of terminology. If you are saying that a natural candidate key is *not* a key just because you prefer to call it an "index" then I guess that's fine for you personally. However, perhaps that explains why you think (wrongly in my opinion) that people who insist on natural keys are "missing the point". I'm very sure that those people you refer to (I'm one of them) are using the term "key" in its more widely accepted, textbook sense: a candidate key. Their view and mine is therefore the same as yours: a natural candidate key is what, in your terms "establishes uniqueness", which is why it is so important. I mention this distinction only in case it helps the OP understand your point better.

I would also say that, as a rule, it seems unwise to use the term index if you mean key because keys and indexes are very different things - for reasons that would be off-topic to go into here.

HTH
Go to the top of the page
 
BruceM
post Jul 25 2019, 10:07 AM
Post#11


UtterAccess VIP
Posts: 7,923
Joined: 24-May 10
From: Downeast Maine


I think of the key as the field or fields involved in linking to other tables. It seems I am referring to a multi-field index with a unique constraint where you are using the term candidate key. Part of the reason may be that I am using Access terminology, which sometimes differs from terminology in other systems. I am in the process in my company of making the transition to using SQL Server as the database engine (still using Access as the fe), and am finding a number of places where the terminology seems to differ.

As for the definition of candidate key and other terms, I have read several explanations, and have come away with almost as many definitions. The textbook definition may depend somewhat on the textbook being used.
Go to the top of the page
 
MadPiet
post Jul 25 2019, 10:32 AM
Post#12



Posts: 3,220
Joined: 27-February 09



Just to confuse things <g> you could do something like

CODE
SELECT *
FROM Person p
WHERE EXISTS (SELECT 1
                        FROM Certifications c
                        WHERE c.PersonID = p.PersonID
                        AND c.CertificationType IN ('State License', 'Life Support Certification'));


That would return just the distinct records from the Person table. The good part is that you don't need a GROUP BY, so doesn't require sorting etc.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd August 2019 - 08:17 AM