My subform shows records I don't want to see. 1

[Sidonie]

I am working on a Purchase Order Database and am trying to setup a form to handle the receipt of goods.

At the moment I have a [PO Table] for the official order details which is linked to a [PO Sub Table] by Order Number. The [PO Sub Table] handles the body of the Order. I have a separate table linked to the [PO Sub Table] by line number, which records the receipt of goods against each particular line number. This makes sure that on my form, users can keep receiving goods on each particular line item until the line has been completely received, no matter how many deliveries it takes to achieve this. My form is setup to display calculated controls which display the qty ordered, qty rec'd and qty backordered, each time goods are received for each line item on the SubForm, (this part works fine).

However, I am trying to design my underlying query to ignore line items which have been received in full. At the moment it ignores line items that have been fully received in one delivery. But, when a line has been fully received over several deliveries, it still displays on the form. (My Form also displays that line number as many times as there have been goods receipted on the subform, this is really annoying!!)

I need some clever person to show me how to construct my query so that my form will display only records where the [Qty Ordered] from (PO Sub Table) is greater than the [QtyRecd] from (PO Material Reception Table), remembering that I need to caluclate the sum of [QtyRecd] for each line number on an order.

Would you know of a way that I can get my form & subform to display only those line items which have an outstanding balance?

Desperately needing help!!! Thanks.

 

[B827]

Try using the following SQL, you might need to break it down into two parts, it is sometimes a bit tricky to get the results you want.

SELECT [PO Sub Table].OrderNo, [PO Material Reception Table].Line, [PO Sub Table].Qty, Sum([PO Material Reception Table].[Qty Rec]) AS [Total Received], [Qty]-[Total Received] AS Outstanding
FROM [PO Material Reception Table] INNER JOIN [PO Sub Table] ON ([PO Material Reception Table].Line = [PO Sub Table].Line) AND ([PO Material Reception Table].OrderNo = [PO Sub Table].OrderNo)
GROUP BY [PO Sub Table].OrderNo, [PO Material Reception Table].Line, [PO Sub Table].Qty;
Sandy

 

[B827]

Sorry I should have read your posting more carefully the SQL below is what you need:

SELECT [PO Sub Table].OrderNo, [PO Material Reception Table].Line, [PO Sub Table].Qty, Sum([PO Material Reception Table].[Qty Rec]) AS [Total Received], [Qty]-[Total Received] AS Outstanding
FROM [PO Material Reception Table] INNER JOIN [PO Sub Table] ON ([PO Material Reception Table].OrderNo = [PO Sub Table].OrderNo) AND ([PO Material Reception Table].Line = [PO Sub Table].Line)
GROUP BY [PO Sub Table].OrderNo, [PO Material Reception Table].Line, [PO Sub Table].Qty
having ((([Qty]-Sum([PO Material Reception Table].[Qty Rec]))>0)); Sandy

 

[Sidonie]

Thank you so much Sandy. You wouldn't believe the amount of hours I have wasted trying to get this to work and yet you made it seem so easy.

Again, thanks for your help!!!

Cheers
SID

 

[Sidonie]

Sandy,

I had data in the Material Reception Table when I set up the SQL you gave me so I was able to see that it was working. Then I dediced to delete all data in the Material Reception Table and start from scratch, as if I was using the database for the first time, just to make certain there were no bugs.

Now it doesn't work becuase we are saying show [Outstanding]>0, when in fact there are no records/amounts at all in [QtyRecd] for our statement to Sum. I have tried adding "Or Is Null" to the Having Line of the SQL,(because I was getting my original query to return the correct results with it before). It still doesn't work.

Can you please tell me what I have done wrong. This is my SQL as it is at the moment.

SELECT tblPOSub.[PO Number], tblPO.[Supplier Name], tblPO.[PO Date], tblPOMaterialReception.LineNumber, tblPOSub.[Qty Ordered], Sum(tblPOMaterialReception.QtyRecd) AS [Total Received], [Qty Ordered]-[Total Received] AS Outstanding, tblPOSub.CatalogID, tblPOSub.[Goods Description], tblPOSub.[Unit Price], tblPOSub.[GST Value]
FROM tblPO INNER JOIN (tblPOSub INNER JOIN tblPOMaterialReception ON tblPOSub.LineNumber = tblPOMaterialReception.LineNumber) ON tblPO.[PO Number] = tblPOSub.[PO Number]
GROUP BY tblPOSub.[PO Number], tblPO.[Supplier Name], tblPO.[PO Date], tblPOMaterialReception.LineNumber, tblPOSub.[Qty Ordered], tblPOSub.CatalogID, tblPOSub.[Goods Description], tblPOSub.[Unit Price], tblPOSub.[GST Value]
HAVING ((([Qty Ordered]-Sum([tblPOMaterialReception].[QtyRecd]))>0 Or ([Qty Ordered]-Sum([tblPOMaterialReception].[QtyRecd])) Is Null));

Thanks for your help.

SID

 

[Sidonie]

Sandy,

Good News!!! I combined what I had originally with the tricky bits of the SQL you gave me and now I have what I want.

Thank you so much for your help.

Cheers
SID

 

[B827]

Sid,
that is good news - glad to be of assistance. Sandy