Find the week before calculation

[RyanESmith7]

I have a table that is a log of weekly records, and when I'm presenting the current record, I need to be able to pull data from the week before, so I'm currently using a calculated field with a reference to This Week Start Date - 7. But every now and then, the weeks are not a week apart. So what's the best way to find the Previous Week Start Date when it isn't an exact 7 day match?

Thanks!

[comment]

I don't understand your question. Are you trying to perform a find? If so, which records do you expect to find? And why would you need "a calculated field" for this?

[RyanESmith7]

Sorry, I should explain further...

Table: WeeklyLog
WeekStart(Date Field)
PrevWeekStart (Calculated Field, returns Date, Calculation = WeekStart-7)

Then I have a table occurence PreviousWeekLog_TO that is setup like this:

Then on one of my report layouts I do some calculations between the two tables to show changes from week to week in the data. 
This works perfectly when the two weeks are 7 days apart, which is 90-95% percent of the time. However, sometimes a client will start a new WeeklyLog 8-n days after the PreviousWeekLog->WeekStart. What I would like to do is make my calculated field "PrevWeekStart" be more dynamic. A Client will log for anywhere from 6-36 weeks of data, so I'd like to find the previous WeeklyLog record that the client had (in date order) rather than just assume it was 7 days prior.

This scenario works:
Record 1, WeekStart = 10/1/16
Record 2, WeekStart = 10/8/16

This scenario does not work:
Record 1, WeekStart = 10/1/16
Record 2, WeekStart = 10/9/16

[comment]

If I understand this correctly, the WeekStart field should be a Date field, with auto-entered calculated value =

Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 1

This will automatically enter the date of the Sunday of the current week (i.e. the week in which the record was created).

To fix existing records, replace the field's contents with a similar calculation, using WeekStart instead of Get ( CurrentDate ). Make sure you have a backup before you do this, as there is no undo.

 

28 minutes ago, RyanESmith7 said:

I'd like to find the previous WeeklyLog record that the client had

What you show is a relationship, not a find.

 

Edited October 19, 2016 by comment

[RyanESmith7]

Yes, I am looking for a relationship, not a find. My apologies for the confusion.

I can't use the first day of the week because the client can start their tracking on any day of the week they want AND they can even skip weeks of tracking on occasion. So I'm not actually looking for Sunday, I want the record (from the same table, via table occurrence) that is just before this record. 

For example, I would want it to return something like this:

My current calculation is correct except for that RED one in the example above. Because the user skipped 15 days between logging. So I'm trying to figure out the WeekStart of the record before.

Thanks for spending so much time trying to understand me... sometimes it's hard for me to convey my issues in writing.

[comment]

10 minutes ago, RyanESmith7 said:

I want the record (from the same table, via table occurrence) that is just before this record. 

Then you should define the relationship as:

WeeklyLog::WeekStart > PrevWeekLog::WeekStart

and sort the records on the PrevWeekLog side by WeekStart, descending. This will make the most recent previous log the first related record of each log. No calculation fields are required for this.

[RyanESmith7]

Sorry for the late reply, Comment, but thank you... that worked!