grabbing data from one table based on selection in another

[Guest]

I have two tables. I have a furniture table [Item number] and a fabric
table[FabricSpecs]. The furniture table has three fabric fields [Item
number.FabricSpec] [FabricSpec2] [FabricSpec3] (yes, the names are
confusingly similar). Each of the fabric fields in the [Item number] table
looks up it's data based on the [FabricSpecs] table. (For example, there may
be five different fabrics defined in a table. A chair might have three of
these fabrics on it. A couch might have two other fabrics. The chair and the
couch might have one of the fabrics in common)

Each fabric record has different information about the fabric. How much it
costs [FabricSpecs.FabricCost], where you can buy it, etc. The furniture
table has other information such as how much fabric is needed for the piece
[Item number.FabricAmount1], [FabricAmount2] and [FabricAmount3]. (for
example, a chair might need only 1 yard of fabric while a couch needs 20
yards).

I created a query that basically looks at all of the fields in both tables.
I need it to do a simple math equation for each fabric. I need it to
calculate the cost of the fabric (which might be $10 for fabric1, $50 for
fabric2 and $2 for fabric3) and multiply it by the amount of fabric used in
the peice of furniture.

The problem I'm running in to is that it always multiplies the [Item
number.FabricAmount1] by the first [FabricSpecs.FabricCost] selected in the
[Item number] record. (for example if I selected fabric1 which is $10 as the
first fabric for the chair and fabric2 which is $2 as the second fabric for
the chair, it always multiplies the [Item number.FabricAmount1] by $10.

right now I'm using the following
FabricCost1: [Item Number.FabricAmount1]*[FabricSpecs.FabricCost]
FabricCost2: [Item Number.FabricAmount2]*[FabricSpecs.FabricCost]
FabricCost3: [Item Number.FabricAmount3]*[FabricSpecs.FabricCost]

How can I get it to recognize that the cost of the second fabric selection
is the cost of fabric2 not fabric1???

Basically, how can I grab the cost of fabric2 from the fabric table, based
on a selection of fabric2 [Item number.FabricSpec2] from the second fabric
field from the furniture field?

Lila

 

[Jamie Collins]

I have two tables. I have a furniture table [Item number] and a fabric
table[FabricSpecs]. The furniture table has three fabric fields [Item
number.FabricSpec] [FabricSpec2] [FabricSpec3] (yes, the names are
confusingly similar). Each of the fabric fields in the [Item number] table
looks up it's data based on the [FabricSpecs] table. (For example, there may
be five different fabrics defined in a table. A chair might have three of
these fabrics on it. A couch might have two other fabrics. The chair and the
couch might have one of the fabrics in common)

Each fabric record has different information about the fabric. How much it
costs [FabricSpecs.FabricCost], where you can buy it, etc. The furniture
table has other information such as how much fabric is needed for the piece
[Item number.FabricAmount1], [FabricAmount2] and [FabricAmount3]. (for
example, a chair might need only 1 yard of fabric while a couch needs 20
yards).

I created a query that basically looks at all of the fields in both tables.
I need it to do a simple math equation for each fabric. I need it to
calculate the cost of the fabric (which might be $10 for fabric1, $50 for
fabric2 and $2 for fabric3) and multiply it by the amount of fabric used in
the peice of furniture.

