Create Date Ranges from Data 3

[MikeAuz1979]

Hi,

Using Access 2000 I have table data like:

Site Month MDQ
1005 01-jan-01 100
1005 01-feb-01 100
1005 01-mar-01 200
1005 01-apr-01 200

Is there any way to convert this into a result like:
(I.e. To convert it from actual monthly data into MDQ ranges)

Site StartDate EndDate MDQ
1005 01-jan-01 28-feb-01 100
1005 01-mar-01 30-Apr-01 200

Thanks for any help
Mike

 

[dhookom]

This should work for your example data:

SELECT Site, MDQ, Min([Month]) as StartDate, DateAdd("m",1,[Month])-1 as EndDate
FROM [data like]
GROUP BY Site, MDQ;

Can't even guess how it will handle more data.

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]

 

[r937]

believe it should be DateAdd("m",1,Max([Month]))-1

r937.com | rudy.ca

 

[dhookom]

Good catch. Thanks.

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]

 

[MikeAuz1979]

Thanks heaps guys, hard to pick who gets the star.

 

[PHV]

hard to pick who gets the star
Obviously BOTH !

 

[lespaul]

I'll give Rudy a star!

 

[dhookom]

Anyone who cleans up after me deserves a star ;-)

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]

 

[Darrylles]

Hi,

I gave rudy a star as well.

Erm - do I get a star for cleaning up after Mike (in that Mike should have given rudy a star)?

atb

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.