Newbie question about creation of tables

Question

Hello to all!

I'm Ronald and a newbie in MS Access.  I am trying to create our stock management list cause currently we're just using excel spreadsheet to manage our products list.  I'm just starting to create a database in access.

My first table is ProductList with fields ProductCode(PK), ProductName, OrderClientType, TypeOfUse. This table will be seldom touched or manipulate.  

The second table is called BatchNumCode/Quantity consists of fields BatchID (my PK, set to auto increment), ProductCode (my FK),  BatchNumCode and Quantity. This table will be updated from time to time as products come and go almost every 3 to 4 days only and a product can have 2 or 3 batch number codes.  

My question is, in table BatchNumCode/Quantity, ProductCode will be deleted and replaced by a new batch number very often.  Would it be ok if i use a primary key with autonum/increment or ramdom considering that entries will be deleted and replaced from time to time ?  And how will this affect in the long run.

i attached here the table relation.  thank you and sorry for my english.

Answer 1

My question is, in table BatchNumCode/Quantity, ProductCode will be deleted and replaced by a new batch number very often.  Would it be ok if i use a primary key with autonum/increment or ramdom considering that entries will be deleted and replaced from time to time ?  And how will this affect in the long run.

Hi Ronald,

You can safely use the primary key with autonumber/increment. The numbers will ONLY be used internally by Access to join records, so there is no reason to bother on the gaps.

The autonumber is stored as a Long number, thus a range of 0 to about 65000 * 65000. If you add about 200 records per day, or about 65000 per year, it will take about 65000 years before you are exhausted of numbers. When you reach the limits of the numbers, you would also have reached them with random numbers. In that case you are ready to move to 64-bits processing.

Imb.

Answer 2

so sir, even if that record becomes obsolete in just 3 days and the quantity is zero and we'll no longer refer to it in the future, it's ok if it resides there?

Thank you so much sir for your quick reply.  now i can continue creating the database.   

Answer 3

so sir, even if that record becomes obsolete in just 3 days and the quantity is zero and we'll no longer refer to it in the future, it's ok if it resides there?

Hi Ronald,

You can delete such a record, or you can mark it in one way or the other as "not actual anymore", using e.g. a time stamp. In the latter case you could see it as a kind of archieving, for later reference.

Imb.

Answer 4

For an illustration of how a record can either be permanently deleted, or marked as deleted, with the facility for restoring it later if necessary, see DeleteDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

---

Ken Sheridan, Stafford, England

Answer 5

so sir, even if that record becomes obsolete in just 3 days and the quantity is zero and we'll no longer refer to it in the future, it's ok if it resides there?

Hi Ronald,

You can delete such a record, or you can mark it in one way or the other as "not actual anymore", using e.g. a time stamp. In the latter case you could see it as a kind of archieving, for later reference.

Imb.

ok sir. thank you for the inputs.  have patience answering to my questions as i will post again some more in the future.  you are a big help to me, sir.  thank you so much!

Answer 6

For an illustration of how a record can either be permanently deleted, or marked as deleted, with the facility for restoring it later if necessary, see DeleteDemo.zip in my public databases folder at:



Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

---

Ken Sheridan, Stafford, England

whoah!  Sir Ken, this is a great reference for a beginner like me.  i really appreciate, sir!!!

Answer 7

Hi mabalasik,

If there is no other issue related with your original post, I would suggest you mark the helpful reply as answer.

If you have any new issue, please feel free to post new thread.

Best Regards,

Edward

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].