Help to modify query I didn't write

[MarcieFess]

I need help modifying a query I didn't write...or, if it's better that I write a query using the results of this query, I'm open to that.

I have a query:

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 believe the SP and P are aliases that the person who wrote the query created because I mistakenly used spaces in the field names when I created the tables.

This is a simple query that returns the Quantity on Hand for each product in the Store Products table.

I have to run a report (it's already created) that will list each product individually, sorted by aisle number in the store.

For another report (this has to be printed by store, and an individual store must be able to be reported on demand...maybe that's another question), I have to total the QOH for each Hazard Class.

Here's what I have so far...and it's not running. I get an error "You tried to execut a query that does not include the specified expression 'Store Name' as part of an aggregate function."

SELECT StoreProductsQuery.[Store Name], [Hazard Class].[Hazard Class], QuantityOnHandQuery.QOH, Sum([QuantityOnHandQuery].[QOH]) AS SumQOH
FROM QuantityOnHandQuery, [Hazard Class], StoreProductsQuery, HMIRFReportQuery
WHERE (((StoreProductsQuery.[Store Name])="Riverpark") AND (([Hazard Class].[Hazard Class])="COMBUSTIBLE LIQUIDS II"));

Basically I need to be able to total on each Hazard Class...I created this query specifically for Combustible Liquids II but if I can get 1 query to do it all so they don't have to run 100 different queries, that would be great. I also tried to get the query to run just for the Riverpark store, but the fewer changes this company has to make to be able to run the required reports, the better.

Someone please help???? :banghead:

 

[sxschech]

Hi Marcie,

Looks like you are missing the GROUP BY clause.

SELECT StoreProductsQuery.[Store Name], [Hazard Class].[Hazard Class],  [COLOR=DarkOliveGreen][COLOR=Blue][I]QuantityOnHandQuery.QOH[/I][/COLOR],[/COLOR] Sum([QuantityOnHandQuery].[QOH]) AS SumQOH
FROM QuantityOnHandQuery, [Hazard Class], StoreProductsQuery, HMIRFReportQuery
WHERE (((StoreProductsQuery.[Store Name])="Riverpark") AND (([Hazard Class].[Hazard Class])="COMBUSTIBLE LIQUIDS II"))
[B][COLOR=Red]GROUP BY StoreProductsQuery.[Store Name], [Hazard Class].[Hazard Class][/COLOR][/B];

Since you are doing a sum, I would remove the first QuantityOnHandQuery.QOH and add the part in red to your sql statement and see how it goes. There may be other issues, but that stands out in the query you provided.

 

[MarcieFess]

Well the query has now been running for 15 minutes and I can't break into it. Access is unavailable to me. Any suggestions?

 

[MarcieFess]

It's supposedly waiting for a response from me...but if it's asked me something it's hidden behind the Access window which I'm trying to minimize but can't.

 

[MSAccessRookie]

I have some additional questions that may resolve your runtime issues.

[COLOR=black]SELECT StoreProductsQuery.[Store Name], [Hazard Class].[Hazard Class],  [I]QuantityOnHandQuery.QOH[/I], Sum([QuantityOnHandQuery].[QOH]) AS SumQOH[/COLOR]
[B][COLOR=seagreen]FROM QuantityOnHandQuery, [Hazard Class], StoreProductsQuery, [/COLOR][COLOR=red]HMIRFReportQuery[/COLOR][/B]
[COLOR=black]WHERE (((StoreProductsQuery.[Store Name])="Riverpark") AND (([Hazard Class].[Hazard Class])="COMBUSTIBLE LIQUIDS II"))[/COLOR]
[COLOR=black]GROUP BY StoreProductsQuery.[Store Name], [Hazard Class].[Hazard Class];[/COLOR]

Your FROM Statement appears to have at least two problems:

1. Your Query does not indicate how the Tables are related to each other. Because it does not, the Query will compare every Row of Every Table with Every Row of each of the other Tables. The larger the total number of Rows, the longer this will take. The standard way to fix this issue would be to creat JOIN Statements that define the relationships, but it is also possible to add conditions to the WHERE Statements.
2. There is a Table being Linked (HMIRFReportQuery) that is never being considered either as an element to Select, or a a Condition to consider. If you do not need it, remove it from the Query. If you do, Update the Query to define its need.

Try these two suggestions and get back to us if you still have issues.

 

[MarcieFess]

I'm not sure where to join them.

QuantityOnHandQuery contains 2 fields...the Store Product Key is the Access-produced unique identifier that indicates which product in which store, and the QOH which is a calculated field, taking the number in the package multiplied by the number of cases and the size to get the total volume or weight, then dividing it by the appropriate conversion rate to convert to pounds or gallons.

Hazard Class contains Hazard Key, an autonumber field, Type which indicates the type of hazard and which is probably unnecessary completely in the entire database, Hazard Class which is the name of the hazard classification (i.e. Combustible Liquids II), which is what I need a total QOH for, and then several other fields containing limit amounts that will be used in another query.

StoreProductsQuery contains UPC, which is the UPC number of each product carried by the company, Store Name which is the name of the individual locations, ProductName which is the name of the product, Cases which is the total number of cases of that product in that particular store, and a few more fields that aren't used in this query. The query isn't really doing anything, just pulling pieces of information from several tables.

I must be missing a piece here.

I need a total QOH for each Hazard Class...listed by store name.

The QOH is coming from the QuantityOnHandQuery; the Hazard Class is coming from the Hazard Class Table, and the Store Name is coming from the StoreProductsQuery. I could probably just as easily get the Store Name from the Store Products table instead of the query...I created the query in hopes that it would make the product input form easier to use (something's broken there too).

All of that information may need to actually be stored in a separate table, as each store has its own set of reports that have to be printed quarterly.

 

[MarcieFess]

OK I'm trying to come at this from another position.

I have designed a different Query.

SELECT [Store Information].[Store Name], [Store Department].[Aisle Number], [Hazard Class].[Hazard Class], Product.ProductName, Product.Chemical, Product.ChemicalAbstract, Product.PhysicalState, QuantityOnHandQuery.QOH, [Store Products].[Annual Waste]
FROM (([Store Information] INNER JOIN [Store Department] ON [Store Information].[Store Key] = [Store Department].[Store Key]) INNER JOIN (([Hazard Class] INNER JOIN Product ON [Hazard Class].[Hazard Key] = Product.[HazardKey]) INNER JOIN [Store Products] ON Product.[UPC] = [Store Products].[UPC]) ON ([Store Department].[Department Key] = Product.[DepartmentName]) AND ([Store Information].[Store Key] = [Store Products].[Store Key])) INNER JOIN QuantityOnHandQuery ON [Store Products].[Store Product Key] = QuantityOnHandQuery.[Store Product Key]
ORDER BY [Store Information].[Store Name], [Store Department].[Aisle Number], [Hazard Class].[Hazard Class], Product.ProductName;

This query results in a list of the individual products with their Hazard Class and QOH (which is all I'm concerned with for the time being...it's also giving me other information that is used in the report I've already completed successfully). It's supposed to be By Store Name...but it's only returning results for 1 of the stores, even though I have 2 stores completely finished (someone else is doing the rest...we'll end up with about 50 stores).

Not sure why I'm only seeing results from 1 store.

I would like to limit the results of the QOH to 4 decimals.

I believe this query has all the information I need...I need Store Name, Hazard Class, and QOH (a calculated field using a query).

Once I solve the issue of only getting 1 store reported, I think I can use this query to create a Crosstab Query that will give me the results I need.

 

[MarcieFess]

Because my issues in the past have been linked to the type of JOIN I have, I've looked at this piece of code and the fields.


SELECT [Store Information].[Store Name], [Store Department].[Aisle Number], [Hazard Class].[Hazard Class], Product.ProductName, Product.Chemical, Product.ChemicalAbstract, Product.PhysicalState, QuantityOnHandQuery.QOH, [Store Products].[Annual Waste]

FROM (([Store Information] INNER JOIN [Store Department] ON [Store Information].[Store Key] = [Store Department].[Store Key]) INNER JOIN (([Hazard Class] INNER JOIN Product ON [Hazard Class].[Hazard Key] = Product.[HazardKey]) INNER JOIN [Store Products] ON Product.[UPC] = [Store Products].[UPC]) ON ([Store Department].[Department Key] = Product.[DepartmentName]) AND ([Store Information].[Store Key] = [Store Products].[Store Key])) INNER JOIN QuantityOnHandQuery ON [Store Products].[Store Product Key] = QuantityOnHandQuery.[Store Product Key]
ORDER BY [Store Information].[Store Name], [Store Department].[Aisle Number], [Hazard Class].[Hazard Class], Product.ProductName;

The blue fields are the only ones that are on the MANY side of the joins.

Could this be the issue?

 

[MarcieFess]

Is something about this query causing it to only pull from unique UPCs? Could that be causing it to leave out what it considers to be "duplicate" UPCs from different stores?