MarcieFess
Registered User.
- Local time
- Today, 11:07
- Joined
- Oct 25, 2012
- Messages
- 107
I think my head is beginning to spin and I am beginning to think in circles about queries.
Is it better to create queries to display information in forms and reports (forms to update information, reports to print it out), than to just have the forms and reports get the information themselves?
I have a Quantity On Hand query that only uses 2 fields...
SELECT SP.[Store Product Key], (([P].[Package]*[P].[Size]*[SP].[Cases])/[P].[ConversionRate]) AS QOH
FROM Product AS P INNER JOIN [Store Products] AS SP ON P.UPC = SP.UPC;
I will need to print reports based on this QOH query...several different reports, putting the information out in several different ways.
One report must take the QOH (for an individual store, not in total) for a particular Hazard Class such as Combustible Liquids II, compare the QOH to parameters for each hazard class, and check either a Yes or No box in 2 different places (based on 2 different sets of parameters) in the report. It must be able to do this for each individual store site.
For example: Let's use the Combustible Liquids II hazard class. The query has to total the QOH for all items listed as this hazard class. It must then look at the Storage type for this hazard class...if the QOH for all CLII products stored INSIDE is >25, then it must check the YES box in a column on the report; otherwise it would be NO. It must do the same for all items stored OUTSIDE, only it's >60 for items stored outside. If either one of these events is true, it has to check the YES box.
The second part of this is that the query must also check the USE (which can be OPEN or CLOSED) and compare it to variables for those 2 situations...if the amount for closed >120 or if it's >30 for open...then the YES box must be checked in another column of the form.
This holds true for every hazard class of products in a particular store...and each hazard class has its own set of parameters.
Right now I have the parameters loosely defined in the Hazard Class table. I didn't want to spend too much time on that in case it's the wrong way to go.
Am I biting off something too big to chew? Is a query like this possible for an amateur like me? There are about 60 different hazard classes, all on 1 report.
I've begun the report design...just typing all of these parameters etc. into text boxes because that doesn't change from store to store...the only thing the report has to generate for each store is the YES or NO boxes being checked.
Marcie, the newbie
Access 2010
Is it better to create queries to display information in forms and reports (forms to update information, reports to print it out), than to just have the forms and reports get the information themselves?
I have a Quantity On Hand query that only uses 2 fields...
SELECT SP.[Store Product Key], (([P].[Package]*[P].[Size]*[SP].[Cases])/[P].[ConversionRate]) AS QOH
FROM Product AS P INNER JOIN [Store Products] AS SP ON P.UPC = SP.UPC;
I will need to print reports based on this QOH query...several different reports, putting the information out in several different ways.
One report must take the QOH (for an individual store, not in total) for a particular Hazard Class such as Combustible Liquids II, compare the QOH to parameters for each hazard class, and check either a Yes or No box in 2 different places (based on 2 different sets of parameters) in the report. It must be able to do this for each individual store site.
For example: Let's use the Combustible Liquids II hazard class. The query has to total the QOH for all items listed as this hazard class. It must then look at the Storage type for this hazard class...if the QOH for all CLII products stored INSIDE is >25, then it must check the YES box in a column on the report; otherwise it would be NO. It must do the same for all items stored OUTSIDE, only it's >60 for items stored outside. If either one of these events is true, it has to check the YES box.
The second part of this is that the query must also check the USE (which can be OPEN or CLOSED) and compare it to variables for those 2 situations...if the amount for closed >120 or if it's >30 for open...then the YES box must be checked in another column of the form.
This holds true for every hazard class of products in a particular store...and each hazard class has its own set of parameters.
Right now I have the parameters loosely defined in the Hazard Class table. I didn't want to spend too much time on that in case it's the wrong way to go.
Am I biting off something too big to chew? Is a query like this possible for an amateur like me? There are about 60 different hazard classes, all on 1 report.
I've begun the report design...just typing all of these parameters etc. into text boxes because that doesn't change from store to store...the only thing the report has to generate for each store is the YES or NO boxes being checked.
Marcie, the newbie
Access 2010
