A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help on Handling Superceeded parts



 
 
Thread Tools Display Modes
  #1  
Old February 18th, 2009, 09:25 PM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default Help on Handling Superceeded parts

I have a database to track parts being ordered. I have been having issues
with parts that have been superceeded by newer parts. I have a Parts Table
as follows
T_PartNumbers
PartNumberID (pk)
PartNumber
Description

and a order list
T_PartsOrder
OrderID (pk)
PartNumberID (fk)
Qtytoorder
DateRequested

I have a form based on my T_Parts with a lookup Control FindPartNumberID_Combo
When a part is selected in the combo the form goes to that Partnumbers
Record (this was done with the Wizard) I am hoping for help with a function
that would check to see if the partNumber being looked up is superceeded and
if so have a dialog box pop up that would give me the option of selecting the
"Old" Part number or the "New" Partnumber then have the form jump to
whichever is selected.

I am thinking maybe a third table T_Superceededparts
ID (pk)
PartNumberID (fk)
NewPartNumberID (fk)

and some code for the lookup that might check this table first and if the
PartNumberID is found show a dialog box that will let me proceed with either
option.. Maybe im off base with my idea. The people that know what they are
doing seem to always have the most Elegantly Simple answers..

Im open to any ideas
Thanks
Barry



  #2  
Old February 18th, 2009, 10:36 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Help on Handling Superceeded parts

Barry,

It looks like you already understand that the solution starts with
information structure, then table structure, then all of that other stuff.

Your described proposed table method is a junction table which will support
many-to-many relationships, and will let you add a field to record more info
that relates to the transition itself. This is the most versatile (and
complicated) method. If each superceded part only has one replacement, I
think you could simplify this by just adding a FK "SupercededBy" field to
your part number table and then link the table to itself.

After you decide on your table structure, the other folks who answer these
posts are 10 times better than I am at deciding the best way to add that
functionality that you described.


"Barry A&P" wrote:

I have a database to track parts being ordered. I have been having issues
with parts that have been superceeded by newer parts. I have a Parts Table
as follows
T_PartNumbers
PartNumberID (pk)
PartNumber
Description

and a order list
T_PartsOrder
OrderID (pk)
PartNumberID (fk)
Qtytoorder
DateRequested

I have a form based on my T_Parts with a lookup Control FindPartNumberID_Combo
When a part is selected in the combo the form goes to that Partnumbers
Record (this was done with the Wizard) I am hoping for help with a function
that would check to see if the partNumber being looked up is superceeded and
if so have a dialog box pop up that would give me the option of selecting the
"Old" Part number or the "New" Partnumber then have the form jump to
whichever is selected.

I am thinking maybe a third table T_Superceededparts
ID (pk)
PartNumberID (fk)
NewPartNumberID (fk)

and some code for the lookup that might check this table first and if the
PartNumberID is found show a dialog box that will let me proceed with either
option.. Maybe im off base with my idea. The people that know what they are
doing seem to always have the most Elegantly Simple answers..

Im open to any ideas
Thanks
Barry



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:23 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.