Counting Inside the field

[kencpeck]

Hello,
I have a query and in one of the fields contain the following,

tot([Putaway]+[Replen]+[Wrong Loc]+[Receiv/Load]+[Mixed]+[Damage])*2)+(([Leftoff]+[Keying]+[Process Error])*8)+(([Mispick]+[Old Pt Values])*5)+([Adjust]*8)+([Misc]*1)+([1Pt Value]*1)+([2Pt Value]*2)+([3Pt Value]*3)+([4Pt Value]*4)+([5Pt Value]*5)+([6Pt Value]*6)+([7Pt Value]*7)


The [#Pt Values] are True/False checkboxes, while everything else is a number. when the boxes are checked it does not add them in.

I'm also having a problem with ([1Pt Value]*1) specifically. When it is checked it is adding 28 to the report.

please help!

 

[Dystonia]

Access stores -1 against any check boxes which are ticked and zero against any checkboxes which are unticked

When they are ticked your query is basically multiplying -1 by an integer which will produce a minus figure or by zero which will produce zero

 

[plog]

In check boxes, True = -1 and False = 0. So, if they are checked they should be subtracting from the total.

However, your whole methodology seems wrong for a database. It reeks of a spreadsheet mindset. I'm pretty sure your tables are not laid out properly. Each value that goes into the sum shouldn't have its own column. Instead it should be in its own row in a new table.

I highly suggest you look into normalization (https://en.wikipedia.org/wiki/Database_normalization) and structure your database properly.

 

[arnelgp]

You should multiple the chkboxes with - 1 to make thme positive. Add code to reauery your query on each chkboxes AfterUpdatr event.

 

[kencpeck]

We figured it out. Thanks anyways guys!

 

[Mark_]

kencpeck,

If you have solved this issue, can you please do the following;
1) Restate problem with solution (in case others encounter it)?
2) Mark the thread as "Solved"?

Sounds like an interesting problem that may be someone else's solution!