UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Pending Purchase Order Calculation, Access 2010    
 
   
prabu
post Feb 21 2020, 02:13 PM
Post#1



Posts: 12
Joined: 8-April 18



Dear Friends,

Please help me to calculate the pending order calculation:
The Scenario is >>

The company taking many projects from the government.
The company placing purchase orders.

Now, I want to calculate pending purchase orders.

The Purchase Order Master

Project No - Order No - SupplierNo
PRJT1 - ORDR1 - SUPLR1
PRJT1 - ORDR2 - SUPLR1
PRJT2 - ORDR3 - SUPLR2

Purchase Order Details

OrderNo - Sno - ItemNo - Unit - POQuantity - Price - Amount - BalanzPOQuantity - Bal PO Amount
ORDR1 - 1 - Item1 - Ea - 10 - 10.00 - 100.00 - 0 - 0.00
ORDR1 - 2 - Item2 - Ea - 20 - 20.00 - 400.00 - 5 - 100.00

ORDR2 - 1 - Item1 - Ea - 100 - 10.00 - 1000.00 - 0 - 0.00

ORDR3 - 1 - Item3 - Ea - 30 - 15.00 - 450.00 - 0 - 0.00


The Questions are:

How to calculate the BalanzPOQty and Bal PO Amount?

NOW RECEIVING MATERIALS AGAINT PURCHASE ORDERS

Note:
1. ORDR1, ORDR2 are belongs to one project and one supplier.
2. 1 Receiving combines 2 orders

ReceivingNo - SupplierNo - Project No - Order No -
REV1 - SUPLR1 - PRJT1 - ORDR1, ORDR2
REV2 - SUPLR2 - PRJT2 - ORDR3

Purchase Order Details

ReceivingNo - Sno - ItemNo - Unit - RevQuantity - Price - Amount
REV1 - 1 - Item1 - Ea - 110 - 10.00 - 1100.00
REV1 - 2 - Item2 - Ea - 15 - 20.00 - 300.00

REV2 - 1 - Item3 - Ea - 30 - 15.00 - 450.00


How to do?

How to combine Itm1 from two orders to one receiving?


Regards
Prabu.

Go to the top of the page
 
projecttoday
post Feb 21 2020, 06:27 PM
Post#2


UtterAccess VIP
Posts: 11,811
Joined: 10-February 04
From: South Charleston, WV


What is the desired result? Where are you having a problem?

--------------------
Robert Crouser
Go to the top of the page
 
MadPiet
post Feb 21 2020, 09:54 PM
Post#3



Posts: 3,574
Joined: 27-February 09



