what is the best way to do this...

[misschanda via AccessMonster.com]

hello,
i have this form, that has four possible experiments per record per project
number. Each experiment has a main resin and 3 additives or 4 ingredients,
therefore possible of 16 ingredients. conversely, each ingredient is
associated with a entered supplier, therefore 16 suppliers,

I am trying to design the query so that when a a certain supplier is searched
throughout table

i will be able to formulate a report as follows:

supplier name

materials from particular supplier (from any ingredients)

project number each material is linked to.

Futhermore, if that ingredient was used as a resin a check will appear under
column main resin or etc.

how do you advise this.

currently i have;
SELECT TBLLABLINE.[Trial Number], TBLLABLINE.[Project Number], TBLLABLINE.
SupplierB, TBLLABLINE.SupplierC, TBLLABLINE.SupplierD, TBLLABLINE.SupplierA,
*
FROM TBLLABLINE
WHERE (((TBLLABLINE.SupplierA) Like "*" & [forms]![lablineextrusion1]!
[text515] & "*")) OR (((TBLLABLINE.SupplierB) Like "*" & [forms]!
[lablineextrusion1]![text515] & "*")) OR (((TBLLABLINE.SupplierC) Like "*" &
[forms]![lablineextrusion1]![text515] & "*")) OR (((TBLLABLINE.SupplierD)
Like "*" & [forms]![lablineextrusion1]![text515] & "*"));

thanks \
la

 

[Michel Walsh]

A relational database is based on relation between tables, take note that we
use the plural: tables. It won't be much 'relation' if there was just
one table, after all (even if it is possible to be in relation with
ourselves )




Have a table for Suppliers (supplierID*, supplierName, ... ). * denotes the
primary key candidate.
Have a table for Ingredients (ingredientID*, ingredientName, ... ).
Have a junction table between Suppliers and Ingredients:
SuppliersIngredients (supplierID*, ingredientID*)
which has one row for each possible association between a supplierID and
an ingredientID.
Have a junction table between Ingredients and Experiments:
ExperimentsIngredients ( experimentID*, ingredientID*, amountOfIngredient,
.... )
which has one row for each possible association between an experimentID
and an ingredientID

I fail to see what additives do in the picture ( for THIS particular
problem).


Make a query that will join ExperimentsIngredients with
SuppliersIngredients and with the basic tables, such as Ingredients and
Suppliers, as required.


Make a report based on the previous *query*. Use the wizard. Group, in the
report wizard, over the supplier, and over the ingredient. Have the
experimentation as detail. Have the other fields from ExperimentsIngredients
in the details part, as it fits.




Hoping it may help,
Vanderghast, Access MVP

 

[KARL DEWEY]

You are first talking about ' main resin and 3 additives or 4 ingredients'
and then suppliers. Your query has nothing about ingredients.

What is your table structure? From the query it looks like a spreadsheet
with [Trial Number], [Project Number], SupplierA, SupplierB, SupplierC, and
SupplierD.

 

[misschanda via AccessMonster.com]

Michael, Is there a way to not show the ingrediants that don't have the
supplier entered by the user?
I tried to re-do the tables and created more confusion for my self. Within
the record on the form, there are many ingrediant/supplier entered, I just
want a way to "connect each ingrediant with a supplier" so if a certain
supplier is not entered, than that ingrediant will not show. Currently, All
ingrediants are showing no matter what supplier is entered.

Is there not a delete field statement or validation statement i could insert.

thanks,
misschanda

A relational database is based on relation between tables, take note that we
use the plural: tables. It won't be much 'relation' if there was just
one table, after all (even if it is possible to be in relation with
ourselves )

Have a table for Suppliers (supplierID*, supplierName, ... ). * denotes the
primary key candidate.
Have a table for Ingredients (ingredientID*, ingredientName, ... ).
Have a junction table between Suppliers and Ingredients:
SuppliersIngredients (supplierID*, ingredientID*)
which has one row for each possible association between a supplierID and
an ingredientID.
Have a junction table between Ingredients and Experiments:
ExperimentsIngredients ( experimentID*, ingredientID*, amountOfIngredient,
... )
which has one row for each possible association between an experimentID
and an ingredientID

I fail to see what additives do in the picture ( for THIS particular
problem).

Make a query that will join ExperimentsIngredients with
SuppliersIngredients and with the basic tables, such as Ingredients and
Suppliers, as required.

Make a report based on the previous *query*. Use the wizard. Group, in the
report wizard, over the supplier, and over the ingredient. Have the
experimentation as detail. Have the other fields from ExperimentsIngredients
in the details part, as it fits.

Hoping it may help,
Vanderghast, Access MVP

hello,
i have this form, that has four possible experiments per record per

[quoted text clipped - 36 lines]

thanks \
la

 

[misschanda via AccessMonster.com]

what exactly do you mean spreadsheet???

