Total Query

[melonwand]

My apologies it's frmPartNumbers.



Thanks,
Dawn

[Robeen]

I tried poking around in there but was not able to see what you described.

Could you spell it out for me - step by step - what I should do to see what you're talking about?

When I open the Form and pull up F2010 I don't see the numbers you mention.

I see Qty Sold - 48, and Available Sale 16028.25.

[melonwand]

Ok, that number is exagerated. That number is supposed to be the total of the "Sq Ft Available by Batch" (subform) minus the Qty Sold. I'm not sure by it's increasing the quantity.

[Robeen]

I'm attaching a jpg of what i see when I run the form and get F2010 from the drop-down box.

The Sq Ft Available by Batch subform actually has TWO rows of data - two batch numbers & the first row has Batch Number 32246 & Batch Sq. Ft. 438.75.

It seems to me that your total [16028.25] isn't in any way connected to the total of the two numbers in the "Sq Ft Available by Batch" subform.

Where is that calculation being done that subtracts 48 & is giving me 16028.25?

[melonwand]

If you look at that subform, the query is Stock/Batch Qry and the numbers are all messed up. The footage is correct in the inventory table. I'm not sure where the mess up is, but my queries are not calculating correctly. I'm about to pull out all of my hair. Any suggestions??? I'm all ears and eyes.

[Robeen]

Could you paste the SQL for your 'Stock/Batch Qry' here for me, please?

[melonwand]

SELECT Inventory.PartNumber, Inventory.BatchNumber, [Stock Qty Query].SumOfQty, Sum([SO Parts/Pricing].ActualSF) AS SumOfActualSF
FROM [Stock Qty Query] INNER JOIN (([Part Numbers] INNER JOIN Inventory ON [Part Numbers].PartNumber = Inventory.PartNumber) LEFT JOIN [SO Parts/Pricing] ON Inventory.BatchNumber = [SO Parts/Pricing].BatchNumber) ON [Stock Qty Query].PartNumber = [Part Numbers].PartNumber
GROUP BY Inventory.PartNumber, Inventory.BatchNumber, [Stock Qty Query].SumOfQty
HAVING ((([Stock Qty Query].SumOfQty)>0))
ORDER BY Inventory.PartNumber, Inventory.BatchNumber;

[Robeen]

It might be a little before I can get to this - but the thing that strikes me right away is that you are once again grouping by Inventory.BatchNumber.

I followed your query back and found that the Query from which you are getting your Sums for the Part Numbers - is returning the correct Sums.

See if you can fix this last query of yours by not grouping on Batch Number. It's the same reason as I explained in the context of another query of yours. You have Part Numbers that have more than one Batch Number - so if you group on Part Number AND Batch Number, any Part Number that has more than one Batch Number - will be duplicated for each Batch Number.

Play around with it - literally - you'll get it working.

I'll help if you can't get it to work - but I think you will be able to fix this yourself.

Instead of your 'Stock/Batch Qry' - create a new query [name it Stock_Batch_Qry maybe?] without the Batch Number field - and before you do any grouping - make sure your totals are coming in correct.

Let me know how it goes. I'm a little busy right now but I should have time to help later if you haven't figured it out.

[melonwand]

I'm not sure how all of that happened because I basically copied and pasted what you had originally sent me. Maybe I'm over complicating this. Can I have a form that calculates in a text field in the code ex. On Load etc....

[Robeen]

Yes - You can have a calculated field on a Form.

All you need to do if you have the correct Numbers for what is in stock and what has been ordered - you can easily do the Math in code.

What you'll need to do is:

Declare Variables for Qty Available, Qty Ordered, Qty Remaining:
Dim intQtyAvailable, intQtyOrdered, intQtyRemaining as Integer

1. Have a Text Box [QtyRemaining] on your Form into which you want the calculated total to go.

2. SetFocus on the Qty Available text box:
Me.QtyAvailable.SetFocus

3. Get the value from QtyAVailable:
intQtyAvailable = Me.QtyAVailable

4. SetFocus on the Qty Ordered text box:
Me.QtyOrdered.SetFocus

5. Get the value from Qty Ordered:
intQtyOrdered = Me.QtyOrdered

6. Set focus on QtyRemaining text box.

7. Set value of QtyRemaining:
Me.QtyRemaining = intQtyRemaining

You'll have to make sure you can get the correct values into QtyAvailable and QtyOrdered.

Hope this helps.