Generating Customer ID

  • Thread starter Thread starter MichaelB
  • Start date Start date
M

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.
 
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
 
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.
 
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
 
Back
Top