Unable to show previous years Volume (DLookup function)

[nebula13]

Hello everyone,
This is the 3rd issue I am posting on this forum and have so far been extremely grateful with the quality and speed of responses. Here is my latest issue:

I have a table, "Daily_Volume", that has 3 fields "LogDate" (dd/mm/yyyy), Volume (Currency), and RecordCount (Count of year to date entries)

I have a query that calculates the running total and average for month to date. I need to create another field which is able to lookup the volume numbers from the previous year and display them next to the volume numbers for the current year. Since dates change each year, it would suffice to show the 2008 volume next to the 2009 volume based on the record count field (e.g. by matching the 20th record of 2008 with the 20th record of 2009).

I figured I should use the Dlookup function where record count is the same and the year is Year(LogDate)-1. However I cannot get this code to work. Maybe I should also be using a DateAdd function? Please let me know if you need any further clarification.

Any ideas would be greatly appreicated! I thank you all in advance and wish you happy holidays!

Ben

 

[jackd]

I mocked up your table and populated it with some dates and volumes for 2008 and 2009.

Created queries to
Select the data for 2008 and 2009 separately, and added a field MyYear showing which Year the data represents.

Created 2 new tables, based on the queries.
VolumeData2008, VolumeData2009
In each table I added a new field called RecordNum (autonumber)
to give a unique recordNum in year years volumedata.

Can now query these 2 tables to show
Volume(2008),LogDate(2008),MyYear(2008), Volume(2009),LogDate(2009),MyYear(2009)

This query is

SELECT VolumeData2008.volume
, VolumeData2008.MyYear, VolumeData2008.logDate
, VolumeData2009.volume, VolumeData2009.MyYear
, VolumeData2009.logDate
FROM VolumeData2008 , VolumeData2009
Where VolumeData2008.RecordNum = VolumeData2009.RecordNum;

This presents your data side by side.

I'm sure we could do more if I had your queries.

Results based on my sample data:

VolumeData2008.volume VolumeData2008.MyYear VolumeData2008.logDate VolumeData2009.volume VolumeData2009.MyYear VolumeData2009.logDate
10 2008 1/2/2008 7 2009 1/4/2009
10 2008 1/3/2008 12 2009 1/5/2009
20 2008 1/4/2008 31 2009 1/6/2009
32 2008 1/5/2008 23 2009 1/7/2009
21 2008 1/7/2008 24 2009 1/10/2009
25 2008 1/8/2008 21 2009 1/11/2009
30 2008 1/9/2008 19 2009 1/12/2009
17 2008 1/10/2008 18 2009 1/13/2009

Note: The Dates do not align exactly, but that is the function of RecordNum

 

[jackd]

Here is an example of the underlying table, "Daily_Volume", assuming that there is data for every month starting in 2008 (I am using january as an example)

LogDate Volume
1/1/2008 ---$5
1/3/2008 ---$10
2/5/2008 ---$3
1/2/2009 ---$4
1/4/2009 ---$11
2/4/2009 ---$7


The field I am trying to create (LastYearsVolume)would simply identify which 2008 volume corresponds to each date this year. So the output might look like:
LogDate ----Volume ---LastYearsVolume
1/2/2009 ------$4 -------$5
1/4/2009 ------$11 ------$10
2/4/2009-------$7 -------$3


The reason I am not posting the code is because there are a bunch of other variables that would make it complicated to understand the specific issue I am trying to resolve

Thanks,
Ben

Yes I understand there may be more complications with the other fields.

Why is Volume defined in terms of $? Is this Sales or something?
Take a look at my last post re RecordNum, I think that is key to your procedure.

Here's a sample more in your format:

LogDate ThisYearVolume LastYearsVolume
1/4/2009 7 10
1/5/2009 12 10
1/6/2009 31 20
1/7/2009 23 32
1/10/2009 24 21
1/11/2009 21 25
1/12/2009 19 30
1/13/2009 18 17

 

[jackd]

I just need to figure this out, the sooner the better. Until then, I will manually update the root table to use 2009 dates with 2009 volumes and match them with 2008 volumes.

I tried having 2 seperate queriest for 2008 and 2009, but this breaks down once I apply running averages (I am trying to keep it as simple as possible).

I appreciate any help, but I understand if this issue is more complicated than it seems. My VBA skills are not quite on the level of figuring this out without spending a great deal of time.

Thank you for taking the time to consider this issue

Ben

Hey Ben,

Are you still working on this or did you give up?

jack

 

[nebula13]

Hi Jack,
I am sorry for the delay, I have not had a chance to respond. I really appreciate you spending so much time on this issue. My original solution was to create 2 side by side queries for 2008 volume and 2009 volume, but I wanted to simplify and automate the process of identifying what last year's volume was.
For the time being, I am just using seperate columns for the year. I will look at the code you sent and see if it might help, but either way i just wanted to thank you again for your help. It is very nice of you!
Regarding your question, I am reporting on high volume trading activity which is measured in US Dollars.

all the best,
Ben

 

[jackd]

No problem. Take a look and we can exchange ideas then.

 

[nebula13]

I have another issue which I just posted about where you might have some input. I need to create a function which takes the average of the last 5 volume numbers and displays it in a report. If you are willing, the post is here: http://www.mrexcel.com/forum/showthread.php?t=436660

As a wise falafel stand owner once told me, "Thank you for you"

Ben