Solved

creating report from table

Posted on 2011-09-28
2
200 Views
Last Modified: 2012-05-12
Hello,
have attached a sample db.
if you look at the table [tblFinal]
I need to create a report from that table- that displays like rptTest-but that works-
I created a crosstab query to create this report- but not sure this is how to do this-
in rptTest- i have a total field that is empty
i need a total field column to total all the weeks totals for each record.
and am trying to capture 1 week of data in report-
thankyou

db1.mdb
0
Comment
Question by:davetough
2 Comments
 
LVL 5

Accepted Solution

by:
Khalid Mehmood Awan earned 250 total points
ID: 36776230
Try this query...


SELECT  name,  round( sum(totalhours) , 2)
FROM tblFinal Group by  name


You can use this query to create a report.
For testing this query Goto Create then Query Design then add table then right click on query name (small tab) and goto SQL view.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 36812743
The issue with Crosstab "Reports" is that they hardcode the column headings

This means that if you run the report and 8/21/2011 is not in the dataset, the report will fail.
The same issuse arises when you have a new date, since it is not in the original report, it will fail.
See here for more info:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_24577123.html

You can create a "Dynamic" Crosstab report though...
http://support.microsoft.com/kb/328320
...But this is way too complex IMHO.

It might just be best to leave it as a crosstab query.

Finally an alternative may be to do this in a Pivot table.

JeffCoachman
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question