Queries

[Jim W]

I have a table that holds records based on a StartDate. Is there away that I can query this table and either make a new table or update an existing table and add a new field to the table based on the StartDate of the record.

Table 1 has

StartDate - and several other fields
11/11/03 - x x x x x x x x x x x x x other data

now when I query this table I want to make a new table or update an existing table so the new table would look like this

Week StartDate - and several other fileds
45 11/11/03 - x x x x x x x x x x x x x other data

the week 45 would come from another table where I define Week 45 as 11/10/03 - 11/16/03

so any record in table 1 that has a startdate of 11/11 to 11/16/03
would get a week 45 added to table 2

can this be done? if so I could really use some help

Thank you

Jim

 

[directormac]

now when I query this table I want to make a new table or update an existing table... so any record in table 1 that has a startdate of 11/11 to 11/16/03 would get a week 45 added to table 2

Out of curiousity, what are you wanting to accomplish by this? It looks suspiciously like you want to get a list of records in "table 1" sorted by the week number, or use the week number as a selection criteria.

You could get that result much easier by creating a select query based on "table 1", then adding a column where the "field" row is WeekNum: format([StartDate],"ww").

Just a thought... If that's not helpful (which is likely), shout out...

--Fishing Mac

 

[Jim W]

Here is a bit more detail of what I need to do.

I have a dbase that tracks down equipment. User's enter service calls into the dbase every time they work on a system. I have this part of the dbase working very well so now is time to move to the next step. I now want to build charts for system performance. The 1st one is Availability. The equation is Weekly Availability = TotalHrs - DownHrs / TotalHrs * 100. Now I have this working however its not quite right. It works as long as a system that goes down come up in the same week. If it roles into the next week I can't capture this so my hours don't add up.
If it roles over to next week then the availability for that week needs some of the down hours. So my thoughts were to build a query that will run every Sunday at a certin time. My weeks go Start on Monday and End on Sunday so example 11/10/03 to 11/16/03 = week 45. So every Sunday a query runs
and checks the Systems / Downs and will add to a table the Weekly down hrs. per system. Does this make any sense?

I did try your suggestion and it did return a week number but looks like its off a week. Does access use Sunday as a start? Can this be changed?

Thankyou so much for the responce any advice or suggestions would really help me out.

Thank you
Jim

 

[IanH]

The format function has an option to change the start day of the week, and the start week of the year. If you check out access help (look up 'format function') it will explain in detail.

The simple summary is that format takes the form ...
Format(expression[, format[, firstdayofweek[, firstweekofyear]]])

Where firstday of week and firstweekofyear are optional extras in the code.

So, if you want firstdayofweek = Monday and where the first week of the year is the first one with a full week (the second part is needed as it is the only way I could get a query to output week 45 for 10th Nov to 16th Nov), you would need to use ...

WeekNum: Format[startdate],"ww",2,3)

Regards

Ian