year/ week numbering

[Daniel]

Good evening,

How can I create a field in a query that will populate itself with a
sequential numbering of year - week starting at 2004 - 23 to the current year
- week?

ie:
2004 - 23
2004 - 24
2004 - 25
..
..
..
2006 - 16

Thank you,

Daniel

 

[Duane Hookom]

Do you have any tables you are using in this query?

 

[Daniel]

Duane,

Yes. My original query was using the date(s) from various tables and using
the format(,"yyyy") and format(,"ww") functions I was able to 'group by' and
get a listing. However, This left wholes in my graph because not all the
weeks had entries and made it very difficult to merge different tbl info
because they did not all have certain week.... That's why I thought a
function that would populate a sequential listing year-week, which I then
could use in conjunction with my existing queries would solve my problem?!

For this specific query I have a table from which I am pulling 3 date fields
(Needed, Committed and Delivered) and a drawing number and Drawing ID (which
is the primary key). I am trying to get a a query, to create a chart with,
that will list the number of drawing Needed/Committed/Delivered by year-week.

I am open to any better way of working.

Daniel

 

[Duane Hookom]

Create a table with all the "date" values without any holes. Then use a join
in your query that includes all the values from your new table.

 

[Daniel]

Duane,

I thought of that, but my problem with that solution is that it does not
stay current... so that in three week, it will be missing the three latest
week entries... need a dynamic solution. Can you help me?

Daniel

 

[Duane Hookom]

Why would your date table not include dates through 12/31/2010?