How to obtain logical combination for query result?

[~ angel ~ via AccessMonster.com]

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

the fields in this table are:
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!

 

[Jamie Collins]

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

the fields in this table are:
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.

--

 

[Amy Blankenship]

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

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

the fields in this table are:
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.

 

[~ angel ~ via AccessMonster.com]

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:
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.

--

 

[~ angel ~ via 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!

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]

--

 

[~ angel ~ via AccessMonster.com]

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!

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]

--

 

[Amy Blankenship]

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 ;-)

 

[Amy Blankenship]

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

 

[~ angel ~ via AccessMonster.com]

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.

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

 

[Amy Blankenship]

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

 

[~ angel ~ via AccessMonster.com]

Hi Amy,
sort of ...

I have the Guitars and Options tables set-up
I also have the Codes table of all the unique codes which the programmer will
add all them shortly.

I am not sure on these parts of what you have suggested below.
Yes, options can interchangeable used by any guitar as far as I know.

Let's not get hung-up on the T or R
example, here's a code with neither
Code CodeID
B52-2V 50


What do you mean here "OptionTypeCode FK to OptionTypes"?
Each of the Options in the Options table has a unique OptionID, although each
option itself is a unique itemnumber
Each of the Guitars in the Guitars table has a unique GuitarID, although each
guitar itself is a unique itemnumber
Each of the Codes in the ProgrammingCodes table has a unique CodeID

My ProgramCodes table:
GuitarID
OptionID
CodeID
GuitarOptionCodeID

I don't get the GuitarBaseOptions part, there is no such thing??
so then the Client portion is also confusing.

so from the ProgramCodes table:
i'm missing the part of how the
I can get the 1 GuitarOptionCodeID for this scenario: GuitarID with a CodeID
and the 1 GuitarOptionCodeID for this scenario as well: GuitarID with 1
OptionID
BUT
how do I get the GuitarOptionID for when the GuitarID and OptionIDs have
several CodeIDs? What links the GuitarID and OptionIDs combi to obtain the
GuitarOptionID?

GuitarID OptionID CodeID GuitarOptionCodeID
C66M - - B52-2V - 76
C66M - CAP - B52-2V - 77
C66M - HSS - B52-2V - 78
C66M - - B521-2R - 79
C66M - CAP - B521-2R - 80
C66M - HSS - B521-2R - 81
C66M - - B521-2TV - 82
C66M - CAP - B531-2TV - 83
C66M - HSS - B541-2TV - 84

results would be:
C66M - NULL - B52-2V and B521-2R and B521-2TV
C66M - CAP - B52-2V
C66M - HSS - B52-2V
C66M - CAP and HSS - B531-2R and B541-2TV


since the GuitarOptionCodeID is unique for every entry, i can't use that as
the unique code identifier for the guitar and guitar option combo.

Amy Blankenship wrote:

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

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

 

[Amy Blankenship]

Hi Amy,
sort of ...

I have the Guitars and Options tables set-up
I also have the Codes table of all the unique codes which the programmer
will
add all them shortly.

I am not sure on these parts of what you have suggested below.
Yes, options can interchangeable used by any guitar as far as I know.

Let's not get hung-up on the T or R
example, here's a code with neither
Code CodeID
B52-2V 50

That is what the "or whatever" is for. Possibly you have a B type? Bottom?
It is a geneal catchall that allows you to have full control over that piece
of the option code that is shared and, as importantly, a way to call up what
the meaning of that is at weill.

What do you mean here "OptionTypeCode FK to OptionTypes"?

Foreign key. So you link out to the OptionTypeCode table, and not only do
you manage to auto fill the T, P, B, or whatever, you also are able to
present a user-readable description of what that means.

Each of the Options in the Options table has a unique OptionID, although
each
option itself is a unique itemnumber
Each of the Guitars in the Guitars table has a unique GuitarID, although
each
guitar itself is a unique itemnumber
Each of the Codes in the ProgrammingCodes table has a unique CodeID

