Comparing Two Fields Within the Same Table

[KimE]

I have a database of rents for all of my company's offices. I want to create a query that shows when there is an increase or decrease.

I would like a returned phrase like INCREASE, DECREASE, SAME, or STOPPED when the query runs.

I have tried many different codes and none worked with error messages such as bad syntax and etc.

Thank you for any and all help.

[Seth Schrock]

Assuming that you are wanting to compare two fields in the same record, you could use the Switch function either in a query or in a textbox's control source.

Switch(Field1 > Field2, "Increase", Field1 = Field2, "Same", Field1 < Field2, "Decrease", Field1 Is Null, "Stopped")

[Rabbit]

If you are not talking about two fields in the same row, then you have not given us any information about your data and how it's structured and we can't help you without that. All we can say is join the table to itself to get your two related rows together.

[NeoPa]

I would guess that Rabbit has this right Kim.
You need to give us the full question with the basic details if you want us even to be able to help you.

We want to, but you need to ensure we have the basics at least in order to be able to.

[KimE]

Yes, the fields are in the same row. The column headers are the months and year (AUG-2015, SEP-2015, etc) and the first cell of each row is the office name.

I hope this helps.

[Seth Schrock]

Then my solution in post #2 would work. However, it seems like your database isn't normalized. Problems like this become a whole lot simpler in a normalized database. See Database Normalization and Table Structures.