Match columns with a sub query and return query as column?

Question

So write now I have two types of data in my DB Table "forecast" data and "Actual" data all the field for these rows are the same except for the 'Type' Field that indicates whether the data is a "forcast" or Actual and the Quantity field.

What I need to do is determine the accuracy of the forecast by dividing the matching Rows Quantity Field. T

So I would have this

Table Orders

Order No.   Delivery Date   Quantity(cases)     Type
1234         6/20/2014           100            Forecast
1234         6/20/2014            70            Actual

Then do a query that returns as Accuracy so something like...

SELECT Order No., Deliverydate FROM orders WHERE Order No. = "1234" then (SubQuery) As Accuracy

Query Result

Order No.   Delivery Date   Accuracy
1234        6/20/2014         70%

So the Sub query/queries need to match the forecasts then return the divided quantity as a new column.

Answer 1

This may be way after the fact...

Using the field names in the original question:

Select f.orderNo
, f.deliveryDate
, format(a.quantity/f.quantity, 'Percent') as accuracy
From (Select * from tbl_Orders where type='forecast') f
Inner Join (Select * from tbl_Orders where type='actual') a on f.orderNo = a.orderNo

This was done in MS Access 2010

Answer 2

So first I did a Join Called Central Query then created a query using that query.

So first I create two Queries, one for Forecasts (test) and one for just orders (Join-test)

So I join the two queries on the Deliver Date (RDD), Customer, and UPC and That is called from "Central Query"

Then I take the Quantities from Central Accuracy and Divide them here is the code.

Part 1 (there is another query that gets just signals (forecasts)

SELECT Customer, RDD, UPC, Shipto, Quantity, Type
FROM order_signals
WHERE Type = 'Order' AND RDD < NOW();

Use the two Queries to create "Central Query"

SELECT test.Customer, [Join-test].Customer, test.Quantity, [Join-test].Quantity, [Join-test].UPC, [Join-test].UPC, [Join-test].RDD
FROM test INNER JOIN [Join-test] ON (test.RDD = [Join-test].RDD) AND (test.Customer = [Join-test].Customer) AND (test.UPC = [Join-test].UPC);

Then I use the columns from central Query and I'm good!

SELECT [Central Query].[Join-test].Customer, [Central Query].UPC, [Central Query].test.Quantity, [Central Query].[Join-test].Quantity, Format(1-Abs(([Central Query].[Join-test].Quantity-[Central Query].test.Quantity)/[Central Query].test.Quantity),"Percent") AS Accuracy, [Central Query].RDD
FROM [Central Query];

Answer 3

I made a query for SQL server. Let me know if it works for MS Access -

select orderno, deliverydate, Forecast, [Order], 
convert(decimal(5,2), ([Order]*100.0) / Forecast)  as Accuracy
from
(
select orderno, deliverydate, quantity, [type]
from Orders
) as src
pivot(
max(quantity)
for [type] in ([Forecast],[Order])
)as pvt

For MS Access (what I have so far) -

I have never used MS Access. But, I tried to make the MS Access query using the example given here - TRANSFORM and PIVOT in Access 2013 SQL. Let me know if that works for you.

So your actual column names are different from what you gave. The queries based on those columns names are given below.

Create a query called queryPivot -

TRANSFORM Max(Quantity)
SELECT UPC, RDDto
FROM order_signals
GROUP BY UPC, RDDto
PIVOT [Type];

Create another query called querySelectFromPivot -

SELECT queryPivot.UPC, queryPivot.RDDto,
(queryPivot.Actual * 100.0/ queryPivot.Forecast) as 'Accuracy'
FROM queryPivot;

This is where I am stuck. It gives me blank values due to the missing values in your sample data. I tried to select just Actual * 100.0 instead, but that does not give me any decimal values. So, try to find out how to convert a column to decimal form. After that this query should work. (Man...access is so unintuitive for sql folks.)