SELECT statement subquery

[Lisa Jones]

Hello,
I hope one of you gurus out there can help!
I have a table similar to this:

Part Qty UnitCost ExtendedCost Period
123a 2 .25 .50 2003-05
123a 2 .23 .46 2003-04
123a 2 .20 .40 2003-03
456b 10 .30 3.00 2003-05
456b 8 .30 2.40 2003-04
456b 8 .29 2.32 2003-02

I want to create a query that separates this data
into "Prior" and "Current" based on input from the user.
Here is how I want the query to look:

Part PriorQty CurrQty PriorUnit$ CurrUnit$
123a 2 2 .23 .25
456b 8 10 .30 .30

I tried using the SELECT statement as follows in each
field:

PriorQty: (SELECT [Qty] FROM

WHERE [Period]=[Enter
PriorPd as YYYY-MM].)

Usually this gives me a message that "No more than one
record will be returned." I tried adding the ALL
predicate. Still didn't work.

I need this Query to create a report that will show Prior
month data, Current month data, and a Variance. I don't
want to dump this into Excel, but I've been working on it
for three days now and nothing I'm doing in Access seems
to work.

Thanks for any ideas!
/Lisa

 

[JL]

It probably will take more than one query to do the job.
1) Create a query that will contain the current (sort them
by part (asc) and Period (dsc), and use first). It will
get the Lastest Period.
2) Create another query and exclue the record that you get
form the first query. Then do the same as first query
(sort them by part(asc) and Period (dsc), and use first).
It should give you the second Period.
3) Join the first query and labeled them (Prior and Curr).
Hope this will help.

 

[John Spencer (MVP)]

The following UNTESTED SQL statement MIGHT work.

SELECT Current.Part, Current.Qty, Current.UnitCost, Prior.Qty, Prior.UnitCost
FROM Table as Current INNER JOIN Table as Prior
ON Current.Part = Prior.Part AND
Current.Period = Format(DateAdd("m",-1,DateValue(Prior.Period &
"-01")),"yyyy\-mm")
WHERE Current.Period = [Enter Period]

Watch out for line wrapping in the ON Clause. Also, be aware that this will not
return any record if there is no Prior period for the part. For instance, if
you were trying to return Part 456b for 2003-04 period there is no corresponding
2003-03 record. If that is a consideration, then this can still be done, but
the query becomes more complex.