Total Query

[melonwand]

Let me start out by saying I'm not a "programmer". I know enough to cause trouble...



I have created a query which takes information from Sales Order, Parts & Pricing and Shipping Info tables. In the query, I total the pricing and qty and have created a "Subtotal" field which gives me the Order total. My problem is when I try to add the shipping cost in, it adds the shipping cost for each row that is related to the sales order. For example, I have 3 items in the sales order, but one shipping cost of $25. I totals the sales order properly, but adds the shipping 3 times. I'm about to pull my hair out. What am I doing wrong? Below is the query results and I have attached the table & query relationship information. I would appreciate any help!!!!



SalesOrderTotals qrySalesOrder#PartOrderedColorActualSFPriceSubTotalShipping ChargesTotal0000140504050-NABUK 4050 BITTERSWEET80.25$12.10$971.03$25.00$996.0300001BX7110BX7110-BOXLAND 7110 BRONZ27.75$8.05$223.39$25.00$248.3900001F2006F2006-FLORIDA 2006 BLUE BLACK155.5$10.40$1,617.20$25.00$1,642.20

[Robeen]

Any chance you can post your db here?

[melonwand]

Hi Robeen,

This is the DB. Any help would be greatly appreciated.

Thanks,
Dawn

[Robeen]

What is the name of the query that you are having trouble with?

I don't see a Query that takes information from:
SalesOrder
Parts & Pricing
Shipping Info

I have created a query which takes information from Sales Order, Parts & Pricing and Shipping Info tables.

The other thing - if I need to respond to a prompt - tell me what to put in.

[melonwand]

Its the SalesOrdersTotal qry. I removed the Sales order tbl. It will ask for sales order #. Enter 1

[Robeen]

Your problem is with your concept of how the Group By works in a query.

