ranking (pls refer to previous post 1/9)

[carol]

ok, i have tried to simulate the same sql using two joined
tables. can't get it figured out. need to have a final
ranking field in a new table created by query. can you
help me out!! here's the code. thx.

SELECT [ITEM DATA].[PFG#], [ITEM DATA].ITEM, [ITEM DATA].
[VENDOR#], [ITEM DATA].PACK, [ITEM DATA].[CASE LOT], [ITEM
DATA].CATEGORY, [ITEM DATA].[TOTAL CASE OZ], [ITEM
DATA].SIZE, [ITEM DATA].[ITEM STATUS], [ITEM DATA].[SET UP
DATE], [ITEM DATA].SUBCATEGORY, [ITEM DATA].[CATEGORY#],
[ITEM DATA].BUYER, [ITEM DATA].[MFG#], [Item Pricing].
[start date], [Item Pricing].Price, [Item Pricing].[end
date], [Item Pricing].open, [Item Pricing].[open date],
[Item Pricing].[contract sent], [Item Pricing].[contract
recd]
(SELECT COUNT(*) + 1 FROM currentpricing2
WHERE item pricing.pfg#=currentpricing2.pfg#
and currentpricing2.startdate<item pricing.startdate) as
rank
FROM [ITEM DATA] LEFT JOIN [Item Pricing] ON [ITEM DATA].
[PFG#] = [Item Pricing].[PFG#]
into currentpricing2
ORDER BY [ITEM DATA].[PFG#], [Item Pricing].[start date]
DESC;

 

[Michel Walsh]

Hi,


It seems you are missing [ ] around the table name Item Pricing ( 2
places).

It didn't look any further to see if there was something else.


Hoping it may help,
Vanderghast, Access MVP