report that changes according to user input

[Gilberto]

Hello,

I have a list of products with two properties (fields) which are: LEVEL and VARIANT. Each property has 7 possibilities:

LEVEL can be:
Level1
Level2
Level3...
Lavel7

And VARIANT:
V1
V2
V3...
V7

I have created reports which display product belonging to every possible situation:
ie. products belonging to LEVEL1 AND V1, products belonging to LEVEL2 AND V1....in this way i have created 49 DIFFERENT reports based on 49 different queries.

I was wondering if there is a way to create only ONE query and ONE report which will ask the user for the combination of properties desired (which level and which variant) so that the report displays those products.

The main issue is to avoid having to CREATE every single different query AND report with very simple differences.

Thanks,
Gilberto

[nico5038]

Hello,

I have a list of products with two properties (fields) which are: LEVEL and VARIANT. Each property has 7 possibilities:

LEVEL can be:
Level1
Level2
Level3...
Lavel7

And VARIANT:
V1
V2
V3...
V7

I have created reports which display product belonging to every possible situation:
ie. products belonging to LEVEL1 AND V1, products belonging to LEVEL2 AND V1....in this way i have created 49 DIFFERENT reports based on 49 different queries.

I was wondering if there is a way to create only ONE query and ONE report which will ask the user for the combination of properties desired (which level and which variant) so that the report displays those products.

The main issue is to avoid having to CREATE every single different query AND report with very simple differences.

Thanks,
Gilberto

Here you can use the SQL's Cartesian product.
First create a query named "qryLevel" with ProductID and LEVEL
Then create a query named "qryVariant" with ProductID and Variant
Next create a query named "qryAll" like:

select * from qryLevel, qryVariant WHERE qryLevel.ProductID = qryVariant.ProductID

This will give all combinations for every ProductID.

Finally you could create a new query and add in the WHERE clause for the LEVEL and/or Variant an IN() clause like

select * from qryAll where Level in ('Level1','Level3');

Getting the idea ?

Nc;o)

[Gilberto]

Here you can use the SQL's Cartesian product.
First create a query named "qryLevel" with ProductID and LEVEL
Then create a query named "qryVariant" with ProductID and Variant
Next create a query named "qryAll" like:

select * from qryLevel, qryVariant WHERE qryLevel.ProductID = qryVariant.ProductID

This will give all combinations for every ProductID.

Finally you could create a new query and add in the WHERE clause for the LEVEL and/or Variant an IN() clause like

select * from qryAll where Level in ('Level1','Level3');

Getting the idea ?

Nc;o)

Thanks Nico, i believe this is exactly what i need.

However i forgot to mention that every product can belong to several variables and/or levels...i dont know if thats a problem.

I already created:
qrylevel:

SELECT Engineering.[Product Name French], Engineering.Level1, Engineering.Level2, Engineering.Level3, Engineering.Level4, Engineering.Level5, Engineering.Level6, Engineering.Level7
FROM Engineering; 

qryvariant: [code]
SELECT Engineering.[Product Name French], Engineering.Variant1, Engineering.Variant2, Engineering.Variant3, Engineering.Variant4, Engineering.Variant5, Engineering.Variant6, Engineering.Variant7
FROM Engineering;

qryall:

SELECT *
FROM qryLevel, qryVariant
WHERE (((qryLevel.[Product Name French])=[qryVariant].[product name french]));

I THINK this is going well so far now when i run the QRYALL i get something like this:
product name french-------level1-----level2...-----product name french----variant1---variant2...
with the name of the product and either "1" or "0" under the level or variant they belong to.

i just have NO idea how to create the last query so that the user could find the products belonging to "x" level WITH "y" variant.

Thanks.

[nico5038]

Oops, your table setup changed from "column levels" like:
L1
L2
L3
into rows like:
Engineering.Level1, Engineering.Level2, Engineering.Level3, .....

The use of levels in one table this way is considered "not normalized" and shouldn't be done as it gives the selection trouble you're in now.

Your engineering table should look like:
[Product Name French], Level

For each level a row needs to be present.
Same goes for the Variants.

Having that setup will make my query setup work and will allow to filter every level and variant combination by filtering the field with the needed value(s).

