RyanESmith7 Posted October 19, 2016 Share Posted October 19, 2016 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! Link to comment Share on other sites More sharing options...
comment Posted October 19, 2016 Share Posted October 19, 2016 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? Link to comment Share on other sites More sharing options...
RyanESmith7 Posted October 19, 2016 Author Share Posted October 19, 2016 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 Link to comment Share on other sites More sharing options...
comment Posted October 19, 2016 Share Posted October 19, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
RyanESmith7 Posted October 19, 2016 Author Share Posted October 19, 2016 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. Link to comment Share on other sites More sharing options...
comment Posted October 19, 2016 Share Posted October 19, 2016 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. Link to comment Share on other sites More sharing options...
RyanESmith7 Posted November 4, 2016 Author Share Posted November 4, 2016 Sorry for the late reply, Comment, but thank you... that worked! Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 2857 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now