Issues with aggreagates again

[heart01]

Hi all I received answer previously regarding calculations that show on a form and then in a query. I have everything working on the form showing the calcs and I have used two queries off one another as previously suggest and entered the SQL details as previously suggested everything works excllent. I thought that I had covered everything however, I have to include another field in the table named "power cost per unit" which I have done and updated all reports and queries or so I thought. this field has to be calculated on the Grand Total divided by the QTY to get the cost per unit gst and freight incl. the other thing I have to try figure out is I need to figure out how to separately enter a mark up price (this markup price will be a dynamic figure). I have been round and round in circles and keep getting messages about not being able to something or rather about aggregegates and at times i have the syntax error. I know this is long as I post the two queries that the database works from however could someone please cast their trained and experienced eye over it and advise where I am going wrong? (I have highlighted the two areas below where I have entered the information of the field that I require.

PRODUCT TOTALS QUERY
SELECT [Products_QU_fou ndationnot in use].ID, [Products_QU_fou ndationnot in use].[Date Recvd], [Products_QU_fou ndationnot in use].[Invoice NO], [Products_QU_fou ndationnot in use].Suppliers, [Products_QU_fou ndationnot in use].[Product Name], [Products_QU_fou ndationnot in use].Type, [Products_QU_fou ndationnot in use].Size, [Products_QU_fou ndationnot in use].[Load Index], [Products_QU_fou ndationnot in use].Type, [Products_QU_fou ndationnot in use].[Supplier Cost Per Unit], [Products_QU_fou ndationnot in use].QTY, [Products_QU_fou ndationnot in use].GST, [Products_QU_fou ndationnot in use].[gst per item], [Products_QU_fou ndationnot in use].[Freight Cost], [Products_QU_fou ndationnot in use].[Freight Cost Per Item], Sum(([Supplier Cost Per Unit]*[QTY])+[GST]+[Freight Cost]) AS [GRAND TOTAL Freight plus GST incl], [Products_QU_fou ndationnot in use].Comments, [Products_QU_fou ndationnot in use].[Date Outgoing], [Products_QU_fou ndationnot in use].[Type Out], [Products_QU_fou ndationnot in use].[Qty Out], [Products_QU_fou ndationnot in use].Freight, [Products_QU_fou ndationnot in use].[Freight Send Cost], [Products_QU_fou ndationnot in use].[Remaining Stock], Sum(([GRAND TOTAL Freight plus GST incl]/[QTY]) AS [power cost per unit]FROM [Products_QU_fou ndationnot in use]
GROUP BY [Products_QU_fou ndationnot in use].ID, [Products_QU_fou ndationnot in use].[Date Recvd], [Products_QU_fou ndationnot in use].[Invoice NO], [Products_QU_fou ndationnot in use].Suppliers, [Products_QU_fou ndationnot in use].[Product Name], [Products_QU_fou ndationnot in use].Type, [Products_QU_fou ndationnot in use].Size, [Products_QU_fou ndationnot in use].[Load Index], [Products_QU_fou ndationnot in use].Type, [Products_QU_fou ndationnot in use].[Supplier Cost Per Unit], [Products_QU_fou ndationnot in use].QTY, [Products_QU_fou ndationnot in use].GST, [Products_QU_fou ndationnot in use].[gst per item], [Products_QU_fou ndationnot in use].[Freight Cost], [Products_QU_fou ndationnot in use].[Freight Cost Per Item], [Products_QU_fou ndationnot in use].Comments, [Products_QU_fou ndationnot in use].[Date Outgoing], [Products_QU_fou ndationnot in use].[Type Out], [Products_QU_fou ndationnot in use].[Qty Out], [Products_QU_fou ndationnot in use].Freight, [Products_QU_fou ndationnot in use].[Freight Send Cost], [Products_QU_fou ndationnot in use].[Remaining Stock], [Products_QU_fou ndationnot in use].[Freight Company], [Products_QU_fou ndationnot in use].[power cost per unit];

THEN IN THE OTHER QUERY NAMED Products_QU_fou ndationnot in use

SELECT Products.ID, Products.[Date Recvd], Products.[Invoice NO], Products.Suppli ers, Products.[Product Name], Products.Type, Products.Size, Products.[Load Index], Products.[Supplier Cost Per Unit], Products.QTY, Sum([Supplier Cost Per Unit]*[Qty]*0.1) AS GST, ([GST]/[Qty]) AS [gst per item], Products.[Freight Cost], Sum([Freight Cost]/[QTY]) AS [Freight Cost Per Item], Products.[GRAND TOTAL Freight plus GST incl], Products.[Freight Company], Products.Commen ts, Products.[Date Outgoing], Products.[Type Out], Products.[Qty Out], Products.Freigh t, Products.[Freight Send Cost], Sum([Qty]-[Qty Out]) AS [Remaining Stock], Products.[ power cost per unit]
FROM Products
GROUP BY Products.ID, Products.[Date Recvd], Products.[Invoice NO], Products.Suppli ers, Products.[Product Name], Products.Type, Products.Size, Products.[Load Index], Products.[Supplier Cost Per Unit], Products.QTY, Products.[Freight Cost], Products.[GRAND TOTAL Freight plus GST incl], Products.[Freight Company], Products.Commen ts, Products.[Date Outgoing], Products.[Type Out], Products.[Qty Out], Products.Freigh t, Products.[Freight Send Cost], Products.[GRAND TOTAL Freight plus GST incl], Products.[Freight Cost Per Item], Products.[power cost per unit];
many many many thanks

[colintis]

The GROUP BY in the 1st query may have error because [Products_QU_fou ndationnot in use].[power cost per unit] is not part of the selected field, you can try removing [Products_QU_fou ndationnot in use] or the whole thing and see if it works.

Also, in the select fields of your 2nd query, i saw there's a space after the [ in Products.[ power cost per unit] (if this is copied directly from your query). Try and see if it works.

Remember to use [CODE] tag with the codes :)

[heart01]

hello Colintis I tried your suggestion and I am still having issues...I have played around a little further and now I can see the calculations in the query but all of the other calc on the form have dissapeared (didnt touch any of these on the form) and are showing in the box the "#name?" .. still cannot figured it out my apologies

cheers
deb

[colintis]

hello Colintis I tried your suggestion and I am still having issues...I have played around a little further and now I can see the calculations in the query but all of the other calc on the form have dissapeared (didnt touch any of these on the form) and are showing in the box the "#name?" .. still cannot figured it out my apologies

cheers
deb

can you post the code that you wrote? maybe its variable type error or some code settings not correct.

[heart01]

can you post the code that you wrote? maybe its variable type error or some code settings not correct.

Yes thank you. I still have not complete understanding of how it all goes together so I appreciate the help...here is the codes for the two queries that have been written...
QUERY ONE

SELECT [Products_QU_foundationnot in use].ID, [Products_QU_foundationnot in use].[Date Recvd], [Products_QU_foundationnot in use].[Invoice NO], [Products_QU_foundationnot in use].Suppliers, [Products_QU_foundationnot in use].[Product Name], [Products_QU_foundationnot in use].Type, [Products_QU_foundationnot in use].Size, [Products_QU_foundationnot in use].[Load Index], [Products_QU_foundationnot in use].Type, [Products_QU_foundationnot in use].[Supplier Cost Per Unit], [Products_QU_foundationnot in use].QTY, [Products_QU_foundationnot in use].GST, [Products_QU_foundationnot in use].[gst per item], [Products_QU_foundationnot in use].[Freight Cost], [Products_QU_foundationnot in use].[Freight Cost Per Item], [GRAND TOTAL Freight plus GST incl]/[QTY] AS [Cost Per Unit], Sum(([Supplier Cost Per Unit]*[QTY])+[GST]+[Freight Cost]) AS [GRAND TOTAL Freight plus GST incl], [Products_QU_foundationnot in use].Comments, [Products_QU_foundationnot in use].[Date Outgoing], [Products_QU_foundationnot in use].[Type Out], [Products_QU_foundationnot in use].[Qty Out], [Products_QU_foundationnot in use].Freight, [Products_QU_foundationnot in use].[Freight Send Cost], [Products_QU_foundationnot in use].[Remaining Stock]
FROM [Products_QU_foundationnot in use]
GROUP BY [Products_QU_foundationnot in use].ID, [Products_QU_foundationnot in use].[Date Recvd], [Products_QU_foundationnot in use].[Invoice NO], [Products_QU_foundationnot in use].Suppliers, [Products_QU_foundationnot in use].[Product Name], [Products_QU_foundationnot in use].Type, [Products_QU_foundationnot in use].Size, [Products_QU_foundationnot in use].[Load Index], [Products_QU_foundationnot in use].Type, [Products_QU_foundationnot in use].[Supplier Cost Per Unit], [Products_QU_foundationnot in use].QTY, [Products_QU_foundationnot in use].GST, [Products_QU_foundationnot in use].[gst per item], [Products_QU_foundationnot in use].[Freight Cost], [Products_QU_foundationnot in use].[Freight Cost Per Item], [Products_QU_foundationnot in use].Comments, [Products_QU_foundationnot in use].[Date Outgoing], [Products_QU_foundationnot in use].[Type Out], [Products_QU_foundationnot in use].[Qty Out], [Products_QU_foundationnot in use].Freight, [Products_QU_foundationnot in use].[Freight Send Cost], [Products_QU_foundationnot in use].[Remaining Stock], [Products_QU_foundationnot in use].[Freight Company];

QUERY TWO (the highlight in bold is what I have added "cost per item" was prev as above power cost per unit)

SELECT Products.ID, Products.[Date Recvd], Products.[Invoice NO], Products.Suppliers, Products.[Product Name], Products.Type, Products.Size, Products.[Load Index], Products.[Supplier Cost Per Unit], Products.QTY, Sum([Supplier Cost Per Unit]*[Qty]*0.1) AS GST, ([GST]/[Qty]) AS [gst per item], Products.[Freight Cost], Sum([Freight Cost]/[QTY]) AS [Freight Cost Per Item], Products.[GRAND TOTAL Freight plus GST incl], Products.[Freight Company], Products.Comments, Products.[Date Outgoing], Products.[Type Out], Products.[Qty Out], Products.Freight, Products.[Freight Send Cost], Sum([Qty]-[Qty Out]) AS [Remaining Stock], Products.[Cost Per Unit], [B][GRAND TOTAL Freight plus GST incl]/[QTY][/B]FROM Products
GROUP BY Products.ID, Products.[Date Recvd], Products.[Invoice NO], Products.Suppliers, Products.[Product Name], Products.Type, Products.Size, Products.[Load Index], Products.[Supplier Cost Per Unit], Products.QTY, Products.[Freight Cost], Products.[GRAND TOTAL Freight plus GST incl], Products.[Freight Company], Products.Comments, Products.[Date Outgoing], Products.[Type Out], Products.[Qty Out], Products.Freight, Products.[Freight Send Cost], Products.[Freight Cost Per Item], [B]Products.[Cost Per Unit];[/B]

So as mentioned Now all my queries work but the calculations on the forms have now dissapeared and just now showing #name?,,,,,,,,, ,

[heart01]

Yes thank you. I still have not complete understanding of how it all goes together so I appreciate the help...here is the codes for the two queries that have been written...
QUERY ONE

SELECT [Products_QU_foundationnot in use].ID, [Products_QU_foundationnot in use].[Date Recvd], [Products_QU_foundationnot in use].[Invoice NO], [Products_QU_foundationnot in use].Suppliers, [Products_QU_foundationnot in use].[Product Name], [Products_QU_foundationnot in use].Type, [Products_QU_foundationnot in use].Size, [Products_QU_foundationnot in use].[Load Index], [Products_QU_foundationnot in use].Type, [Products_QU_foundationnot in use].[Supplier Cost Per Unit], [Products_QU_foundationnot in use].QTY, [Products_QU_foundationnot in use].GST, [Products_QU_foundationnot in use].[gst per item], [Products_QU_foundationnot in use].[Freight Cost], [Products_QU_foundationnot in use].[Freight Cost Per Item], [GRAND TOTAL Freight plus GST incl]/[QTY] AS [Cost Per Unit], Sum(([Supplier Cost Per Unit]*[QTY])+[GST]+[Freight Cost]) AS [GRAND TOTAL Freight plus GST incl], [Products_QU_foundationnot in use].Comments, [Products_QU_foundationnot in use].[Date Outgoing], [Products_QU_foundationnot in use].[Type Out], [Products_QU_foundationnot in use].[Qty Out], [Products_QU_foundationnot in use].Freight, [Products_QU_foundationnot in use].[Freight Send Cost], [Products_QU_foundationnot in use].[Remaining Stock]
FROM [Products_QU_foundationnot in use]
GROUP BY [Products_QU_foundationnot in use].ID, [Products_QU_foundationnot in use].[Date Recvd], [Products_QU_foundationnot in use].[Invoice NO], [Products_QU_foundationnot in use].Suppliers, [Products_QU_foundationnot in use].[Product Name], [Products_QU_foundationnot in use].Type, [Products_QU_foundationnot in use].Size, [Products_QU_foundationnot in use].[Load Index], [Products_QU_foundationnot in use].Type, [Products_QU_foundationnot in use].[Supplier Cost Per Unit], [Products_QU_foundationnot in use].QTY, [Products_QU_foundationnot in use].GST, [Products_QU_foundationnot in use].[gst per item], [Products_QU_foundationnot in use].[Freight Cost], [Products_QU_foundationnot in use].[Freight Cost Per Item], [Products_QU_foundationnot in use].Comments, [Products_QU_foundationnot in use].[Date Outgoing], [Products_QU_foundationnot in use].[Type Out], [Products_QU_foundationnot in use].[Qty Out], [Products_QU_foundationnot in use].Freight, [Products_QU_foundationnot in use].[Freight Send Cost], [Products_QU_foundationnot in use].[Remaining Stock], [Products_QU_foundationnot in use].[Freight Company];

QUERY TWO (the highlight in bold is what I have added "cost per item" was prev as above power cost per unit)

SELECT Products.ID, Products.[Date Recvd], Products.[Invoice NO], Products.Suppliers, Products.[Product Name], Products.Type, Products.Size, Products.[Load Index], Products.[Supplier Cost Per Unit], Products.QTY, Sum([Supplier Cost Per Unit]*[Qty]*0.1) AS GST, ([GST]/[Qty]) AS [gst per item], Products.[Freight Cost], Sum([Freight Cost]/[QTY]) AS [Freight Cost Per Item], Products.[GRAND TOTAL Freight plus GST incl], Products.[Freight Company], Products.Comments, Products.[Date Outgoing], Products.[Type Out], Products.[Qty Out], Products.Freight, Products.[Freight Send Cost], Sum([Qty]-[Qty Out]) AS [Remaining Stock], Products.[Cost Per Unit], [B][GRAND TOTAL Freight plus GST incl]/[QTY][/B]FROM Products
GROUP BY Products.ID, Products.[Date Recvd], Products.[Invoice NO], Products.Suppliers, Products.[Product Name], Products.Type, Products.Size, Products.[Load Index], Products.[Supplier Cost Per Unit], Products.QTY, Products.[Freight Cost], Products.[GRAND TOTAL Freight plus GST incl], Products.[Freight Company], Products.Comments, Products.[Date Outgoing], Products.[Type Out], Products.[Qty Out], Products.Freight, Products.[Freight Send Cost], Products.[Freight Cost Per Item], [B]Products.[Cost Per Unit];[/B]

So as mentioned Now all my queries work but the calculations on the forms have now dissapeared and just now showing #name?,,,,,,,,, ,

to hard basket?

[heart01]

Well it appears that all it working now. I have no idea what I did to get it working however, all calculations are now visible on the form and both queries that I have to use to have the calcs working on the form and the queries for the reports see to be working ... Ill just go back into the database another time just to make sure...thanking you for all help ...kind regards deb