Can you redesign your tables ?

Nic;o)

[Gilberto]

Oops, your table setup changed from "column levels" like:
L1
L2
L3
into rows like:
Engineering.Level1, Engineering.Level2, Engineering.Level3, .....

The use of levels in one table this way is considered "not normalized" and shouldn't be done as it gives the selection trouble you're in now.

Your engineering table should look like:
[Product Name French], Level

For each level a row needs to be present.
Same goes for the Variants.

Having that setup will make my query setup work and will allow to filter every level and variant combination by filtering the field with the needed value(s).

Can you redesign your tables ?

Nic;o)

Hello Nico.

Indeed, i understand and have been reading about normalization before. However i dont understand how i can redesign my tables. How can i create rows for every product belonging to different levels and variants. Wouldnt that result in an engineering table with product names repeated a lot??? ie. product name "pencil" if belonging to level 1 AND level 2 AND level 3 AND VARIANT 1 and VARIANT 5 would appear in the table 5 times as i understand...would that the right thing??? and how should i then create the fields for table engineering representing each independet level (leel1, level2, level3...level7) and variant (variant1...variant7). how could that be done???

Thanks again.

Im still VERY new with access.

[Gilberto]

Hello Nico.

Indeed, i understand and have been reading about normalization before. However i dont understand how i can redesign my tables. How can i create rows for every product belonging to different levels and variants. Wouldnt that result in an engineering table with product names repeated a lot??? ie. product name "pencil" if belonging to level 1 AND level 2 AND level 3 AND VARIANT 1 and VARIANT 5 would appear in the table 5 times as i understand...would that the right thing??? and how should i then create the fields for table engineering representing each independet level (leel1, level2, level3...level7) and variant (variant1...variant7). how could that be done???

Thanks again.

Im still VERY new with access.

I already tried creating separate tables:
ENGINEERING
ProductNameFrench
Weight (just to show that there are more data entered for every product)

LEVEL with fields:
ProductNameFrench
Level

VARIABLE with:
ProductNameFrench
Variable

however the problem i have is that in the ENGINEERING FORM, the user selects ONE product, and for that product he selects the level and variable check boxes to which that product belongs. I DONT want the user to have to create a record for the same product every time he checks ONE box. So how can i do it so that after selecting ONE product the user can check as many boxes of variable1, variable2....variable7 or/and level1...level7 that product belongs to, so that in each table (variable and level) one independent record is created for every independent selection???

ie ENGINEERING TABLE
pencil----8kg

ie LEVEL TABLE
PENCIL----- level1
PENCIL----- level3
PENCIL------level7

ie VARIABLE TABLE
pencil---variable4
pencil---variable6

this is what i would need in order to normailze (i think) BUT i dont know how the user can do this WITHOUT having to select the product (pencil) 5 different times (each with every "option") in the FROM.


Thanks again.

[nico5038]

Hello Nico.

Indeed, i understand and have been reading about normalization before. However i dont understand how i can redesign my tables. How can i create rows for every product belonging to different levels and variants. Wouldnt that result in an engineering table with product names repeated a lot??? ie. product name "pencil" if belonging to level 1 AND level 2 AND level 3 AND VARIANT 1 and VARIANT 5 would appear in the table 5 times as i understand...would that the right thing??? and how should i then create the fields for table engineering representing each independet level (leel1, level2, level3...level7) and variant (variant1...variant7). how could that be done???

Thanks again.

Im still VERY new with access.

Normally you have a tblProduct with the properties of the product. When you need to record different Level/Variant combinations for the same product then an additional table is needed with as ForeignKey (FK) the ProductID (but you could also use the name).
When a product only belongs to one Level, then the Level field is placed in the tblProduct. When multiple Levels are needed then a new relationtable tblProductLevel is created holding the ProductID and the Level. This is a RelationTable because also a tblLevel is created to hold all available Levels.
Same as for tblProductLevel and tblLevel goes for the Variants.
When there's a relation between the Levels and Variants (You always need both to be defined together), then you use one relation table to hold the combination.

Nic;o)

[Gilberto]