Well if you're not willing to revisit your table structure at all then I'm
not sure I can help you. But I am not exactly sure how the ProgrammingCodes
relate to the options. I suspect that the OptionCode I suggested would fit
that bill, once you concatenate the related option codes together.

My ProgramCodes table:
GuitarID
OptionID
CodeID
GuitarOptionCodeID

OK, so you have a base GuitarID, and that now means that any sets of options
are referring to that base guitar, not to one specific guitar, which is what
the ClientGuitar table would have gained you. Once you have defined the
ClientGuitar, then you can have multiple ClientGuitarOptions applied to it.

I don't get the GuitarBaseOptions part, there is no such thing??

There should be, unless all guitars can have all options. You need to have
a way to present to the user which options are available on which guitar.

so then the Client portion is also confusing.

You need to be able to call out a particular guitar for that one client.
You cannot apply the options to the definition of the base model, which is
what your current table structure tries to do.

so from the ProgramCodes table:
i'm missing the part of how the
I can get the 1 GuitarOptionCodeID for this scenario: GuitarID with a
CodeID
and the 1 GuitarOptionCodeID for this scenario as well: GuitarID with 1
OptionID
BUT
how do I get the GuitarOptionID for when the GuitarID and OptionIDs have
several CodeIDs? What links the GuitarID and OptionIDs combi to obtain the
GuitarOptionID?

The OptionID be associated with only one CodeID. Each option should be its
own thing. If you have a business case for one option having multiple code
ID's you should share it. That will require a table structure that is even
more complex. However, I suspect you're doing something wonky with your
tables that is confusing you.

GuitarID OptionID CodeID GuitarOptionCodeID
C66M - - B52-2V - 76
C66M - CAP - B52-2V - 77
C66M - HSS - B52-2V - 78
C66M - - B521-2R - 79
C66M - CAP - B521-2R - 80
C66M - HSS - B521-2R - 81
C66M - - B521-2TV - 82
C66M - CAP - B531-2TV - 83
C66M - HSS - B541-2TV - 84

results would be:
C66M - NULL - B52-2V and B521-2R and B521-2TV
C66M - CAP - B52-2V
C66M - HSS - B52-2V
C66M - CAP and HSS - B531-2R and B541-2TV

Your first problem is you are trying to associate the options with the
_definition_ of a base guitar rather than with the _instance_ of a guitar
that is being configured by a client. I am not clear on why you would have
a null OptionID, yet have a CodeID. My understanding is that your codes are
in place in order to get the options programmed. So the information of what
code to use should be in your Options table, not lumped in with everything
else.

since the GuitarOptionCodeID is unique for every entry, i can't use that
as
the unique code identifier for the guitar and guitar option combo.

Yes. You need to structure all your tables so that ultimately you can get
the information out of them that you need. It is not a case of just adding
a table and throwing everything in and hoping it works. All your tables
need to work together for good ;-).

You really need to read up on normalization. I can tell you this stuff
until I am blue in the face, but unless you understand the underlying
concepts you will still keep trying to hold on to a structure that doesn't
work.

-Amy

 

[Amy Blankenship]

R and T do have meaning in the real world, and if nothing else can be used
to help present the options to the user in a logical manner to make it
easier to select the ones they want.

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.

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

 

[~ angel ~ via AccessMonster.com]

this is the set-up i think i've gathered that i need to do

GuitarOptionCodeID Guitar Option Code
1 - C66M -
- B52-2V
1 - C66M -
- B521-2R
1 - C66M -
- B521-2TV

2 - C66M - CAP
- B52-2V
2 - C66M - CAP
- B521-2R
2 - C66M - CAP
- B531-2TV

3 - C66M - HSS
- B52-2V
3 - C66M - HSS
- B521-2R
3 - C66M - HSS
- B541-2TV

now, not sure how to assign the GuitarOptionCodeID and when the Guitar and
options change??
so not sure how to

R and T do have meaning in the real world, and if nothing else can be used
to help present the options to the user in a logical manner to make it
easier to select the ones they want.

