Missing Date due to no data in crosstab query

[DMUM via AccessMonster.com]

hello i have a client who wants a report that shows all hours worked for each
employee for the period of 7 days. They only want to put in the end date and
have the report produce the other 6 dates. i.e Input of 3/27/2006 will show
dates of 3/27,3/26,3/25,3/24,3/23,3/22. I have no problem with this part - I
created 2 queries, the first is a standard select with parameter of date and
the second is a crosstab query with a criteria of Between [Date] and ([Date]-
7).

What I am having a problem with is that the client wants the report to show
all 7 days regardless of activity. So if a date Between 3/27 and 3/22 is a
Sunday or Holiday, it does not show up in the CrossTab query. Unfortuneatly
the client wants the dates as column headers so changing the column names to
Days as some of the posts suggest is not something I can take advantage of.

I have been creating Access Db;s for a while, but I am a long ways from being
anywhere close to most of you in this newsgroup, so if at all possible,
please be as explicit as possible in your response.

Just in case, here is the SQL View of my queries:

query 1: qRPT_TimeSheet1

PARAMETERS[Date]DateTime;
SELECT tblTimeSheet.Date,tblRimeSheet.EmpNo, tblTimeSheet.Name,tlkup.ClassNo,
tlkup.Rate,tblTimeSheet.Hours
FROM tblTimeSheet INNER JOIN tlkupClass ON tblTimesheet.Class.tlkup.ClassNo

TRANSFORM Nz(Sum(Hours),00 AS TotalHours
SELECT qRPT_TimeSheet1.EmpNo, qRPT_TimeSheet1.Name, qRPT_TimeSheet1.ClassNo,
qRPT_TimeSheet1.Rate
FROM qRPT_TImeSheet1
WHERE(((qRPT_TimeSheet1.Date) Between [Date] And ([Date]-6)))
GROUP BY qRPT_TimeSheet1.EmpNo, qRPT_TimeSheet1.Name, qRPT_TimeSheet1.ClassNo,
qRPT_TimeSheet1.Rate
PIVOT qRPT_TimeSheet1.Date;

I haven't even attempted to create the report yet.

Thank you in advance for your help

 

[Trever B]

Hi,

Just thinking ahead.

If you run the query with column headings as "Sun","Mon" etc and get the
answer you want. (But not what your boss wants.)

Then create a report where you can change the heading names to what your
boss wants.

Then everybody will be happy.

 

[DMUM via AccessMonster.com]

hugh? I don't want it if my "boss" doesn't want it I still have the
delimma of getting what my boss wants, so unless I am really missing the boat
here, I still need help getting what my boss wants - yes?

Hi,

Just thinking ahead.

If you run the query with column headings as "Sun","Mon" etc and get the
answer you want. (But not what your boss wants.)

Then create a report where you can change the heading names to what your
boss wants.

Then everybody will be happy.

hello i have a client who wants a report that shows all hours worked for each
employee for the period of 7 days. They only want to put in the end date and

[quoted text clipped - 35 lines]

Thank you in advance for your help

 

[Duane Hookom]

There is a solution at http://www.tek-tips.com/faqs.cfm?fid=5466 which uses
months as the date interval. It wouldn't take much to change the example to
using days as the interval.

 

[DMUM via AccessMonster.com]

Hi Duane
Thank you very much for giving me the link. I have been trying to get it to
work the way I need it to, but I am not getting the correct days. I need it
to calculate 7 days from the date the user inputs to my form. so if the date
is 3/26 it needs to calculate backwards from there to give me a begining date
of 3/20. I am not sure what the query result is givinge me, but I think it
is giving me everything between today's date and the end date I put in.
Whatever it is doing It isn't what I need. Any suggestions?

I tried changing the parameter so I could get the dates between - this is
what I tried
colhead: "dy" & datediff("d",([forms]![frmA]![textA]),([forms]![frmA]![textA])
-7)
Result - no data in the DY columns

There is a solution at http://www.tek-tips.com/faqs.cfm?fid=5466 which uses
months as the date interval. It wouldn't take much to change the example to
using days as the interval.

hello i have a client who wants a report that shows all hours worked for each
employee for the period of 7 days. They only want to put in the end date and

[quoted text clipped - 35 lines]

Thank you in advance for your help

 

[DMUM via AccessMonster.com]

Can anyone else give me some help? Duane?

Hi Duane
Thank you very much for giving me the link. I have been trying to get it to
work the way I need it to, but I am not getting the correct days. I need it
to calculate 7 days from the date the user inputs to my form. so if the date
is 3/26 it needs to calculate backwards from there to give me a begining date
of 3/20. I am not sure what the query result is givinge me, but I think it
is giving me everything between today's date and the end date I put in.
Whatever it is doing It isn't what I need. Any suggestions?

I tried changing the parameter so I could get the dates between - this is
what I tried
colhead: "dy" & datediff("d",([forms]![frmA]![textA]),([forms]![frmA]![textA])
-7)
Result - no data in the DY columns

There is a solution at http://www.tek-tips.com/faqs.cfm?fid=5466 which uses
months as the date interval. It wouldn't take much to change the example to

[quoted text clipped - 5 lines]