This would be a whole lot easier to answer if you provided something like a table instead of just a bunch of text. =(
Go to the top of the page
 
FrankRuperto
post Feb 21 2020, 11:03 PM
Post#4



Posts: 809
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Agreed, we need to see each table, their fields, and how the tables are related. Preliminarily, I would think you would have a status field that gets updated to reflect if a purchase order is pending or completed. In the realworld, sometimes not all items related to a purchase order are received in a shipment, wrong or damaged items were shipped, etc. etc. so this all needs to be tracked.
This post has been edited by FrankRuperto: Feb 21 2020, 11:06 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
prabu
post Mar 4 2020, 06:55 AM
Post#5



Posts: 12
Joined: 8-April 18



Dear Friends

Please Note the Details

Companys: Company1, Company2
Projects:
Company1 – Project1
Company2- Project2, Project3
Job:
The Project place various purchase orders to run the project.
The Store Personnel Receive the material.

My Scope:
Check the Pending Order Details
Update Stock on hand after each receiving and issuing material.
Using MS Office:
Company Table: CompanyNo, CompanyName
Project Table: ProjectNo, ProjectName
Supplier Table: SupplierNo, SupplierName
Order Master Table: OrderDate, OrderNo, SupplierNo, ProjectNo, CopmanyNo
Order Detail Table: OrderNo, Sno, ItemNo, OrderQty, Price
Calculation on order Table: summarize the ordered item/s based on OrderNo & ItemNo (for pending order calculation)
Receive Master Table: ReceiveDate, RevNo, OrderNo, SupplierNo, ProjectNo, CopmanyNo
Order Detail Table: RevNo, Sno, ItemNo, RevQty, Price
1. One time Receive or Multiple Time Receive may occur.
2. All items or Partial Items Or None will be received while receiving.
3. Cancel the ordered for the items which or not required.



1. ORDER Query

SELECT OrderMaster.OrderNo, OrderDetail.ItemNo, [1ItemMaster].ItemName, OrderDetail.OrderQty, OrderDetail.Price, [OrderQty]*[Price] AS OrderAmount
FROM OrderMaster INNER JOIN (1ItemMaster INNER JOIN OrderDetail ON [1ItemMaster].ItemNo = OrderDetail.ItemNo) ON OrderMaster.OrderNo = OrderDetail.OrderNo;


OrderQuery

OrderNo ItemNo ItemName OrderQty Price OrderAmount
Order1 70620001 Intermediate Transom 2.5 mtr 10 1 10
Order1 70625001 Pipe 48.3mm O.D x 4mm x 6m - Galv.BS - 1139 20 2 40
Order2 70620001 Intermediate Transom 2.5 mtr 30 1 30
Order2 70625001 Pipe 48.3mm O.D x 4mm x 6m - Galv.BS - 1139 40 2 80
Order2 70605001 Scaffolding Ledger - 1.2 mtr 50 3 150

2. RECEIVE QUERY

SELECT ReceiveMaster.OrderNo, ReceiveDetail.ItemNo, [1ItemMaster].ItemName, Sum(ReceiveDetail.RevQty) AS SumOfRevQty, ReceiveDetail.Price, [SumOfRevQty]*[Price] AS RevAmount
FROM ReceiveMaster INNER JOIN (1ItemMaster INNER JOIN ReceiveDetail ON [1ItemMaster].ItemNo = ReceiveDetail.ItemNo) ON ReceiveMaster.RevNo = ReceiveDetail.RevNo
GROUP BY ReceiveMaster.OrderNo, ReceiveDetail.ItemNo, [1ItemMaster].ItemName, ReceiveDetail.Price, [SumOfRevQty]*[Price];

RevQuery

OrderNo ItemNo ItemName SumOfRevQty Price RevAmount
Order1 70620001 Intermediate Transom 2.5 mtr 10 1 10
Order1 70625001 Pipe 48.3mm O.D x 4mm x 6m - Galv.BS - 1139 20 2 40
Order2 70620001 Intermediate Transom 2.5 mtr 30 1 30
Order2 70625001 Pipe 48.3mm O.D x 4mm x 6m - Galv.BS - 1139 25 2 50



CancelPOQuery

OrderNo ProjectNo ITemNo ItemName CancelOrderQty Price CancelAmount
Order2 26030015 70605001 Scaffolding Ledger - 1.2 mtr 50 3 150


MY CALCULATED QUERY GIVES DUPLICATE RECORDS. HOW TO ELIMINATE IT?

OrderNo ItemNo ItemName OrderQty SumOfRevQty Expr1
Order1 70620001 Intermediate Transom 2.5 mtr 10 10
Order1 70620001 Intermediate Transom 2.5 mtr 10 20
Order1 70625001 Pipe 48.3mm O.D x 4mm x 6m - Galv.BS - 1139 20 10
Order1 70625001 Pipe 48.3mm O.D x 4mm x 6m - Galv.BS - 1139 20 20
Order2 70620001 Intermediate Transom 2.5 mtr 30 30
Order2 70620001 Intermediate Transom 2.5 mtr 30 25
Order2 70625001 Pipe 48.3mm O.D x 4mm x 6m - Galv.BS - 1139 40 30
Order2 70625001 Pipe 48.3mm O.D x 4mm x 6m - Galv.BS - 1139 40 25
Order2 70605001 Scaffolding Ledger - 1.2 mtr 50 30
Order2 70605001 Scaffolding Ledger - 1.2 mtr 50 25

TO BE CALCULATE????????????????????????????????????????????????????????????????????
1. Pending Order QTY BY OrderNo, ItemNo = OrderQty – SumofRevQty - CancelPOQty
2. Pending Order QTY BY ProjectNo, OrderNo ItemNo = OrderQty – SumofRevQty - CancelPOQty

3 How to combine and calculate the total Received Qiantity based on Company, Poject, Supplier and Etc.
??????????????????????????????????????????????????????????????????????????????????????????

Prabu





Attached File(s)
Attached File  PendingOrderCalculation.pdf ( 133.32K )Number of downloads: 6
 
Go to the top of the page
 
projecttoday
post Mar 4 2020, 12:27 PM
Post#6


UtterAccess VIP
Posts: 11,811
Joined: 10-February 04
From: South Charleston, WV


QUOTE
MY CALCULATED QUERY GIVES DUPLICATE RECORDS. HOW TO ELIMINATE IT?

OrderNo ItemNo ItemName OrderQty SumOfRevQty Expr1
Order1 70620001 Intermediate Transom 2.5 mtr 10 10
Order1 70620001 Intermediate Transom 2.5 mtr 10 20
Order1 70625001 Pipe 48.3mm O.D x 4mm x 6m - Galv.BS - 1139 20 10
Order1 70625001 Pipe 48.3mm O.D x 4mm x 6m - Galv.BS - 1139 20 20
Order2 70620001 Intermediate Transom 2.5 mtr 30 30
Order2 70620001 Intermediate Transom 2.5 mtr 30 25
Order2 70625001 Pipe 48.3mm O.D x 4mm x 6m - Galv.BS - 1139 40 30
Order2 70625001 Pipe 48.3mm O.D x 4mm x 6m - Galv.BS - 1139 40 25
Order2 70605001 Scaffolding Ledger - 1.2 mtr 50 30
Order2 70605001 Scaffolding Ledger - 1.2 mtr 50 25


I don't see 2 that are the same.

What is the query?

--------------------
Robert Crouser
Go to the top of the page
 
prabu
post Mar 6 2020, 12:12 PM
Post#7



Posts: 12
Joined: 8-April 18



Dear Mr. ProjectToday

Please note the details:

The Company and its divisions

Company = Heavy Engineering Co. or Dredging Co. = MYcompany (in Table)
Each Company having many division = CompanyGroupNo (in Table)
Each division have many projects = Project 1, Project2
Each project places many orders for their material requirement = PO1, Po2
Each order Receive Material One time or Multiple Time = Rev1, Rev2
Not Required items will be cancelled.

THE SEQUENCE OF JOB



????????????????????????????????????????????????????????????????????????????
WHAT TO BE CALCULATED?

THE FINAL BALANCE QUANTITY = PO QTY – SUM OF RECEIVED QTY – CANCELLED QTY

1. Query for Purchase Order: ProjectNo, OrderNo, ItemNo, PoQty, Price, POamount is OK.
2. Query for Receive Query: ProjectNo, OrderNo, ItemNo, SumofRevQty, Price, Revamount is OK.

3. Query for FINAL BALANCE QTY & AMOUNT:

Final Calculation query gives error or duplicate records.

PROJECTNO, ORDERNO, ITEMNO, ORDERQTY, SUMOFREVQTY, CANCELQTY
ORDERQTY - SUMOFREVQTY - CANCELQTY AS BLANCEPOQTY,
PRICE,
ORDERAMOUNT, AMOUNT OF SUMOFREVQTY, CAMCELAMOUNT,
ORDERAMOUNT - AMOUNT OF SUMOFREVQTY - CANCELAMOUNT AS BALNCEAMOAUNT

?????????????????????????????????????????????????????????????????????????????????

What to be calculated:
Pending order quantity details are calculate for each item in each order
Purchase order
- Each project place orders for the material requirement.
- One order contains one or many order items

ProjectNo: PRJT1
OrderNo: PO1

What to be calculated (for Each Receiving)

POAmount: 7250.00
Net Received Amount: 3250.00
Net Balance Amount: 4000.00

Receive items again purchase order
- We receive ordered items at one time or multiple times
- One Receive contains one line items or many line items
- Each item may receive full order quantity or Partial order quantity or Null
- ItemNo: Item1, POQty: 100, PO Price: 10.00 PO Amount: 1000.00
- ItemNo: Item2, POQty: 150, PO Price: 15.00 PO Amount: 2250.00
- ItemNo: Item3, POQty: 200, PO Price: 20.00 PO Amount: 4000.00

- What to be calculated (for Each Receiving)
-
- ItemNo: Item1, NetRceiveQty: 100, PO Price: 10.00 Net Balance Rev Amount: 0.00
- ItemNo: Item2, NetRceiveQty: 150, PO Price: 15.00 Net Balance Rev Amount : 0.00
- ItemNo: Item3, NetRceiveQty: 0, PO Price: 20.00 Net Balance Rev Amount: 4000.00

------------------------------------------------------------------------------------------------
PURCHSE ORDER

PurchaseOrderMAster

MyCompany : GD
CompanyGroupNo : CM10
ProjectNo : PRJT1
PODate : Mar. 01, 2019
PONo : PO1
SupplierNo : Sup1

PurchaseOrderDetail


PONo - ItemNo - ItemName - POqty - Price - Amount
PO1 - Item1 - Readymix C30 - 100 - 10.00 - 1000.00
PO1 - Item2 - Readymix C14 - 150 - 15.00 - 2250.00
PO1 - Item3 - Readymix C10 - 200 - 20.00 - 4000.00
----------------------
Total - 7250.00
END OF PURCHASE ORDER
---------------------------------------------------------------------------------------------------
RECEIVING (First time Receive)

One order received two times

Receive Master

MyCompany : GD
CompanyGroupNo : CM10
ProjectNo : PRJT1
PODate : Mar. 05, 2019
RevNo : Rev1 (Primary Key)
PONo : PO1
SupplierNo : Sup1
OrderAmount : 7250.00
RecivedAmount : 2500.00
BalanceAmount : 4750.00

Receive Detail

RevNo - ItemNo - ItemName - Revqty - Price - Amount
Rev1 - Item1 - Readymix C30 - 100 - 10.00 - 1000.00
PO1 - Item2 - Readymix C14 - 100 - 15.00 - 1500.00
Total - 2500.00

Receive Detail - QUERY CALCULATION
TO BE CALCULATED
--------------------------
NET - NET - Bal - Bal
Rev REV - REV - PO - PO
RevNo - ItemNo - ItemName - Revqty - Price - Amount - QTY - Amount - Qty Amount
Rev1 - Item1 - Readymix C30 - 100 - 10.00 - 1000.00 - 100 - 1000 - 0 - 0
Rev1 - Item2 - Readymix C14 - 100 - 15.00 - 1500.00 - 100 - 1500 - 50 - 750
-----------------------------------------------------
Total - 2500.00 - 2500 - - 750


END OF FIRST TIME RECEIVING
------------------------------------------------------------------------------------------------
Receive Master (Second time Receive)

MyCompany : GD
CompanyGroupNo : CM10
ProjectNo : PRJT1
PODate : Mar. 10, 2019
RevNo : Rev2 (Primary Key)
PONo : PO1
SupplierNo : Sup1
OrderAmount : 7250.00 ( to be calculated)
RecivedAmount : 4000.00 ( to be calculated)
BalanceAmount : 3250.00 ( to be calculated)

Receive Detail


RevNo - ItemNo - ItemName - Revqty - Price - RevAmount
REV2 - Item2 - Readymix C14 - 50 - 15.00 - 750.00 (partial qty Received)
Total - 750.00

Receive Detail - CALCULATION QUERY

CALCULATED
--------------------------
NET NET Bal Bal
Rev REV REV PO PO
RevNo - ItemNo - ItemName - Revqty - Price - Amount - QTY - Amount - Qty Qty
Rev2 - Item2 - Readymix C14 - 50 - 15.00 - 750.00 - 150 - 2250 - 0 - 0
--------------------------------------------------
Total - 700.00 - 2500 - - 0

END of Second time receiving
---------------------------------------------------------------------------------------------
PURCHSE ORDER

PurchaseOrderMAster CALCULATION

MyCompany : GD
CompanyGroupNo : CM10
ProjectNo : PRJT1
PODate : Mar. 01, 2019
PONo : PO1
SupplierNo : Sup1
POAmount : 7250.00 ( to be calculated)
ReceivedAmount : 3250.00 ( to be calculated)
BalanceAmount : 4000.00 ( to be calculated)

PurchaseOrderDetail
TO BE CALCULATED
------------------------------
Net Net Net Net
PO REv Rev Balanz Balanz
PONo - ItemNo - ItemName - POqty - Price - Amount - Qty - Amt Qty Amt
PO1 - Item1 - Readymix C30 - 100 - 10.00 - 1000.00 - 100 - 1000 - 0 - 0
PO1 - Item2 - Readymix C14 - 150 - 15.00 - 2250.00 - 150 - 2250 - 50 - 0
PO1 - Item3 - Readymix C10 - 200 - 20.00 - 4000.00 - 0 - 0 - 200 - 4000
-------------------------------------------------------
Total - 7250.00 - 3250 - 4050

End of Purchase order calculation
--------------------------------------------------------------------------------------------------------

Please support

Prabu.













Go to the top of the page
 
prabu
post Mar 6 2020, 12:37 PM
Post#8



Posts: 12
Joined: 8-April 18



1. PURCHASE ORDER

PURCHASE ORDER MASTER TABLE

CompanyNo
ProjectNo,
OrderNo
Supplierno

PURCHASE ORDER DETAIL TABLE (one order contain one or many detail)

OrderNo, ItemNo, ItemName, PoQty, Price, POamount

2. RECEIVE MATERIAL (One time Receiving or Multiple time Receiving)

RECEIVE MASTER TABLE

RevNo,
CompanyNo
ProjectNo,
OrderNo
Supplierno

RECEIVE DETAIL TABLE

RevnO, ItemNo, Itemname, RevQty, Price, Revamount

3. CANCEL ORDER

CancelNo,
CompanyNo
ProjectNo,
OrderNo
Supplierno

Cancel DETAIL TABLE

CancelnO, ItemNo, Itemname, cancelQty, Price, cancelamount

4. QUERY FOR PENDING ORDER CALCULATION

PROJECTNO, ORDERNO, ITEMNO, ITEMNAME, ORDERQTY, SUMOFREVQTY, CANCELQTY,
ORDERQTY - SUMOFREVQTY - CANCELQTY AS BLANCEPOQTY,
PRICE,
ORDERAMOUNT, AMOUNT OF SUMOFREVQTY, CAMCELAMOUNT,
ORDERAMOUNT - AMOUNT OF SUMOFREVQTY - CANCELAMOUNT AS BALNCEAMOAUNT

This pending order calculation results duplicate records.

Regards
Prabu.
Go to the top of the page
 
projecttoday
post Mar 6 2020, 01:35 PM
Post#9


UtterAccess VIP
Posts: 11,811
Joined: 10-February 04
From: South Charleston, WV


Duplicate records are usually caused by join - multiplication. But you need to post the SQL code for this query. It's impossible to tell from this list where the problem is.

--------------------
Robert Crouser
Go to the top of the page
 
orange999
post Mar 6 2020, 01:43 PM
Post#10



Posts: 2,080
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


prabu,

As others have requested, please provide:
a copy of your database (trimmed of data to only highlight the issue) OR
a screen capture of your tables and relationships AND/OR
a copy of the query SQL involved.

Specific info with an example of
a)what you have and
b) what you want/need.

