Freaky Query

  • Thread starter Thread starter Maxine
  • Start date Start date
M

Maxine

I have the following 4 fields in a form:
LastName (F)
FirstName (L)
Birthdate (mm/dd/yy)
Gender (1 male;2 female)

I now have to create a query that produces a unique file#
for each record. This file number uses the first initial
of first name, first initial of last name, then birthdate,
then gender all in one, without any spaces like:
FLYYYYDDMMG

How do I go about extracting these first letter intials
from the whole name? Then reversing the order of the
birthdate, and is there a way to combine all in one field?

Or would it be easier to enter it manually?

Thanks,
Maxine
 
Maxine,

Eezy-peezy! :-)

SELECT Left(FirstName, 1) & Left(LastName, 1)
& Format(BirthDate, "yyyyddmm")
& (LCase(Gender) = "male")+2 As FileNo
FROM tblMyTable

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
I have the following 4 fields in a form:
LastName (F)
FirstName (L)
Birthdate (mm/dd/yy)
Gender (1 male;2 female)

I now have to create a query that produces a unique file#
for each record. This file number uses the first initial
of first name, first initial of last name, then birthdate,
then gender all in one, without any spaces like:
FLYYYYDDMMG
Click to expand...

Ok. Suppose you have twin brothers, Bob Stevens and Bill Stevens. Same
first initials, same birthdate, same gender - nonunique file number.

Or you could have Mike Cleveland and Mauricio Caldaron, who just
happen to have been born on the same day.

Or you could have someone who was adopted after being found abandoned
who doesn't have any accurate birthdate.
How do I go about extracting these first letter intials
from the whole name? Then reversing the order of the
birthdate, and is there a way to combine all in one field?
Click to expand...

Well, you can DO it: Left([FirstName], 1) & Left([LastName], 1) &
Format(DOB, "yyyyddmm") & [Gender].
Or would it be easier to enter it manually?
Click to expand...

It would be far better *not to use such an intelligent key AT ALL*.
It's not guaranteed to be unique; it can only be made unique by making
it unintelligible (e.g. by adding a meaningless number to all
duplicate pairs).

Just use an Autonumber or a meaningless, programmatically assigned
sequential number. A composite key such as you suggest is essentially
always far more hassle than benefit!
 
Yea John!!

I agree strongly. The Primary Key should not contain any "useful
information". It will lead to problems just as you have stated.
Seems like 1/2 of my life is unscrewing exactly this problem, it seems
almost as strong as the sex urge to somehow code stuff into the Primary Key
for the table (maybe genes we inheirated from early days of flat file
databases).

Cheers,

Ed Warren

John Vinson said:
I have the following 4 fields in a form:
LastName (F)
FirstName (L)
Birthdate (mm/dd/yy)
Gender (1 male;2 female)

I now have to create a query that produces a unique file#
for each record. This file number uses the first initial
of first name, first initial of last name, then birthdate,
then gender all in one, without any spaces like:
FLYYYYDDMMG
Click to expand...

Ok. Suppose you have twin brothers, Bob Stevens and Bill Stevens. Same
first initials, same birthdate, same gender - nonunique file number.

Or you could have Mike Cleveland and Mauricio Caldaron, who just
happen to have been born on the same day.

Or you could have someone who was adopted after being found abandoned
who doesn't have any accurate birthdate.
How do I go about extracting these first letter intials
from the whole name? Then reversing the order of the
birthdate, and is there a way to combine all in one field?
Click to expand...

Well, you can DO it: Left([FirstName], 1) & Left([LastName], 1) &
Format(DOB, "yyyyddmm") & [Gender].
Or would it be easier to enter it manually?
Click to expand...

It would be far better *not to use such an intelligent key AT ALL*.
It's not guaranteed to be unique; it can only be made unique by making
it unintelligible (e.g. by adding a meaningless number to all
duplicate pairs).

Just use an Autonumber or a meaningless, programmatically assigned
sequential number. A composite key such as you suggest is essentially
always far more hassle than benefit!
Click to expand...
 
I don't think J.V. meant for all cases.

There are other situations where it is perfectly logical to use Natural Key
as the Primary Key for the Tables. Note that names & DOB are definitely NOT
Candidate Keys.
 
Ed

I'm with Van on this. A "Natural Key" may be "unique enough" to serve as a
Primary Key, so there's no blanket reason why an arbitrary (i.e.,
Autonumber) value must be used. The issue in this post seemed more pointed
at use of "intelligent" keys -- i.e., embedding (multiple) meaning in a
primary key.

Jeff Boyce
<Access MVP>
 
Jeff Boyce said:
Ed

I'm with Van on this. A "Natural Key" may be "unique enough" to serve as a
Primary Key, so there's no blanket reason why an arbitrary (i.e.,
Autonumber) value must be used. The issue in this post seemed more pointed
at use of "intelligent" keys -- i.e., embedding (multiple) meaning in a
primary key.
Click to expand...

Beyond that, it was never stated that the unique file# was going to be
used as a key in the database. It could just as easily have been meant to
identify paper documents where glancing at it would be of benefit.
 
Back
Top