Relationship Advice

[accessfleet]

Hi Sadie:

So you want new system to start where the old system left off. When I transitioned from my old dos based system to new access based system I started a fresh work order number series to permit me to see at a glance old system numbers vs new system numbers.

How many old part numbers do you currently have?

How many new part numbers do you expect to generate each year?

I have found that things level off after awhile.

John..

 

[Lightwave]

I am using bogus data right now too, but it would be nice to have the autonumbers start from 1 again once I start entering real data and delete the fake stuff. Any idea how to do this?

When Access uses the autonumber field which the majority of developers use for Primary Keys they will never reset to 1. They go up from 1 and continue. If you create 5 records and then delete the last 4 the next autogenerated number will be the one after the last and not the one before the 3 deleted records.

As accessfleet indicates as with OEM or Product codes each record is regarded as unique in that databases history and in relational database design the link between tables is completely fundamental to the idea behind relations - renumbering a particular field in one table will not renumber the equivalent numbers in any related (but separate) table thus destroying the integrity of the relationship.

Think of it like x and y chromosomes. Change your chromosomes and your parents will no longer be your parents you will be someone elses child!
A single change will change EVERY single relation forward and back.

 

[Sadie Hewgill]

Ok, so here's the dilemma then, I shouldn't be auto numbering my part numbers because I want to get them back if i delete them. But I want the numbers to be sequential and the user to automatically be presented with the next available part number when they enter a new part. I have heard I can use Dmax +1. How do I implement this?

 

[accessfleet]

Hi Sadie:

I just spotted this on wikipedia made me think of your project. You keep thinning about it and yoll getter done.

John.

Keys are some of the most important objects in all relational databases as they tie everything together. A primary key is a column which is the identifier for a given entity, where a foreign key is a column in another table which refers a primary key.
These keys can also be made up from several columns, in which case they are composite keys. In many cases the primary key is an auto generated integer which has no meaning for the business entity being represented, but solely exists for the purpose of the relational database - commonly referred to as a surrogate key.
As there will usually be more than one datasource being loaded into the warehouse the keys are an important concern to be addressed.
Your customers might be represented in several data sources, and in one SSN (Social Security Number) might be the primary key, phone in another and a surrogate in the third. All the customers needs to be consolidated into one dimension table.
A recommended way to deal with the concern is to add a warehouse surrogate key, which will be used as foreign key from the fact table.[1]
Usually updates will occur to a dimensions source data, which obviously must be reflected in the data warehouse.
If the primary key of the source data is required for reporting the dimension already contains that piece of information for each row If the source data uses a surrogate key, the ware house must keep track of it even though it is never used in queries or reports.
That is done by creating a lookup table which contains the warehouse surrogate key and the originating key [2]. This way the dimension is not polluted with surrogates from various source systems, while the ability to update is preserved.
The lookup table is used in different ways depending on the nature of the source data. There are 5 types to consider [3], where three selected ones are included here:
Type 1:
- The dimension row is simply updated to match the current state of the source system. The warehouse does not capture history. The lookup table is used to identify which dimension row to update/overwrite.
Type 2:
- A new dimension row is added with the new state of the source system. A new surrogate key is assigned. Source key is no longer unique in the lookup table.
Fully-logged:
- A new dimension row is added with the new state of the source system, while the previous dimension row is updated to reflect it is no longer active and record time of deactivation.

Work should be put in to guidance on which situations the options apply to. Is that solely a business decision?
Which factors influence the choice? The update strategy might (full wipe, incremental etc.)

 

[Lightwave]

Ok, so here's the dilemma then, I shouldn't be auto numbering my part numbers because I want to get them back if i delete them. But I want the numbers to be sequential and the user to automatically be presented with the next available part number when they enter a new part. I have heard I can use Dmax +1. How do I implement this?

Sadie this comes up every now and then. Ask yourself is it really important to have completly sequential numbers. Will it be a problem if 99% are sequential and there's gaps here and there where records have been deleted?