This is one of those cases where one picture is worth 1,000 words.

Clarity is key to getting focused response(s).
This post has been edited by orange999: Mar 6 2020, 01:44 PM

--------------------
Good luck with your project!
Go to the top of the page
 
prabu
post Mar 8 2020, 12:31 PM
Post#11



Posts: 12
Joined: 8-April 18



Yes Brother

Please find the attached Doc.

Kindly support me to fix the error.

Regards
Prabu.
Attached File(s)
Attached File  PURCHASE_ORDER_QUERY.zip ( 226.42K )Number of downloads: 13
 
Go to the top of the page
 
orange999
post Mar 8 2020, 01:54 PM
Post#12



Posts: 2,080
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Would be better to see your database, and specific instructions to observe the error/issue? Reading about it in word is not efficient.

I see some potential spelling issues in this

ORDERAMOUNT, AMOUNT OF SUMOFREVQTY, CAMCELAMOUNT,
ORDERAMOUNT - AMOUNT OF SUMOFREVQTY - CANCELAMOUNT AS BALNCEAMOAUNT
These may not be an issue, but seem suspicious.

--------------------
Good luck with your project!
Go to the top of the page
 
prabu
post Mar 10 2020, 01:04 PM
Post#13



Posts: 12
Joined: 8-April 18



