cancel
Showing results for 
Search instead for 
Did you mean: 

Total qty field in OACT and PCH1 tables

Former Member
0 Kudos

Hi,

What field in the OACT and PCH1 tables will give me a total quantity of pieces on the a/p invoice or purchase order, respectively?

Thanks,

Shelby

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Suda, tried to add a few columms on the query you set up for me but ran into a grouping error. I'm not too familiar with grouping. How would I set up the SQL to add Cardname, DocDate, and Comments?

Thanks,

Shelby

Former Member
0 Kudos

Hi

Are you trying add the fields that are present in Suda query.?

You can just use Ctrl Click on the colum and it gives you a total .

Otherwise ,try this

you need to add field and same to group if you are using sum

SELECT T1.DocNum AS 'Document Number',T1.CardName , T1.DocDate,T1.Comments,SUM(T1.TotalExpns) AS 'Total Freight Charges',

SUM(T0.Quantity) AS 'Quantity'

FROM [dbo].[PCH1] T0 INNER JOIN [dbo].[OPCH] T1 ON T1.[DocEntry] = T0.DocEntry

WHERE T1.DocType = 'I'

GROUP By T1.DocNum,T1.Cardname,T1.DocDate,T1.Comments

Hope this help

bishal

Edited by: Bishal Adhikari on Mar 6, 2009 1:21 PM

Former Member
0 Kudos

I'm trying to add more fields to the query that Suda did for me. In other words, I asked for DocNum, freight charges and total quantity, then realized it would be beneficial to also see the Cardname, DocDate and comments fields. The only thing that needs to be summed is the total quantity, which Suda's query addressed.

When I tried to add fields into the query, I added the Cardname directly after SELECT T1.DocNum AS 'Document Number', but I don't understand the grouping well enough and apparently should have also formatted it somehow there as well.

Any thoughts?

Thanks,

Shelby

Former Member
0 Kudos

Edited the message ,please check

Thank you

Former Member
0 Kudos

You could check the syntax and create your own:

SELECT T1.DocNum AS 'Document #',T1.CardName , T1.DocDate,T1.Comments,

SUM(T0.Quantity) AS 'Total Quantity'

FROM dbo.PCH1 T0 INNER JOIN dbo.OPCH T1 ON T1.DocEntry = T0.DocEntry

WHERE T1.DocType = 'I'

GROUP By T1.DocNum,T1.Cardname,T1.DocDate,T1.Comments

Thanks,

Gordon

Former Member
0 Kudos

Thanks to all of you - I appreciate it.

Shelby

Answers (2)

Answers (2)

Former Member
0 Kudos

There is no relationship between quantity and OACT table. As for the total quantity for any documents, there is none. You could only find each individual line item quantity from PCH1 or POR1 for A/P Invoice or PO individually.

Thanks,

Gordon

Former Member
0 Kudos

Hi Shelby,

You mean the quantity by rows? The PCH1.Quantity is the field of purchase invoice item quantity. The POR1.Quantity field is for purchase order item quantity.

Regards,

Nghia

Former Member
0 Kudos

Thanks for your reply-

What I'm looking for is the total quantity for the whole a/p invoice. I'm setting up a query pulled from a/p item invoices that will show me the total freight charge on the invoice and the total number of units on the invoice.

Reason this is needed is because we track freight charges per unit purchased for costing reasons.

Some of our invoices are created by copying from a goods receipt PO; others are created without copying. Using 2007 A.

Any help is appreciated,

Shelby

former_member583013
Active Contributor
0 Kudos

Shelby,

The following SQL would give you the AP Invoice No, Total Freight and SUM(Quantity)

SELECT T1.DocNum AS 'Document Number', SUM(T1.TotalExpns) AS 'Total Freight Charges', 
SUM(T0.Quantity) AS 'Quantity' 
FROM  [dbo].[PCH1] T0  INNER  JOIN [dbo].[OPCH] T1  ON  T1.[DocEntry] = T0.DocEntry
WHERE T1.DocType = 'I'
GROUP By T1.DocNum

Suda

Former Member
0 Kudos

Thanks, Suda, this query is what I need.

Thanks to Gordon and Nghia as well.

Shelby

Former Member
0 Kudos

Suda, one last question. Your query pulls what I need, but I tried to add some columns, in particular Cardname, DocDate and Comments field, but an error comes back concerning grouping. I'm not very familiar with the grouping function. How would I structure the query adding these fields?

Thanks,

Shelby