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]

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

Could you show us the query you are working with for your current results, and the query you are suggesting for last year's corresponding recordCounts?

 

[nebula13]

Ill do my best to clarify:
The table "Daily_Volume" is has 2 relevant fields: LogDate, Volume. This table is populated with a daily volume amount for every business day going back 2 years (there are about 260 records each year).

My query (see below) simply needs to add another column which matches 2009 volumes with their corresponding 2008 volume (according to the number of records since the begining of the year), so a reccord from 2/15/2009 will have a corresponding record from 2008 (within a few days of 2/15/2008). To do this I have created a column that counts the number of records for each year.

Im not sure if the code will help, but here it is:

SELECT Daily_Volume.LogDate, tbl_Daily_Volume.Volume, (DCount("[Volume]","[tbl_Daily_Volume]","Format(LogDate,'yyyy') = '" & Format([LogDate],'yyyy') & "' AND [LogDate]-1 < #" & [LogDate] & "#")) AS Yearly_Count
FROM tbl_Daily_Volume;

I know this is a job better suited for Excel, but I need to do it in an Access query. Any feedback is greatly appreciated!

Thanks again,
Ben

 

[nebula13]

Any ideas?

Thanks!
Ben

 

[jackd]

Any ideas?

Thanks!
Ben

Ben , I'm not sure what you are showing us. For example your query

SELECT Daily_Volume.LogDate, tbl_Daily_Volume.Volume, (DCount("[Volume]","[tbl_Daily_Volume]","Format(LogDate,'yyyy') = '" & Format([LogDate],'yyyy') & "' AND [LogDate]-1 < #" & [LogDate] & "#")) AS Yearly_Count
FROM tbl_Daily_Volume;

indicates 2 different tables
Daily_Volume and tbl_Daily_Volume
Running this query would give an error.

at one point you wanted to use DLookup now it's DCount?

Is this just an exercise or is it really something useful to your work?

 

[nebula13]

Hi Jack,
This is directly related to a time sensitive project I am working on. The query I posted is the Daily Volume query, which is based on tbl_daily_volume. I used the count field to create a parameter for the dlookup function I would like to use to create a new field which automatically associates daily revenue from last year with this year. I figured the count field might be useful, since you cant just assume that if there was an entry on 12/15/2009 that there would be an entry on 12/15/2008. instead a more useful comparison would be to find the 260th entry (count) in 2009 and associate it with the 260th entry in 2008.
Since I have to show some results, I am doing this manually now by copy and pasting 2008 revenue side by side with 2009 revenue. However in the near future I will have to automate this process.

Hence, it would be a massive help if I could figure out how to use the dlookup function that uses the year and the year to date record count as parameters for finding last years volume. I just cant seem to figure out how to make it work. Please let me know if there is anything else I can clarify

All the best,
Ben

 

[jackd]

Hi Jack,
This is directly related to a time sensitive project I am working on. The query I posted is the Daily Volume query, which is based on tbl_daily_volume. I used the count field to create a parameter for the dlookup function I would like to use to create a new field which automatically associates daily revenue from last year with this year. I figured the count field might be useful, since you cant just assume that if there was an entry on 12/15/2009 that there would be an entry on 12/15/2008. instead a more useful comparison would be to find the 260th entry (count) in 2009 and associate it with the 260th entry in 2008.
Since I have to show some results, I am doing this manually now by copy and pasting 2008 revenue side by side with 2009 revenue. However in the near future I will have to automate this process.

Hence, it would be a massive help if I could figure out how to use the dlookup function that uses the year and the year to date record count as parameters for finding last years volume. I just cant seem to figure out how to make it work. Please let me know if there is anything else I can clarify

All the best,
Ben

Ben,
I'm not sure if DLookup would work.. I don't use it enough to tell for sure.
If it were my issue, I might try a few things.

Perhaps, you could build an array for last year and one for this year.
Populate with last year's data fields, and this year's up to today.
That way your array pointer,could point at the 200th entry for each year.
It would take some vba to do it, but it is easily reproducible.

It maybe that someone knows how to use the DLookup with multiple recordsets, but I don't.

Why is your Volume field defined as datatype Currency????
What time frame are you working with?

 

[nebula13]

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

 

[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

I have mocked up a table Daily_Volume with fields logDate and Volume.

What is your query name and structure.

 

[nebula13]

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