Comparing columns

[Pvarga]

I have 2 columns that contain results of testing. I need to show whether there was a gain or loss in scores. I have a code (1,2,3,or 4) instead of the text to make it easier. I need to have my query compare the two and tell me if the difference between the 2 is > < or =. I am new to Access and have tried to find this answer by reading old posts but if it is there I am not recognizing it. Also if it is a string to type in please tell me where!

Thanks

Tricia

Thanks

 

[Fornatian]

Include both columns in a new query

In a new column, on the same line as the other fields type:

Difference: [Col1]-[Col2]

If you sort by that column you will be able to see the differences grouped. Additionally to only those that have improved put >0 in the criteria for the calculated field.

Ian

 

[Jack Cowley]

Create a query and drag the two columns to the QBE grid if you want to show them. In the third column put this in the top field:

Results: IIf([Column1]>[Column2],"Increase",IIf([Column1]<[Column2],"Decrease","Equal"))

If you do not want to show the two columns you only need the one with the code above. Be sure and change Column1 and Column2 in the code above to the actual names of the fields in your table with the data in them.

 

[Pvarga]

This works great but I forgot a couple things. First if someone has not tested it shows a zero as pre and post and counts as staying the same which will throw off the statistics. And I also need to run this to show how many were <> or + for each level of each subject. Should I try to make a really big query or several small ones?

Thanks, you are so good at this.

Tricia