1. Purchase Order Query


SELECT OrderMaster.OrderNo, OrderMaster.ProjectNo, OrderDetail.ItemNo, [1ItemMaster].ItemName, OrderDetail.OrderQty, OrderDetail.Price, [OrderQty]*[Price] AS Amount
FROM OrderMaster INNER JOIN (1ItemMaster INNER JOIN OrderDetail ON [1ItemMaster].ItemNo = OrderDetail.ItemNo) ON OrderMaster.OrderNo = OrderDetail.OrderNo;

Query Result

ProjectNo OrderNo ItemNo ItemName OrderQty Price Amount
26030015 Order1 70620001 Intermediate Transom 2.5 mtr 150 15 2250
26030015 Order1 70625001 Pipe 48.3mm O.D x 4mm x 6m 200 20 4000
26030015 Order1 70605001 Scaffolding Ledger - 1.2 mtr 100 10 1000

2. Query for Sum of Received Quantity

SELECT ReceiveMaster.SupplierNo, ReceiveMaster.ProjectNo, ReceiveMaster.OrderNo, ReceiveDetail.ItemNo, Sum(ReceiveDetail.RevQty) AS SumOfRevQty, ReceiveDetail.Price, [SumOfRevQty]*[Price] AS RevAmount, [1ItemMaster].ItemName
FROM ReceiveMaster INNER JOIN (1ItemMaster INNER JOIN ReceiveDetail ON [1ItemMaster].ItemNo = ReceiveDetail.ItemNo) ON ReceiveMaster.RevNo = ReceiveDetail.RevNo
GROUP BY ReceiveMaster.SupplierNo, ReceiveMaster.ProjectNo, ReceiveMaster.OrderNo, ReceiveDetail.ItemNo, ReceiveDetail.Price, [SumOfRevQty]*[Price], [1ItemMaster].ItemName;

