Report Question

[ccoffelt]

I am writing a report using MS Office Access 2003. Currently the output looks like this:

Department Name
Equipment # Category Hrs.
OPR-012 BM 2
OPR-058 CM 3
OPR-061 BM 2

I want it to look like this:

Department Name
Equipment # BM CM
OPR-012 2
OPR-058 3
OPR-061 2

Is there a way to do this???

 

[Lilliabeth]

hard to say without knowing the structure of the tables, but here's a guess that is probably close to correct:
control source property of a new textbox:

=IIF([Category]="BM",[Hrs],"")

--Lilliabeth

 

[ccoffelt]

Thanks! That works great. Now, can you tell me how to sum each column now?

 

[lespaul]

you mean for a total at the bottom of the report?

Leslie

Have you met Hardy Heron?

 

[ccoffelt]

I want a some of each category. I used the suggested formula below in the category field:

Department Name
Equipment # BM CM
OPR-012 =IIF([Category]="BM",Hrs],"")
OPR-058
OPR-061

Got the ouput I was looking for which is listed below:

Department Name
Equipment # BM CM
OPR-012 2
OPR-058 3
OPR-061 2

Now I want: Total 4 3

 

[Lilliabeth]

Name the... =IIF([Category]="BM",Hrs],"") ...textbox something like txtBM.

Then, in the report footer, create a textbox with this control source:

=SUM([txtBM])

--Lilliabeth

 

[ccoffelt]

I tried that and when I ran the report I entered the start date 7/1/08, end date 7/24/08, and it came up with a box to Enter Parameter Value txtBM??? Ugh

 

[lespaul]

you're not adding it to the query are you? you add it to the REPORT....

Leslie

Have you met Hardy Heron?

 

[ccoffelt]

Nope didn't add it to the query. Added it to the REPORT

 

[lespaul]

if you are getting a parameter value prompt then somewhere in the query is txtBM...what's the SQL?

Leslie

 

[Lilliabeth]

Did you name the original textbox to txtBM?

Did you put the new textbox in the Report Footer section?


--Lilliabeth

 

[Lilliabeth]

I'm really sorry to have misspoken.

Try this in the report footer:

=Sum(IIF([Category]="BM",[Hrs],0)

--Lilliabeth

 

[ccoffelt]

No need to apologize. I tried the latest suggestion and it worked great! Thanks so much for your help Lilliabeth.