Creating a Unique index

[Colin Coleman]

Hi
I have inherited a database that creates employee contracts in word 2007 that are launched from a form in Access 2007. i am looking for the correct way to be able to have a unique employee number and a version record,

I need to ensure that only the current live record version is printed or available to edit.

i need to enter the employee number and the system would then maybe run an sql to see what the next available issue number would be.

I use Firebird normally and i would create a Unique Compound Index on the employee number + Version Number, Then when an employee list is presented it uses the employee number and the largest version number to retreive the correct record.

How can i achive this in Access 2007.

Thanks if someone could point me in the right direction.

Colin

 

[Rabbie]

Try looking up the DMAX function in Access help

 

[Colin Coleman]

Brilliant, thats cool,

How do i go about creating a unique index on the table to stop employee 1000 version 1 being allowed again?

Many Thanks
Colin

 

[georgedwilkinson]

Go into table design, select the "indexes" button at the top, name your index, select your fields, and click type in "Yes" for "unique".

 

[Colin Coleman]

Hi George,
Sorry i think i missed the little word "compound" index where i can have many employee 1000 records but only 1 record with version 1....

Sorry

 

[Pat Hartman]

I would use a separate table for history. Keeping multiple versions of the same live record will present constant problems with joins. Leave the most up to date version in the live file and as a change is made, store the old version in the history table. The history table will only be used for reporting and will never be joined to "live" tables.

 

[georgedwilkinson]

Yeah, it sounds like "version" is an attempt to bypass good design rules. Don't do that or you'll never figure it out with a hundred experts helping you.

Keep your version in a separate table, as Pat suggested. Between the 2 of us, we've got over 50 years doing this stuff.

 

[Colin Coleman]

Thanks Guys, im gonna forget about compound indexes in Access and do it your way by making a copy of the master table and archiving each record in when the employee contract is printed.... much safer as you say when you dont understand the odd ways access works.

Im sure i read you can create compound indexes on two fields in access ...maybe i was mistaken, i only use compound indexs to ensure the integrity of the database....but hey....its Access !! i gotta go with the flow, and you guys must have been there before so i rearly appreciate your reccomendations as regards to not using access to look after data integrity....

off to do a heap of recoding.........Cheers guys....keep it up

Regards
Colin Coleman

 

[neileg]

You can create compound indexes in Access, it just that your application is one where there is a better answer to your question.

 

[Colin Coleman]

thanks Neil,

Any chance of stepping me through how to actually do it, because the dialog box that lets me create indexes, wont allow me to type 2 field names on one line ...... where did i go wrong ?

Cheers Colin

 

[The_Doc_Man]

That box has more than one column. One of the columns lets you name the index. Let's say you have an index you call Fred. (Hey, it's a nice name!) Index Fred consists of fields Bob and Ted. So in the first column, name Fred. Next column, name Bob. On the next line (if that is the box I'm thinking of), SKIP the first column and put Ted in the second column.

If it's the wrong box, then "my bad"

 

[The_Doc_Man]

As a side note, I'm glad Pat posted on the idea of versioning in the same table. If it weren't for having to help with preparations for Hurricane Gustav, I might have taken the time to analyze this. Here's my take...

We talk about normalization and how it helps you keep things straight. But there is another view of it. Normalization helps you isolate the entities you deal with in your business model, making them "purer" (more pure?) representations of reality.

A model in which you have a thousand versions of employee Fred is misleading, because all you have today is Fred. Not a thousand Freds. (No matter HOW valuable he really is...) Therefore the table misrepresents Fred's reality.

If you make archives of Fred's single-record description, time-tagging the archive with the date on which it was made, that archive table becomes the description of Fred's history, which is different from Fred's current reality.

Therefore, in theory, the multi-version table was serving two different purposes, thus blurring its purpose and making it harder to represent what you wanted it to represent. By splitting the table to "current Fred" and "Fred throughout history" you purify the two tables and completely isolate the inconsistencies.

Now, the "practical" side of that...

If your old table has a compound primary key of Fred's ID and the version ID, then when you ask "Which record represents Fred?" the answer isn't instantly clear. To be purely normalized as a personnel table, only one record should exist with Fred's ID and represent him. But that purity is lost with versioning. The "versioned" table doesn't represent people, but rather represents people-oriented historical data - because that is what the KEYS say it represents. I.e. we can infer table purpose from its PK, and that PK says "personnel transactions." In a pure personnel table, if you have Fred's ID, you find Fred.

Of course, all of this is 20-20 because it is hindsight.

 

[Pat Hartman]

I like your explaination

 

[Colin Coleman]

So Do I...... SO i am purified.....Now i just have a little coding to do to copy the 20 memo fields over, not sure where to start doing that, but im looking .....

I had to use memos because the Word inserted text is about 10K in some cases.

Anyway....very helpfull

Thanks Guys...#