UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Handle Duplicate Part Number In Inventory System, Access 2003    
 
   
Pencil
post Sep 8 2019, 09:21 AM
Post#1



Posts: 17
Joined: 5-November 11



I am fairly new to data bases design and have built a few in the past, but I have come across this issue.

I am not sure how to handle part numbers that have the same part number in my table. I know there is the YEs Don't Allow Duplicate, but prices change for the same part number. Maybe I am look at this the wrong way. In the past system I have always just used the PN an then if the price increased for this I would just put the year behind the part number like example PN-19. This worked fine in as long as the supplier only increase their price once in that year, but I have come across where they have increase prices two three times in a year.


So currently I have a table that is set to Yes All Duplicate in the PN field, and I have four PN enter that are now the same. The price has not changed yet as I purchased them all this week.
When I go to charge them out on a form showing I only used one the report brings across all 4 of these same part numbers.

What I am doing wrong here and is there a better way to handle this situation. I sat here yesterday racking my brain and just can think of the best possible solution.


Any help or advice would be much appreciated. Again I am by no means a advance user in access still learning by trail and error mostly errors most days, but I am learning.
Go to the top of the page
 
theDBguy
post Sep 8 2019, 09:29 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,382
Joined: 19-June 07
From: SunnySandyEggo


Hi. For something like this, I would use several tables. For example:

tblParts
PartNo, PK

tblSuppliers
SupplierID, PK

tblPartSuppliers
PartID, PK
PartNo, FK
SupplierID, FK

tblPartPrices
PriceID, PK
PartID, FK
Price
EffectiveDate

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ADezii
post Sep 8 2019, 10:34 AM
Post#3



Posts: 2,678
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I pretty much see what theDBGuy sees.
    1. A MANY <==> MANY Relationship exists between Parts and Suppliers so a Linking Table is required (tblPartSuppliers).
    2. Within the Linking Table, I created a Composite, Unique, Non-Primary Index on the [PartNo]/[SupplierID] Fields. In this manner every [PartNo] and [SupplierID] must be Unique and cannot be duplicated.
    3. With multiple Prices for Parts a possibility, a Related (Child) Table is needed Linked to tblParts.
  2. The above descriptions can be displayed graphically as indicated below.
  3. Hope this helps.

Attached File(s)
Attached File  Index.JPG ( 32.97K )Number of downloads: 5
 
Go to the top of the page
 
arnelgp
post Sep 8 2019, 10:52 AM
Post#4



Posts: 1,447
Joined: 2-April 09
From: somewhere out there...


do you keep dates for price change?
you can retrieve 1 PN based on the latest price.

create a Total query using PN, Max(price_date).

from this query you can retrieve only 1 record
per PN:

Select * From table_PN Inner Join Query1
On table_PN.PN = Query1.PN And table_PN.price_date = Query1.MaxOfPrice_Date;

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
GroverParkGeorge
post Sep 8 2019, 11:18 AM
Post#5


UA Admin
Posts: 35,867
Joined: 20-June 02
From: Newcastle, WA


Perhaps you will get some insight into working with a Relational Database Application created with MS Access from studying the materials here.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Pencil
post Sep 8 2019, 03:48 PM
Post#6



Posts: 17
Joined: 5-November 11



Wow thanks everyone and this looks like it is getting complicated. might be above my head

Yes it would be nice to be able to choose the price from first in first out with the parts thing.

DBguy not sure what the PK , FK, stand for are these the joins to the relationships?

Thanks for the graphic display ADezil this might help me when trying to figure out how to set this up.

GroverParkGeorge thanks I will look at some of these tutorials as I am sure I am going to need to study this a bit more..

pullhair.gif
Go to the top of the page
 
GroverParkGeorge
post Sep 8 2019, 03:50 PM
Post#7


UA Admin
Posts: 35,867
Joined: 20-June 02
From: Newcastle, WA


