stumped

[Lhuffst]

The report I'm trying to create has multiple columns.

The report needs sum by day by name so we end up with
Date Name ID TZ Tot Meter New Meter special etc
3/2/15 Dave 7124 1 15 0 0
3/2/15 Larry 907 22 29 1 1
3/2/15 Terrel 122 1 1 0 0

3/3/15 etc...

the problem I'm having is that the t,z column sums only certain types of meters, the special column is specific code (type of meter), the new meters has a new code as well.

the table that I get the codes from is

tbldataentry
dataEntryId    DataEntryCSIS
1              tst Mtr Results
2              Warranty Mtrs
3              Mtrs Processed
4              FH Mtrs

so the tz column is to add the numer of sheets if the dataEntryID is 1 or 2 or 4.
the New Meters adds the number of sheets if the dataentryid is 5

I tried a crosstab query and it worked fine if I only wanted to show one column and I could see where I could put conditiions on the columns.

here is the query I was trying
SELECT tblDataEntryTEST.DateAdded, TUserUsers.UserAbbrv, TUserUsers.WSSCID, tblDataEntryTEST.DataEntryType, tblDataEntryTEST.Sheets, tblDataEntryCSISType.DataEntryCSIS
FROM TUserUsers INNER JOIN (tblDataEntryTEST INNER JOIN tblDataEntryCSISType ON tblDataEntryTEST.DataEntryType = tblDataEntryCSISType.DataEntryID) ON TUserUsers.UserId = tblDataEntryTEST.UserId;

On the report I put a textbox (txtTZ) =IIf([DataEntryType]=1 Or [DataEntrytype]=2,[Sheets],0)
In the report summary I put another text box =Sum([txtTZ])
I don't get any errors but I also get all the records back and it doesn't sum properly
What am I doing wrong?
thanks
Lhuffst

 

[dhookom]

You can't sum a text box. When you say "number of sheets" do you actually mean number of records?

Can you use the Pre TGML tags so we understand your:
... we end up with
Date Name ID TZ Tot Meter New Meter special etc
3/2/15 Dave 7124 1 15 0 0
3/2/15 Larry 907 22 29 1 1
3/2/15 Terrel 122 1 1 0 0

3/3/15 etc...

It's unreadable.

Also, please add some carriage returns in your SQL so the lines are so long and remove statements from the code tag like "here is the query I was trying"

Duane
Hook'D on Access
MS Access MVP