Daily breakdown per week

[Guest]

Here is the table that I'd like to pull the results from:

tblTransaction:
TransactionID
TransactionDate
TaskID
EmployeeID

My weeks run from Saturday to Friday. Data is updated on the following
Mon/Tue. I need to be able to select all the records from the previous week.
I would like this information on a per Employee per day basis such that I can
produce a timesheet report for each employee.

In the end, I would like a report that shows, for example:

Fred Bloggs

DAY DATE TASKID
Sat 4/03/06 1
Sun 5/03/06 2
Mon 6/03/06 3
Tues 7/03/06 7
Weds
Thurs 9/03/06 12
Fri 10/03/06 14

I'm fine with the report design. I just need help with the underlying query.
I've never quite figured out date/time queries.

Thanks

Dave

 

[Tom Ellison]

Dear David:

As your example shows, there may be days when an employee does not work.
What you do not show, or say, is whether an employee may be tasked to more
than one TASKID on any given date. I'll ignore that for now, but it may
come back to bite.

In order for this to work, I recommend a table of dates. You will need one
row for every date. To start, please just manuall create them. An
automated method of creating them can be added later.

Table: AllDates
ADate
1/1/2006
1/2/2006
1/3/2006

etc.

With this, a query can be created. The next step is a cross-product of
every employee with every date in the current week. You must supply the
date for Saturday, defining the week to be queried:

SELECT Employee.EmployeeID, AllDates.ADate, DatePart("w", AllDates.ADate, 7,
3) AS WeekDay
FROM AllDates, Employee
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww", [Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Now you can LEFT JOIN this to tblTransaction and obtain the task(s) from
there:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS WeekDay,
T.TaskID
FROM AllDates D, Employee E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.Adate
AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww", [Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Any good so far?

Tom Ellison

 

[OfficeDev18 via AccessMonster.com]

Hi, David,

Look in the help file for the GroupOn property and the DateGrouping property.
Used in conjunction, I believe you report will be in the order you want. if I
had more time, I'd be happy to elaborate. Unfortunately I don't.

Hope this helps,

Sam

 

[Guest]

The first part works great. The second part has a syntax error that I can't
spot. The first part definately indicates it's heading in the right direction.