And why shouldn't you be autonumbering your parts...? Isn't DMax + 1 autonumbering them in a different way. All numbers are related so its the nature of arithmatic that to count is to autonumber.

The autonumber facility essentially does exactly what you are asking it keeps a private tab of the latest number and then allocates the next number when a new record is created. It ensures that this is totally unique and it ensures that no other future record has the same number from now until eternity.

It will have the result that the total record numbers aren't likely to be the maximum PKID value. For example if you create a database and then make 40 dummy records and then delete. Although there may be no records in the database when you come to create a new record the first record is likely to be autonumbered 41.

Compacting the database would reset the autonumber to 0 such that the next record is 1. However if there is one record in the database with PKID of 41 compacting the database will result in a new autogenerated number of 42.

That's just the way the world is.

If you really must have sequential numbers as a code. For example you only have 10 products and for branding purposes you want to almost brand the values 1 to 10. I would avoid trying to manipulate the PKID and create a separate field that you just enter a value into but then this field essentially becomes the products name.

Be warned though I think if you create enough "products" and you will start getting duplicates.

 

[accessfleet]

Hi Sadie:

I think Lightwave is on point. Your system will need to be flexible. Historically things change. If you design a rigid system, the changes will make you nurts.

 

[Sadie Hewgill]

unfortunately there is a system already in place. We have already assigned numbers to real parts and now those numbers have meaning. So I have to be able to change the numbers I am autogenerating to be these specific numbers, and then I would like to have them sequentially generated after that. I think Dmax gives more flexibility there since the data type is actually a number i should be able to edit it

 

[Lightwave]

Sadie Ok I see you have historical information.

If I were you I would keep using autonumber to generate PKID that are used in the background to link any tables. I would then store the product code in a separate field and for all new records going forward this could be autogenerated. You could use a number of different ways to generate this including DMax. If you are ditching the old system you probably won't have to edit this product code very often. If you are keeping the old system you will need to go back to it and obtain the product codes to generate new codes to ensure that the two sytems match.

Assuming you don't have to go back to the old system
In Access 2003 you can generate an automatic product code as follows...

Within the form create a text box for the field Product Code.

Right click within the fields and select Properties a new windo appears and move the cursor to Default Value.
Clicking on the dots button to the right will take you through to a half way house where you can choose the formula creator if you want you could create a simple default of something like (format may vary but experiment)

="Suffix" & [PKID]

This will be subsequently editable and only created when a new record is created. You shouldn't have any issues either with linking tables because the real linking occurs on the PKID so all data integrity is maintained. For referential integrity Access 2003 requires tables to be related via long integer fields so linking them through a product code may not work if there are already characters within historical product codes. You may need to do some initial data manipulation if you have historical related tables as they will not contain the surrogate PKIDs relating to the header table that will be created the first time you import the old information.

Creating these surrogate ID field and updating the related tables will be a quick job though once you understand what you are doing.

 

[Lightwave]

Sorry came to the party a bit late and didn't read all of the first page missing it completly apologies if I have stated points already made.

 

[Sadie Hewgill]

I found a way to reset my autonumbers so now I think I will just go with that, and not delete any until I put the real data in.

 

[The_Doc_Man]

Sadie, just remember this: AUTONUMBER is a valuable tool but it must NEVER EVER have a meaning before-the-fact other than "unique identifier for a record." To use an autonumber is to invite grief if the number has ANY OTHER MEANING AT ALL.

Among other things, if you have a problem entering a record and have to back it out, the autonumber DOESN'T back out. It was already committed. DON'T rely on autonumbers for anything that has external meaning. I'm telling you this now to save your sanity later.

Use autonumbers for things like "transaction ID keys" or "arbitrary invoice numbers." Things that don't have to be sequential and in fact could easily be random without loss of function. Using autonumbers for anything structured is a serious mistake.

 

[accessfleet]

Hi Sadie:

What the doc man said is a more precise way of saying what I tried to share with you.

It sounds like you are ready to go so good luck..

John.