Number Field Auto Value and Decimal change

[LDP]

Hello Everyone...

Is it possibe to change number values that have been entered in a number field originally formatted as standard/single/1 decimal, such as 40.1 to be automatically converted to read 40.01? Unfortunately, now we need to have 40.09, 40.10, 40.11 to 40.99

Thanks for your time and thoughts.
LDP

 

[willir]

LDP

This seems to be more of a VBA coding question. Or perhaps an SQL question.

If I understand you correctly, you want to increase the decimal part of the number by a factor of ten.

sngOld = int(sngOld) + ((sngOld - int(sngOld))/10)

Note: If these are chapeters or document numbers, although these are numbers, it may be better to define them as text. Similar to why phone numbers are used as text data. (You may get rounding errors, queries become awkward, etc.)

Richard

 

[LDP]

Thanks Richard

Unfortunately, the table has about 1800 existing records related to other tables ,queries and reports. This field is also the primary key, indexed in other tables. We never thought we would have to go beyond xx.9 for any base number xx. These are re-submittals of construction shop drawings. You're correct, I'd like to decrease the decimal number by a factor of 10. For instance, xx.1 becomes xx.01 so we may add the xx.09, xx.10, xx.11 and/or xx.12 records in any given series.

Thanks Richard for help.

 

[willir]

LDP

You have your work cut out for you, but here is a possible plan.

- Backup your database.
- Break your relationships
- Add an autonumber to the main table, and make it your primary key. This will create an autonumber for all records.
- Add a foreign key to all linked tables.
- Use the query wizard to create an update query. This query will use your document number to link the maint table to the other tables, and will add the autonumber to table being updated as a foreign key.
- Repeat the last step for each affected table.
- Run reports and queries to ensure your information is correct.
- Create new relationships using the autonumber instead of your document ID number.
- Edit your forms, reports and queries to accomodate the new schema.
- Any reference to the document ID should referennce your main table using the autonumber. This way, changes made on the main table will be refelcted everywhere.
- You may choose to delete the old document number that exists in the associated tables at some time.

As you can see from the school of hard knocks, editing a primary key is a tad inconvenient.

By the way, I have used autonumber in this example. I am not sure of your environment, but review this site and read up on autonumber. It has never failed me, but there are some subject matter experts that prefer not to use it.

Richard

 

[LDP]

Thanks for your reply Richard.

I'm not completely understanding your idea. Will the Autonumber now be used provide the second decimal place value, ie. 40.09 or 40.11 to match the document number? We receive the documents non-sequentially, that is, we may recieve SD #267.00 to-day but also recieve the fifth revision of SD #40 (40.5)as well. Both documents enter the database as unique records about the same time so there can be no real relation to the Autonumber value and the document SD# value. SD #40.1 may ahve been entered 3 months ago thus having a much smaller autonumber. I think your suggestion of using the equation may be all we need to decrease the decimal value of the numbers in the SD# field by a factor of ten after breaking the relationships. Would you run the equation through a query?
Thanks Richard for you patience on this simple/difficult question.

 

[willir]

LDP

I understand your confusion.

Basically, I am suggesting to use a autonumber or serial number as the primary key. And the Document number will be used as a description.

For example, and I am guessing here...

tblMasterDoc

DocID - autonumber, primary key
SDID - text or numberic, eg. 267.00, 267.01
+ document details - dates, owner and stuff

tblProject (ad libing here)

ProjectID - autonumber, primary key
DocID - long interger, foreign key referencing tblMasterDoc
+ project details...

The project table references the document table using the DocID number which is generated by the system (hence the name autonumber). The user will only see the SDID, your current document numbering system. And the SDID will only exist in one locatiom, the tblMasterDoc, or whatever you have called it.

Lessons learned from days gone by...

In a past life, I worked as a programmer / support rep. The software house wrote account software for government. I had an on-going discussion with one the senior programers. He was using the G/L (general ledger) account number as the primary key. This seemed to work -- except peridiocally, the G/L chart or numbering system had to be changed - incorporate new rules, legislation, etc. Since the G/L account number was the primary key used in transactions, invoices, and numerous balance and history tables, this task was extremely awkward, and took a considerable amount of time -- days and days. And business stopped during the conversion.

I finally won my argument, 2 years later. A sequence number was used as the reference, and the only location for the G/L account number on on G/L master table. Afterwards, renumbering the G/L chart took minutes and not days. Subsequent to this, all other senior programers were instructed to use sequence numbers as the primary key in their tables.

Your situation reminds me of this past situation.

Next, more about autonumbers. When creating a field for a table in Access, one of the types offered is "autonumber". Basically, this is a long interger that the system or Access controls. For simple databases, it is a very convenient system.

The typical way autonumbers works is that uses a sequential numbering system - 1 - 2 - 3, etc. If a person starts entering a new record, the system generates the next sequential number, - 4 - in this case. The one thing that bothers some people is that if the user now aborts the entry, the - 4 - is lost, not used, and the next sequential number is - 5 -. This is just the way it works, and the data integrity is not at risk.

For a replciated database, things get interesting. for autonumbering, Access uses a random number instead of a sequential number. I have never had problems with this, but I know others who have warned developers on this issue. (See FAQ written by MichaelRed).

Per changing your documentation number. Which is your original issue...

If you decide to use SN ID number as the primary key, you will still have to break the relationships between your master and associated tables. Then change the SN number in each table. Then recreate the relationships.

If you want to try using the autonumber, you will only have to fix the SD records in the Master table. But you will have to modify your forms, reports and queries.

After backing up the database
You can run the update with an update query. Review the generated listing before committing. I suspect this would be the simplest way. But it becomes an all or none thing -- once the update is run, re-running the query to fix some missed records may result in messing up other records.

However, would be tempted to do this in code simply because it would allow me to perform the record update which would give me more control.

However, this probably a personal thing. I feel more comfortable using code. I know others would prefer using straight SQL. SQL is definitely faster. VBA would allow you to check records before updating.

Richard

 

[LDP]

Thanks Richard..for the background and explanation for Autonumbering.

Another point I forgot to mention, which is fairly critical, sorry Richard, the transmittal document numbers which I'm trying to pump down the decimal on may have multiple documents under the same submittal number. Example SD# 40.2 may have 5 drawings submitted with it( therefore multiple records with 40.2). So in the database, if I key on the autonumber will it matter that the same SD# field number may have different autonumbers because of the multiple records in each? It sounds confusing to explain..it might be easier to show you the table.

I'm going to study your Autonumber response again.

Thanks Richard
Cheers

 

[willir]

LDP

The autonumber should work.

You asically have a MasterDoc and SubDoc, one-to-many relationship. The master document will contain the primary reference. And the SubDoc records would contain a link, using the autonumber as a foreign key to the MasterDoc. Also, since we have not destroyed the SD#, you can also select for this.

My question is that your primary key is the SD# but you may have multiple occurences of SD#'s?? A primary key is unique, meaning only one. The foreign key when used on the "many" side of a one-to-one or many-to-many relastionship can have many occurances.

 

[LDP]

Thanks Richard.

You're right. The Main table where the SD# is the primary
there was only one record /SD#. Other linked tables have an ID(Autonumber)field with SD# as a foreign key linked in one to many relationships. In the these tables there are many records with the same SD#.

I'll try the Autonumber re-configuration in the main table
thanks Richard.