the table's field's are: resina, resinb, resinc, resind, supplierresina,
supplierresinb, supplierresinc, supplierresind, additive1a, additive1b,
additive1c, additive1d, supplieradditive1a, supplieraddtive1b,
supplieradditive1c, supplieradditive1d, additive2a, additive2b, addtive2c,
additive2d, additive3a, additive3b, additive3c, additive3d, supplieraddtive3a,
supplieradditive3b, supplieradditive3c, supplieradditive3d, supplieraddtive2a,
supplieraddtive2b, supplieradditive2c, supplieradditive2d.

in the earlier post i combined the resin and additive and called them
ingrediants for simplicity.

In addition there are percentage used, and temperature, associated with each
resin/supplier or additive/supplier.

thanks
misschanda

You are first talking about ' main resin and 3 additives or 4 ingredients'
and then suppliers. Your query has nothing about ingredients.

What is your table structure? From the query it looks like a spreadsheet
with [Trial Number], [Project Number], SupplierA, SupplierB, SupplierC, and
SupplierD.

hello,
i have this form, that has four possible experiments per record per project

[quoted text clipped - 31 lines]

thanks \
la

 

[KARL DEWEY]

Re-read what Michel Walsh wrote about relational tables.
Relational table lay out like this --
Items
Nut
Pecan
Walnut
Peanut
Fruit
Apple
Gala
Macintosh
Granny Smith
Peach
Orange

Item -
Type -
Variety-
These table are related one-to-many instead of spreadsheet fashion.
You should have this layout --
Supplier - name, address, phone, etc.
Resin - trade name, chemical name, quanity, strenght, etc.
Additive - ...
--
KARL DEWEY
Build a little - Test a little

what exactly do you mean spreadsheet???

the table's field's are: resina, resinb, resinc, resind, supplierresina,
supplierresinb, supplierresinc, supplierresind, additive1a, additive1b,
additive1c, additive1d, supplieradditive1a, supplieraddtive1b,
supplieradditive1c, supplieradditive1d, additive2a, additive2b, addtive2c,
additive2d, additive3a, additive3b, additive3c, additive3d, supplieraddtive3a,
supplieradditive3b, supplieradditive3c, supplieradditive3d, supplieraddtive2a,
supplieraddtive2b, supplieradditive2c, supplieradditive2d.

in the earlier post i combined the resin and additive and called them
ingrediants for simplicity.

In addition there are percentage used, and temperature, associated with each
resin/supplier or additive/supplier.

thanks
misschanda

You are first talking about ' main resin and 3 additives or 4 ingredients'
and then suppliers. Your query has nothing about ingredients.

What is your table structure? From the query it looks like a spreadsheet
with [Trial Number], [Project Number], SupplierA, SupplierB, SupplierC, and
SupplierD.

hello,
i have this form, that has four possible experiments per record per project

[quoted text clipped - 31 lines]

thanks \
la

 

[Michel Walsh]

If you use multiple tables and join then with inner join, then an ingredient
without supplier won't be picked up by the inner join (since there will be
no match for the supplier for this ingredient). There is nothing out of the
ordinary to do, under such a tables design.


Vanderghast, Access MVP

Michael, Is there a way to not show the ingrediants that don't have the
supplier entered by the user?
I tried to re-do the tables and created more confusion for my self. Within
the record on the form, there are many ingrediant/supplier entered, I just
want a way to "connect each ingrediant with a supplier" so if a certain
supplier is not entered, than that ingrediant will not show. Currently,
All
ingrediants are showing no matter what supplier is entered.

Is there not a delete field statement or validation statement i could
insert.

thanks,
misschanda

A relational database is based on relation between tables, take note that
we
use the plural: tables. It won't be much 'relation' if there was
just
one table, after all (even if it is possible to be in relation with
ourselves )

Have a table for Suppliers (supplierID*, supplierName, ... ). * denotes
the
primary key candidate.
Have a table for Ingredients (ingredientID*, ingredientName, ... ).
Have a junction table between Suppliers and Ingredients:
SuppliersIngredients (supplierID*, ingredientID*)
which has one row for each possible association between a supplierID
and
an ingredientID.
Have a junction table between Ingredients and Experiments:
ExperimentsIngredients ( experimentID*, ingredientID*, amountOfIngredient,
... )
which has one row for each possible association between an
experimentID
and an ingredientID

I fail to see what additives do in the picture ( for THIS particular
problem).

Make a query that will join ExperimentsIngredients with
SuppliersIngredients and with the basic tables, such as Ingredients and
Suppliers, as required.

Make a report based on the previous *query*. Use the wizard. Group, in the
report wizard, over the supplier, and over the ingredient. Have the
experimentation as detail. Have the other fields from
ExperimentsIngredients
in the details part, as it fits.

Hoping it may help,
Vanderghast, Access MVP

hello,
i have this form, that has four possible experiments per record per

[quoted text clipped - 36 lines]

thanks \
la