Append Query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Query with:
Date (date audit was done)
Pass (# good audits)
Fail (# failed audits)

I need to create a table/query that holds a :
Week-ending date
totPass (total audits for that week that passed)
totFail (total audits for that week that passed)

The user would input a date and how many weeks he would like to look at and
the table/query would hold the information so we could make a chart showing,
say the last four weeks (4 entries).

I'm not sure how to get a week-ending date. I can make an Append Query
showing the total pass fail audit for the week but not sure how to add a
week-ending date. I want to be able to say set a start_date and then the
number weeks and have it fill a table with the weekly data, but I am open to
any ideas. I'm not a stranger to vba(excel) but I am just now learning Access
VBA.
 
Assuming Friday is the last day of the week, here is a function that returns
the Friday of the week for the date passed:

Function WeekStartDate(BaseDate As Date) As Date
'Dave Hargis 9/04
'Returns the last date of the accounting week for the date entered)

WeekStartDate = DateAdd("d", vbMonday - DatePart("w", BaseDate), BaseDate)
End Function

Then, If you are going to want several weeks, then do a loop and add a week:

dtmWkEnd = WeekEndDate(Date)
For intWkCount = 1 to intNumberOfWeeks -1
'Do whatever with this weeks end date here
dtmWkEnd = dtmWkEnd + 7
Next intWkCount
 
Back
Top