Generating Customer ID

[MichaelB]

I am new to Access and I am looking for any help on how I can automatically
generate a customer id based on the following criteria below:
First 2 characters of the customers last name
First 2 characters of the customers first name
2 digit month
2 digit year
5 digit sequential number
example: for john doe making the 1st purchase today:
do + jo + 10 + 08 + 00001 = dojo100800001
jane doe making the 2nd purchase:
doja100800002

If anyone has any better suggestions, I would be greatly appreciative.

Thanks for any help.

 

[Jeff Boyce]

Michael

How do you propose to differentiate between

dojo100800001
and
dojo100800002?

No, these aren't the same person, but two different individuals ("John Doe";
"Jonathan Dover").

Are you wedded (or welded) to the pattern you described?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MichaelB]

No, Jeff I am not, and thanks for the reply. Here is where my simplistic
idea came from, I was originally trying to come up with a simple customer id
that I was going to use in Excel, and yes I do know that Excel is not a db,
especially a relational db, but it was a way to look at a customer id and
automatically tell that they have been a customer since May 2008 for example.
It would also be easy to look up when they said their name was john doe, I
could simply start searching for dojo in the customer id field without
thinking, then search for their name. Please keep in mind that I am new to
Access and "real" databases. I was going to go with ACT! until many people
suggested that I expand on my knowledge of Excel and learn Access, which
would give me much more design/overall capabilities than ACT! would. I am
learning in my study of the relationships map in the Northwind sample db,
that there is probably no need for a customized id, but I just thought that
it was weird starting with the number 1 as an Customer ID. Can you point me
in the right direction. This is my first day with Access I have been reading
as much as I can, watching demos and trying my best to understand.

 

[Jeff Boyce]

Michael

Buckle up, buddy! The kind of mind set that works in Excel will not help
you much (and may interfere) with learning to use Access.

I point out the following three learning curves folks have to get past to
create effective (i.e. get used) applications:
1) relational data base design and normalization
2) Access' tips and tricks (how Access does things)
3) User-friendly (no, make that user-enabling) graphical user interface
design

Oh yes, one more thing... if you haven't done software development before,
you'll need to learn this, too.

If the project is under time constraints and/or if you are not likely to be
given the time/resources to learn these yourself, consider hiring someone
who has already learned these.

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP