Query Dates by Week Num then String as Date Range

L

laknight

Hi,

I have these fields queried in design view:

Date WeekNum
1/1/2008 1
1/2/2008 1
1/3/2008 1
....
1/8/2008 2
1/9/2008 2
1/10/2008 2
.... and so on

How can I create another column field in design view to give me this result:

Date WeekNum DateRange
1/1/2008 1 1/1/2008 - 1/3/2008
1/2/2008 1 1/1/2008 - 1/3/2008
1/3/2008 1 1/1/2008 - 1/3/2008
....
1/8/2008 2 1/8/2008 - 1/10/2008
1/9/2008 2 1/8/2008 - 1/10/2008
1/10/2008 2 1/8/2008 - 1/10/2008
.... and so on

Using XP, A2003
Thanks for any help!
 
S

S.Clark

Create a table to hold:

WeekNo
StartDate
EndDate

Link this new table with your other table by the weekNo. Display the dates
in the query.
 
K

KARL DEWEY

UNTESTED --
First create a totals query named WeekSpread like this --
SELECT [WeekNum], Min([Date]) AS MinOf Date, Max([Date]) AS MaxOfDate
FROM [YourTable]
GROUP BY [WeekNum];

Then use query WeekSpread in a second query like this --
SELECT [Date], [WeekNum], [MinOf Date] & " - " & [MaxOfDate] AS [DateRange]
FROM [WeekSpread] LEFT JOIN [YourTable] ON [WeekSpread].[WeekNum]=
[YourTable].[WeekNum];
 
L

laknight

Karl,

Thanks for your quick response. I need to include [Year] as part of the
query because there are multiple years with corresponding weeks (eg. 1/1/2008
week 1 and 1/1/2007 week 1)


KARL DEWEY said:
UNTESTED --
First create a totals query named WeekSpread like this --
SELECT [WeekNum], Min([Date]) AS MinOf Date, Max([Date]) AS MaxOfDate
FROM [YourTable]
GROUP BY [WeekNum];

Then use query WeekSpread in a second query like this --
SELECT [Date], [WeekNum], [MinOf Date] & " - " & [MaxOfDate] AS [DateRange]
FROM [WeekSpread] LEFT JOIN [YourTable] ON [WeekSpread].[WeekNum]=
[YourTable].[WeekNum];

--
KARL DEWEY
Build a little - Test a little


laknight said:
Hi,

I have these fields queried in design view:

Date WeekNum
1/1/2008 1
1/2/2008 1
1/3/2008 1
...
1/8/2008 2
1/9/2008 2
1/10/2008 2
... and so on

How can I create another column field in design view to give me this result:

Date WeekNum DateRange
1/1/2008 1 1/1/2008 - 1/3/2008
1/2/2008 1 1/1/2008 - 1/3/2008
1/3/2008 1 1/1/2008 - 1/3/2008
...
1/8/2008 2 1/8/2008 - 1/10/2008
1/9/2008 2 1/8/2008 - 1/10/2008
1/10/2008 2 1/8/2008 - 1/10/2008
... and so on

Using XP, A2003
Thanks for any help!
Click to expand...
Click to expand...
 
K

KARL DEWEY

Are you entering the week number or using a date function? Because
12/31/2007 is in the 53'd week. These queries uses the date function to
calculate the week.

STILL UNTESTED --
First create a totals query named WeekSpread like this --
SELECT Format([Date],"yyyy") AS [Year], Format([Date],"ww") AS Week_Num,
Min([Date]) AS MinOf Date, Max([Date]) AS MaxOfDate
FROM [YourTable]
GROUP BY Format([Date],"yyyy"), Format([Date],"ww");

Then use query WeekSpread in a second query like this --
SELECT [Date], [Year], Week_Num, [MinOf Date] & " - " & [MaxOfDate] AS
[DateRange]
FROM [WeekSpread] LEFT JOIN [YourTable] ON [WeekSpread].[Year]=
[YourTable].[Year] AND [WeekSpread].[WeekNum]=
[YourTable].[Week_Num];

--
KARL DEWEY
Build a little - Test a little


laknight said:
Karl,

Thanks for your quick response. I need to include [Year] as part of the
query because there are multiple years with corresponding weeks (eg. 1/1/2008
week 1 and 1/1/2007 week 1)


KARL DEWEY said:
UNTESTED --
First create a totals query named WeekSpread like this --
SELECT [WeekNum], Min([Date]) AS MinOf Date, Max([Date]) AS MaxOfDate
FROM [YourTable]
GROUP BY [WeekNum];

