VBA code to produce Access report

Topic

New Reply

I have an access database that I am trying to create a report from a query. The query contains information based on various departments. The report will need to list the topics on the left and have the department names aligned from left to right. I want to be able to read the data, calculate a percentage and display the answer under the correct department. It is similar to a crosstab query or a pivot table. I have the work on the code and I have it printing department #1 information everywhere. I can’t seem to get it to read the next department.

Here is a sample:

Standard…….Department01….Deparment 02..etc

Data correct……100%…………75%……….
Forms correct…..100%…………100%……..

The standards are hard coded in the report using a label. On the on format event, I go to the routine and and calculate the totals.
I know there is a design flaw, it would have been easier to have all the standards as records but this was inherited and I was told to produce this report. I know that this is feasible in VBA. I have a degree in programming and in my early years could get COBOL and BASIC to do anything. My knowledge of VBA is very limited.

Can anyone help? Is this as clear as mud?

Thanks,
Deborah

Reply | Quote

Replies

>>Is this as clear as mud?<<

Pretty much so. I'd start with trying to get this to work as much as possible with a crosstab query, then just use the crosstab as the recordsource for your report. In most cases with Access, you are better off doing the work in the query, rather than the report itself (and I realized this runs counter to how we used to do things in other languages).

I can't really offer anything more specific, because I'm not quite sure exactly what you are doing.

Reply | Quote

Mark,

Thanks for the reply. I have tried using a crosstab query and then producing the report from the query. The problem is design. A crosstab will only allow you to calculate one field not 70 fields. I would have to create a crosstab for every option and then link them together based on chart number. This would bog me in the mud! I have a query produced that gives me the correct totals by department. I have tried displaying with the Page setup and columns. It gives me the correct data but I can not display my standards on the left as row headings. I have it working in a pivot table but can not get the pivot table to print correctly from my switchboard. I will be sharing this data with about 200 directors and they need to click a button and get a report. The pivot table requires, Edit, go to excel and print. UUUGGGHHH!

I really need to redesign this monster but I don’t have the time to do it.

Thanks again!
Deborah

Reply | Quote