Yes, if you are still learning things like "PK" or Primary Key and "FK" or Foreign Key, it's a good idea to invest some time in learning how Relational Database Applications work before jumping into the deep end.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Pencil
post Sep 8 2019, 04:40 PM
Post#8



Posts: 17
Joined: 5-November 11



I guess I should have know what those Symbols meant, the mind is like mush today. I know a bit about Relationship but still get confused at times. I like the deep end and I always dive right end, and saving different version so I can go back if I screw up..

Thanks again everyone for the advise and I am sure Ill be back with more question once I start throwing this together..
Go to the top of the page
 
ADezii
post Sep 8 2019, 04:42 PM
Post#9



Posts: 2,678
Joined: 4-February 07
From: USA, Florida, Delray Beach


Realizing that this may all be a little overwhelming for you, I'v attached a Demo Database with only Sample Tables and the Relationships Window which will display once the DB is Opened. Hope this helps.
Attached File(s)
Attached File  Parts_Demo.zip ( 23.71K )Number of downloads: 5
 
Go to the top of the page
 
Pencil
post Sep 8 2019, 05:45 PM
Post#10



Posts: 17
Joined: 5-November 11



thanks ADezii I have already created the tables and have the relationships set-up.

The problem I have run into is I have data entry into another table with part numbers. Trying to import these into the new tables that I have created. I started with the Tbl_Parts which all worked fine, but now i am trying to get data into the Tbl_Parts_Suppliers but because the part number is set to the FK it won't let me import data from Excel. I think this is because there are some part numbers in my other table that I am importing have txt in the part number..

not sure how to go forward now. I know if I change the field from Number to Text and try and change it back after importing the data that won't work..

Go to the top of the page
 
BruceM
post Sep 9 2019, 06:36 AM
Post#11


UtterAccess VIP
Posts: 7,968
Joined: 24-May 10
From: Downeast Maine


QUOTE
the part number is set to the FK

That means the part number is the primary key in another table. If so, you cannot have duplicates. With part number (catalog number, etc.) tables I tend not to enforce the uniqueness of a part number, etc. because of the possibility that two different companies will happen to use the same one.

You would do better to have an autonumber PK (data type Long Integer by definition) in the part number table, and link on that value. Some other aspect of the record, such as the supplier ID in combination with the part number, is the unique constraint (the combination of values that makes the number unique).

As for price, I use the current price when creating an order or invoice, storing that value with the line item information. If there is a reason to keep a historical record of the list prices over time another approach is needed, but that seems rather unlikely.
Go to the top of the page
 
ADezii
post Sep 9 2019, 07:08 AM
Post#12



Posts: 2,678
Joined: 4-February 07
From: USA, Florida, Delray Beach


Keep in mind a couple of points when attempting to populate your Tables:
  1. Referential Integrity is enforced between the [PartNo] Field in tblParts and the [PartNo] Field in tblPartSuppliers. You cannot enter a [PartNo] in tblPartSuppliers unless it first exists in tblParts.
  2. Referential Integrity is enforced between the [SupplierID] Field in tblSuppliers and the [SupplierID] Field in tblPartSuppliers. You cannot enter a [SupplierID] in tblPartSuppliers unless it first exists in tblSuppliers.
  3. These Tables need to be populated in a specific order so that everything will be in sync.
  4. The combination of [PartNo] and [SupplierID] in tblPartSuppliers must be 'Unique', since they comprise a Unique Index.
  5. The first illustration below will work, but the second will not since the last Record will produce an Error (Duplication).
  6. Populating Junction Tables can be especially tricky. You can try Deleting all Relationships, populating Tables, then re-establishing those Relationships.

This post has been edited by ADezii: Sep 9 2019, 07:09 AM
Attached File(s)
Attached File  I1.JPG ( 15.66K )Number of downloads: 1
Attached File  I2.JPG ( 16.74K )Number of downloads: 2
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th October 2019 - 05:50 AM