Primary Key

[rinie79]

Hi guys! im a beginner using ms access. can anybody help to settle my prob. i have assigned 4 tables just like below:-

Table Item:
IDitem
Itemname
Realquantity

Table RegisterItem:
IDitem
Itemname
Dateregister
Quantityregister

Table Request
IDitem
Itemname
Daterequest
IDstaff
Quantityrequest

Table Staff
IDstaff
Namestaff

Actually, i'm tryin to create a stock management system in my office where there is stock in & stockout. here i put IDitem as primary key (datatype=text) becoz our IDitem in manual record start with "001". The problem is, when i add a new record for example "001" in the "IDitem" field got error "the changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship".

For example:

form_registeritem

IDitem Itemname Dateregister Quantityregister
001 Pen 02-Mei-2010 50
001 Pen 30-Mei-2010 20

* can't add new record for the same IDitem.
* Pen ID is 001 so if we have new stock of this item we have to register it in our record.

~Need Helps~

 

[SteveSchapel]

Rinie,

If the value is to be repeated in the same field for more than one record, then by definition it cannot be the table's Primary Key, as the purpose of the primary key is to uniquely identify each record. IDItem may be applicable as the Primary Key in the Items table, but not in the RegisterItem table. Can you tell us a little more, with further data examples, about the relationship between Items and RegisterItems?

You may find this article of interest:
http://accesstips.datamanagementsolutions.biz/primarykey.htm

By the way, there is no need to make a Text data type, for the reason you stated. The concept here is that the value stored and the value displayed are not necessarily the same. Probably a numerical value for this ID will be easier to manage from a database point of view, and it is each to Format it to show up with the leading zeros when you want to show it on your forms and reports.

 

[Galaxiom]

Firstly deal with normalisation issues. You are repeating the ItemName field in multiple tables. Whereever the item appears outside the Item table it should be referred to only by its primary key. Search the forum for normalization and make sure you understand this before continuing as it is of fundamental importance in all database designs.

A Primary Key is a field which identifies a unique record in a table. You apparently have the IDItem field in the RegisterItem and Request tables as a Primary Key which is why it won't let you include it twice.

It should not be a PK but a foreign key that refers to the PK of the record in the Item table.

It would probably be better to store the IDItem as an integer. Adding the leading zeros can be done in the format for the control on forms and reports.

 

[rinie79]

i dont know which one shud i put as a primary key. cud u all guide me how to solve this problem. i'll explain the actual cycle of our stock transaction and hope this wud help.

Table RegisterItem
For example: Current quantity of item "stapler" is 30. Our new stock-in is 50 then we have to register it as a record. So the current quantity of this item becomes 80 (30+50)

Table Request
For example: Current quantity of item "stapler" is 80. My colleague Sarah request for this item 3 units so there will be a deduction of current quantity and it becomes 77.

* these cycles of transactions are never end
* Table Item wud be set as mainform and Table RegisterItem & Request as subforms.

~still need help~

 

[Nicolette]

I'm also some what of a newbie but I was thinking...
Do you Have to have individual records of each Stock-In item? Because you might be able to just do something along the lines of adding to the current stock level via tblPurchaseOrders or something. But if you absolutly must have a record for each addition to the stock then you will have to find something else to use as the primary key because like Steve Schapel said you can not have multiple entries of a primary key each record must have it's own that is specific and unique to that record.

 

[Galaxiom]

i dont know which one shud i put as a primary key.

Item will have a Primary Key that is used to represent the item wherever it is is found in other tables. Similarly staff will have a PK that indicates the matching record in the staff table whenever you need to refer to someone such as in Request. Any ancilliary information for a reports of form such as the item name is retreived from the Item table as required.

Request carries the FK for the Item and the FK for Staff indicating the request for the item is for that staff member.

The Request may also have a PK itself. This is used wherever you need to refe to that request from another table. It may not be required in this case.

For example: Current quantity of item "stapler" is 30. Our new stock-in is 50 then we have to register it as a record. So the current quantity of this item becomes 80 (30+50)

It often surprises new developers that proper normalisation does not maintain the current stock quantity in the Item table. Instead the current quantity should be calculated on the fly as required from the number traded in inwards and outwards transanctions.

Add all the purchases and subtract the filled requests. This always gives the correct figure. Updating a live figure at every tranaction eventually trips over and the stored quantity conflicts with the record of transactions.

A figure can be maintained occasionally so that all previous transactions need not be looked up for everafter. However even after the archiving the opening stock will often be rolled over into a special transaction rather than including a quantity field in the Item table.

 

[rinie79]

thanks for the replies. Here i attached the file so we can discuss it. Can anyone give me the formula in field "Currentquantity" (Currentquantity+Quantityregister-Quantityrequest) and where supposed i put the formula. Pleaseee...

 

[Galaxiom]

Determine the current quantity in a query.
Hint: It uses the Sum([fieldname]) SQL agreggate function.

However your sample database does not yet relect any of the normalization advice already given. You need to change this and the form design to be based on queries before the other other stuff can be sorted at all.

 

[rinie79]

can u teach me step by step coz i'm not familiar with SQL


~submit this project on this coming tuesday... can i...~

 

[DCrake]

So it has took 9 posts to find out that this is an assignment project, not a real life situation.

 

[rinie79]

u r wrong. this is a real life situation actually.

 

[vbaInet]

u r wrong. this is a real life situation actually.

Are you sure he's wrong? For someone (i.e. DCrake) that owns a company that creates real life solutions: www.xcraftlimited.co.uk

 

[rinie79]

becoz he said that the project i create is not a real life situation. maybe he thought that im a college student... i just want the solutions of my problem thats it...

 

[vbaInet]

Before you can realise a solution, you must change your table structure as you have been advised by Galaxiom. Without that the sql solution wouldn't work. Re-read what was given and try to change the foreign keys.

 

[DCrake]

~submit this project on this coming tuesday... can i...~

Unless something was lost in the translation it reads like "I need an answer now as the project has to be summited by next Tuesday"

 

[vbaInet]

I've just had a look at your database and to my amazement you don't have ANY relationships and some tables don't have Primary Keys. It's back to the basics for you:

http://r937.com/relational.html
http://support.microsoft.com/kb/100139

http://databases.about.com/od/tutorials/l/aarelationship1.htm