Amy,
not sure if you saw my last post.

[quoted text clipped - 29 lines]

 

[Jamie Collins]

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

Do you ask that question whenever someone posts VBA code in direct
response to an OP? When Jet syntax is enhanced to include code
comments, I'll use them ;-)

Amy, it's a matter of style and content. When describing table
structure in a newsgroup post, I prefer SQL DDL because it's explicit,
exacting, concise, etc; also, some Jet features are only available via
SQL DDL e.g. CHECK constraints.

I note from previous posts that you don't read SQL DDL; if you are
willing to learn, I recommend:

Intermediate Microsoft Jet SQL for Access 2000
http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

Myself, I've no objection to your style but I tend to be bothered when
content is lacking. As we've seen, you describe your table structures
using natural language and on occasions have fallen short on content
IMO e.g. where you have proffered an autonumber PK but have made no
mention of candidate keys.

Jamie.

--

 

[Jamie Collins]

My ProgramCodes table:
GuitarID
OptionID
CodeID
GuitarOptionCodeID

I don't see how this table can work because, from the sample data in
your OT, it seems all options for the item have to be considered _in
combination_ when determining the code.

For example, it cannot be said that guitar item 'DC135' with option 26
generates code '139T' because you've told us that when RB is also on
the same guitar '139T' will not apply but '139T-RB' instead. You have
confirmed elsewhere in this thread that there's no logic to this i.e.
it's not simply a case of appending '-RB' to the code.

Your OT showed us how the legal combinations for between zero and two
options all resulted in _exactly_ two unique codes. I don't know how
to extrapolate this for up to six options generating _up to_ four
unique codes. I'd need to see a *lot* more sample data to be
comfortable to suggesting a structure e.g. to know how different items
produce different codes for the same options, etc.

Jamie.

--

 

[Amy Blankenship]

Do you ask that question whenever someone posts VBA code in direct
response to an OP? When Jet syntax is enhanced to include code
comments, I'll use them ;-)

Amy, it's a matter of style and content. When describing table
structure in a newsgroup post, I prefer SQL DDL because it's explicit,
exacting, concise, etc; also, some Jet features are only available via
SQL DDL e.g. CHECK constraints.

If the user can't understand the content, the style doesn't matter. You
haven't taught her any principles she can use to tailor your solution to her
own circumstances or even given her pointers to search terms she could use
to enhance her understanding.

I note from previous posts that you don't read SQL DDL; if you are
willing to learn, I recommend:

I don't take SQL DDL as any sort of explanation of anything, which it isn't.
You cannot teach users anything about how to design databases this way,
particularly when the design you come up with is simply wrong.

Intermediate Microsoft Jet SQL for Access 2000
http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

Myself, I've no objection to your style but I tend to be bothered when
content is lacking. As we've seen, you describe your table structures
using natural language and on occasions have fallen short on content
IMO e.g. where you have proffered an autonumber PK but have made no
mention of candidate keys.

Yeah Mr. Hot Stuff. You recommended two different tables, just because you
noticed many codes had different prefixes. You're an Access genius! Just
because you like to prove you can write unintelligible (to the average user)
code all the time doesn't mean you actually ARE smarter than anyone else.
It just means you like to APPEAR smarter.

-Amy

 

[Amy Blankenship]

I think you need to step back and tell us what the actual relationship is
between the Option and the OptionCode. How can different options have the
same code? You need to quit being hung up on the table design you thought
would work, and allow yourself to be more open to the advice you have asked
for.

-Amy

this is the set-up i think i've gathered that i need to do

GuitarOptionCodeID Guitar Option Code
1 - C66M -
- B52-2V
1 - C66M -
- B521-2R
1 - C66M -
- B521-2TV

2 - C66M - CAP
- B52-2V
2 - C66M - CAP
- B521-2R
2 - C66M - CAP
- B531-2TV

3 - C66M - HSS
- B52-2V
3 - C66M - HSS
- B521-2R
3 - C66M - HSS
- B541-2TV