The problem I'm running in to is that it always multiplies the [Item
number.FabricAmount1] by the first [FabricSpecs.FabricCost] selected in the
[Item number] record. (for example if I selected fabric1 which is $10 as the
first fabric for the chair and fabric2 which is $2 as the second fabric for
the chair, it always multiplies the [Item number.FabricAmount1] by $10.

right now I'm using the following
FabricCost1: [Item Number.FabricAmount1]*[FabricSpecs.FabricCost]
FabricCost2: [Item Number.FabricAmount2]*[FabricSpecs.FabricCost]
FabricCost3: [Item Number.FabricAmount3]*[FabricSpecs.FabricCost]

How can I get it to recognize that the cost of the second fabric selection
is the cost of fabric2 not fabric1???

Basically, how can I grab the cost of fabric2 from the fabric table, based
on a selection of fabric2 [Item number.FabricSpec2] from the second fabric
field from the furniture field?

Your 'confusing' column names is a probable symptom of a design
problem. I think you may be modelling the relationship between
furniture items and their fabric covering incorrectly (denormalized).

Consider this structure (ANSI-92 Query Mode SQL DDL syntax):

CREATE TABLE Furniture
(
item_number INTEGER NOT NULL UNIQUE,
item_name VARCHAR(30) NOT NULL UNIQUE
)
;
CREATE TABLE FabricSpecs
(
fabric_name VARCHAR(30) NOT NULL UNIQUE,
fabric_cost_per_yard DECIMAL (8, 4) NOT NULL
)
;
CREATE TABLE FurnitureCoverings (
item_number INTEGER NOT NULL
REFERENCES Furniture (item_number),
fabric_name VARCHAR(30) NOT NULL
REFERENCES FabricSpecs (fabric_name),
UNIQUE (item_number, fabric_name),
fabric_amount DECIMAL(6, 3) NOT NULL,
CHECK (fabric_amount > 0.000)
)
;

Some test data (SQL DML):

INSERT INTO Furniture (item_number, item_name)
VALUES (1, 'The Chair')
;
INSERT INTO Furniture (item_number, item_name)
VALUES (2, 'The Couch')
;
INSERT INTO FabricSpecs (fabric_name, fabric_cost_per_yard)
VALUES ('fabric1', 10)
;
INSERT INTO FabricSpecs (fabric_name, fabric_cost_per_yard)
VALUES ('fabric2', 50)
;
INSERT INTO FabricSpecs (fabric_name, fabric_cost_per_yard)
VALUES ('fabric3', 2)
;
INSERT INTO FurnitureCoverings (item_number, fabric_name,
fabric_amount)
VALUES (1, 'fabric1', 1)
;
INSERT INTO FurnitureCoverings (item_number, fabric_name,
fabric_amount)
VALUES (1, 'fabric2', 0.5)
;
INSERT INTO FurnitureCoverings (item_number, fabric_name,
fabric_amount)
VALUES (2, 'fabric1', 20)
;
INSERT INTO FurnitureCoverings (item_number, fabric_name,
fabric_amount)
VALUES (2, 'fabric2', 8)
;
INSERT INTO FurnitureCoverings (item_number, fabric_name,
fabric_amount)
VALUES (2, 'fabric3', 5)
;

And the desired results:

SELECT C1.item_number, F1.item_name, S1.fabric_name,
S1.fabric_cost_per_yard, C1.fabric_amount, S1.fabric_cost_per_yard *
C1.fabric_amount AS fabric_cost_this_item
FROM
(
FurnitureCoverings AS C1
INNER JOIN FabricSpecs AS S1
ON C1.fabric_name = S1.fabric_name
)
INNER JOIN Furniture AS F1
ON C1.item_number = F1.item_number
;

Jamie.

--

 

[Guest]

What Jamie is really trying to say is that what you really need is 3 tables
(Furniture, FurnitureFabric, and Fabric.

This structure allows you to assign as many fabrics as you need to a
particular piece of furniture. Where the FurnitureFabric table would contain
FurnitureID, FabricID, and Quantity columns.

Having said this, you can modify your query a couple of different ways.

1. You could have multiple copies of the Fabric table in the query grid.
To do this, select the Furniture table, and three copies of the Fabric table
into the query grid. Join Fabric1 from the Furniture table to the FabricID
field in one of the Fabric tables, join Fabric2 from the Furniture table to
the FabricID field in the second Fabric table, and join Fabric3 from the
Furniture table to the FabricID field in the third table. Once you have done
this, right click on each of the joins and change the join to select all
records from the Furniture table and only the matching records from the
joined Fabric table. Now when you calculate your costs they will look like:

FabricCost1: [ItemNumber].FabricAmount1 * [FabricSpecs].FabricCost
FabricCost2: [ItemNumber].FabricAmount2 * [FabricSpecs_1].FabricCost
FabricCost3: [ItemNumber].FabricAmount3 * [FabricSpecs_2].FabricCost

2. The other way to do this is to use the DLOOKUP Domain function to get
the fabric cost for each of the fabric types.

FabricCost1: [ItemNumber].FabricAmount1 *
DLOOKUP("FabricCost", "FabricSpecs", "FabricID = " & [ItemNumber].Fabric1)

FabricCost2: [ItemNumber].FabricAmount2 *
DLOOKUP("FabricCost", "FabricSpecs", "FabricID = " & [ItemNumber].Fabric2)

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

I have two tables. I have a furniture table [Item number] and a fabric
table[FabricSpecs]. The furniture table has three fabric fields [Item
number.FabricSpec] [FabricSpec2] [FabricSpec3] (yes, the names are
confusingly similar). Each of the fabric fields in the [Item number] table
looks up it's data based on the [FabricSpecs] table. (For example, there may
be five different fabrics defined in a table. A chair might have three of
these fabrics on it. A couch might have two other fabrics. The chair and the
couch might have one of the fabrics in common)

Each fabric record has different information about the fabric. How much it
costs [FabricSpecs.FabricCost], where you can buy it, etc. The furniture
table has other information such as how much fabric is needed for the piece
[Item number.FabricAmount1], [FabricAmount2] and [FabricAmount3]. (for
example, a chair might need only 1 yard of fabric while a couch needs 20
yards).

I created a query that basically looks at all of the fields in both tables.
I need it to do a simple math equation for each fabric. I need it to
calculate the cost of the fabric (which might be $10 for fabric1, $50 for
fabric2 and $2 for fabric3) and multiply it by the amount of fabric used in
the peice of furniture.

The problem I'm running in to is that it always multiplies the [Item
number.FabricAmount1] by the first [FabricSpecs.FabricCost] selected in the
[Item number] record. (for example if I selected fabric1 which is $10 as the
first fabric for the chair and fabric2 which is $2 as the second fabric for
the chair, it always multiplies the [Item number.FabricAmount1] by $10.

right now I'm using the following
FabricCost1: [Item Number.FabricAmount1]*[FabricSpecs.FabricCost]
FabricCost2: [Item Number.FabricAmount2]*[FabricSpecs.FabricCost]
FabricCost3: [Item Number.FabricAmount3]*[FabricSpecs.FabricCost]

How can I get it to recognize that the cost of the second fabric selection
is the cost of fabric2 not fabric1???

Basically, how can I grab the cost of fabric2 from the fabric table, based
on a selection of fabric2 [Item number.FabricSpec2] from the second fabric
field from the furniture field?

Your 'confusing' column names is a probable symptom of a design
problem. I think you may be modelling the relationship between
furniture items and their fabric covering incorrectly (denormalized).

Consider this structure (ANSI-92 Query Mode SQL DDL syntax):

CREATE TABLE Furniture
(
item_number INTEGER NOT NULL UNIQUE,
item_name VARCHAR(30) NOT NULL UNIQUE
)
;
CREATE TABLE FabricSpecs
(
fabric_name VARCHAR(30) NOT NULL UNIQUE,
fabric_cost_per_yard DECIMAL (8, 4) NOT NULL
)
;
CREATE TABLE FurnitureCoverings (
item_number INTEGER NOT NULL
REFERENCES Furniture (item_number),
fabric_name VARCHAR(30) NOT NULL
REFERENCES FabricSpecs (fabric_name),
UNIQUE (item_number, fabric_name),
fabric_amount DECIMAL(6, 3) NOT NULL,
CHECK (fabric_amount > 0.000)
)
;

Some test data (SQL DML):

INSERT INTO Furniture (item_number, item_name)
VALUES (1, 'The Chair')
;
INSERT INTO Furniture (item_number, item_name)
VALUES (2, 'The Couch')
;
INSERT INTO FabricSpecs (fabric_name, fabric_cost_per_yard)
VALUES ('fabric1', 10)
;
INSERT INTO FabricSpecs (fabric_name, fabric_cost_per_yard)
VALUES ('fabric2', 50)
;
INSERT INTO FabricSpecs (fabric_name, fabric_cost_per_yard)
VALUES ('fabric3', 2)
;
INSERT INTO FurnitureCoverings (item_number, fabric_name,
fabric_amount)
VALUES (1, 'fabric1', 1)
;
INSERT INTO FurnitureCoverings (item_number, fabric_name,
fabric_amount)
VALUES (1, 'fabric2', 0.5)
;
INSERT INTO FurnitureCoverings (item_number, fabric_name,
fabric_amount)
VALUES (2, 'fabric1', 20)
;
INSERT INTO FurnitureCoverings (item_number, fabric_name,
fabric_amount)
VALUES (2, 'fabric2', 8)
;
INSERT INTO FurnitureCoverings (item_number, fabric_name,
fabric_amount)
VALUES (2, 'fabric3', 5)
;

And the desired results:

SELECT C1.item_number, F1.item_name, S1.fabric_name,
S1.fabric_cost_per_yard, C1.fabric_amount, S1.fabric_cost_per_yard *
C1.fabric_amount AS fabric_cost_this_item
FROM
(
FurnitureCoverings AS C1
INNER JOIN FabricSpecs AS S1
ON C1.fabric_name = S1.fabric_name
)
INNER JOIN Furniture AS F1
ON C1.item_number = F1.item_number
;

Jamie.

 

[Jamie Collins]

What Jamie is really trying to say is that what you really need is 3 tables
(Furniture, FurnitureFabric, and Fabric.

This structure allows you to assign as many fabrics as you need to a
particular piece of furniture. Where the FurnitureFabric table would contain
FurnitureID, FabricID, and Quantity columns.

Thanks for trying to clarify the situation but I try to avoid telling
an OP what they *need*, other than perhaps, "If you want a better
quality answer then you *need* to provide us with more information."
How do you know the OP doesn't have good business reasons for their
design choices? For example, they could have a validation rule to
ensure that the per yard cost for a single item of furniture does not
exceed 100 USD. With their existing design this is simple:

FabricCost1 + FabricCost2 + FabricCost3 <= 100.00

with perhaps modifications to handle the NULL value (maybe not every
piece of their furniture comprises exactly three fabrics). My proposal
would make such a rule trickier to define, therefore I merely ask the
OP to give consideration to an alternative design which may or may not
be more suitable.

Jamie.

--

 

[Guest]

Jamie,

Totally agree that there are instances where someone might make a conscience
decision not to normalize, and you raise one of these.

However, in my experience, the norm is that people who are new to databases
tend to treat them like spreadsheets and use repetetive fields (FabricID1,
FabricAmount1, FabricID2, ...). When I see this, I like to recommend a more
normalized structure. In other cases, they are just stuck with what others
developed, and are unsure how to accomplish what they want with what they
have to work with. In either case, I try to give them a potential solution
to the problem given their current table structure.

Dale

 

[John W. Vinson]

I have two tables. I have a furniture table [Item number] and a fabric
table[FabricSpecs]. The furniture table has three fabric fields [Item
number.FabricSpec] [FabricSpec2] [FabricSpec3] (yes, the names are
confusingly similar). Each of the fabric fields in the [Item number] table
looks up it's data based on the [FabricSpecs] table. (For example, there may
be five different fabrics defined in a table. A chair might have three of
these fabrics on it. A couch might have two other fabrics. The chair and the
couch might have one of the fabrics in common)

If you're committed to this design decision - and Jamie's suggestions about
normalization are well taken! - what you can do is create a Query by adding
[Item number] to the query grid, and then adding [Fabric Specs] *THREE TIMES*.
Access will alias them as [Fabric Specs], [Fabric Specs1] and [Fabric Specs2].
Join each of these tables to the corresponding FabricSpec field in your table.
This will give you all three specs individually, each associated with the
correct fabric.

A better design would be to recognize that each Item has many Fabrics, and
each Fabric may be used in many Items; and properly model this many to many
relationship with a new table, ItemFabrics, with an ItemID and a FabricSpec
field. If an item requires three fabrics, this table would have three records;
if it requires four, you can just add a fourth record (rather than completely
redesigning your table, your queries, your forms and your reports, as you'll
need to do with your non-normalized design).

John W. Vinson [MVP]

 

[Guest]

You are right. Originally this was a spreadsheet. Someone (not I) turned it
into a database, but they were still using it mainly as a set of three
spreadsheets (which were turned into tables) but into which you could insert
pictures and run reports. When I found the database I was excited that
someone had finally moved it into a database, but knew it could do much more.
They were still required to insert the same information in two or three
locations which was completely unnecessary so I'm trying to streamline the
existing database.

For example, the fabric table was linked to the furniture table using a
lookup field, and the cost of the fabric was in the fabric table, but also
had to be typed into the furniture table. If the cost of the fabric changed,
it also had to be changed in each furniture record where it was typed in.

I will examine each suggestion and see which my intermediate knowledge of
access allows me to understand enough to impliment.

My next task is to get the picture of the fabric (which is stored as a text
value just as in the Northwind sample database) on the furniture form to
change every time you change the fabric selection, just as the price should
change... but that's a different question. I was hoping they were related
enough for me to glean the answer to that question from the answer to this
question. If not, I'll post again.

Thank you!

 

[Guest]

This is an interesting point. I assume I'd use a sub-form to accomplish
entering this on a form, right?

 

[John W. Vinson]

This is an interesting point. I assume I'd use a sub-form to accomplish
entering this on a form, right?

Exactly. Makes displaying your picture much easier too - just put a Picture
control on the subform and update its picture property in the subform's
Current event.

John W. Vinson [MVP]

 

[Guest]

For now I'm going to just add the table two more times and will play with the
sub form idea to see if I can get it to work. I am having one strange
formatting issue that I can't overcome.

The query takes the number of yards needed from the furniture table and the
price of the fabric from the other. It multiplies them together and comes up
with a dollar amount. It works great for each of the three fabric equations.

The problem arrises when I have another field that adds the three equations
together. It adds them properly, but shows the total as a regular number, not
a currency. On the form, I have them listed together (furniture amount,
fabric total and grand total). Again, the math is fine, but it just looks
wrong.

I have two tables. I have a furniture table [Item number] and a fabric
table[FabricSpecs]. The furniture table has three fabric fields [Item
number.FabricSpec] [FabricSpec2] [FabricSpec3] (yes, the names are
confusingly similar). Each of the fabric fields in the [Item number] table
looks up it's data based on the [FabricSpecs] table. (For example, there may
be five different fabrics defined in a table. A chair might have three of
these fabrics on it. A couch might have two other fabrics. The chair and the
couch might have one of the fabrics in common)