This is your SQL [View -> SQL View in Query Design Mode]:

 
SELECT DISTINCT [Shipping Info].[SalesOrder#], [SO Parts/Pricing].PartOrdered, [SO Parts/Pricing].Color, [SO Parts/Pricing].ActualSF, [SO Parts/Pricing].Price, [ActualSF]*[Price] AS SubTotal, [Shipping Info].[Shipping Charges], [ActualSF]*[Price]+[Shipping Charges] AS Total
FROM [Shipping Info] LEFT JOIN [SO Parts/Pricing] ON [Shipping Info].[SalesOrder#] = [SO Parts/Pricing].[SalesOrder#]
GROUP BY [Shipping Info].[SalesOrder#], [SO Parts/Pricing].PartOrdered, [SO Parts/Pricing].Color, [SO Parts/Pricing].ActualSF, [SO Parts/Pricing].Price, [ActualSF]*[Price], [Shipping Info].[Shipping Charges], [ActualSF]*[Price]+[Shipping Charges]
HAVING ((([Shipping Info].[SalesOrder#])=[Sales Order?]));

You'll notice it says:

GROUP BY [Shipping Info].[SalesOrder#], [SO Parts/Pricing].PartOrdered, [SO Parts/Pricing].Color, [SO Parts/Pricing].ActualSF, [SO Parts/Pricing].Price, [ActualSF]*[Price], [Shipping Info].[Shipping Charges], [ActualSF]*[Price]+[Shipping Charges]

This is where the problem resides.
You have three different 'PartOrdered' items.
You told Access to Group By '[SO Parts/Pricing].PartOrdered'.
Those three parts are different - so they HAVE to be on separate rows.
Once each of those is on a separate row - all the data for each of them is ALSO on a separate row.

So . . .
Open your Query.
Click View [Top - Left].
Click SQL View.

Paste the following into your SQL View window.

 
 
SELECT DISTINCT [Shipping Info].[SalesOrder#], Sum([SO Parts/Pricing].ActualSF) AS SumOfActualSF, Sum([SO Parts/Pricing].Price) AS SumOfPrice, Sum([ActualSF]*[Price]) AS SubTotal, Sum([Shipping Info].[Shipping Charges]) AS [SumOfShipping Charges], Sum([ActualSF]*[Price]+[Shipping Charges]) AS Total
FROM [Shipping Info] LEFT JOIN [SO Parts/Pricing] ON [Shipping Info].[SalesOrder#] = [SO Parts/Pricing].[SalesOrder#]
GROUP BY [Shipping Info].[SalesOrder#]
HAVING ((([Shipping Info].[SalesOrder#])=[Sales Order?]));

Now - if you run your query, you will get one row of data with all the numeric fields Totalled.

I don't know if you really want to Sum all the numeric fields.
For instance - your three Shipping Values are now totalled to $75.00.
I'm not sure if you want to charge $25.00 * 3 for shipping . . .
You can 'Avg' the Shipping column & get $25 - which might be ok for THIS order - but what about other orders . . . will the same logic still be good?

Go back to Query Design.

Two rows under Actual_SF: ActualSF - you will see 'Sum' with a down arrow to the right of it.
If you click the down arrow - you will see the different types of 'aggregate' functions you can do with the values in the field.

Play around with it and let me know if you need more help.

[melonwand]

That is wonderful, but I only want one shipping charge for the whole sales order, so rather than $75 is should be $25. If I select AVG, that works for the column, but how do I have it add it to the total sale as $25 not $75. This would be the case for all sales orders entered. Thank you so much you have been extremely helpful.

Dawn

[melonwand]

Hi Robeen,

I think I got it, I can total it by using the query in the report. OMG this is awesome, you have been extremely helpful.

Thanks again!!!!!
Dawn

[Robeen]

Happy to Help, Dawn!

[melonwand]

Hi Robeen,

I have another query issue that I can't seem to figure out. Is there a way to have a query field populate with information from another field in the same query? This the the query...

SELECT [Stock/Batch2 Qry].PartNumber, [Stock/Batch2 Qry].BatchNumber, [Stock/Batch2 Qry].SumOfQty, [Stock/Batch2 Qry].SumOfActualSF, [Stock/Batch2 Qry].QtyAvail
FROM [Stock/Batch2 Qry];

First, Can SumOfActualSF be defaulted to 0 if it is blank?
Second, Can QtyAvail default to SumOfActualSF if it is blank?

I have a feeling you're going to tell me no, but I figured I'd ask.

Thanks for your help
Dawn

[Robeen]

Hi Dawn!

1.
SumOfActualSF CAN be defaulted to 0 if it is Null.

What you will need to do is in your query design, in the field that has 'SumOfActualSF' - change it to say something like:
ActualSFSum: Nz([SumOfActualSF],0)

Now - if SumOfActualSF is Null - you will see a 0 there when you run the query.
However - if there is something other than Null - that formula will not have any effect.
See if using the Nz function works.
If it doesn't, you can create a Function to force a 0 in there - or you can try using an IIF function in the query itself.
I can help you with those if you need.

2.
I don't understand this:

Second, Can QtyAvail default to SumOfActualSF if it is blank?

Do you mean - can QtyAvail default to SumOfActualSF if QtyAvail is 'blank'?
Or do you mean - can QtyAvail default to SumOfActualSF if SumOfActualSF is 'blank'?

Also - what if Qty Avail is 'blank' AND SumOfActualSF is ALSO 'blank'?

Either way - Yes - you can nmake this happen too.
Tell me exactly what you need and we'll figure it out.

3.
You'll notice I put 'blank' in tick marks.
That's because 'blank' may be what we see when we run our queries - but it is not a term that Access will recognize.

What appears to be blank to us may be a Null value that you can handle using the Nz function.
Or it may be an Empty string [""].
Or - as is the case with a database that I work with at my job - it could be one or more Space characters [" "].
So - you'll really have to be aware of these possibilities when you create your queries.
There are pretty simple ways to find out what's in a field that appears blank to the eye. One of them being the use of the Nz function.

I'll wait to hear from you.

[melonwand]

I'm going to upload a copy of the DB for you to see it. I think I'm doing something wrong. If you look as Stock/Batch2 Qry. I need the blanks in the SumOfActualSF to be 0. In QtyAvail, I need it to be the sum of SumOfQty less the SumOfActualSF. You will also notice that it duplicates in SumOfQty and doesn't group it by batch then sum the qty. The file is large so I posted it here for you to look at.

http://www.box.net/shared/t3ekjq0lvu2imibj4n0d

Thanks again for your help

Hi Dawn!

1.
SumOfActualSF CAN be defaulted to 0 if it is Null.

What you will need to do is in your query design, in the field that has 'SumOfActualSF' - change it to say something like:
ActualSFSum: Nz([SumOfActualSF],0)

Now - if SumOfActualSF is Null - you will see a 0 there when you run the query.
However - if there is something other than Null - that formula will not have any effect.
See if using the Nz function works.
If it doesn't, you can create a Function to force a 0 in there - or you can try using an IIF function in the query itself.
I can help you with those if you need.

2.
I don't understand this:



Do you mean - can QtyAvail default to SumOfActualSF if QtyAvail is 'blank'?
Or do you mean - can QtyAvail default to SumOfActualSF if SumOfActualSF is 'blank'?

Also - what if Qty Avail is 'blank' AND SumOfActualSF is ALSO 'blank'?

Either way - Yes - you can nmake this happen too.
Tell me exactly what you need and we'll figure it out.

3.
You'll notice I put 'blank' in tick marks.
That's because 'blank' may be what we see when we run our queries - but it is not a term that Access will recognize.

What appears to be blank to us may be a Null value that you can handle using the Nz function.
Or it may be an Empty string [""].
Or - as is the case with a database that I work with at my job - it could be one or more Space characters [" "].
So - you'll really have to be aware of these possibilities when you create your queries.
There are pretty simple ways to find out what's in a field that appears blank to the eye. One of them being the use of the Nz function.

I'll wait to hear from you.

[Robeen]

To get what you need - you will need to do steps 1., 2., & 3. in order.

But first . . .

Run your existing query as it is and then look at what I say in part 3. of this post. It will explain what the problem is with your Group By.

1.
In your 'SumOfActualSF' field, I now have:
ActualSFSum: Nz([SumOfActualSF],0)
Now I see all 0's where there was a blank before.

You can put that change in right away if you want.

2.
To get your QtyAvail going . . .
Go to the SQL View of your query and replace what is in there with this:

 
SELECT [Stock/Batch Query].PartNumber, [Stock/Batch Query].BatchNumber, [Stock/Batch Query].SumOfQty, Nz([SumOfActualSF],0) AS ActualSFSum, [SumOfQty]-[ActualSFSum] AS QtyAvail
FROM [Stock/Batch Query]
ORDER BY [Stock/Batch Query].PartNumber, [Stock/Batch Query].BatchNumber;

You'll notice that I have removed all the Grouping from the query.
That is explained below.

3.
You are still having problems with what you are attempting with your Group By.

If you look at all the values for any ONE PartNumber, you will notice that SOMETHING IS DIFFERENT between the two rows of data for the part number.

Eg: PartNumber 0104:
There are two different BatchNumbers.

Your 'Group By' is saying put all rows that have the same PartNumber AND the same BatchNumber in one Group.
So - every time the PartNumber - OR BatchNumber - changes - there will be a new 'Group' [new row in your query results].

To see your query really working with Group By, try this:
Create a new Query.
When it asks you to select a Table [in the ShowTable dialog box] - click 'Close'.
Open SQL View [top left Corner of window] and paste this in:

SELECT [Stock/Batch2 Qry].PartNumber, Sum([Stock/Batch2 Qry].SumOfQty) AS SumOfSumOfQty, Sum([Stock/Batch2 Qry].ActualSFSum) AS SumOfActualSFSum, Sum([Stock/Batch2 Qry].QtyAvail) AS SumOfQtyAvail
FROM [Stock/Batch2 Qry]
GROUP BY [Stock/Batch2 Qry].PartNumber;

This will give you a row of data for each PartNumber.

Let me know how this works for you.

[melonwand]

This works with one issue. I have a form that includes Stock Availability and anytime that part number is listed, it multiplies it by the number of times it is listed. For example... Product #F2010 shows the SumOfQty as 7248 square feet when it is actually 2416 square feet less 48 square feet that were sold. The the Total Available should be 2368 square feet. This is the link to the most recent version of the DB that I saved a copy of with the changes. Did I enter something incorrectly? The form is Inventory/Pricing.
http://www.box.net/shared/edky6nb0js5hvrenz0ot

[Robeen]

I couldn't find a form named 'Inventory/Pricing'.