VLookup for use in Access query

[AZGuy82]

I have been trying without successful and wondered if the Tek-Tips forum could help me put a query together in Access.

There are two tables I would like to use - TRANSACTIONS and VALUATIONS

TRANSACTIONS has Fund,TradeDate,ParValue
VALUATIONS has Fund, ValueDate, Price

I would like to create a query that outputs every day (ValueDate) between a specified range, even if the date does not exist in the VALUATIONS table. If it doesn't exist, it would bring the previous existing Price from VALUATIONS in the list.

The code I have can only generate a holdings if the VALUATIONS table has a ValueDate.

SELECT VALUATIONS.ValueDate, VALUATIONS.Fund, Sum(IIf([ValueDate]>=[TradeDate],[ParValue],0)) AS Holdingss, VALUATIONS.Price
FROM TRANSACTIONS, VALUATIONS
GROUP BY VALUATIONS.ValueDate, VALUATIONS.Fund, VALUATIONS.Price
HAVING (((VALUATIONS.ValueDate)>[Specify Start Date] And (VALUATIONS.ValueDate)<[Specify End Date]));

Could somebody suggest whether I could do this?

 

[trevil620]

Try this:

SELECT VALUATIONS.ValueDate, VALUATIONS.Fund, Sum(IIf([ValueDate]>=[TradeDate],[ParValue],0)) AS Holdingss, VALUATIONS.Price
FROM TRANSACTIONS LEFT JOIN VALUATIONS ON TRANSACTIONS.Fund = VALUATIONS.Fund
GROUP BY VALUATIONS.ValueDate, VALUATIONS.Fund, VALUATIONS.Price
HAVING (((VALUATIONS.ValueDate)>[Specify Start Date] And (VALUATIONS.ValueDate)<[Specify End Date]) Or (VALUATIONS.ValueDate) Is Null));

 

[AZGuy82]

Thanks for your response Trevil620,
I tried your suggestion and while it does work, it doesn't populate for values that don't exist in [ValueDate].
If it helps, here's an example of the output I was hoping for.

TRANSACTIONS Table

Fund   TradeDate   ParValue
ABC    06/05/2013  50
ABC    06/08/2013  80
DEF    06/08/2013  100

VALUATIONS Table

Fund   ValueDate   Price
ABC    05/31/2013  100
DEF    05/31/2013  100
ABC    06/04/2013  99
ABC    06/08/2013  95
DEF    06/08/2013  100

I was hoping to be able to run a query that, if I entered >=[06/04/2013] and <=[6/10/2013] would result in an output like this:

ValueDate   Fund  Par    Price
06/04/2013  ABC   0      100
06/05/2013  ABC   50     99
06/06/2013  ABC   50     99
06/07/2013  ABC   50     99
06/08/2013  ABC   130    95
06/09/2013  ABC   130    95
06/10/2013  ABC   130    95

06/04/2013  DEF   0      100
06/05/2013  DEF   0      100
06/06/2013  DEF   0      100
06/07/2013  DEF   0      100
06/08/2013  DEF   100    100
06/09/2013  DEF   100    100
06/10/2013  DEF   100    100

Hope that's more helpful.
Thanks very much

 

[trevil620]

I'm afraid I don't understand the relationship between the tables and the values you used as an example.
1. In the VALUATIONS table there are two records that have dates outside your selection range, thus your SQL would not be selecting them.
2. Your desired result shows two groups of 7 records each, thus 14 rows. Not sure how that can happen even if you select all rows.
Please explain what the relationship between the two tables is supposed to be.
Yes, you could use a UNION query to get everything from both tables combined, but not sure until I understand better.

Sometimes computer problems are like genealogy... The answer to one problem leads to two more!

 

[PHV]

even if the date does not exist in the VALUATIONS table
I'd use a tabla with all days (date) as a master table which left join the others.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AZGuy82]

I'll do my best to answer .. hopefully this makes sense, like it does in my head!:

1. In the VALUATIONS table there are two records that have dates outside your selection range, thus your SQL would not be selecting them.

Yes you're right it wouldn't select them, I was hoping what the query would do would be to look up each date in the query criteria (i.e. >=06/04/2013 and <=06/10/2013) against the dates in the VALUATIONS table to identify the most recent price, like a Vlookup in Excel would. So for ABC because there was a Price of 100 in VALUATIONS on 05/31/2013, the 06/03/2013 price it would find would be 100 as that was the most recent price, while the price on 06/04/2013 would be 99 because there was a more recent date/price value in VALUATIONS for ABC.

2. Your desired result shows two groups of 7 records each, thus 14 rows. Not sure how that can happen even if you select all rows. Please explain what the relationship between the two tables is supposed to be. Yes, you could use a UNION query to get everything from both tables combined, but not sure until I understand better. ]

I was thinking that because the query criteria specified >=06/04/2013 and <=06/10/2013, there would be 7 date outputs. And because there were two Funds (ABC and DEF) if the query output grouped by Fund and Date, it would generate 14 results.


As further background, the VALUATIONS table would have at most one Price for each Fund in a ValueDate. Some days there might be prices, but on days that there are no prices the previous day's price would apply.
The TRANSACTIONS table, meanwhile, may have any number of transactions on a Fund on a TradeDate, buying the ParValue amount (e.g. could be two identical trades on the same day)

The query is trying to do two things in one go - it's trying to generate by date every fund's parvalue and every fund's most recent price on that date. So by providing a criteria of >=06/04/2013 and <=06/10/2013, ideally the query would generate each day as a row, and then use TRANSACTIONS to sum up the ParValue of each Fund if its TradeDate is less than that day. It would also concurrently look up that day under VALUATIONS and identify the most recent price on that Fund.

Hopefully that helps clarify a bit?

I'd use a tabla with all days (date) as a master table which left join the others.

I tried creating a master table and left joining but because there is not a one-to-one relationship between the master table's dates and the dates in the VALUATION table, it simply left it blank.

Thanks very much for your quick replies.

 

[trevil620]

So, it appears there is really NO direct relationship between the two tables. It appears that "TRANSACTIONS" are exactly as the name implies - STOCK Transactions. Then "VALUATIONS" table seems to be just 'historical quotes' for stock on a given day.
You should heed what PHV said and create a table of dates. Once you do that, you can grab the most recent VALUATION, plus show transactions.

Sometimes computer problems are like genealogy... The answer to one problem leads to two more!

 

[AZGuy82]

Once you do that, you can grab the most recent VALUATION, plus show transactions.

For the grabbing most recent VALUATION part, how would you suggest that be done? A left join wouldn't come up with a result if there are no corresponding dates.

 

[trevil620]

I think that was PHV's idea - create a table with a row for each date in the range you want to process.

Sometimes computer problems are like genealogy... The answer to one problem leads to two more!