Primary and Foreign Key Question.

[Schae235]

So I have a table 'TableA' that has unique part number on it. 'TableB' is a certain category of parts. 'TableC' is a different category. What I would like to happen is every time a new part is created on 'TableB' or 'TableC' a unique record is created on 'TableA' and pushes the part number to the table. That record in TableA would then be 'blocked off' and the next record that would be created would make a new unique record in TableA

I am sure that this is an Access 101 question but I can't seem to figure it out.



Thanks,
M.S.

[RayMilhon]

If table B and Table C are categories of Table A. Why would Table B and Table C have the individual part number. Also why 2 Tables with the same type of data.

[Rawb]

It sounds like you're using a non-normalized Table structure. If both TableB and TableC are categories of items in TableA, then you should probably be using a structure more like the following:

• Table PartCategories
  
  • Column Category_ID - The unique ID for each category of part. Autonumber, Long Integer. Primary Key.
  • Column Category_Name - The "friendly" name of each category of part. Text String.
  
  
• Table PartNumber
  
  • Column Part_ID - The unique ID of each part. This Column is for use internally by the system and is not necessarily the official "part number". Autonumber, Long Integer. Primary Key.
  • Column Category_ID - The ID of the category of part this is. Long Integer. Foreign Key -> PartCategories.Category_ID.
  • Column Part_Number - The user friendly "part number". Text String.
  
  

If, for some reason you have to use your existing structure, you would almost have to use VBA code to do what you want.

[Schae235]

Rawb and RayMilhon, thank you for your replies and I am sorry for the delayed response. I was on vacation for the holidays.

RayMilhon I want to keep a list of the part numbers in TableA but we have multiple different categories of part numbers where I want to be able to keep different information for each category. I used to work in a produce shop so an example using that would be they stock both apples and grapes and want to keep different information on each (Apples: size, color, taste, price, etc./ Grapes: texture, price, firmness, size, etc.) but we still want to have a uniform number to order each item by. So both apples and the grapes would have P#### numbers.

Rawb, I think this solution sounds like it might work. I will have to attempt it this afternoon to see for sure though. I am relatively inexperienced in Access so it involves a lot of fumbling around right now.