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
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
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?