Normally you have a tblProduct with the properties of the product. When you need to record different Level/Variant combinations for the same product then an additional table is needed with as ForeignKey (FK) the ProductID (but you could also use the name).
When a product only belongs to one Level, then the Level field is placed in the tblProduct. When multiple Levels are needed then a new relationtable tblProductLevel is created holding the ProductID and the Level. This is a RelationTable because also a tblLevel is created to hold all available Levels.
Same as for tblProductLevel and tblLevel goes for the Variants.
When there's a relation between the Levels and Variants (You always need both to be defined together), then you use one relation table to hold the combination.

Nic;o)

Thanks Nico i need to read more about relation tables. My question is, at the end there will be a table with multiple fields like (Level1, level2...leve7) and "1" and "0" under each for every produc (just as i have my mail table today).
If not...then what will be the control source for evey checkbox (level1, level2, level3...level7)???

I just still cant see the solution very clear.

Thanks.

[nico5038]

You don't record checkboxes, but Level values like 1, 2 , 3
When you have a product X for Level 1, 3 and 5 you get in the relationtable just three rows:
X 1
X 3
X 5
Now you can issue a query to find "3" with a WHERE Level = "3" or "1" and "5" with WHERE Level IN (1 , 5)
Hope you can see now that you're now filtering on fieldcontent (1,3,5) instead of fieldnames.(Level1,Level3,Level5) an thus you can re-use the same query by changing the WHERE criteria and there's no need to create a new query when the level(s) needed change.

Nic;o)

[Gilberto]

You don't record checkboxes, but Level values like 1, 2 , 3
When you have a product X for Level 1, 3 and 5 you get in the relationtable just three rows:
X 1
X 3
X 5
Now you can issue a query to find "3" with a WHERE Level = "3" or "1" and "5" with WHERE Level IN (1 , 5)
Hope you can see now that you're now filtering on fieldcontent (1,3,5) instead of fieldnames.(Level1,Level3,Level5) an thus you can re-use the same query by changing the WHERE criteria and there's no need to create a new query when the level(s) needed change.

Nic;o)

Thanks Nico, i understand that now, my only doubt is how the user will specify that product X belnogs to level "3","1" and "5". I thought of check boxes cause its much faster, easier and less confusing for the user, but even with textboxes....do i need to hace 7 checkboxes for each level or can i have just one box where the user can enter SEVERAL levels ("3","1" and "5") i dont think that can be done??? Lastly how will the form record, in this case, 3 different records in the table? do i have to add some code????

Thanks

[nico5038]

A linked "relationtable" is normally displayed in a subform.
When you use the wizard Access will propose a field to link the form with. Just accept that field (will be the ProductCode) and Access will thus synchronize the subform to show only rows for that specific product.
When adding a new product, Access will fill in the produccode, thus only the level needed needs to be specified.

For me it's not clear what the relation between Level and Variant is. When they have a relation, then the relationtable needs to hold both. Can you elaborate on those fields ?

Nic;o)

[Gilberto]

A linked "relationtable" is normally displayed in a subform.
When you use the wizard Access will propose a field to link the form with. Just accept that field (will be the ProductCode) and Access will thus synchronize the subform to show only rows for that specific product.
When adding a new product, Access will fill in the produccode, thus only the level needed needs to be specified.

For me it's not clear what the relation between Level and Variant is. When they have a relation, then the relationtable needs to hold both. Can you elaborate on those fields ?

Nic;o)

Level and Variable are just two different types of categories for the products, beign Variant a higher caterogy. As an example, for automotive products i have Variant1="3 doors" and for THIS variant a product can belong to: Level1="Basic Car" level2="Enterprise car", level3="police car". Variant2="5 doors"...with same levels.
So a product, "simple headrest no LCD" would belong to variant1, level 1 AND level 3, but not to level2...not variant2 but indeed be present in variant4 AND variant5 with their corresponding levels.

I still dont understand how on the form the user will select the product only once and be able to check either boxes or enter text in textboxes for every level and variant for THAT product without having to create one record with the same product for every level and/or variant.

Thanks

[nico5038]