One clarification (and it'll probably change everything), many tasks can be
completed on the same day. Worst case scenario, I just select the records
from the appropriate week and put them in date order with the date formatted
to display as days of the week.

Thanks

Dave

Dear David:

As your example shows, there may be days when an employee does not work.
What you do not show, or say, is whether an employee may be tasked to more
than one TASKID on any given date. I'll ignore that for now, but it may
come back to bite.

In order for this to work, I recommend a table of dates. You will need one
row for every date. To start, please just manuall create them. An
automated method of creating them can be added later.

Table: AllDates
ADate
1/1/2006
1/2/2006
1/3/2006

etc.

With this, a query can be created. The next step is a cross-product of
every employee with every date in the current week. You must supply the
date for Saturday, defining the week to be queried:

SELECT Employee.EmployeeID, AllDates.ADate, DatePart("w", AllDates.ADate, 7,
3) AS WeekDay
FROM AllDates, Employee
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww", [Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Now you can LEFT JOIN this to tblTransaction and obtain the task(s) from
there:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS WeekDay,
T.TaskID
FROM AllDates D, Employee E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.Adate
AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww", [Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Any good so far?

Tom Ellison

Here is the table that I'd like to pull the results from:

tblTransaction:
TransactionID
TransactionDate
TaskID
EmployeeID

My weeks run from Saturday to Friday. Data is updated on the following
Mon/Tue. I need to be able to select all the records from the previous
week.
I would like this information on a per Employee per day basis such that I
can
produce a timesheet report for each employee.

In the end, I would like a report that shows, for example:

Fred Bloggs

DAY DATE TASKID
Sat 4/03/06 1
Sun 5/03/06 2
Mon 6/03/06 3
Tues 7/03/06 7
Weds
Thurs 9/03/06 12
Fri 10/03/06 14

I'm fine with the report design. I just need help with the underlying
query.
I've never quite figured out date/time queries.

Thanks

Dave

 

[Tom Ellison]

Dear David:

If I were having this problem on my computer, I would read and note the
error message, then go look at the SQL of the query to see what is causing
the problem.

You need to give me those same advantages working with you through the
newsgroups.

Please post the SQL in which you have this problem, and quote the error
message. I'll see what I can do with that.

Tom Ellison

The first part works great. The second part has a syntax error that I
can't
spot. The first part definately indicates it's heading in the right
direction.

One clarification (and it'll probably change everything), many tasks can
be
completed on the same day. Worst case scenario, I just select the records
from the appropriate week and put them in date order with the date
formatted
to display as days of the week.

Thanks

Dave

Dear David:

As your example shows, there may be days when an employee does not work.
What you do not show, or say, is whether an employee may be tasked to
more
than one TASKID on any given date. I'll ignore that for now, but it may
come back to bite.

In order for this to work, I recommend a table of dates. You will need
one
row for every date. To start, please just manuall create them. An
automated method of creating them can be added later.

Table: AllDates
ADate
1/1/2006
1/2/2006
1/3/2006

etc.

With this, a query can be created. The next step is a cross-product of
every employee with every date in the current week. You must supply the
date for Saturday, defining the week to be queried:

SELECT Employee.EmployeeID, AllDates.ADate, DatePart("w", AllDates.ADate,
7,
3) AS WeekDay
FROM AllDates, Employee
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww", [Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Now you can LEFT JOIN this to tblTransaction and obtain the task(s) from
there:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS WeekDay,
T.TaskID
FROM AllDates D, Employee E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.Adate
AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww", [Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Any good so far?

Tom Ellison

Here is the table that I'd like to pull the results from:

tblTransaction:
TransactionID
TransactionDate
TaskID
EmployeeID

My weeks run from Saturday to Friday. Data is updated on the following
Mon/Tue. I need to be able to select all the records from the previous
week.
I would like this information on a per Employee per day basis such that
I
can
produce a timesheet report for each employee.

In the end, I would like a report that shows, for example:

Fred Bloggs

DAY DATE TASKID
Sat 4/03/06 1
Sun 5/03/06 2
Mon 6/03/06 3
Tues 7/03/06 7
Weds
Thurs 9/03/06 12
Fri 10/03/06 14

I'm fine with the report design. I just need help with the underlying
query.
I've never quite figured out date/time queries.

Thanks

Dave

 

[Guest]

The error message is "Syntax Error in LEFT JOIN operation". I thought it may
have been to do with the aliases so I used the full table names instead and
that didn't help. I made sure all the table names and field names matched
exactly what I have. I made sure the code didn't have any linebreaks where it
shouldn't after copy/pasting. I checked the help files to see the correct
syntax of the LEFT JOIN and all looks ok.

Dave

Dear David:

If I were having this problem on my computer, I would read and note the
error message, then go look at the SQL of the query to see what is causing
the problem.

You need to give me those same advantages working with you through the
newsgroups.

Please post the SQL in which you have this problem, and quote the error
message. I'll see what I can do with that.

Tom Ellison

The first part works great. The second part has a syntax error that I
can't
spot. The first part definately indicates it's heading in the right
direction.

One clarification (and it'll probably change everything), many tasks can
be
completed on the same day. Worst case scenario, I just select the records
from the appropriate week and put them in date order with the date
formatted
to display as days of the week.

Thanks

Dave

Dear David:

As your example shows, there may be days when an employee does not work.
What you do not show, or say, is whether an employee may be tasked to
more
than one TASKID on any given date. I'll ignore that for now, but it may
come back to bite.

In order for this to work, I recommend a table of dates. You will need
one
row for every date. To start, please just manuall create them. An
automated method of creating them can be added later.

Table: AllDates
ADate
1/1/2006
1/2/2006
1/3/2006

etc.

With this, a query can be created. The next step is a cross-product of
every employee with every date in the current week. You must supply the
date for Saturday, defining the week to be queried:

SELECT Employee.EmployeeID, AllDates.ADate, DatePart("w", AllDates.ADate,
7,
3) AS WeekDay
FROM AllDates, Employee
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww", [Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Now you can LEFT JOIN this to tblTransaction and obtain the task(s) from
there:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS WeekDay,
T.TaskID
FROM AllDates D, Employee E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.Adate
AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww", [Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Any good so far?

Tom Ellison


Here is the table that I'd like to pull the results from:

tblTransaction:
TransactionID
TransactionDate
TaskID
EmployeeID

My weeks run from Saturday to Friday. Data is updated on the following
Mon/Tue. I need to be able to select all the records from the previous
week.
I would like this information on a per Employee per day basis such that
I
can
produce a timesheet report for each employee.

In the end, I would like a report that shows, for example:

Fred Bloggs

DAY DATE TASKID
Sat 4/03/06 1
Sun 5/03/06 2
Mon 6/03/06 3
Tues 7/03/06 7
Weds
Thurs 9/03/06 12
Fri 10/03/06 14

I'm fine with the report design. I just need help with the underlying
query.
I've never quite figured out date/time queries.

Thanks

Dave

 

[Tom Ellison]

Dear David:

Perhaps you might benefit from having me, also, check the syntax of your
LEFT JOIN. If you provide the SQL code, as I asked before, I could give it
a try.

Tom Ellison

The error message is "Syntax Error in LEFT JOIN operation". I thought it
may
have been to do with the aliases so I used the full table names instead
and
that didn't help. I made sure all the table names and field names matched
exactly what I have. I made sure the code didn't have any linebreaks where
it
shouldn't after copy/pasting. I checked the help files to see the correct
syntax of the LEFT JOIN and all looks ok.

Dave

Dear David:

If I were having this problem on my computer, I would read and note the
error message, then go look at the SQL of the query to see what is
causing
the problem.

You need to give me those same advantages working with you through the
newsgroups.

Please post the SQL in which you have this problem, and quote the error
message. I'll see what I can do with that.

Tom Ellison

The first part works great. The second part has a syntax error that I
can't
spot. The first part definately indicates it's heading in the right
direction.

One clarification (and it'll probably change everything), many tasks
can
be
completed on the same day. Worst case scenario, I just select the
records
from the appropriate week and put them in date order with the date
formatted
to display as days of the week.

Thanks

Dave

:

Dear David:

As your example shows, there may be days when an employee does not
work.
What you do not show, or say, is whether an employee may be tasked to
more
than one TASKID on any given date. I'll ignore that for now, but it
may
come back to bite.

In order for this to work, I recommend a table of dates. You will
need
one
row for every date. To start, please just manuall create them. An
automated method of creating them can be added later.

Table: AllDates
ADate
1/1/2006
1/2/2006
1/3/2006

etc.

With this, a query can be created. The next step is a cross-product
of
every employee with every date in the current week. You must supply
the
date for Saturday, defining the week to be queried:

SELECT Employee.EmployeeID, AllDates.ADate, DatePart("w",
AllDates.ADate,
7,
3) AS WeekDay
FROM AllDates, Employee
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww", [Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Now you can LEFT JOIN this to tblTransaction and obtain the task(s)
from
there:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS WeekDay,
T.TaskID
FROM AllDates D, Employee E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.Adate
AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww", [Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Any good so far?

Tom Ellison


Here is the table that I'd like to pull the results from:

tblTransaction:
TransactionID
TransactionDate
TaskID
EmployeeID

My weeks run from Saturday to Friday. Data is updated on the
following
Mon/Tue. I need to be able to select all the records from the
previous
week.
I would like this information on a per Employee per day basis such
that
I
can
produce a timesheet report for each employee.

In the end, I would like a report that shows, for example:

Fred Bloggs

DAY DATE TASKID
Sat 4/03/06 1
Sun 5/03/06 2
Mon 6/03/06 3
Tues 7/03/06 7
Weds
Thurs 9/03/06 12
Fri 10/03/06 14

I'm fine with the report design. I just need help with the
underlying
query.
I've never quite figured out date/time queries.

Thanks

Dave

 

[Guest]

Sorry, here we go:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS WeekDay,
T.TaskID
FROM AllDates D, Employees E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.ADate AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.ADate, 7, 3) = DatePart("ww", [Enter Date: ],
7, 3)
ORDER BY Employees.EmployeeID, AllDates.ADate;

Dear David:

Perhaps you might benefit from having me, also, check the syntax of your
LEFT JOIN. If you provide the SQL code, as I asked before, I could give it
a try.

Tom Ellison

The error message is "Syntax Error in LEFT JOIN operation". I thought it
may
have been to do with the aliases so I used the full table names instead
and
that didn't help. I made sure all the table names and field names matched
exactly what I have. I made sure the code didn't have any linebreaks where
it
shouldn't after copy/pasting. I checked the help files to see the correct
syntax of the LEFT JOIN and all looks ok.

Dave

Dear David:

If I were having this problem on my computer, I would read and note the
error message, then go look at the SQL of the query to see what is
causing
the problem.

You need to give me those same advantages working with you through the
newsgroups.

Please post the SQL in which you have this problem, and quote the error
message. I'll see what I can do with that.

Tom Ellison


The first part works great. The second part has a syntax error that I
can't
spot. The first part definately indicates it's heading in the right
direction.

One clarification (and it'll probably change everything), many tasks
can
be
completed on the same day. Worst case scenario, I just select the
records
from the appropriate week and put them in date order with the date
formatted
to display as days of the week.

Thanks

Dave

:

Dear David:

As your example shows, there may be days when an employee does not
work.
What you do not show, or say, is whether an employee may be tasked to
more
than one TASKID on any given date. I'll ignore that for now, but it
may
come back to bite.

In order for this to work, I recommend a table of dates. You will
need
one
row for every date. To start, please just manuall create them. An
automated method of creating them can be added later.

Table: AllDates
ADate
1/1/2006
1/2/2006
1/3/2006

etc.

With this, a query can be created. The next step is a cross-product
of
every employee with every date in the current week. You must supply
the
date for Saturday, defining the week to be queried:

SELECT Employee.EmployeeID, AllDates.ADate, DatePart("w",
AllDates.ADate,
7,
3) AS WeekDay
FROM AllDates, Employee
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww", [Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Now you can LEFT JOIN this to tblTransaction and obtain the task(s)
from
there:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS WeekDay,
T.TaskID
FROM AllDates D, Employee E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.Adate
AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww", [Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Any good so far?

Tom Ellison


Here is the table that I'd like to pull the results from:

tblTransaction:
TransactionID
TransactionDate
TaskID
EmployeeID

My weeks run from Saturday to Friday. Data is updated on the
following
Mon/Tue. I need to be able to select all the records from the
previous
week.
I would like this information on a per Employee per day basis such
that
I
can
produce a timesheet report for each employee.

In the end, I would like a report that shows, for example:

Fred Bloggs

DAY DATE TASKID
Sat 4/03/06 1
Sun 5/03/06 2
Mon 6/03/06 3
Tues 7/03/06 7
Weds
Thurs 9/03/06 12
Fri 10/03/06 14

I'm fine with the report design. I just need help with the
underlying
query.
I've never quite figured out date/time queries.

Thanks

Dave

 

[Tom Ellison]

Dear David:

Sorry, David. I didn't realize you were just pasting and using my query,
which I had given you earlier. I get involved in several problems like
yours every day, and I don't always track all the details of every one
perfectly. In any case, its a good thing to double check.

Now, I created, best as I could see, the details of your problem in a new
database, and put together a working solution before posting back. So,
given my assumptions about your database, this was a working query.

This does assume you created an AllDates table with the column ADate,
exactly as I recommended. You do have that, right? Double check the
spellings, please, for the table name and the column name.

Next, somewhere along the line, I have assumed the existence of a table I
called Employees, with the column EmployeeID. I don't see where you
mentioned this table. The reason for it is to be able to include every
employee in the report, even if that employee didn't have any transactions
for the subject week. You may need to adjust my query for the actual name
of the Employees table, and the name of the EmployeeID column I assumed.
Can you do that? If you need me to do it, please provide the actual name of
this table and column. Or, if you prefer to have an employee disappear from
the report if he has no transactions for the week, we can just drop this
table. I also note that you show the employee name. This may not be what
is in the EmployeeID column. If so, you're going to need the Employees
table to obtain that name, anyway.

Sorry, I haven't meant to mislead you, but it looks like I referenced facts
not in evidence. You see, I presumed there would be an Employees table and
created one in my mockup of your problem, then probably forgot to explain
those assumptions to you when I posted back. Sometimes things get a little
too rushed. Anyway, I expect that to be where the problem now lies.

Tom Ellison

Sorry, here we go:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS WeekDay,
T.TaskID
FROM AllDates D, Employees E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.ADate AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.ADate, 7, 3) = DatePart("ww", [Enter
ate: ],
7, 3)
ORDER BY Employees.EmployeeID, AllDates.ADate;

Dear David:

Perhaps you might benefit from having me, also, check the syntax of your
LEFT JOIN. If you provide the SQL code, as I asked before, I could give
it
a try.

Tom Ellison

The error message is "Syntax Error in LEFT JOIN operation". I thought
it
may
have been to do with the aliases so I used the full table names instead
and
that didn't help. I made sure all the table names and field names
matched
exactly what I have. I made sure the code didn't have any linebreaks
where
it
shouldn't after copy/pasting. I checked the help files to see the
correct
syntax of the LEFT JOIN and all looks ok.

Dave

:

Dear David:

If I were having this problem on my computer, I would read and note
the
error message, then go look at the SQL of the query to see what is
causing
the problem.

You need to give me those same advantages working with you through the
newsgroups.

Please post the SQL in which you have this problem, and quote the
error
message. I'll see what I can do with that.

Tom Ellison


The first part works great. The second part has a syntax error that
I
can't
spot. The first part definately indicates it's heading in the right
direction.

One clarification (and it'll probably change everything), many tasks
can
be
completed on the same day. Worst case scenario, I just select the
records
from the appropriate week and put them in date order with the date
formatted
to display as days of the week.

Thanks

Dave

:

Dear David:

As your example shows, there may be days when an employee does not
work.
What you do not show, or say, is whether an employee may be tasked
to
more
than one TASKID on any given date. I'll ignore that for now, but
it
may
come back to bite.

In order for this to work, I recommend a table of dates. You will
need
one
row for every date. To start, please just manuall create them. An
automated method of creating them can be added later.

Table: AllDates
ADate
1/1/2006
1/2/2006
1/3/2006

etc.

With this, a query can be created. The next step is a
cross-product
of
every employee with every date in the current week. You must
supply
the
date for Saturday, defining the week to be queried:

SELECT Employee.EmployeeID, AllDates.ADate, DatePart("w",
AllDates.ADate,
7,
3) AS WeekDay
FROM AllDates, Employee
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww",
[Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Now you can LEFT JOIN this to tblTransaction and obtain the task(s)
from
there:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS
WeekDay,
T.TaskID
FROM AllDates D, Employee E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.Adate
AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww",
[Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Any good so far?

Tom Ellison


Here is the table that I'd like to pull the results from:

tblTransaction:
TransactionID
TransactionDate
TaskID
EmployeeID

My weeks run from Saturday to Friday. Data is updated on the
following
Mon/Tue. I need to be able to select all the records from the
previous
week.
I would like this information on a per Employee per day basis
such
that
I
can
produce a timesheet report for each employee.

In the end, I would like a report that shows, for example:

Fred Bloggs

DAY DATE TASKID
Sat 4/03/06 1
Sun 5/03/06 2
Mon 6/03/06 3
Tues 7/03/06 7
Weds
Thurs 9/03/06 12
Fri 10/03/06 14

I'm fine with the report design. I just need help with the
underlying
query.
I've never quite figured out date/time queries.

Thanks

Dave

 

[Guest]

Thanks for your help. Pretty much all of your assumptions are correct. I have
a table called Employees (I added the "s" where required in your query) which
does have a column EmployeeID (it also has columns LastName and FirstName). I
created the table you recommended for the dates, this is called AllDates with
the column ADate.

As I said, I've looked everywhere for the syntax error and can't see it.
I'll try typing it directly rather than copy/pasting just to be sure errors
haven't been introduced this way. Just to reiterrate, the first piece of SQL
you posted works as expected, it's only when adding the LEFT JOIN that the
syntax error occurs.

Dave

Dear David:

Sorry, David. I didn't realize you were just pasting and using my query,
which I had given you earlier. I get involved in several problems like
yours every day, and I don't always track all the details of every one
perfectly. In any case, its a good thing to double check.

Now, I created, best as I could see, the details of your problem in a new
database, and put together a working solution before posting back. So,
given my assumptions about your database, this was a working query.

This does assume you created an AllDates table with the column ADate,
exactly as I recommended. You do have that, right? Double check the
spellings, please, for the table name and the column name.

Next, somewhere along the line, I have assumed the existence of a table I
called Employees, with the column EmployeeID. I don't see where you
mentioned this table. The reason for it is to be able to include every
employee in the report, even if that employee didn't have any transactions
for the subject week. You may need to adjust my query for the actual name
of the Employees table, and the name of the EmployeeID column I assumed.
Can you do that? If you need me to do it, please provide the actual name of
this table and column. Or, if you prefer to have an employee disappear from
the report if he has no transactions for the week, we can just drop this
table. I also note that you show the employee name. This may not be what
is in the EmployeeID column. If so, you're going to need the Employees
table to obtain that name, anyway.

Sorry, I haven't meant to mislead you, but it looks like I referenced facts
not in evidence. You see, I presumed there would be an Employees table and
created one in my mockup of your problem, then probably forgot to explain
those assumptions to you when I posted back. Sometimes things get a little
too rushed. Anyway, I expect that to be where the problem now lies.

Tom Ellison

Sorry, here we go:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS WeekDay,
T.TaskID
FROM AllDates D, Employees E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.ADate AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.ADate, 7, 3) = DatePart("ww", [Enter
ate: ],
7, 3)
ORDER BY Employees.EmployeeID, AllDates.ADate;

Dear David:

Perhaps you might benefit from having me, also, check the syntax of your
LEFT JOIN. If you provide the SQL code, as I asked before, I could give
it
a try.

Tom Ellison


The error message is "Syntax Error in LEFT JOIN operation". I thought
it
may
have been to do with the aliases so I used the full table names instead
and
that didn't help. I made sure all the table names and field names
matched
exactly what I have. I made sure the code didn't have any linebreaks
where
it
shouldn't after copy/pasting. I checked the help files to see the
correct
syntax of the LEFT JOIN and all looks ok.

Dave

:

Dear David:

If I were having this problem on my computer, I would read and note
the
error message, then go look at the SQL of the query to see what is
causing
the problem.

You need to give me those same advantages working with you through the
newsgroups.

Please post the SQL in which you have this problem, and quote the
error
message. I'll see what I can do with that.

Tom Ellison


The first part works great. The second part has a syntax error that
I
can't
spot. The first part definately indicates it's heading in the right
direction.

One clarification (and it'll probably change everything), many tasks
can
be
completed on the same day. Worst case scenario, I just select the
records
from the appropriate week and put them in date order with the date
formatted
to display as days of the week.

Thanks

Dave

:

Dear David:

As your example shows, there may be days when an employee does not
work.
What you do not show, or say, is whether an employee may be tasked
to
more
than one TASKID on any given date. I'll ignore that for now, but
it
may
come back to bite.

In order for this to work, I recommend a table of dates. You will
need
one
row for every date. To start, please just manuall create them. An
automated method of creating them can be added later.

Table: AllDates
ADate
1/1/2006
1/2/2006
1/3/2006

etc.

With this, a query can be created. The next step is a
cross-product
of
every employee with every date in the current week. You must
supply
the
date for Saturday, defining the week to be queried:

SELECT Employee.EmployeeID, AllDates.ADate, DatePart("w",
AllDates.ADate,
7,
3) AS WeekDay
FROM AllDates, Employee
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww",
[Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Now you can LEFT JOIN this to tblTransaction and obtain the task(s)
from
there:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS
WeekDay,
T.TaskID
FROM AllDates D, Employee E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.Adate
AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww",
[Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Any good so far?

Tom Ellison


Here is the table that I'd like to pull the results from:

tblTransaction:
TransactionID
TransactionDate
TaskID
EmployeeID

My weeks run from Saturday to Friday. Data is updated on the
following
Mon/Tue. I need to be able to select all the records from the
previous
week.
I would like this information on a per Employee per day basis
such
that
I
can
produce a timesheet report for each employee.

In the end, I would like a report that shows, for example:

Fred Bloggs

DAY DATE TASKID
Sat 4/03/06 1
Sun 5/03/06 2
Mon 6/03/06 3
Tues 7/03/06 7
Weds
Thurs 9/03/06 12
Fri 10/03/06 14

I'm fine with the report design. I just need help with the
underlying
query.
I've never quite figured out date/time queries.

Thanks

Dave

 

[Tom Ellison]

Dear David:

If you positively cannot find the error, and given that my attempted mock-up
of your situations here must be somehow different from what you have, then I
recommend you email me an attached copy of your database. It does not need
to be an extensive thing, just the referenced tables and the queries we have
been doing. Please be sure to do the following:

Make a copy of your database, perhaps by importing only those important
portions (3 tables and 2 queries I believe)

Rename the file extension on this file from MDB to XXX

Zip up the file

Attach the zipped file

If you do not rename the extension from MDB to XXX, Windows Security will
block it here and I won't be able to use it. There are ways around this,
but they are a bit painful for me. I'd rather make you do the work. : )

I'll be watching.

Tom Ellison

Thanks for your help. Pretty much all of your assumptions are correct. I
have
a table called Employees (I added the "s" where required in your query)
which
does have a column EmployeeID (it also has columns LastName and
FirstName). I
created the table you recommended for the dates, this is called AllDates
with
the column ADate.

As I said, I've looked everywhere for the syntax error and can't see it.
I'll try typing it directly rather than copy/pasting just to be sure
errors
haven't been introduced this way. Just to reiterrate, the first piece of
SQL
you posted works as expected, it's only when adding the LEFT JOIN that the
syntax error occurs.

Dave

Dear David:

Sorry, David. I didn't realize you were just pasting and using my query,
which I had given you earlier. I get involved in several problems like
yours every day, and I don't always track all the details of every one
perfectly. In any case, its a good thing to double check.

Now, I created, best as I could see, the details of your problem in a new
database, and put together a working solution before posting back. So,
given my assumptions about your database, this was a working query.

This does assume you created an AllDates table with the column ADate,
exactly as I recommended. You do have that, right? Double check the
spellings, please, for the table name and the column name.

Next, somewhere along the line, I have assumed the existence of a table I
called Employees, with the column EmployeeID. I don't see where you
mentioned this table. The reason for it is to be able to include every
employee in the report, even if that employee didn't have any
transactions
for the subject week. You may need to adjust my query for the actual
name
of the Employees table, and the name of the EmployeeID column I assumed.
Can you do that? If you need me to do it, please provide the actual name
of
this table and column. Or, if you prefer to have an employee disappear
from
the report if he has no transactions for the week, we can just drop this
table. I also note that you show the employee name. This may not be
what
is in the EmployeeID column. If so, you're going to need the Employees
table to obtain that name, anyway.

Sorry, I haven't meant to mislead you, but it looks like I referenced
facts
not in evidence. You see, I presumed there would be an Employees table
and
created one in my mockup of your problem, then probably forgot to explain
those assumptions to you when I posted back. Sometimes things get a
little
too rushed. Anyway, I expect that to be where the problem now lies.

Tom Ellison

Sorry, here we go:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS WeekDay,
T.TaskID
FROM AllDates D, Employees E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.ADate AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.ADate, 7, 3) = DatePart("ww", [Enter
ate: ],
7, 3)
ORDER BY Employees.EmployeeID, AllDates.ADate;

:

Dear David:

Perhaps you might benefit from having me, also, check the syntax of
your
LEFT JOIN. If you provide the SQL code, as I asked before, I could
give
it
a try.

Tom Ellison


The error message is "Syntax Error in LEFT JOIN operation". I
thought
it
may
have been to do with the aliases so I used the full table names
instead
and
that didn't help. I made sure all the table names and field names
matched
exactly what I have. I made sure the code didn't have any linebreaks
where
it
shouldn't after copy/pasting. I checked the help files to see the
correct
syntax of the LEFT JOIN and all looks ok.

Dave

:

Dear David:

If I were having this problem on my computer, I would read and note
the
error message, then go look at the SQL of the query to see what is
causing
the problem.

You need to give me those same advantages working with you through
the
newsgroups.

Please post the SQL in which you have this problem, and quote the
error
message. I'll see what I can do with that.

Tom Ellison


The first part works great. The second part has a syntax error
that
I
can't
spot. The first part definately indicates it's heading in the
right
direction.

One clarification (and it'll probably change everything), many
tasks
can
be
completed on the same day. Worst case scenario, I just select the
records
from the appropriate week and put them in date order with the
date
formatted
to display as days of the week.

Thanks

Dave

:

Dear David:

As your example shows, there may be days when an employee does
not
work.
What you do not show, or say, is whether an employee may be
tasked
to
more
than one TASKID on any given date. I'll ignore that for now,
but
it
may
come back to bite.

In order for this to work, I recommend a table of dates. You
will
need
one
row for every date. To start, please just manuall create them.
An
automated method of creating them can be added later.

Table: AllDates
ADate
1/1/2006
1/2/2006
1/3/2006

etc.

With this, a query can be created. The next step is a
cross-product
of
every employee with every date in the current week. You must
supply
the
date for Saturday, defining the week to be queried:

SELECT Employee.EmployeeID, AllDates.ADate, DatePart("w",
AllDates.ADate,
7,
3) AS WeekDay
FROM AllDates, Employee
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww",
[Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Now you can LEFT JOIN this to tblTransaction and obtain the
task(s)
from
there:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS
WeekDay,
T.TaskID
FROM AllDates D, Employee E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.Adate
AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww",
[Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Any good so far?

Tom Ellison


Here is the table that I'd like to pull the results from:

tblTransaction:
TransactionID
TransactionDate
TaskID
EmployeeID

My weeks run from Saturday to Friday. Data is updated on the
following
Mon/Tue. I need to be able to select all the records from the
previous
week.
I would like this information on a per Employee per day basis
such
that
I
can
produce a timesheet report for each employee.

In the end, I would like a report that shows, for example:

Fred Bloggs

DAY DATE TASKID
Sat 4/03/06 1
Sun 5/03/06 2
Mon 6/03/06 3
Tues 7/03/06 7
Weds
Thurs 9/03/06 12
Fri 10/03/06 14

I'm fine with the report design. I just need help with the
underlying
query.
I've never quite figured out date/time queries.

Thanks

Dave

 

[Tom Ellison]

Dear David:

I was forced to restructure the query considerably. Jet didn't like some
things which I considered to be rather straight forward. Well, there's no
substitute for having the real thing.

SELECT X.EmployeeID, X.FirstName, X.LastName,
D.ADate, DatePart("w", D.ADate, 7, 3) AS WeekDay,
X.TaskID
FROM AllDates D,
(SELECT E.EmployeeID, E.FirstName, E.LastName, T.TaskID
FROM Employees E
LEFT JOIN tblTransaction T
ON T.EmployeeID = E.EmployeeID) X
WHERE DatePart("ww", D.Adate, 7, 3) = DatePart("ww", [Enter Date: ], 7,
3)
ORDER BY X.EmployeeID, D.ADate, X.TaskID

Now, your data shows there to be multiple tasks for some employees on some
days, and employees with no tasks for the whole week. All this is reflected
in the query resutls.

If you want the days of the week named, you would need a table that relates
the days of the week to the names of those days. Join that in here, too,
and you'd be fixed.

Tom Ellison

Dear David:

If you positively cannot find the error, and given that my attempted
mock-up of your situations here must be somehow different from what you
have, then I recommend you email me an attached copy of your database. It
does not need to be an extensive thing, just the referenced tables and the
queries we have been doing. Please be sure to do the following:

Make a copy of your database, perhaps by importing only those important
portions (3 tables and 2 queries I believe)

Rename the file extension on this file from MDB to XXX

Zip up the file

Attach the zipped file

If you do not rename the extension from MDB to XXX, Windows Security will
block it here and I won't be able to use it. There are ways around this,
but they are a bit painful for me. I'd rather make you do the work. : )

I'll be watching.

Tom Ellison

Thanks for your help. Pretty much all of your assumptions are correct. I
have
a table called Employees (I added the "s" where required in your query)
which
does have a column EmployeeID (it also has columns LastName and
FirstName). I
created the table you recommended for the dates, this is called AllDates
with
the column ADate.

As I said, I've looked everywhere for the syntax error and can't see it.
I'll try typing it directly rather than copy/pasting just to be sure
errors
haven't been introduced this way. Just to reiterrate, the first piece of
SQL
you posted works as expected, it's only when adding the LEFT JOIN that
the
syntax error occurs.

Dave

Dear David:

Sorry, David. I didn't realize you were just pasting and using my
query,
which I had given you earlier. I get involved in several problems like
yours every day, and I don't always track all the details of every one
perfectly. In any case, its a good thing to double check.

Now, I created, best as I could see, the details of your problem in a
new
database, and put together a working solution before posting back. So,
given my assumptions about your database, this was a working query.

This does assume you created an AllDates table with the column ADate,
exactly as I recommended. You do have that, right? Double check the
spellings, please, for the table name and the column name.

Next, somewhere along the line, I have assumed the existence of a table
I
called Employees, with the column EmployeeID. I don't see where you
mentioned this table. The reason for it is to be able to include every
employee in the report, even if that employee didn't have any
transactions
for the subject week. You may need to adjust my query for the actual
name
of the Employees table, and the name of the EmployeeID column I assumed.
Can you do that? If you need me to do it, please provide the actual
name of
this table and column. Or, if you prefer to have an employee disappear
from
the report if he has no transactions for the week, we can just drop this
table. I also note that you show the employee name. This may not be
what
is in the EmployeeID column. If so, you're going to need the Employees
table to obtain that name, anyway.

Sorry, I haven't meant to mislead you, but it looks like I referenced
facts
not in evidence. You see, I presumed there would be an Employees table
and
created one in my mockup of your problem, then probably forgot to
explain
those assumptions to you when I posted back. Sometimes things get a
little
too rushed. Anyway, I expect that to be where the problem now lies.

Tom Ellison


Sorry, here we go:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS WeekDay,
T.TaskID
FROM AllDates D, Employees E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.ADate AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.ADate, 7, 3) = DatePart("ww", [Enter
ate: ],
7, 3)
ORDER BY Employees.EmployeeID, AllDates.ADate;

:

Dear David:

Perhaps you might benefit from having me, also, check the syntax of
your
LEFT JOIN. If you provide the SQL code, as I asked before, I could
give
it
a try.

Tom Ellison


The error message is "Syntax Error in LEFT JOIN operation". I
thought
it
may
have been to do with the aliases so I used the full table names
instead
and
that didn't help. I made sure all the table names and field names
matched
exactly what I have. I made sure the code didn't have any
linebreaks
where
it
shouldn't after copy/pasting. I checked the help files to see the
correct
syntax of the LEFT JOIN and all looks ok.

Dave

:

Dear David:

If I were having this problem on my computer, I would read and
note
the
error message, then go look at the SQL of the query to see what is
causing
the problem.

You need to give me those same advantages working with you through
the
newsgroups.

Please post the SQL in which you have this problem, and quote the
error
message. I'll see what I can do with that.

Tom Ellison


The first part works great. The second part has a syntax error
that
I
can't
spot. The first part definately indicates it's heading in the
right
direction.

One clarification (and it'll probably change everything), many
tasks
can
be
completed on the same day. Worst case scenario, I just select
the
records
from the appropriate week and put them in date order with the
date
formatted
to display as days of the week.

Thanks

Dave

:

Dear David:

As your example shows, there may be days when an employee does
not
work.
What you do not show, or say, is whether an employee may be
tasked
to
more
than one TASKID on any given date. I'll ignore that for now,
but
it
may
come back to bite.

In order for this to work, I recommend a table of dates. You
will
need
one
row for every date. To start, please just manuall create them.
An
automated method of creating them can be added later.

Table: AllDates
ADate
1/1/2006
1/2/2006
1/3/2006

etc.

With this, a query can be created. The next step is a
cross-product
of
every employee with every date in the current week. You must
supply
the
date for Saturday, defining the week to be queried:

SELECT Employee.EmployeeID, AllDates.ADate, DatePart("w",
AllDates.ADate,
7,
3) AS WeekDay
FROM AllDates, Employee
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww",
[Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Now you can LEFT JOIN this to tblTransaction and obtain the
task(s)
from
there:

SELECT E.EmployeeID, D.ADate, DatePart("w", D.ADate, 7, 3) AS
WeekDay,
T.TaskID
FROM AllDates D, Employee E
LEFT JOIN tblTransaction T
ON T.TransactionDate = D.Adate
AND T.EmployeeID = E.EmployeeID
WHERE DatePart("ww", AllDates.Adate, 7, 3) = DatePart("ww",
[Enter
ate: ], 7, 3)
ORDER BY Employee.EmployeeID, AllDates.ADate;

Any good so far?

Tom Ellison


message
Here is the table that I'd like to pull the results from:

tblTransaction:
TransactionID
TransactionDate
TaskID
EmployeeID

My weeks run from Saturday to Friday. Data is updated on the
following
Mon/Tue. I need to be able to select all the records from the
previous
week.
I would like this information on a per Employee per day basis
such
that
I
can
produce a timesheet report for each employee.

In the end, I would like a report that shows, for example:

Fred Bloggs

DAY DATE TASKID
Sat 4/03/06 1
Sun 5/03/06 2
Mon 6/03/06 3
Tues 7/03/06 7
Weds
Thurs 9/03/06 12
Fri 10/03/06 14

I'm fine with the report design. I just need help with the
underlying
query.
I've never quite figured out date/time queries.

Thanks

Dave