Hello!
(I can e-mail examples of what I am going from and want to get to if it will help.)
I have a table that holds information about our designers projects. This tables holds the Job#, Date in, Due Date, Description, Customer, Estimated Design hours, Actual Design Hours, Checker, & Date Completed. What I need to do is make a table or query off of this that will group this information by the week and total the estimated hours & actual hours. I can get the totals if someone can help me get the data grouped by calendar weeks. Is this possible? I have never grouped data in this manor in Access.
Thanks in advance for any help you can give me.

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

I suppose you could use the datediff()function and mod() functions to determine the day of the week is relative to a specific different day - ex how many days away from the most recent monday mod(7) will tell you when the week starts and ends, its a vague answer but I think it could work, dont really have time to work on it now

I googled the functions datediff()

http://livedocs.macromedia.com/coldf...s/functi58.htm

The syntax for the DateDiff function is:
DateDiff("datepart", "date1", "date2")

datepart - String specifying the units in which to count; for example yyyy requests a date difference in whole years.

yyyy: Years
q: Quarters
m: Months
y: Days of year (same as d)
d: Days
w: Weekdays (same as ww)
ww: Weeks
h: Hours
n: Minutes
s: Seconds

date1 - beginning date to be measured, in the range 100 AD-9999 AD.

date2 - ending ending date to be measured, in the range 100 AD-9999 AD.



and mod()

The syntax for the Mod function is:
Mod( number, divisor )

http://www.asp101.com/articles/steven/mod/default.asp
http://www.techonthenet.com/excel/formulas/mod.php


-Measure x number of days from the first monday of the year, mod will determine what day if the week it is, it can then be determined when the beginning and end of the week is using math. You can query based on specific day range once you know when the beginning and end of the week is, totals can beaccumulated and reset based on how many days or weeks from the starting point.

Im a little busy to provie an example but Im sure this will get the creative minded to provide a working example, if not i could be persuauded to provide a demo this weekend, send me an email

[email protected]

cheers
scott

There is a WEEKNUM function in Excel, but I can't seem to get it to work, and the help just doesnt.

It is supposed to work like:

= weeknum(dttmYourDate, 1)

The 1 can be replaced by a 2, sets the start point apparently, but... it just throws an error in Excel for me... if it works for you, then maybe add the Excel reference and use this function?

The internet says something about a valuepak or add-in for it, but I'm on 2k3 and it still doesnt work!? Probably a business decision that has been made soemwhere or other, maybe different where you are?

Food for thought...

Lee

Hi,

   This can be a problem if the project starts in one week, and ends three weeks later, with only a total hours, and not broken out in a sub table for the job that shows actual hours worked per week. This is the better way to do it.

   That being said, add a column to your query called "Week" and then put this in the expression line:

Week:(DatePart("ww", [Date In]))

This will return a number that is the number of the work week in the calendar year.

Typically you might do something like this on a report, however:

Dim dtIn, dtComplete As Date
Dim lHours, lAvg As Long
Dim iWeeks As Integer

dtIn = Me.Date_in
dtComplete = Me.Date_Completed
lHours = Me.Actual_Design_Hours
iWeeks = DatePart("ww", dtIn) - DatePart("ww", dtComplete)

lAvg = lHours / iWeeks

'Then attribute the average hours per week on a project to each week from the dtIn to the dtComplete.

You can see the problems with not using an actual hours worked per project subtable.

HTH








mmcdonal

Thank you for your replies. I have used the first idea given and it works perfectly. The project not ending in the same week that it started was a problem, but I got around it by creating a query that showed how many hours came in each week, then another query showing how many hours were completed each week. Then I just joined them with a union query. Worked wonderfully!

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!