Report to look like ExHell sheet

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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.
 
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.
 
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?
 
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];
 
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...
 
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...
 
Back
Top