Level and Variable are just two different types of categories for the products, beign Variant a higher caterogy. As an example, for automotive products i have Variant1="3 doors" and for THIS variant a product can belong to: Level1="Basic Car" level2="Enterprise car", level3="police car". Variant2="5 doors"...with same levels.
So a product, "simple headrest no LCD" would belong to variant1, level 1 AND level 3, but not to level2...not variant2 but indeed be present in variant4 AND variant5 with their corresponding levels.

I still dont understand how on the form the user will select the product only once and be able to check either boxes or enter text in textboxes for every level and variant for THAT product without having to create one record with the same product for every level and/or variant.

Thanks

Getting clearer.
Basically we have two options:
1 - Record separate Level(s) and Variant(s) for each product
This will result in having to define little information (just the variants and levels), but won't allow you to record the situation that a Variant / Level specified isn't supported.
2 - Record the valid combinations of Level and Variant.
This will result in recording more information, but also allow any combination to be defined.

Sample:
1 - Assume Variant 1 till 3 and Level 1 till 3.
For a product having Variant 1 and 2 and Level 1 and 3 gives always all combinations and thus this crosstable:
L1 L2 L3
V1 x x
V2 x x
V3
2 - Basically the same crosstable will be possible, but instead of defining just the Variants and Levels, here we define the combination. Lets assume the combination V2 and L3 isn't supported, then we get the corsstable:
L1 L2 L3
V1 x x
V2 x
V3
This is a situation that can't be defined using method 1.

In both cases we'll record the Level and the Variant "outside" the tblProduct in a so-called "relation table" as we can have multiple combinations.
For situation 1 we can have a tblProductVariant and a tblProductLevel with two fields. Both will have the ProductID and the ID of the Variant / Level as the second field.
For situation 2 we'll have one table (tblProductLevelVariant) with the ProductID and both the LevelID and VariantID.

Both situations are "handled" by defining a subform (or subforms for situation 1) for the "relation table(s)". Using linked subforms will make filling the ProductID obsolete, as Access will fille them for automatically.

All you need to decide is wether we need the "fast" but "limited" situation 1 or the "slower" but "versatile" situation 2 for recording the data.

Nic;o)

[Gilberto]

Getting clearer.
Basically we have two options:
1 - Record separate Level(s) and Variant(s) for each product
This will result in having to define little information (just the variants and levels), but won't allow you to record the situation that a Variant / Level specified isn't supported.
2 - Record the valid combinations of Level and Variant.
This will result in recording more information, but also allow any combination to be defined.

Sample:
1 - Assume Variant 1 till 3 and Level 1 till 3.
For a product having Variant 1 and 2 and Level 1 and 3 gives always all combinations and thus this crosstable:
L1 L2 L3
V1 x x
V2 x x
V3
2 - Basically the same crosstable will be possible, but instead of defining just the Variants and Levels, here we define the combination. Lets assume the combination V2 and L3 isn't supported, then we get the corsstable:
L1 L2 L3
V1 x x
V2 x
V3
This is a situation that can't be defined using method 1.

In both cases we'll record the Level and the Variant "outside" the tblProduct in a so-called "relation table" as we can have multiple combinations.
For situation 1 we can have a tblProductVariant and a tblProductLevel with two fields. Both will have the ProductID and the ID of the Variant / Level as the second field.
For situation 2 we'll have one table (tblProductLevelVariant) with the ProductID and both the LevelID and VariantID.

Both situations are "handled" by defining a subform (or subforms for situation 1) for the "relation table(s)". Using linked subforms will make filling the ProductID obsolete, as Access will fille them for automatically.

All you need to decide is wether we need the "fast" but "limited" situation 1 or the "slower" but "versatile" situation 2 for recording the data.

Nic;o)

Thanks Nico, i definitely need OPTION 2. Can you explain a bit more...

[nico5038]

Little to explain, my comment "For situation 2 we'll have one table (tblProductLevelVariant) with the ProductID and both the LevelID and VariantID." already shows the solution table needed.
So besides creating a table tblVariant and tblLevel you need the tblProductLevelVariant holding all three primary keys.

Best to define for all three fields a "Lookup" (the second tab at the bottom when defining a table. Thus the user will get a combo to select the code.

Nic;o)