Report to look like ExHell sheet

G

Guest

I have a report that is to look like an Excell spread sheet. Why, because
they think it is the only way they can understand the data. Anyway, I do not
know if it is possible but here are the requirements Data to be viewed
horizontally and to include "EmployeeName" with 8 colums (6 seperate Dates
for that week with total Hours "Thour" in each colum under each date, the
sum of the "Thour" before the week started colum 1, the sum of "Thour"
acquired for the week of the report columb 7 and a quarterly to date total of
"Thour" in colum8. I know Access is not desiged to be a spread sheet but the
data is in the DB and it would take week to input into a spread sheet.
 
G

Guest

Getting the daily totals can be fairly easy using the solution found at
http://www.tek-tips.com/faqs.cfm?fid=5466. You will need to change the date
interval from month to day.

Then you need to create separate totals queries that calculate the other
required totals. Combine the first crosstab query from above with these
totals queries to get everything your report needs.
 
G

Guest

Thank you! I was able to get the report to look as it should with one
exception, I get a Name? in the column labels "Text Boxes" and I am unable to
locate problem. I have check for error =DateAdd("M",0,Forms!frmA!txtEndDate).
Where do I go from here.
 
G

Guest

Where is your date range coming from? _Forms!frmA!txtEndDate_ is the latest
date in the range for my sample report. Can you provide some SQL views?
 
G

Guest

Here as requested.
PARAMETERS [Forms]![frmA]![txtEndDate] DateTime;
SELECT AuthorizedOWNOTDL_Crosstab.Expr2, AuthorizedOWNOTDL_Crosstab.[Total
Of otdliD], AuthorizedOTDL_Crosstab.[Total Of otdliD],
AuthorizedOPTOTDL_Crosstab.[Total Of otdliD],
AuthorizedOFFOTDL_Crosstab.[Total Of otdliD], AuthorizedOFFOTDL_Crosstab.OF0,
AuthorizedOPTOTDL_Crosstab.OP0, AuthorizedOWNOTDL_Crosstab.OWn0,
AuthorizedOTDL_Crosstab.MTh0, AuthorizedOFFOTDL_Crosstab.OF1,
AuthorizedOPTOTDL_Crosstab.OP1, AuthorizedOWNOTDL_Crosstab.OWn1,
AuthorizedOTDL_Crosstab.MTh1, AuthorizedOPTOTDL_Crosstab.OP2,
AuthorizedOFFOTDL_Crosstab.OF2, AuthorizedOWNOTDL_Crosstab.OWn2,
AuthorizedOTDL_Crosstab.MTh2, AuthorizedOFFOTDL_Crosstab.OF3,
AuthorizedOPTOTDL_Crosstab.OP3, AuthorizedOWNOTDL_Crosstab.OWn3,
AuthorizedOTDL_Crosstab.MTh3, AuthorizedOPTOTDL_Crosstab.OP4,
AuthorizedOFFOTDL_Crosstab.OF4, AuthorizedOWNOTDL_Crosstab.OWn4,
AuthorizedOTDL_Crosstab.MTh4, AuthorizedOFFOTDL_Crosstab.OF5,
AuthorizedOWNOTDL_Crosstab.OWn5, AuthorizedOPTOTDL_Crosstab.OP5,
AuthorizedOTDL_Crosstab.MTh5, AuthorizedOFFOTDL_Crosstab.OF6,
AuthorizedOPTOTDL_Crosstab.OP6, AuthorizedOWNOTDL_Crosstab.OWn6,
AuthorizedOTDL_Crosstab.MTh6
FROM AuthorizedOFFOTDL_Crosstab LEFT JOIN (AuthorizedOPTOTDL_Crosstab LEFT
JOIN (AuthorizedOTDL_Crosstab LEFT JOIN AuthorizedOWNOTDL_Crosstab ON
AuthorizedOTDL_Crosstab.Expr2 = AuthorizedOWNOTDL_Crosstab.Expr2) ON
AuthorizedOPTOTDL_Crosstab.Expr2 = AuthorizedOTDL_Crosstab.Expr2) ON
AuthorizedOFFOTDL_Crosstab.Expr2 = AuthorizedOPTOTDL_Crosstab.Expr2
ORDER BY AuthorizedOTDL_Crosstab.[Total Of otdliD];
 
G

Guest

Apparently you have a form (frmA) with a text box (txtEndDate) that is open
and has a ligitimate date value entered. Make sure the name of the text box
on the report is not the name of a field in your report's record source.

--
Duane Hookom
Microsoft Access MVP


Nick said:
Here as requested.
PARAMETERS [Forms]![frmA]![txtEndDate] DateTime;
SELECT AuthorizedOWNOTDL_Crosstab.Expr2, AuthorizedOWNOTDL_Crosstab.[Total
Of otdliD], AuthorizedOTDL_Crosstab.[Total Of otdliD],
AuthorizedOPTOTDL_Crosstab.[Total Of otdliD],
AuthorizedOFFOTDL_Crosstab.[Total Of otdliD], AuthorizedOFFOTDL_Crosstab.OF0,
AuthorizedOPTOTDL_Crosstab.OP0, AuthorizedOWNOTDL_Crosstab.OWn0,
AuthorizedOTDL_Crosstab.MTh0, AuthorizedOFFOTDL_Crosstab.OF1,
AuthorizedOPTOTDL_Crosstab.OP1, AuthorizedOWNOTDL_Crosstab.OWn1,
AuthorizedOTDL_Crosstab.MTh1, AuthorizedOPTOTDL_Crosstab.OP2,
AuthorizedOFFOTDL_Crosstab.OF2, AuthorizedOWNOTDL_Crosstab.OWn2,
AuthorizedOTDL_Crosstab.MTh2, AuthorizedOFFOTDL_Crosstab.OF3,
AuthorizedOPTOTDL_Crosstab.OP3, AuthorizedOWNOTDL_Crosstab.OWn3,
AuthorizedOTDL_Crosstab.MTh3, AuthorizedOPTOTDL_Crosstab.OP4,
AuthorizedOFFOTDL_Crosstab.OF4, AuthorizedOWNOTDL_Crosstab.OWn4,
AuthorizedOTDL_Crosstab.MTh4, AuthorizedOFFOTDL_Crosstab.OF5,
AuthorizedOWNOTDL_Crosstab.OWn5, AuthorizedOPTOTDL_Crosstab.OP5,
AuthorizedOTDL_Crosstab.MTh5, AuthorizedOFFOTDL_Crosstab.OF6,
AuthorizedOPTOTDL_Crosstab.OP6, AuthorizedOWNOTDL_Crosstab.OWn6,
AuthorizedOTDL_Crosstab.MTh6
FROM AuthorizedOFFOTDL_Crosstab LEFT JOIN (AuthorizedOPTOTDL_Crosstab LEFT
JOIN (AuthorizedOTDL_Crosstab LEFT JOIN AuthorizedOWNOTDL_Crosstab ON
AuthorizedOTDL_Crosstab.Expr2 = AuthorizedOWNOTDL_Crosstab.Expr2) ON
AuthorizedOPTOTDL_Crosstab.Expr2 = AuthorizedOTDL_Crosstab.Expr2) ON
AuthorizedOFFOTDL_Crosstab.Expr2 = AuthorizedOPTOTDL_Crosstab.Expr2
ORDER BY AuthorizedOTDL_Crosstab.[Total Of otdliD];


Duane Hookom said:
Where is your date range coming from? _Forms!frmA!txtEndDate_ is the latest
date in the range for my sample report. Can you provide some SQL views?
Click to expand...
Click to expand...
 
G

Guest

Thank You! Works like a champ.

Duane Hookom said:
Apparently you have a form (frmA) with a text box (txtEndDate) that is open
and has a ligitimate date value entered. Make sure the name of the text box
on the report is not the name of a field in your report's record source.

--
Duane Hookom
Microsoft Access MVP


Nick said:
Here as requested.
PARAMETERS [Forms]![frmA]![txtEndDate] DateTime;
SELECT AuthorizedOWNOTDL_Crosstab.Expr2, AuthorizedOWNOTDL_Crosstab.[Total
Of otdliD], AuthorizedOTDL_Crosstab.[Total Of otdliD],
AuthorizedOPTOTDL_Crosstab.[Total Of otdliD],
AuthorizedOFFOTDL_Crosstab.[Total Of otdliD], AuthorizedOFFOTDL_Crosstab.OF0,
AuthorizedOPTOTDL_Crosstab.OP0, AuthorizedOWNOTDL_Crosstab.OWn0,
AuthorizedOTDL_Crosstab.MTh0, AuthorizedOFFOTDL_Crosstab.OF1,
AuthorizedOPTOTDL_Crosstab.OP1, AuthorizedOWNOTDL_Crosstab.OWn1,
AuthorizedOTDL_Crosstab.MTh1, AuthorizedOPTOTDL_Crosstab.OP2,
AuthorizedOFFOTDL_Crosstab.OF2, AuthorizedOWNOTDL_Crosstab.OWn2,
AuthorizedOTDL_Crosstab.MTh2, AuthorizedOFFOTDL_Crosstab.OF3,
AuthorizedOPTOTDL_Crosstab.OP3, AuthorizedOWNOTDL_Crosstab.OWn3,
AuthorizedOTDL_Crosstab.MTh3, AuthorizedOPTOTDL_Crosstab.OP4,
AuthorizedOFFOTDL_Crosstab.OF4, AuthorizedOWNOTDL_Crosstab.OWn4,
AuthorizedOTDL_Crosstab.MTh4, AuthorizedOFFOTDL_Crosstab.OF5,
AuthorizedOWNOTDL_Crosstab.OWn5, AuthorizedOPTOTDL_Crosstab.OP5,
AuthorizedOTDL_Crosstab.MTh5, AuthorizedOFFOTDL_Crosstab.OF6,
AuthorizedOPTOTDL_Crosstab.OP6, AuthorizedOWNOTDL_Crosstab.OWn6,
AuthorizedOTDL_Crosstab.MTh6
FROM AuthorizedOFFOTDL_Crosstab LEFT JOIN (AuthorizedOPTOTDL_Crosstab LEFT
JOIN (AuthorizedOTDL_Crosstab LEFT JOIN AuthorizedOWNOTDL_Crosstab ON
AuthorizedOTDL_Crosstab.Expr2 = AuthorizedOWNOTDL_Crosstab.Expr2) ON
AuthorizedOPTOTDL_Crosstab.Expr2 = AuthorizedOTDL_Crosstab.Expr2) ON
AuthorizedOFFOTDL_Crosstab.Expr2 = AuthorizedOPTOTDL_Crosstab.Expr2
ORDER BY AuthorizedOTDL_Crosstab.[Total Of otdliD];


Duane Hookom said:
Where is your date range coming from? _Forms!frmA!txtEndDate_ is the latest
date in the range for my sample report. Can you provide some SQL views?
--
Duane Hookom
Microsoft Access MVP


:

Thank you! I was able to get the report to look as it should with one
exception, I get a Name? in the column labels "Text Boxes" and I am unable to
locate problem. I have check for error =DateAdd("M",0,Forms!frmA!txtEndDate).
Where do I go from here.
:

Getting the daily totals can be fairly easy using the solution found at
http://www.tek-tips.com/faqs.cfm?fid=5466. You will need to change the date
interval from month to day.

Then you need to create separate totals queries that calculate the other
required totals. Combine the first crosstab query from above with these
totals queries to get everything your report needs.

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is to look like an Excell spread sheet. Why, because
they think it is the only way they can understand the data. Anyway, I do not
know if it is possible but here are the requirements Data to be viewed
horizontally and to include "EmployeeName" with 8 colums (6 seperate Dates
for that week with total Hours "Thour" in each colum under each date, the
sum of the "Thour" before the week started colum 1, the sum of "Thour"
acquired for the week of the report columb 7 and a quarterly to date total of
"Thour" in colum8. I know Access is not desiged to be a spread sheet but the
data is in the DB and it would take week to input into a spread sheet.
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

Report Sums of Sums 1
specify week in a quarterly report 1
Reports, Sub-reports running sum and no data 2
Linking Report / Sub Report 0
Need Help Urgently - Deadline is Monday 1
Running total by group 1
2 subtotals from one report 3
Coping a whole row to another sheet 5

Top