Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

creating report from table

Posted on 2011-09-28
2
Medium Priority
?
218 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 5

Accepted Solution

by:
Khalid Mehmood Awan earned 1000 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 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

688 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