Then use query WeekSpread in a second query like this --
SELECT [Date], [WeekNum], [MinOf Date] & " - " & [MaxOfDate] AS [DateRange]
FROM [WeekSpread] LEFT JOIN [YourTable] ON [WeekSpread].[WeekNum]=
[YourTable].[WeekNum];

--
KARL DEWEY
Build a little - Test a little


laknight said:
Hi,

I have these fields queried in design view:

Date WeekNum
1/1/2008 1
1/2/2008 1
1/3/2008 1
...
1/8/2008 2
1/9/2008 2
1/10/2008 2
... and so on

How can I create another column field in design view to give me this result:

Date WeekNum DateRange
1/1/2008 1 1/1/2008 - 1/3/2008
1/2/2008 1 1/1/2008 - 1/3/2008
1/3/2008 1 1/1/2008 - 1/3/2008
...
1/8/2008 2 1/8/2008 - 1/10/2008
1/9/2008 2 1/8/2008 - 1/10/2008
1/10/2008 2 1/8/2008 - 1/10/2008
... and so on

Using XP, A2003
Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
L

laknight

Thanks Karl,

I'm using a Date function, and yes I'm getting the week 53 thing. Which is
good and bad I suppose. I need to make totals of my records 3 different ways.
I need daily totals, weekly totals and then monthly totals. So for monthly
totals, having a week 53 is a good thing because I can separate it from week
1, but for weekly totals I would need to combine week 53 with week 1 of the
next year. I thought I read something on these boards about using an ISO
8something,something week format but never bookmarked it and now I can not
find it. I was really trying to avoid making all separate queries and
separate reports for sorting and totaling by daily, weekly and monthly but in
the long run I suppose it will be easier on me than trying to combine things
too much.

I now have the weekly date range working thanks to you. I just need to
figure a way to combine week 53 and week 1 of the next year. Do you know
where I can find information about using the ISO 8...??? week format? If
there is such a thing?

Thanks for your help.

KARL DEWEY said:
Are you entering the week number or using a date function? Because
12/31/2007 is in the 53'd week. These queries uses the date function to
calculate the week.

STILL UNTESTED --
First create a totals query named WeekSpread like this --
SELECT Format([Date],"yyyy") AS [Year], Format([Date],"ww") AS Week_Num,
Min([Date]) AS MinOf Date, Max([Date]) AS MaxOfDate
FROM [YourTable]
GROUP BY Format([Date],"yyyy"), Format([Date],"ww");

Then use query WeekSpread in a second query like this --
SELECT [Date], [Year], Week_Num, [MinOf Date] & " - " & [MaxOfDate] AS
[DateRange]
FROM [WeekSpread] LEFT JOIN [YourTable] ON [WeekSpread].[Year]=
[YourTable].[Year] AND [WeekSpread].[WeekNum]=
[YourTable].[Week_Num];

--
KARL DEWEY
Build a little - Test a little


laknight said:
Karl,

Thanks for your quick response. I need to include [Year] as part of the
query because there are multiple years with corresponding weeks (eg. 1/1/2008
week 1 and 1/1/2007 week 1)


KARL DEWEY said:
UNTESTED --
First create a totals query named WeekSpread like this --
SELECT [WeekNum], Min([Date]) AS MinOf Date, Max([Date]) AS MaxOfDate
FROM [YourTable]
GROUP BY [WeekNum];

Then use query WeekSpread in a second query like this --
SELECT [Date], [WeekNum], [MinOf Date] & " - " & [MaxOfDate] AS [DateRange]
FROM [WeekSpread] LEFT JOIN [YourTable] ON [WeekSpread].[WeekNum]=
[YourTable].[WeekNum];

--
KARL DEWEY
Build a little - Test a little


:

Hi,

I have these fields queried in design view:

Date WeekNum
1/1/2008 1
1/2/2008 1
1/3/2008 1
...
1/8/2008 2
1/9/2008 2
1/10/2008 2
... and so on

How can I create another column field in design view to give me this result:

Date WeekNum DateRange
1/1/2008 1 1/1/2008 - 1/3/2008
1/2/2008 1 1/1/2008 - 1/3/2008
1/3/2008 1 1/1/2008 - 1/3/2008
...
1/8/2008 2 1/8/2008 - 1/10/2008
1/9/2008 2 1/8/2008 - 1/10/2008
1/10/2008 2 1/8/2008 - 1/10/2008
... and so on

Using XP, A2003
Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

functions in criteria 5
show top values 5
Record Plus record in access query 8
Query pulling Time-Framed values 1
Complex Record Selection 1
12 month rolling average by location 1
Advanced problem using auto-number and datediff to find readmissio 1
query to sort nos 2

Top