If you're committed to this design decision - and Jamie's suggestions about
normalization are well taken! - what you can do is create a Query by adding
[Item number] to the query grid, and then adding [Fabric Specs] *THREE TIMES*.
Access will alias them as [Fabric Specs], [Fabric Specs1] and [Fabric Specs2].
Join each of these tables to the corresponding FabricSpec field in your table.
This will give you all three specs individually, each associated with the
correct fabric.

A better design would be to recognize that each Item has many Fabrics, and
each Fabric may be used in many Items; and properly model this many to many
relationship with a new table, ItemFabrics, with an ItemID and a FabricSpec
field. If an item requires three fabrics, this table would have three records;
if it requires four, you can just add a fourth record (rather than completely
redesigning your table, your queries, your forms and your reports, as you'll
need to do with your non-normalized design).

John W. Vinson [MVP]

 

[John W. Vinson]

The problem arrises when I have another field that adds the three equations
together. It adds them properly, but shows the total as a regular number, not
a currency. On the form, I have them listed together (furniture amount,
fabric total and grand total). Again, the math is fine, but it just looks
wrong.

Set the Format property of the form textbox containing this sum to Currency.
It's calculating a number; the display should be done on the form or report,
not in the calculation.

John W. Vinson [MVP]

 

[Guest]

When I go to the Format property drop down box it's blank. I've tried typing
Currency, but it doesn't work. I tried forcing 2 decimal places, but it still
isn't showing up with two decimal places.

Lila

 

[Guest]

I just tried creating an unbound box, changing it to Currency and then
binding it, but it still shows up not formatted as currency.