Query Result

SupplierNo ProjectNo OrderNo ItemNo ItemName SumOfRevQty Price RevAmount
16010000 26030015 Order1 70605001 Scaffolding Ledger - 1.2 mtr 100 10 1000
16010000 26030015 Order1 70620001 Intermediate Transom 2.5 mtr 90 15 1350

3 How to calculate the balance order Qty?
The formula Balance order Qty = PO Qty - Sum of Rev Qty

SELECT RevQuerySumRevQty.OrderNo, RevQuerySumRevQty.ItemNo, RevQuerySumRevQty.SumOfRevQty, RevQuerySumRevQty.Price, RevQuerySumRevQty.RevAmount, OrderQuery.OrderQty, OrderQty-SumofRevQty AS BalQty
FROM RevQuerySumRevQty INNER JOIN OrderQuery ON RevQuerySumRevQty.OrderNo = OrderQuery.OrderNo;

Incorrect Query Result:

OrderNo ItemNo SumOfRevQty Price RevAmount OrderQty BalQty
Order1 70605001 150 10 1500 150
Order1 70605001 150 10 1500 200
Order1 70605001 150 10 1500 100
Order1 70620001 90 15 1350 150
Order1 70620001 90 15 1350 200
Order1 70620001 90 15 1350 100

Please correct the Query thus to get the below said correct result:


