table design question

[NightWalker]

I have part numbers for all the items that my company produces. my question involves possibly splitting the part numbers into multiple fields. here are some examples of the part numbers
I was wondering if I should make 2 fields for the part numbers (split them at the - ) and use them together as a composite key. I am just wondering if splitting them would be better normalization. I forgot to add some part numbers do not have a - in them.

Part Number
6871333-1
6871333-10
6871333-11
6871333-12
6871333-2
6871333-3
6871333-4
6871333-5
6871333-6
6871333-7
6871333-8
6871333-9
6871334-1
6871334-2
6871334-3
6871334-4
6871334-7
6871334-8

[orange]

What does the suffix represent?

[NightWalker]

it represents a different configuration eg. the connector may be turned 90degrees for a -2, 45degrees for a -3. things like that. the base item is the same but there is a slight modification when a suffix is added. Some parts do not have suffixes though.

[Micron]

I usually try offer a solution foremost and not to ask why, but I'll make an exception here. What would be the benefit of doing this? I ask because I can't think of any that would over-ride the risk of not being able to pull it back together again in some form. Any portion of that number needed for comparison purposes is most likely retrievable using simple functions, and presumes you understand the importance of displaying/editing records in forms and not directly into tables.

If you want to split the first part into its own table, you'll have to replace it somewhere down the line by linking the record id of that table parent table with the suffix table records. Really, all that means is that you have additional tables, joins and fields for no reason that I can think of that would otherwise promote good normalization. The most basic idea wrt normalization is to not repeat records, keep related things in their respective tables, join them by a non-fluid characteristic and avoid data disconnects for when some things change (like when Ms. Jones becomes Mrs. Smith). I have really simplified the issue of course, but if you like, I'm sure between myself and others here, we could offer some good links on the subject if you'd like that.

[NightWalker]

my thoughts for splitting this into two fields would be a cascading combobox setup where the user would find the base part number and it would give them all the parts needed to make the product. then they could enter the suffix (-x) and get the modification needed to produce the correct version of the main part. I hope this makes sense.

[orange]

It seems there are 2 concepts involved --base product and modification.
So 2 fields seem appropriate.

But how consistent is the "modification identifier"?

[NightWalker]

all the cables we produce have a suffix but none of the items they attach to do

[Micron]

'K, you posted your reason while I was editing my initial response. So it's not really about normalization from what I've just read. Your reason for doing so does not require you to split the data, but given that there may be more information coming, it's not as bad as I first thought. Your first control could cause the parts list to be displayed based on the common part of the part number (possibly by using the Left, Mid or Right functions or vba to extract the part number). The modification data could come from the suffix, which can appear in the second combo and also cause the variation information to be displayed.

[NightWalker]

Excellent Idea Micron. I didn't think of that. there are sooooooooo many functions that I don't know that could probably make everything I am doing easier but I don't know what they are LOL. I am also having trouble posting another thread in the query heading. I am not sure why. It keeps saying the site is too busy or something like that.

[orange]

The question now is --are the suffixes consistent across all products? Do the suffixes always mean the same thing when they "add info to the base product"?

Are there combinations of product + suffix that are not logical? If so, you'll need some logic to identify and reject these.

[NightWalker]

all the suffixes are a dash with at most 3 numeric values. (-xxx)

[Micron]

Assuming you can pass a suitable reference to the part number, I'm thinking
Left([Part Number],Instr(1,[Part Number],"-")-1) for everything up to the -
Mid([Part Number],InstrRev([Part Number],"-")+1) for everything after the -
Note: you really should avoid spaces in your object names.
This is my go to function reference http://www.techonthenet.com/access/functions/

[NightWalker]

Note: you really should avoid spaces in your object names.

That was from a spreadsheet that someone else built. I have learned the hard way to not have spaces in anything.

This is my go to function reference http://www.techonthenet.com/access/functions/

Thank you for the link this will help me greatly to find new functions to make things easier.

I appreciate all your help on this.