Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here




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


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?

RE: stumped

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"

Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close