Selecting between two values in a query

[NervousFred]

I have two different tables of data that have the same data execpt for one
column called "Asking."

I ahve both tables linked together by the field "Aircraft ID" and am pulling
data out of them using a table query. Is it possible to have Access cross
reference between the two "Asking" Fields and to put in the LOWER of the two
values table that is created by the table query.

A little issue I have is that some of the values in "Asking" might be "Make
Offer" or just blank and in either case I would want Access to pull which
ever values is a number and not "Make Offer' or blank.

Thanks!

 

[Jeff Boyce]

If you have "two ... tables of data that have the same data", you might need
to step back and reconsider whether you really need Access. That
description fits how you'd probably have to approach a business need (which
you haven't described) if you were limited to using a spreadsheet.

But Access is a relational database. You and Access will both have to work
much harder if you insist on feeding it 'sheet data.

You've describe "how" you are trying to do something. If you let us know a
bit more specifically about what having "two different tables of data that
have ..." allows you to do, the folks here may be able to offer alternate
approaches.

And yes, Access can do that, but it all starts with your data, so make sure
you have that well-normalized first...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Lord Kelvan]

i would say paste some sample data of what you have then make up some
sample data of what you want in the end result to help us

 

[NervousFred]

I am pulling data from two different scoures but since it is prices of
aircraft on a market then there is going to be some copies of prices. I need
to have it where the lower price is shown.

Here is some sample data.

Asking(Market Company) Asking(Researcher)
5,000,000 4,800,000
Make Offer 4,700,000
4,700,000 4,700,000


Now both of these have the prices of these 3 aircraft but the table on the
right has more accurate data becuase it was aquired by a researcher here at
my company. I need Access to differentiate between the two values and when I
run the table query only the lower values be put into the table.

If the table query shows make offer or a price that is higher than what our
company knows the price is then it does us no good.

A little catch is that I also need for the table query to show the lower
price from the Market Company if our researcher has a higher price.

In summary I need the lower of the two prices shown in the table created by
the table query as well as if there is a "Make Offer" in either of the
tables I need it to take which ever table as a number value.

I hope this clarifies some.

Thanks for the help agian!

 

[Jeff Boyce]

Not sure I understand the terminology you're using.

If you create a query and add a field in which you determine which of the
two sources/fields is lower, would that do it?

Take a look at the IIF() function ... you could use that to print one thing
(?Make Offer?) if your price was lower, or something else (?Too much!?) if
your price was higher.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[NervousFred]

I believe this is in the right direction.

So it is possible to make another field in the query to determine which of
two values from each field is lower? If so how do I do it?

With the IFF function, I will look more into it.

Jeff, I think that you have the understanding that I want to replace certian
values with "Make Offer" if the values are lower. This is not the case.

The tables that I am linked to come from dealers and they put in Make Offer
as their price. So make offer will allready be in the tables. I need the
query to ignore 'Make Offer' and pull the value from which ever table has a
numerical value and not a text value.

 

[Jeff Boyce]

Again, take a look at the IFF() function in Access HELP. I believe you will
be able to use this in a query to "calculate" what you seem to be after.

Regards

Jeff Boyce
Microsoft Office/Access MVP