Formula needed in query

[Leesha]

I have a query that contains [freq], [duration] [dur_unit], [start_date] and [end_date].

[freq]*[duration] = the total number of visits that are completed between the [start_date] and [end_date].

I need to be able to calculate the number of visits / week ending date (week ending dates are Fridays). In the table that I’ve uploaded, row one works fine in that [freq]*[duration] would come out to 2 vists / week.

Row two is where I have the problem. [freq]*[duration] comes out to 4 visits. My problem is that I need to somehow have each of the visits come up as a separate row for each week or [dur_unit]. The result should look something like this:

Query1
min_freq freq duration freq_unit dur_unit int_unit start_date end_date
2 2 1 WK W 3/27/2010 4/2/2010
1 1 1 WK W 3/26/2010 3/26/2010
1 1 1 WK W 3/27/2010 4/2/2010
1 1 1 WK W 4/3/2010 4/9/2010
1 1 1 WK W 4/10/2010 4/16/2010
WK W

Row 2-5 shows how the original row 2 would look if it were broken down from 1 row with 4 W duration to 4 individual rows with 1 week duration each. Notice that the start and end date ranges are the same in the first of the 4 new rows. This is because there are not 7 days in the week. This is because the original start date is 3/26/2009. Since this is a Friday, it is the week ending date. Therefore the [end_date], which needs to be a Friday for each dur_unit, is the same as the [start_date].

Figuring out this formula as WAY out of my league. Is it even possible?

Thanks,
Leesha

[HansV]

A formula cannot conjure records out of nothing. You'll need to create a table that lists the Fridays.
I created a list of all Fridays in 2010 in Excel and imported it into your database as tblFridays.
You can then create a query based on tblCompliance and tblFridays. The tables aren't linked directly, but through the condition that the Friday field is between start_date and end_date.
The Friday field is used as the end of each weekly period; the start is 6 days before that, or start_date, whichever is later.
See the attached version.

TestLeesha.zip

[Leesha]

Hi Hans!

All I can say is WOW! I'm still trying to digest how it works. I "get it" if it was only one week but I don't understand what makes it keep calculating more rows till the end date is reached.

In testing it to try to figure it out I changed the duration to 6 and extended out the end date. I noticed that if the end date is not a Friday, then it cuts it out. For example, if I enter in a 6 for duration but make the end date 4/28/2010 that 6th week is not included, but it should be. Is there a way to work around that?

Also, since the week ending date range could feasibly be in a year other than 2010 I'll need to be able to have the correct weekending dates. Is there a way to to update tblFriday to have the correct Week ending dates based on the date range in the report?

Thanks!
I'm sooooooooooooo impressed,
Leesha

[HansV]

Because of the condition Between [start_date] And [end_date], the query will include all Fridays that fall within the period.

There is no easy way to generate the list of Fridays on the fly, so if you need to provide for other years than 2010, simply expand tblFridays. It's easiest to generate the dates in Excel since it has an option to fill a range with a series of values, then import into Access. If you create a list of all Fridays from, say, 2000 to 2020, you should be OK for some time. With a bit over 1,000 records, it will still be a very small table for Access.

You originally stated "the [end_date], which needs to be a Friday for each dur_unit", so I assumed that you always wanted to end on a Friday. If the end of the period can be another day of the week, you'll need a formula for that too. See the attached version.

TestLeesha.zip

[Leesha]

Hans! This is perfect. You have no idea how much I appreciate it!! I wish I could say I understand why its working, but hopefully that will come with time!

Thanks!
Leesha

[Leesha]

Back again. Still working on the same report. I'm using a formula for determining the weekending date that you gave me years ago and has always worked. The week ending date is always Friday. The formula is:

WEDate: [Care_date]+6-Weekday([Care_date])

One of the dates for [care_date] is 3/20/2010. The WEDate that is coming up is 3/19/2010. I don't understand why its pulling up this date instead of 3/26/2010. As far as I know this formula has always worked and I've used it frequently. When I calculate weekday([care_date]) on its own, it comes up as a 7. I would think it would be a 1 since its the first day of the week. What am I missing?

Leesha

[JohnH]

The weekday function accepts a second argument, that determines what you want the first day of the week to be. By default Sunday is the first day of the week, so

Code: Select all

Weekday(3/20/2010) =7

But

Code: Select all

Weekday(3/20/2010,7) =1

as this treats Saturday as the first day of the week

[HansV]

John has already answered your question. If you look at the query in the latest version database that I attached, you'll see that the criteria for the Friday field is

Between [start_date] And [end_date]+7-Weekday([end_date],7)

The expression [end_date]+7-Weekday([end_date],7) calculates the first Friday on or after end_date. The reason for Weekday(..., 7) is as explained by John.

[Leesha]

Thanks to both of you! The explanations help me to understand it better!!

Leesha

[StoneChucker]

John has already answered your question. If you look at the query in the latest version database that I attached, you'll see that the criteria for the Friday field is

Between [start_date] And [end_date]+7-Weekday([end_date],7)

The expression [end_date]+7-Weekday([end_date],7) calculates the first Friday on or after end_date. The reason for Weekday(..., 7) is as explained by John.

Hi all,

Please forgive me, but I'm not a strong Access developer. I think I'm looking at what I need, but I'd like some help to confirm.

I want to create a query that looks for all "expired/expiring" accounts in the current week. A week is Sunday - Saturday.

If I use Between Now()-Weekday(Now()) and Now()+7-Weekday(Now()), does this let me see everything between Sunday and Saturday? Please note, I want to be able to run this at any point between Sunday 00h00 and Saturday 23h59, noting that it will usually be run on Monday, or a Tuesday following a holiday Monday.

Thanks.

[HansV]

At the moment I write this, it's Tuesday the 1st of June, 18:05. The expression you mention would select everything between Sunday the 29th of May, 18:05 and Sunday the 5th of June, 18:04. Is that what you want?

[StoneChucker]

At the moment I write this, it's Tuesday the 1st of June, 18:05. The expression you mention would select everything between Sunday the 29th of May, 18:05 and Sunday the 5th of June, 18:04. Is that what you want?

Thanks for responding Hans. That's almost what I want. I'm looking for Sunday 00h00 to Saturday 23h00. So, that is to say, if I run it on Sunday, or Wednesday, or late Saturday night, everything from Sunday midnight to Saturday midnight needs to be collected.

[HansV]

Try

Between Date()-Weekday(Date()) And Date()+7-Weekday(Date())

The Date() function returns the current date, midnight. So for today, the expression is equivalent to between 29-May-2010 0:00 and 05-Jun-2010 0:00.

[StoneChucker]

Thanks Hans, that was close to what I needed.

I've ended up using Is Null Or Between Date()+1-Weekday(Date()) And Date()+7-Weekday(Date()) (wanted blanks included also), as the expression you suggested was giving the Saturday (May 29) to Saturday (June 5).

Thanks for your assistance.

[HansV]

Thanks for catching my mistake!

By the way, does the expiration field contain only the date, or the date+time?

[StoneChucker]

The field only contains the date.

[HansV]

OK, in that case the expression you posted will do exactly what you want.