Access Query to List First Day of Each Week in Year

[mpatter5]

I have a DB2 query that lists the first day of each week in a year. I need to be able to convert this to an Access query.
Here's the DB2 query:

with all_days(dt) as
(select (date('2007-01-01')) from sysibm.sysdummy1
union all
select dt + 1 day from all_days
where dt < '2007-12-31' )
select dt from all_days
where dayofweek_iso(dt) = 1;

Please note that sysimb.sysdummy1 is not an actual table and is similar to Oracle's DUAL table.
Does Access have a 'dummy' table?

If this cannot be accomplished in a query, then maybe a User-Defined Function?
Any help would be greatly appreciated.
Thanks

 

[dhookom]

In Access, you will need a table. I usually keep a table of all dates in mdbs. However you can create a table [tblNum0_9] with a numeric field [Num] and values 0,1,2,...9.
Then create a query with SQL of:

SELECT DateAdd("d",[tblNum0_9].[Num]+[tblNum0_9_1].[Num]*10+[tblNum0_9_2].[Num]*100,#1/1/2007#) AS WeekOf
FROM tblNum0_9, tblNum0_9 AS tblNum0_9_1, tblNum0_9 AS tblNum0_9_2
WHERE (((Weekday(DateAdd("d",[tblNum0_9].[Num]+[tblNum0_9_1].[Num]*10+[tblNum0_9_2].[Num]*100,#1/1/2007#)))=1))
ORDER BY DateAdd("d",[tblNum0_9].[Num]+[tblNum0_9_1].[Num]*10+[tblNum0_9_2].[Num]*100,#1/1/2007#);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mpatter5]

Thanks a million!!!
This does exactly what I needed.
Can you explain how it works. It give me 143 rows (weeks) through 1/1/2007 thru 9/21/2009.
Thanks again...

 

[dhookom]

The query is a cartesian query that uses multiple copies of the same table. These could be different tables like:

tblUnits
=========
0
1
2
...
9

tblTens
=========
0
10
20
...
90

tblHundreds
=========
0
100
200
...
900

tblThousands
=========
0
1000
2000
...
9000

I just used the same table and multiplied the Num field by either 1 or 10 or 100. The query combines every row from each table in all combinations of records from the other tables. I then just added a starting date.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]