Justin C Justin C - 10 days ago 5
SQL Question

Creating a table with one of the column values from an expression

I'm trying to create a table that I can use for Accounts Receivable. It's a very simple table, needing only the total cost of an order, how much has been paid into the order, and the order number itself. I'm trying to adapt an existing query used for report generation to do so.

SELECT DISTINCTROW
Round(Sum(nz([Quantity]*[UnitPrice]*(1-[Discount])*100)/100)+
[RushCharge],2) AS TotalCost,
[Sum Of Payments Query].[Total Payments],
[Order Details].RushCharge AS RushCharge,
Orders.OrderID,
Orders.Cancel,
Orders.PriceQuote
INTO test2
FROM Orders
LEFT JOIN [Sum Of Payments Query]
ON Orders.OrderID = [Sum Of Payments Query].OrderID
GROUP BY Orders.OrderID,
Orders.Cancel,
[Sum Of Payments Query].[Total Payments],
Orders.PriceQuote


The issue is the age of the DB, where the total cost of an order is always dynamically generated instead of being stored somewhere (Even though there is only one form that alters it, but still), meaning I have to resort to the same. The Round function calculates the total cost of the order, and it works elsewhere, but here, it just prompts me for values instead of pulling them from Orders.

What am I doing wrong? I know it has to be something simple.

Answer

I am guessing that [Quantity], [unit price] and [Discount] and RushCharge are fields in [order details] table, which is not included in the query.

you could create a query that returns the totalcost per order

select orderid,
       Round(Sum(nz([Quantity]*[UnitPrice]*(1-[Discount])*100)/100)+
            [RushCharge],2) AS TotalCost from 
from [order details]
group by orderid

and include this in your query in the same way you have [sum of Payments Query]

Comments