ProjectNo OrderNo ItemNo ItemName OrderQty Price Amount Sum of Rev Qty Bal order Qty
26030015 Order1 70620001 Intermediate Transom 2.5 mtr 150 15 2250 90 60
26030015 Order1 70625001 Pipe 48.3mm O.D x 4mm x 6m - 200 20 4000 0 200
26030015 Order1 70605001 Scaffolding Ledger - 1.2 mtr 100 10 1000 100 0

Regards
Prabu

Go to the top of the page
 
prabu
post Mar 10 2020, 01:06 PM
Post#14



Posts: 12
Joined: 8-April 18



1. Purchase Order Query


SELECT OrderMaster.OrderNo, OrderMaster.ProjectNo, OrderDetail.ItemNo, [1ItemMaster].ItemName, OrderDetail.OrderQty, OrderDetail.Price, [OrderQty]*[Price] AS Amount
FROM OrderMaster INNER JOIN (1ItemMaster INNER JOIN OrderDetail ON [1ItemMaster].ItemNo = OrderDetail.ItemNo) ON OrderMaster.OrderNo = OrderDetail.OrderNo;

Query Result

ProjectNo OrderNo ItemNo ItemName OrderQty Price Amount
26030015 Order1 70620001 Intermediate Transom 2.5 mtr 150 15 2250
26030015 Order1 70625001 Pipe 48.3mm O.D x 4mm x 6m 200 20 4000
26030015 Order1 70605001 Scaffolding Ledger - 1.2 mtr 100 10 1000

