Making a query that creates a value base on a table value

  • Thread starter Thread starter james
  • Start date Start date
J

james

Hi
I have a table that has two fields (plus a KEY)
CustomerName
and
BUYSELL

I want to create a query that shows each record with either "RHS" if the
value in BUYSELL = "BUY" or "LHS" if the value in BUYSELL = "SELL"
It does not need to save this in a table just needs to show it on running of
the query. How do I do this? Probably very easy but my knowledge of access
isn't great.

So basically now if I create a query to show all data it will show the
customer names and either "BUY" or "SELL" next to it. I want the query to
show the customer name and the word "RHS" if the value in BUYSELL is "SELL"
or "LHS" if the value in BUYSELL is "BUY". I don't want it to amend the data
in teh table though.

Hope this makes sense.


Thanks
 
James -

In your query, add a field with the following code:

IIf([BUYSELL]="SELL","LHS","RHS")

This assumes either BUY or SELL is in the field. If there can be other
values or nulls, then use the following:

IIf(nz([BUYSELL],"X")="SELL","LHS",IIf(nz([BUYSELL],"X")="BUY","RHS",""))
 
james said:
Hi
I have a table that has two fields (plus a KEY)
CustomerName
and
BUYSELL

I want to create a query that shows each record with either "RHS" if the
value in BUYSELL = "BUY" or "LHS" if the value in BUYSELL = "SELL"
It does not need to save this in a table just needs to show it on running
of
the query. How do I do this? Probably very easy but my knowledge of access
isn't great.

So basically now if I create a query to show all data it will show the
customer names and either "BUY" or "SELL" next to it. I want the query to
show the customer name and the word "RHS" if the value in BUYSELL is
"SELL"
or "LHS" if the value in BUYSELL is "BUY". I don't want it to amend the
data
in teh table though.
Click to expand...


This is what is called a "calculated field", and it's quite simple. In SQL
View, your query would look something like this:

SELECT
CustomerName,
IIf(BUYSELL="BUY", "RHS", IIf(BUYSELL="SELL", "LHS", Null))
As LHRH
FROM YourTable
ORDER BY CustomerName

Youi didn't say what you wanted to return if BUYSELL is neither "BUY" no
"SELL", so I made the expression return Null in that case.
 
estan en malga y torremolinos dentro de dos dias se quieren ir a francia y
escaparse los dos estan infectados con tenia
 
quiero daros las gracias porque si estoy embarazada lo cuidarew porque
confio en vosotros
besos y un saludo erika thais
 
en realidad no me hagais mucho caso porque he perdido un poco la intuicion y
yo siempre me he guiado por mis instintos o mi intuicion tendre que volver
a desarrollarla ahora me guio por la inteligencia que es lo que me queda
pero espero que trateis bien a mi familia porque no se merecen que les hagan
daño ok besos erika
 
Back
Top