Help to write a query

Hello,
I have a database in MS Access containing 2 tables:
the first one called "weeks" has 4 columns: id, week number (ex:
2007/01), start date(ex:01/01/2007), end date(ex: 07/01/2007)
the second one is called "tests" and has 5 columns: index, date, name,
test name, duration

my goal: I want to make a query to get rows containing 3 columns:
week number,
name,
dates = date1, date2, etc.,
tests=test1, test2, etc.,
total duration of test for the given week and given person

To be clear, I am trying to get per week a summary of names with the
tests they did and how long total it lasted. The first tables is just
here to get the week number, given the test date.
My current query returns a list of all tests (table 2) with an added
column of the week number and looks like this:

SELECT date, initials, test, [hours outside 8-19], [Week number] FROM
tests, weeks WHERE date BETWEEN [start date] AND [end date]

I tried lots of things but does not know to get to my goal. Could
someone please help me?

Any help will be appreciated. Thank you in advance

Pierrot

Re: Help to write a query

foutuguy@gmail.com wrote:

> Hello,
> I have a database in MS Access containing 2 tables:
> the first one called "weeks" has 4 columns: id, week number (ex:
> 2007/01), start date(ex:01/01/2007), end date(ex: 07/01/2007)
> the second one is called "tests" and has 5 columns: index, date, name,
> test name, duration
>
> my goal: I want to make a query to get rows containing 3 columns:
> week number,
> name,
> dates = date1, date2, etc.,
> tests=test1, test2, etc.,
> total duration of test for the given week and given person
>
> To be clear, I am trying to get per week a summary of names with the
> tests they did and how long total it lasted. The first tables is just
> here to get the week number, given the test date.
> My current query returns a list of all tests (table 2) with an added
> column of the week number and looks like this:
>
> SELECT date, initials, test, [hours outside 8-19], [Week number] FROM
> tests, weeks WHERE date BETWEEN [start date] AND [end date]
>
> I tried lots of things but does not know to get to my goal. Could
> someone please help me?
>
> Any help will be appreciated. Thank you in advance
>
> Pierrot

You might want to consider a DLookup() to get the week number. TestDate
is from table Tests. S/EDate from Weeks.

SELECT Tests.*, DLookUp("WeekNum","Weeks","SDate <= #" & [Testdate] & "#
And EndDate >= #" & [Tdate] & "#") AS Week
FROM Tests

desenchantee
http://www.youtube.com/watch?v=x65k9dQScT8