2. Query for Sum of Received Quantity

SELECT ReceiveMaster.SupplierNo, ReceiveMaster.ProjectNo, ReceiveMaster.OrderNo, ReceiveDetail.ItemNo, Sum(ReceiveDetail.RevQty) AS SumOfRevQty, ReceiveDetail.Price, [SumOfRevQty]*[Price] AS RevAmount, [1ItemMaster].ItemName
FROM ReceiveMaster INNER JOIN (1ItemMaster INNER JOIN ReceiveDetail ON [1ItemMaster].ItemNo = ReceiveDetail.ItemNo) ON ReceiveMaster.RevNo = ReceiveDetail.RevNo
GROUP BY ReceiveMaster.SupplierNo, ReceiveMaster.ProjectNo, ReceiveMaster.OrderNo, ReceiveDetail.ItemNo, ReceiveDetail.Price, [SumOfRevQty]*[Price], [1ItemMaster].ItemName;

Query Result

SupplierNo ProjectNo OrderNo ItemNo ItemName SumOfRevQty Price RevAmount
16010000 26030015 Order1 70605001 Scaffolding Ledger - 1.2 mtr 100 10 1000
16010000 26030015 Order1 70620001 Intermediate Transom 2.5 mtr 90 15 1350

3 How to calculate the balance order Qty?
The formula Balance order Qty = PO Qty - Sum of Rev Qty

