Defining automatic primary key

[EllieT]

Hi all,

I've been fiddling around with creating a new database (Access 2003, Windows XP) for work the past two weeks and am having trouble defining my own customized (yet still automatic) primary key.

I have three different tables that need data inputted (Clients [P], Complaints [C], and Queries [Q]) and need individual primary keys for each that are easily identifiable by the first letter and indicative year.

I was hoping for something along the lines of P08/, C08/, Q08/ etc... then have the record number after it.

I've tried making the list in an external program and linking it - doesn't work.
Tried making a separate table to link to it - doesn't work.

I'm not sure if making a new row for 'pre-primary key' would work - since I need to report on the full number (my pre-key and the automatic key combined).

Is there any way I can do this? I've been scouring the internet for any info for hours now and my eyes are starting to blur ;)

Thanks for any help. This isn't urgent but was hoping to get this database finished and out of my hands by next week if possible.

[Dököll]

Hi all,

I've been fiddling around with creating a new database (Access 2003, Windows XP) for work the past two weeks and am having trouble defining my own customized (yet still automatic) primary key.

I have three different tables that need data inputted (Clients [P], Complaints [C], and Queries [Q]) and need individual primary keys for each that are easily identifiable by the first letter and indicative year.

I was hoping for something along the lines of P08/, C08/, Q08/ etc... then have the record number after it.

I've tried making the list in an external program and linking it - doesn't work.
Tried making a separate table to link to it - doesn't work.

I'm not sure if making a new row for 'pre-primary key' would work - since I need to report on the full number (my pre-key and the automatic key combined).

Is there any way I can do this? I've been scouring the internet for any info for hours now and my eyes are starting to blur ;)

Thanks for any help. This isn't urgent but was hoping to get this database finished and out of my hands by next week if possible.

Hello EllieT!

Would you like to add your VBA code for a closer look?

Sorry for your troubles!

Dököll

[EllieT]

Hello EllieT!

Would you like to add your VBA code for a closer look?

Sorry for your troubles!

Dököll

Hi Dököll and thanks for helping.

I just went into the Visual Basic screen but its blank (can't click 'view code' or anything) :/ I haven't fiddled with the DB in the VBA coding area as yet (just design/database areas) - that's why I thought I must've been missing something.

Cheers,
Ellie

[Fiddler2]

Do you want the record numbers to be automatically generated too? If so, you would add that into a COMPOSITE key. This is how:
First, in your table, setup a key called (for example) fldRecordNum with a type of Autonumber. Next, setup a key called fldType as a string data type and another called fldYear that is an integer. You then click on the outer left bar to highlight all three at once and click the little key in the toolbar that indicates they are now a "primary key."

Set the default property of the fldType to a C or P or whatever and the fldYear to the currentyear.

I might add that this is a difficult key to work with and although it might seem easier to you from an enduser perspective, it won't be that easy from a developer perspective. Why can't the enduser just read the title of the primary key, that shows up in the column, for example, "PersonnelID" or "DogOwnerID" to know which one he/she is referring to? What does the date embedded in the key mean? Is that the date that the person started or that something happened? Why not just have a field with this date in it that you reference when you build queries? And I'm really curious as to why you are building a table called "queries" unless you are building metadata. It sounds like you are coming from the perspective of someone who has worked with large flat files, like those in a hospital or something. If this is the case, I would encourage you to really get the definition of "relational database" down before I attempted to build anything. It will save you muii headaches later.