first date and last date of that 2 week pay period

[BBBryan]

Thanks for your help.
I will try this out tomorrow. I'm going to a meeting for the rest of the afternoon
I will let you know how I made out with this.


Thanks BBryan

 

[namliam]

Still dont really understand why you would want a function over a inline query calculation?

 

[BBBryan]

Hi JDraw,
After looking at you code. Is not quite what I was looking for.
I think I might not be clear in what I was trying to say.
In your first post you have a code to get the WeekdayName and date for every 2 week beginning 28 Dec 2013.
This is kind of what I was looking for but for every day of the year from a that specified startDate. (You have this code to get the just for the start days of the 2 weekperiod).
Is it possible to somehow code it to add another column for each day of the year.
Jan 01 2014 - Saturday 28/12/2013
Jan 02 2014 - Saturday 28/12/2013
Jan 03 2014 - Saturday 28/12/2013
and so on
Jan 11 2014 - Saturday 11/01/2014
Jan 12 2014 - Saturday 11/01/2014
and so on

I could make a form with the startdate and loopend date with out having to go into the code and change the dates for every new job.

Thanks BBryan

 

[BBBryan]

Hi Namliam,
You still could be right but but I don't want to have to go into the query and change it everytime unless there is another way.

Thanks BBryan

 

[jdraw]

I think you should try again to describe WHAT you need. Please try to separate What you need from HOW you think it can be done in Access.
Why do you need to know every day of the year?

My understanding, which is really a guess since we still don't have a clear statement of requirement, is you have Jobs which have pay periods. Pay periods are 2 weeks in duration. A Pay period may start on a Friday, or a Sat or a Sunday. So for Job x with pay period start Dec 28/2013 (a Saturday) I showed originally all Pay Periods.

In the table and functions I showed theFirstDayOfPayPeriod for a Job and the EndDate fora PayPeriod for a given Job. I was using the table to associate the Jobid with the StartDate and the LastPayEndDate. This approach allows you to have multiple Jobs with different startdates, but by using the function and the JobId, you know the LastPayPeriodEndDate and can calculate the FirstDayofNextActivePayPeriod, the LastDayof theNextActivePayPeriod and then you update the PayPeriodEndDate.

Perhaps I misunderstood, but still feel we do not have a good definition.description of your requirement.

 

[BBBryan]

Sorry, In the big picture the database is the time sheet, of hours, per day.Yes there is different jobs with different start dates and different Payperiods.

I need to know what pay period each day belongs to to make out the time sheets. In the query I want to be able to populate this PayPeriodLookup Qy By the new StartDate of new job.
Then I can use it to put the Dates in that pay period.
I have been filling this in manually.
Here is a smaller sample of my Database.

And thanks for your help I appreciate it.
Thanks BBryan

 

[jdraw]

Here is a smaller sample of my Database.

Nothing attached.

 

[BBBryan]

sorry It didn't load
I will have to make it file smaller. But can't at moment.


BBryan

 

[namliam]

So if I started on Wed Oct-01-2014, the 2 week period would be start on Sat Sept 27 2014.
For that job 1st payperiod would start Sat Sept-27-2014 to Fri Oct-10-2014. (calculated For each day Oct 1 till the 10th)
2nd pay peroiod would be Start Sat Oct-11-2014 to Fri Oct-24-2014.(calculated For each day Oct 11 till the 24th) and so on for that whole job.

The next job might be start on Mon Dec 8 2014. And the payperiod would be Start Sun Dec-7 -2014 to Sat Dec-15-2014. and so on.


Thanks Bryyan

This isnt exactly managable is it, the first starts on saturday, the second starts on sunday
Plus your samples isnt in synch with 28 dec 2013, two week periods around those times would start at 20 sept, 4 oct, 18 oct29 nov and 13 dec

What would be the objection of keeping the 2 week period and paying only a week in the first 2 weeks of a project? If you are meant (as per your OP) to start at 28 dec 2013 and have 2 weeks each time...

Taking this into concideration, is your rule:
When ever the start date of a project, my two week period always starts on the saturday before and so forth?
Or if that is not true, can you define your StartDate of the two week period in one sentence like I am trying to do here? That defination is the KEY here, get that definition right, you fixed the problem.

