Querying a staff database cannot use the key...

Question

0.00/5 (No votes)

See more:

The Boss wants to generate and attendance list for training from the staff database, does not want a unique identifier like a social security number, prefers to enter a Lastname, and have that person added to the list.

I run into an issue when multiple people have the same last name, would like it to ask me for a first name only when a duplicate Lastname is found...

What I have tried:

SQL

SELECT DISTINCT Staff.LastName, Staff.Firstname, Staff.Email
FROM Staff
WHERE {{Staff.Lastname}=[Lastname 1]} AND {{Staff.Firstname}=[Firstname 1]}};

//repeats for eight staff member inputs - max students for the training...

Posted 14-Sep-20 18:30pm

Member 14938351

Updated 14-Sep-20 22:13pm

Maciej Los

v2

Add a Solution

Comments

Sandeep Mewara 15-Sep-20 0:35am

Is email a mandatory field? If so, would suggest combine name and email as the identifier to differentiate people.

Member 14938351 15-Sep-20 1:22am

The email is the reason the list is being generated, the only known is the name.

Maciej Los 15-Sep-20 4:13am

You'll need to write a VBA code to create such of functionality.

Member 14938351 15-Sep-20 10:00am

thanks... I will need to find a tutorial...

[no name] 15-Sep-20 9:55am

So, how many people are we talking about? I know people get weird around employee / payroll records. Let HR sort it out.

Member 14938351 15-Sep-20 10:02am

a list of about 200 people... nothing major no one cares about access to the database records... the way I have it working now requires entering everyone's first and last name (2 prompts per person) ugh... and while they are fine with it - I am not fine with it... using the social would have been the best way - they dont want to use socials... so here I am...

[no name] 15-Sep-20 11:39am

Just dump the list to a "data grid" or excel sheet and let the "user" check off the names. Then use the selections as input to a query. Most of the time users need "something" to move forward with. Then they may come up with more concrete filters. 200 (sortable) names is not "too much". Tell them it's a "black / white list".

Member 14938351 17-Sep-20 13:33pm

They already have something like that ... they literally want this to generate a list to insert into another attendance form, to avoid dealing with bad penmanship. You know people want what they want lol...

Otekpo Emmanuel 15-Sep-20 13:23pm

Then what happens if two persons share the same last name and first name? Cos me, I shared same last and first name with someone that is not even from the same tribe. The best is to create a unique identifier to avoid duplicate results.

If you intend to use Dinstinct rows, then some people names might be omitted

Member 14938351 17-Sep-20 13:31pm

Currently what happens is that by default it prompts for last name and first name. The purpose is to generate a list of emails for a select group of people that you have scheduled for training. At this point there is no one with duplicate first and last names in the database... while there is a distinct key in the database... the people wanting the report do not want to enter ss numbers... its more preference than practicality...

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)