now, not sure how to assign the GuitarOptionCodeID and when the Guitar and
options change??
so not sure how to

R and T do have meaning in the real world, and if nothing else can be used
to help present the options to the user in a logical manner to make it
easier to select the ones they want.

Amy,
not sure if you saw my last post.

[quoted text clipped - 29 lines]

Amy

 

[~ angel ~ via AccessMonster.com]

Option is the the itemnumber
Code is the programming code

not sure how I managed to make you think it's the same.

Guitar (itemnumber) can have many Options (also itemnumbers)
There can be many Codes (programming code) to the following combinations
Just Guitar without any Options
Guitar and 1 Option
Guitar and 2 Option, etc ... up to how every many but right now there is up
to 6 options to a Guitar that can have up to 4 Codes.

Currently 4 Codes to any combinations the most one Guitar (which can also be
said as one Invoice as the same Guitar w/ different and/or same Options can
be on many invoices).


HTH!

I think you need to step back and tell us what the actual relationship is
between the Option and the OptionCode. How can different options have the
same code? You need to quit being hung up on the table design you thought
would work, and allow yourself to be more open to the advice you have asked
for.

-Amy

this is the set-up i think i've gathered that i need to do

[quoted text clipped - 33 lines]

 

[~ angel ~ via AccessMonster.com]

185RR is RIGHT REAR (same for all 3)
186RHT RIGHT HOLLOW TOP (same for all 3)
187RT is RIGHT TOP (top for standard)
187RT38 is RIGHT TOP FOR 38 Option (top for 38 option)
188RT-B is RIGHT TOP FOR BB Option (top for BB option)


"We have 3 guitar models that use,or have 2 options combined.
From example Model DC135
I entered DC135 standard 2 codes then
RB option 2 codes then
26 option 2 codes then
29 option 2 codes these are all good !

Now I need codes to print for 2 options combined, for DC135
RB & 26 options together there is 2 codes for that,and
RB & 29 options together there are 2 codes for that"

and other Guitars and combinations

I've changed since and have come up with this

ComboID Guitar Option
Code
1 - C66M -
- B52-2V
1 - C66M -
- B521-2R
1 - C66M -
- B521-2TV

2 - C66M - CAP
- B52-2V
2 - C66M - CAP
- B521-2R
2 - C66M - CAP
- B531-2TV

3 - C66M - HSS
- B52-2V
3 - C66M - HSS
- B521-2R
3 - C66M - HSS
- B541-2TV

here the B52-2V and B521-2R repeats for each of the others.


AE185
1 - AE185 - - 185RR
1 - AE185 - - 186RHT
1 - AE185 - - 187RT

2 - AE185 - - 185RR
2 - AE185 - - 186RHT
2 - AE185 - - 187RT38

2 - AE185 - - 185RR
2 - AE185 - - 186RHT
2 - AE185 - - 188RT38B

NO OPTIONS 3 CODES = 185RR 186RHT 187RT
38 OPTION NEEDS 3 CODES = 185RR 186RHT 187RT38
BB OPTION NEEDS 3 CODES = 185RR 186RHT 188RT-B
38 & BB OPTIONS 3 CODES = 185RR 186RHT 188RT38B

Notice here that the 185RR and 186RHT repeats in each combination and the
last code (only in this item number example) is different.

I thought the Combo field will help to keep the Code for the Fadal Option
together.
But when you look at Combo 4, the same code repeats.

I can’t have the result repeat the Code, it needs to show it only once for
the Combo.

So if an invoice 111 for item AE185 and the options for this invoice happens
to have BOTH 38 & BB, it needs to ONLY show 38 & BB OPTIONS 3 CODES = 185RR
186RHT 188RT38B

If an invoice 222 for item AE185 just had BB, it needs to show BB OPTION
NEEDS 3 CODES = 185RR 186RHT 188RT-B

So invoice 111 can’t show 188RT-B even though it has an Option of BB BECAUSE
it also has Option 38


you know??