S
Spidey3721
Looking for opinions on the best approach for the following situation:
I am trying to track customer selections for a variety of different product
that are spread out over a number of different residential construciton
projects...
For Example: Owner of unit #404 ([UnitID]) of the "Cherry Lane Project"
([ProjectID]) has selected light fixture D4312df ([LightID]) for their
dining room ([RoomID]), 3" red oak ([FloorID]) for their foyer floor
([RoomID]) , barely grey latex paint ([PaintID]) for their living room
([RoomID]) paint, etc, etc, etc -
So far, for each of these product groups (Paint, Floors, Countertops,
Plumbing Fixtures, Light Fixtures, etc...), I have two tables.
----------------------
1) "Master List" table set up for each main group of items acts as a catalog
(includes manufacturer info, cost, etc...)
2) a "Selections" table, which stores ProjectID (the
buidling/development#),UnitID (The Condo/house #),RoomID (Location in
house), SelectionID (the foreign key to the "Master List" of available
selections.
-----------------------
I plan on having a Selection Form, where the user can make all selections
for each owner, where they can jump between the different projects, as well
as the different Units (Condos or houses).
The problem is: As I move from job to job, I have different products
available.
I am looking for the best design approach - I would like a form that allows
the user to setup new projects, where they can go through all available
products in each main group and choose which items will be offered to
customers on that particular job.
Currently - I am considering two different options and am looking for
opinions:
OPTION #1
include a Yes/No field in each Master List table FOR EACH JOB (a field
called "Cherry Lane"). I could then have a form that shows the product
descriptions, along with a check box for that job. I could maybe have a
[ProjectID] combobox that will determine which Yes/No field is shown for the
user to edit....I could then have control sources setup in the Customer
selection form to change their control source criteria based on which
ProjectID they are dealing with.
Drawbacks: I would rather not have to manually create a new field every time
a new job comes about, unless there is a way to do this in a VB procedure;
Not sure if it makes much sense to keep expanding the table with fields for
each new job. Years from now I may have 100 different Yes/No fields for each
main group's Master List...
OPTION # 2
Create a third table for each main group, where I enter in a new record for
each product that is available every job. This sounds like a lot of
duplication though, for I am thinking I would need a couple hundred records
for each job
OPTION # 3
Wondering if there is a way to just add a field at the end of each Product
where I could LIST out jobs that it is being offered on (FloorID:3" Red Oak
floor, ManufID: Floors Incorporated, JobList: "Cherry Lane", "Locust Lane",
"The Edgewood Building", etc). Thinking I could then have combo boxes have a
WHERE clause that filters out all products that do not include *Cherry
Lane*...
THOUGHTS ????
(Hope this isn't either too confusing/Lacking vaulable information)
I am trying to track customer selections for a variety of different product
that are spread out over a number of different residential construciton
projects...
For Example: Owner of unit #404 ([UnitID]) of the "Cherry Lane Project"
([ProjectID]) has selected light fixture D4312df ([LightID]) for their
dining room ([RoomID]), 3" red oak ([FloorID]) for their foyer floor
([RoomID]) , barely grey latex paint ([PaintID]) for their living room
([RoomID]) paint, etc, etc, etc -
So far, for each of these product groups (Paint, Floors, Countertops,
Plumbing Fixtures, Light Fixtures, etc...), I have two tables.
----------------------
1) "Master List" table set up for each main group of items acts as a catalog
(includes manufacturer info, cost, etc...)
2) a "Selections" table, which stores ProjectID (the
buidling/development#),UnitID (The Condo/house #),RoomID (Location in
house), SelectionID (the foreign key to the "Master List" of available
selections.
-----------------------
I plan on having a Selection Form, where the user can make all selections
for each owner, where they can jump between the different projects, as well
as the different Units (Condos or houses).
The problem is: As I move from job to job, I have different products
available.
I am looking for the best design approach - I would like a form that allows
the user to setup new projects, where they can go through all available
products in each main group and choose which items will be offered to
customers on that particular job.
Currently - I am considering two different options and am looking for
opinions:
OPTION #1
include a Yes/No field in each Master List table FOR EACH JOB (a field
called "Cherry Lane"). I could then have a form that shows the product
descriptions, along with a check box for that job. I could maybe have a
[ProjectID] combobox that will determine which Yes/No field is shown for the
user to edit....I could then have control sources setup in the Customer
selection form to change their control source criteria based on which
ProjectID they are dealing with.
Drawbacks: I would rather not have to manually create a new field every time
a new job comes about, unless there is a way to do this in a VB procedure;
Not sure if it makes much sense to keep expanding the table with fields for
each new job. Years from now I may have 100 different Yes/No fields for each
main group's Master List...
OPTION # 2
Create a third table for each main group, where I enter in a new record for
each product that is available every job. This sounds like a lot of
duplication though, for I am thinking I would need a couple hundred records
for each job
OPTION # 3
Wondering if there is a way to just add a field at the end of each Product
where I could LIST out jobs that it is being offered on (FloorID:3" Red Oak
floor, ManufID: Floors Incorporated, JobList: "Cherry Lane", "Locust Lane",
"The Edgewood Building", etc). Thinking I could then have combo boxes have a
WHERE clause that filters out all products that do not include *Cherry
Lane*...
THOUGHTS ????
(Hope this isn't either too confusing/Lacking vaulable information)