424,054 Members | 1,055 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,054 IT Pros & Developers. It's quick & easy.

Comparing Two Fields Within the Same Table

P: 2
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.
Jul 29 '15 #1
Share this Question
Share on Google+
5 Replies


Seth Schrock
Expert 2.5K+
P: 2,911
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.
Expand|Select|Wrap|Line Numbers
  1. Switch(Field1 > Field2, "Increase", Field1 = Field2, "Same", Field1 < Field2, "Decrease", Field1 Is Null, "Stopped")
Jul 29 '15 #2

Rabbit
Expert Mod 10K+
P: 12,286
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.
Jul 29 '15 #3

NeoPa
Expert Mod 15k+
P: 31,170
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.
Jul 30 '15 #4

P: 2
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.
Aug 3 '15 #5

Seth Schrock
Expert 2.5K+
P: 2,911
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.
Aug 3 '15 #6

Post your reply

Sign in to post your reply or Sign up for a free account.