Using my definition from above, calculating the previous saturday (or sunday) is quite easy to do, but if you want to do that per project automaticaly you will have to include a query solution.
Assuming you have a table that holds the dates and the JobIDs ...

Select JobID, Min(WorkDate) as Startdate
from YourTable
Group by JobID

Save that as "qry_StartdatePerJob
You join this back to your normal table

Select  ....
,                                 Startdate- weekday(Startdate,1)                    as startSaturday
,   Workdate + (Int((Workdate - ( Startdate- weekday(Startdate,1) ) )/14)  ) * 14    as startofperiod
,   Workdate + (Int((Workdate - ( Startdate- weekday(Startdate,1) ) )/14)+1) * 14 -1 as EndOfPeriod
From YourTable
Inner Join qry_startdateperjob on Yourtable.jobid = qry_startdateperjob.jobid

 

[BBBryan]

Sorry I was too busy yesterday to look at this.
I thought I resent the database yesterday too.

Try again to send

Namliam,
I have the date and jobs in the same database.
Are you saying make another tbl and relate it to the date tbl.
It is a little over my head what to do.


Thanks BBryan

 

[namliam]

BBBryan....

No I am not saying create another table, I am saying create 2 queries....

When ever the start date of a project, my two week period always starts on the saturday before and so forth?

Is that true?? If not please provide a proper definition.

Looking at your database you are trying to make a date dimension table... which means my above definition is wrong and your samples are wrong. That would mean my original idea comes back

Try this query on for size...

SELECT 
  MstDates.MediumDate
, [mediumdate]+(#12/28/2013#-[mediumdate])+Int(([mediumdate]-#12/28/2013#)/14)*14 AS StartOfPeriod
, [mediumdate]+(#12/28/2013#-[mediumdate])+(Int(([mediumdate]-#12/28/2013#)/14)+1)*14-1 AS EndOfPeriod
FROM MstDates
WHERE (((MstDates.MediumDate)>=#12/28/2013#));

Just FYI, column names like Time, Day, Month, etc are functions (thus reserved words) in access, you would be better of using differen column names.

 

[BBBryan]

NamLiam,
Thanks.
"When ever the start date of a project, my two week period always starts on the saturday before" is NOT true. Because ever client has a different way they do there time and pay. It could be Sat,sun or mon. I will only know what their period are in the contract when I start a new job.
Also sometimes I have to send in a time sheet weekly but the invoicing is every 2 weeks that is why I need both date periods in my database.
I tried your Qy and it works good! I will be able to use this but I will have to change the date in this query ever time i start a new job and then copy and paste the new dates into the date form.


I was thinking there was a way to do it in a function. But One thing I don't have in my database is the startdate of that payperiod for that client.

In the big database - I have on the main form with a dropdown to look for the payperiod from that client to make the timeSheet. Maybe I could put the startdate of the payperiod and link it to the Qy so I don't have to go in a change the date in the Qy.
Any thoughts on that.

BBryan

 

[namliam]

Also sometimes I have to send in a time sheet weekly but the invoicing is every 2 weeks that is why I need both date periods in my database.

No you dont, and you dont strictly need the week period either. Both are calculated fields that are easy to calculate... in a well designed database we do not store such calculated values.

I tried your Qy and it works good! I will be able to use this but I will have to change the date in this query ever time i start a new job and then copy and paste the new dates into the date form.

No you dont, you need to -on job level- store what is your start date or the starting "rule" as well as the invoice period... Now you are working with 2 week periods what if some jobs have 3 week periods or 4 week or month or 1 week?

All comes down to getting the definition right and designing the database to fit the definition so you dont need to fiddle with things (too much)

In the big database - I have on the main form with a dropdown to look for the payperiod from that client to make the timeSheet. Maybe I could put the startdate of the payperiod and link it to the Qy so I don't have to go in a change the date in the Qy.
Any thoughts on that.

See above which I typed before reading the latter part

 

[BBBryan]

Thanks for your Help
And everyone else.
I appreciate it.

I think my database is not quite set up properly.
I going to think about re-designing my database.
The one thing I got out of this help is that - I need to know exactly what i want need to be able to describe it.


I may ask for some more help....

Thanks BBryan