How to obtain logical combination for query result?

I have a table named ProgramCodes and it may need to be redone.

the fields in this table a
1. Guitar (item)
2. Option (item)
3. Code (code the user enters for the Guitar and Guitar w/ Option(s)
combinations.

So I've created a form and a sub form for the user to be able to enter the
Codes per Guitar and Guitar w/ Option(s)

The main form is just the Guitar item the user can search to find the Guitar
itemto start entering the Codes and combinations by entering the Option items
for the Guitar combo. The record source is from the Guitars table (which is
just all the guitar items)

The sub form is all the fields of the ProgramCodes table.
Linked by the Guitar item.


Please help me how to achieve the following
as noted:
Guitar is an Item
Option is an Item
Code is what the programmer uses for their understanding of how to program
their machines to make the guitar with options if any.

This field (Code) in the ProgramCodes table (and form) is a free-from and
just a text file for the programmer (user) to enter the code(s) associated to
the guitar and guitar with option combos.

So someone purchases a guitar and select several options or not, there will
be various Code (programming codes) to build that guitar is the guitar item
is just the bare guitar (skeleton / shell).
The options make up the different features on the guitar. The programming
codes make these features function in their special ways.

How can the Codes get entered into a table or tables if the current
ProgramCodes table is insufficient so that when you query on, say 127R-RB,
you get all the invoices with DC135 guitar item with ONLY RB as an option
item.

or if you query on an invoice with DC135 to see the programming code you get
the result of just 125R and 135T IF and only IF that item on the invoice had
no option.

or if you query on an invoice with DC135 and the invoice has options 26 and
RB that it'll only show 127R-RB and 136T-RB as my Code result BUT it can't
show the Code for just the 26 or just the RB, it needs to show the combined
Option's Code.


here are the results that need to be obtained
DC135 is a Guitar item:
DC135 without any Options should be assigned Codes = 125R and 135T
DC135 with RB as an Option and should be assigned Codes = 127R-RB and 135T-RB
DC135 with 26 as an Option and should be assigned Codes = 125R and 136T
DC135 with 29 as an option and should be assigned Codes = 125R and 139T
DC135 with 26 & RB are Options and should be assigned Codes = 127R-RB and
136T-RB
DC135 with 29 & RB are Options and should be these Codes = 127R-RB and 139T-
RB (it can not display any of the other codes above, it can only display this
code for this combo)

I hope i made sense. I don't want to complicate this anymore then it is by
itself.

The other tables with Invoice information is insignificant for what needs
happen here with the Codes in ProgramCodes table.


Thank you!

--
thank you for all your help!

~ angel ~

Message posted via http://www.accessmonster.com

On Oct 4, 2:50 am, "~ angel ~ via AccessMonster.com" u33627@uwe
wrote:
I have a table named ProgramCodes and it may need to be redone.

the fields in this table a
1. Guitar (item)
2. Option (item)
3. Code (code the user enters for the Guitar and Guitar w/ Option(s)
combinations.

So I've created a form and a sub form for the user to be able to enter the
Codes per Guitar and Guitar w/ Option(s)

The main form is just the Guitar item the user can search to find the Guitar
itemto start entering the Codes and combinations by entering the Option items
for the Guitar combo. The record source is from the Guitars table (which is
just all the guitar items)

The sub form is all the fields of the ProgramCodes table.
Linked by the Guitar item.

Please help me how to achieve the following
as noted:
Guitar is an Item
Option is an Item
Code is what the programmer uses for their understanding of how to program
their machines to make the guitar with options if any.

This field (Code) in the ProgramCodes table (and form) is a free-from and
just a text file for the programmer (user) to enter the code(s) associated to
the guitar and guitar with option combos.

So someone purchases a guitar and select several options or not, there will
be various Code (programming codes) to build that guitar is the guitar item
is just the bare guitar (skeleton / shell).
The options make up the different features on the guitar. The programming
codes make these features function in their special ways.

How can the Codes get entered into a table or tables if the current
ProgramCodes table is insufficient so that when you query on, say 127R-RB,
you get all the invoices with DC135 guitar item with ONLY RB as an option
item.

or if you query on an invoice with DC135 to see the programming code you get
the result of just 125R and 135T IF and only IF that item on the invoice had
no option.

or if you query on an invoice with DC135 and the invoice has options 26 and
RB that it'll only show 127R-RB and 136T-RB as my Code result BUT it can't
show the Code for just the 26 or just the RB, it needs to show the combined
Option's Code.

here are the results that need to be obtained
DC135 is a Guitar item:
DC135 without any Options should be assigned Codes = 125R and 135T
DC135 with RB as an Option and should be assigned Codes = 127R-RB and 135T-RB
DC135 with 26 as an Option and should be assigned Codes = 125R and 136T
DC135 with 29 as an option and should be assigned Codes = 125R and 139T
DC135 with 26 & RB are Options and should be assigned Codes = 127R-RB and
136T-RB
DC135 with 29 & RB are Options and should be these Codes = 127R-RB and 139T-
RB (it can not display any of the other codes above, it can only display this
code for this combo)

I hope i made sense. I don't want to complicate this anymore then it is by
itself.

The other tables with Invoice information is insignificant for what needs
happen here with the Codes in ProgramCodes table.

Initially I tried to deduce how the options determine the codes, then
decided that the rules may not be the same for other item/option
combinations so played safe by doing it 'long-hand' (I changed the
wording to include the word 'only' to emphasize the fact that
descriptions are mutually exclusive):

CREATE TABLE Items
(
item_code VARCHAR(10) NOT NULL UNIQUE,
item_name VARCHAR(20) NOT NULL
)
;
INSERT INTO Items (item_code, item_name)
VALUES ('DC135', 'Guitar')
;
CREATE TABLE OptionRCodes
(
option_R_code VARCHAR(10) NOT NULL UNIQUE,
CHECK ((option_R_code ALIKE '[1-9][0-9][0-9]R')
OR (option_R_code ALIKE '[1-9][0-9][0-9]R-RB'))
)
;
INSERT INTO OptionRCodes (option_R_code) VALUES ('125R')
;
INSERT INTO OptionRCodes (option_R_code) VALUES ('127R-RB')
;
CREATE TABLE OptionTCodes
(
option_T_code VARCHAR(10) NOT NULL UNIQUE,
CHECK ((option_T_code ALIKE '[1-9][0-9][0-9]T')
OR (option_T_code ALIKE '[1-9][0-9][0-9]T-RB'))
)
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('135T')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('136T')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('139T')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('135T-RB')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('136T-RB')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('139T-RB')
;
CREATE TABLE ItemsSpecs
(
option_R_code VARCHAR(10) NOT NULL
REFERENCES OptionRCodes (option_R_code),
option_T_code VARCHAR(10) NOT NULL
REFERENCES OptionTCodes (option_T_code),
UNIQUE (option_R_code, option_T_code),
item_code VARCHAR(10) NOT NULL
REFERENCES Items (item_code),
description VARCHAR(30) NOT NULL
)
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('125R', '135T', 'DC135', 'without any Options')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('127R-RB', '135T-RB', 'DC135', 'with RB only as an Option')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('125R', '136T', 'DC135', 'with 26 only as an Option')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('125R', '139T', 'DC135', 'with 29 only as an Option')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('127R-RB', '136T-RB', 'DC135', 'with 26 and RB only as
Options')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('127R-RB', '139T-RB', 'DC135', 'with 26 and RB only as
Options')
;

Jamie.

--

You may want to explain in English what all that code is designed to do and
why.

"Jamie Collins" wrote in message
ups.com...
On Oct 4, 2:50 am, "~ angel ~ via AccessMonster.com" u33627@uwe
wrote:
I have a table named ProgramCodes and it may need to be redone.

the fields in this table a
1. Guitar (item)
2. Option (item)
3. Code (code the user enters for the Guitar and Guitar w/ Option(s)
combinations.

So I've created a form and a sub form for the user to be able to enter
the
Codes per Guitar and Guitar w/ Option(s)

The main form is just the Guitar item the user can search to find the
Guitar
itemto start entering the Codes and combinations by entering the Option
items
for the Guitar combo. The record source is from the Guitars table (which
is
just all the guitar items)

The sub form is all the fields of the ProgramCodes table.
Linked by the Guitar item.

Please help me how to achieve the following
as noted:
Guitar is an Item
Option is an Item
Code is what the programmer uses for their understanding of how to
program
their machines to make the guitar with options if any.

This field (Code) in the ProgramCodes table (and form) is a free-from and
just a text file for the programmer (user) to enter the code(s)
associated to
the guitar and guitar with option combos.

So someone purchases a guitar and select several options or not, there
will
be various Code (programming codes) to build that guitar is the guitar
item
is just the bare guitar (skeleton / shell).
The options make up the different features on the guitar. The programming
codes make these features function in their special ways.

How can the Codes get entered into a table or tables if the current
ProgramCodes table is insufficient so that when you query on, say
127R-RB,
you get all the invoices with DC135 guitar item with ONLY RB as an option
item.

or if you query on an invoice with DC135 to see the programming code you
get
the result of just 125R and 135T IF and only IF that item on the invoice
had
no option.

or if you query on an invoice with DC135 and the invoice has options 26
and
RB that it'll only show 127R-RB and 136T-RB as my Code result BUT it
can't
show the Code for just the 26 or just the RB, it needs to show the
combined
Option's Code.

here are the results that need to be obtained
DC135 is a Guitar item:
DC135 without any Options should be assigned Codes = 125R and 135T
DC135 with RB as an Option and should be assigned Codes = 127R-RB and
135T-RB
DC135 with 26 as an Option and should be assigned Codes = 125R and 136T
DC135 with 29 as an option and should be assigned Codes = 125R and 139T
DC135 with 26 & RB are Options and should be assigned Codes = 127R-RB and
136T-RB
DC135 with 29 & RB are Options and should be these Codes = 127R-RB and
139T-
RB (it can not display any of the other codes above, it can only display
this
code for this combo)

I hope i made sense. I don't want to complicate this anymore then it is
by
itself.

The other tables with Invoice information is insignificant for what needs
happen here with the Codes in ProgramCodes table.

Initially I tried to deduce how the options determine the codes, then
decided that the rules may not be the same for other item/option
combinations so played safe by doing it 'long-hand' (I changed the
wording to include the word 'only' to emphasize the fact that
descriptions are mutually exclusive):

CREATE TABLE Items
(
item_code VARCHAR(10) NOT NULL UNIQUE,
item_name VARCHAR(20) NOT NULL
)
;
INSERT INTO Items (item_code, item_name)
VALUES ('DC135', 'Guitar')
;
CREATE TABLE OptionRCodes
(
option_R_code VARCHAR(10) NOT NULL UNIQUE,
CHECK ((option_R_code ALIKE '[1-9][0-9][0-9]R')
OR (option_R_code ALIKE '[1-9][0-9][0-9]R-RB'))
)
;
INSERT INTO OptionRCodes (option_R_code) VALUES ('125R')
;
INSERT INTO OptionRCodes (option_R_code) VALUES ('127R-RB')
;
CREATE TABLE OptionTCodes
(
option_T_code VARCHAR(10) NOT NULL UNIQUE,
CHECK ((option_T_code ALIKE '[1-9][0-9][0-9]T')
OR (option_T_code ALIKE '[1-9][0-9][0-9]T-RB'))
)
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('135T')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('136T')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('139T')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('135T-RB')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('136T-RB')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('139T-RB')
;
CREATE TABLE ItemsSpecs
(
option_R_code VARCHAR(10) NOT NULL
REFERENCES OptionRCodes (option_R_code),
option_T_code VARCHAR(10) NOT NULL
REFERENCES OptionTCodes (option_T_code),
UNIQUE (option_R_code, option_T_code),
item_code VARCHAR(10) NOT NULL
REFERENCES Items (item_code),
description VARCHAR(30) NOT NULL
)
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('125R', '135T', 'DC135', 'without any Options')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('127R-RB', '135T-RB', 'DC135', 'with RB only as an Option')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('125R', '136T', 'DC135', 'with 26 only as an Option')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('125R', '139T', 'DC135', 'with 29 only as an Option')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('127R-RB', '136T-RB', 'DC135', 'with 26 and RB only as
Options')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('127R-RB', '139T-RB', 'DC135', 'with 26 and RB only as
Options')
;

Jamie.

--

Hi Jamie,
I appreciate you trying to take a wing at this one ...

i am a bit confused, and it maybe because of how I explained it.


On your suggestion ...

Items with item_code and item_name is basically the Guitars table I have all
ready and has all the possible Guitar items.

Now it seems like you are suggesting I separate the Codes into 2 tables.
One for the ones with "R" and ones with "T".

R means Rear of the Guitar
T mean Top of the Guitar

i did not include this but it seems you have pulled that out of the Codes as
there's is some logic the Guitar programmer created in the Codes item
numbering.

Then finally creating the match and putting them into the Item Specs table.

is that right?

I am now trying to get more info from the Guitar programmer to see if there's
any real logic in the Codes he uses in case this R and T identifier does not
always work throughout the others.


Jamie Collins wrote:
On Oct 4, 2:50 am, "~ angel ~ via AccessMonster.com" u33627@uwe
wrote:
CREATE TABLE Items
(
item_code VARCHAR(10) NOT NULL UNIQUE,
item_name VARCHAR(20) NOT NULL
)
;
INSERT INTO Items (item_code, item_name)
VALUES ('DC135', 'Guitar')
;
CREATE TABLE OptionRCodes
(
option_R_code VARCHAR(10) NOT NULL UNIQUE,
CHECK ((option_R_code ALIKE '[1-9][0-9][0-9]R')
OR (option_R_code ALIKE '[1-9][0-9][0-9]R-RB'))
)
;
INSERT INTO OptionRCodes (option_R_code) VALUES ('125R')
;
INSERT INTO OptionRCodes (option_R_code) VALUES ('127R-RB')
;
CREATE TABLE OptionTCodes
(
option_T_code VARCHAR(10) NOT NULL UNIQUE,
CHECK ((option_T_code ALIKE '[1-9][0-9][0-9]T')
OR (option_T_code ALIKE '[1-9][0-9][0-9]T-RB'))
)
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('135T')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('136T')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('139T')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('135T-RB')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('136T-RB')
;
INSERT INTO OptionTCodes (option_T_code) VALUES ('139T-RB')
;
CREATE TABLE ItemsSpecs
(
option_R_code VARCHAR(10) NOT NULL
REFERENCES OptionRCodes (option_R_code),
option_T_code VARCHAR(10) NOT NULL
REFERENCES OptionTCodes (option_T_code),
UNIQUE (option_R_code, option_T_code),
item_code VARCHAR(10) NOT NULL
REFERENCES Items (item_code),
description VARCHAR(30) NOT NULL
)
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('125R', '135T', 'DC135', 'without any Options')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('127R-RB', '135T-RB', 'DC135', 'with RB only as an Option')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('125R', '136T', 'DC135', 'with 26 only as an Option')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('125R', '139T', 'DC135', 'with 29 only as an Option')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('127R-RB', '136T-RB', 'DC135', 'with 26 and RB only as
Options')
;
INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code,
description)
VALUES ('127R-RB', '139T-RB', 'DC135', 'with 26 and RB only as
Options')
;

Jamie.

--

--
thank you for all your help!

~ angel ~

Message posted via http://www.accessmonster.com

Hi Amy,
i thought I did on the 2nd post after the initial one.

but going back it appears that never posted.


The "Codes" are guitar programming codes used to program the machine that
programs the guitar based on the options the customer selects.


So a customer purchases a the base Guitar on an invoice and selects various
options to make the end Guitar.

This part of the system is in their Accounting system. The basic data is
extracted and created into two tables in Access.

GuitarHeader (has the customer & invoice info for the base guitar)
GuitarDetails (has the invoicenumber and all the options, since an invoice is
for one guitar, the guitar is not in this file)


Now for the Access part ...

ProgramCodes table (i may have to have this split) has:
Guitar
Code
Option
GuitarCodeOptionID

Because the Code (programming code) is different depending on the Guitar and
guitar with certain option combinations to make the end guitar be what it is
and do the functions that it does, i need to try to create a file to hold all
these Codes to the guitar and guitar and option combinations so that the
Codes will print on a label.

The label goes on the base Guitar with the options and now we need the Codes
to the production team.

The look at the Guitar they need to build and would like to use it and this
only to program their machines.


Currently they have to look at the label for the Guitar and the options
printed (since I don't have the Codes on their yet) and have to look-up a
worksheet to figure out what Codes to enter into their machines.


Having the Codes print with the rest of the Guitar info will save all the
manual looking up.


The ProgramCodes table is set-up with the Form and subform but I don't have
it right because of the combinations.

The combination of the options for a unique code could be 2 or 6, etc ...

i hope this helps clarify the situation.

thanks!



Amy Blankenship wrote:
You may want to explain in English what all that code is designed to do and
why.

On Oct 4, 2:50 am, "~ angel ~ via AccessMonster.com" u33627@uwe
wrote:
[quoted text clipped - 171 lines]

--

--
thank you for all your help!

~ angel ~

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200710/1

Jamie,
ok, ultimately no one really needs to understand the logic behind the code.
just need to know that the Code(s) are unique depending on the Guitar and
Guitar to Options combinations.

The Guitar to Options can be just 1 option to the Guitar item which could
have 1 unique code or up to 4 unique codes
or up to 6 options to the Guitar item with up to 4 unique codes.

The user needs to be able to Select the Guitar (which is set-up on the main
form now).
then
1. enter the Codes for no Options
2. enter the Codes for 1 Option combo
3. enter the Codes for 2 Option combo
4. enter the Codes for 3 Option combo
5. enter the Codes for 4 Option combo
6. enter the Codes for 5 Option combo
or
7. enter the Codes for 6 Option combo

Each Guitar has different codes, each guitar and option(s) has different
codes.

there is no reason to try and make sense of the Codes itself. it'll just be
the result that needs to be shown based on the combinations.


HTH!


~ angel ~ wrote:
Hi Jamie,
I appreciate you trying to take a wing at this one ...

i am a bit confused, and it maybe because of how I explained it.

On your suggestion ...

Items with item_code and item_name is basically the Guitars table I have all
ready and has all the possible Guitar items.

Now it seems like you are suggesting I separate the Codes into 2 tables.
One for the ones with "R" and ones with "T".

R means Rear of the Guitar
T mean Top of the Guitar

i did not include this but it seems you have pulled that out of the Codes as
there's is some logic the Guitar programmer created in the Codes item
numbering.

Then finally creating the match and putting them into the Item Specs table.

is that right?

I am now trying to get more info from the Guitar programmer to see if there's
any real logic in the Codes he uses in case this R and T identifier does not
always work throughout the others.

On Oct 4, 2:50 am, "~ angel ~ via AccessMonster.com" u33627@uwe
wrote:
[quoted text clipped - 79 lines]

--


--
thank you for all your help!

~ angel ~

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200710/1

"~ angel ~ via AccessMonster.com" u33627@uwe wrote in message
news:7931d8aadaf59@uwe...
Hi Amy,
i thought I did on the 2nd post after the initial one.

but going back it appears that never posted.

I was talking to him, not you ;-)

"~ angel ~ via AccessMonster.com" u33627@uwe wrote in message
news:7931c5222482c@uwe...
Hi Jamie,
I appreciate you trying to take a wing at this one ...

i am a bit confused, and it maybe because of how I explained it.


On your suggestion ...

Items with item_code and item_name is basically the Guitars table I have
all
ready and has all the possible Guitar items.

Now it seems like you are suggesting I separate the Codes into 2 tables.
One for the ones with "R" and ones with "T".

R means Rear of the Guitar
T mean Top of the Guitar

i did not include this but it seems you have pulled that out of the Codes
as
there's is some logic the Guitar programmer created in the Codes item
numbering.

It seems to me that instead of having a separate table for each that it
makes more sense to have a table defining code types where one record would
be for R and one would be for T and you could add more records as you needed
more zones. Then you can actually concatenate the zones together with the
numeric part of the code to come up with the full code.

HTH;

Amy

Amy,
not sure if you saw my last post.
we can't use the R and T as the identifiers.

i just spoke with the manager and ultimately the codes do not have any real
logic.
it's programming codes for the guitars.

so, i need to create in Access for a way for the guitar programmer to enter
in the unique Codes for each Guitar and guitar / option combos.

the most important thing they have specified is the combinations that make
tell what code to use as each code is unique depending on the items.


Amy Blankenship wrote:
Hi Jamie,
I appreciate you trying to take a wing at this one ...
[quoted text clipped - 17 lines]
there's is some logic the Guitar programmer created in the Codes item
numbering.

It seems to me that instead of having a separate table for each that it
makes more sense to have a table defining code types where one record would
be for R and one would be for T and you could add more records as you needed
more zones. Then you can actually concatenate the zones together with the
numeric part of the code to come up with the full code.

HTH;

Amy

--
thank you for all your help!

~ angel ~

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200710/1

"~ angel ~ via AccessMonster.com" u33627@uwe wrote in message
news:7931d8aadaf59@uwe...
Hi Amy,
i thought I did on the 2nd post after the initial one.

but going back it appears that never posted.


The "Codes" are guitar programming codes used to program the machine that
programs the guitar based on the options the customer selects.


So a customer purchases a the base Guitar on an invoice and selects
various
options to make the end Guitar.

This part of the system is in their Accounting system. The basic data is
extracted and created into two tables in Access.

GuitarHeader (has the customer & invoice info for the base guitar)
GuitarDetails (has the invoicenumber and all the options, since an invoice
is
for one guitar, the guitar is not in this file)


Now for the Access part ...

ProgramCodes table (i may have to have this split) has:
Guitar
Code
Option
GuitarCodeOptionID

Because the Code (programming code) is different depending on the Guitar
and
guitar with certain option combinations to make the end guitar be what it
is
and do the functions that it does, i need to try to create a file to hold
all
these Codes to the guitar and guitar and option combinations so that the
Codes will print on a label.

The label goes on the base Guitar with the options and now we need the
Codes
to the production team.

The look at the Guitar they need to build and would like to use it and
this
only to program their machines.


Currently they have to look at the label for the Guitar and the options
printed (since I don't have the Codes on their yet) and have to look-up a
worksheet to figure out what Codes to enter into their machines.


Having the Codes print with the rest of the Guitar info will save all the
manual looking up.


The ProgramCodes table is set-up with the Form and subform but I don't
have
it right because of the combinations.

The combination of the options for a unique code could be 2 or 6, etc ...

i hope this helps clarify the situation.

OK, let me try a full text response to your question.

GuitarBase - this is the base guitar the user selected to start out with
GuitarBaseID PK AutoNum
GuitarBaseDesc
etc.

OptionTypes - this is the T or R or whatever
OptionTypeCode PK this will actually contain the T or R or whatever
OptionTypeCodeDesc -Top or Rear or whatever

Options - these are the options available, presumably they can be shared
across guitars
OptionID PK AutoNumber
OptionTypeCode FK to OptionTypes
OptionCode (Jamie will tell you you need to constrain this to be unique) the
important thing is this is your option code without T or R
OptionDesc

GuitarBaseOptions - this tells you which options are available for which
guitar
GuitarBaseID FK to GuitarBase
OptionID FK to Options

ClientGuitar - this is a single particular guitar that has been ordered
ClientGuitarID PK AutoNumber
GuitarBaseID FK GuitarBase
ClientID FK to Clients (not defined here, I figure you can handle it)
etc.

ClientGuitarDetails
ClientGuitarID
OptionID

So, once you have the OptionID, you can easily match it to a particular
guitar and figure out what that option is and how it is coded. Note that
the Options Table may not be correct, because it looks like you can have the
same option code for both the top and the rear. If that is the case, you
would possibly take the OptionTypeCode out of Options and move it to
ClientGuitarDetails. But this should be enough to point you in the right
direction.

HTH;

Amy