SELECT RevQuerySumRevQty.OrderNo, RevQuerySumRevQty.ItemNo, RevQuerySumRevQty.SumOfRevQty, RevQuerySumRevQty.Price, RevQuerySumRevQty.RevAmount, OrderQuery.OrderQty, OrderQty-SumofRevQty AS BalQty
FROM RevQuerySumRevQty INNER JOIN OrderQuery ON RevQuerySumRevQty.OrderNo = OrderQuery.OrderNo;

Incorrect Query Result:

OrderNo ItemNo SumOfRevQty Price RevAmount OrderQty BalQty
Order1 70605001 150 10 1500 150
Order1 70605001 150 10 1500 200
Order1 70605001 150 10 1500 100
Order1 70620001 90 15 1350 150
Order1 70620001 90 15 1350 200
Order1 70620001 90 15 1350 100

Please correct the Query thus to get the below said correct result:


ProjectNo OrderNo ItemNo ItemName OrderQty Price Amount Sum of Rev Qty Bal order Qty
26030015 Order1 70620001 Intermediate Transom 2.5 mtr 150 15 2250 90 60
26030015 Order1 70625001 Pipe 48.3mm O.D x 4mm x 6m - 200 20 4000 0 200
26030015 Order1 70605001 Scaffolding Ledger - 1.2 mtr 100 10 1000 100 0


The required zip is attached.

Regards
Prabu
Attached File(s)
Attached File  PURCHASE_ORDER_QUERY.zip ( 226.42K )Number of downloads: 11
 
Go to the top of the page
 
projecttoday
post Mar 10 2020, 02:27 PM
Post#15


UtterAccess VIP
Posts: 11,811
Joined: 10-February 04
From: South Charleston, WV


Why are you summing by order number and item number? If an order has > 1 items in it, you will get sums for each of the items, no sum for the order.

To sum purchased and received by order:

SELECT OrderQuery.OrderNo, OrderQuery.Amount FROM OrderQuery
UNION ALL
SELECT RecvdQuery.OrderNo, -1 * RecdvQuery.Amount FROM RecvdQuery

Save as PurAndRecvdQuery

SELECT PurAndRecvdQuery.Order, SUM(PurAndRecvdQuery.Amount)
FROM PurAndRecvdQuery GROUP BY PurAndRecvdQuery.OrderNo

You can also sum in a report.

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Mar 10 2020, 03:55 PM
Post#16


UtterAccess VIP
Posts: 11,811
Joined: 10-February 04
From: South Charleston, WV


You might also use the tables in your union instead of the queries.

--------------------
Robert Crouser
Go to the top of the page
 
prabu
post Mar 17 2020, 01:07 PM
Post#17



Posts: 12
Joined: 8-April 18



Dear Sir,

Please help me to give t0 final balance order quantity at any way.

It may be a query or union all.

Regards
Prabu.
Go to the top of the page
 
FrankRuperto
post Mar 17 2020, 02:08 PM
Post#18



Posts: 809
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


I still dont see a downloadable db with sample data we can work with to help you. Can you please provide one?

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
projecttoday
post Mar 17 2020, 02:15 PM
Post#19


UtterAccess VIP
Posts: 11,811
Joined: 10-February 04
From: South Charleston, WV


There are 2 queries in my last post. Did you try them?

--------------------
Robert Crouser
Go to the top of the page
 
prabu
post Mar 22 2020, 05:04 AM
Post#20



Posts: 12
Joined: 8-April 18



dear Mr. Frank,

Please find the attached Doc. with Tables, Table Relation, Order query, Sum of Receive item query, cancel query.

The required calculation is Pending in receive qty = order qty - sum of receive qty.

+

Stock on hand by project wise.

Hope you will clear my requirement.

or

give me your mail id. I will forward the db by email.

Regards
Prabu.
Email. Prabu67@gmail.com
Whats app: 00965 - 99569610.
Kuwait.



Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    5th April 2020 - 06:24 PM