Another query question

  • Thread starter Thread starter Abay
  • Start date Start date
A

Abay

In a query can I select the data based on one set of criteria and in the
same query do calculations based on a different set of criteria .. I want to
select all data but only perform the calculations on some data ..

e.g.

My overall criteria .. select all clients or sometimes select only confirmed
clients, and then (secondary criteria) Calculate currency differences for
those clients who been quoted for a trip (travel agency stuff) in Euro but
paid in US dollars.

When I try to do this I get only one client (with the mis-matched
currencies) selected.

Is there a way to do this in one query or do I have to do a query for each
set of criteria and somehow merge/append the data?

Sql code:

SELECT Clients.Lname, Clients.Property, Clients.Cur_q, Clients.Amt_q,
Clients.Dep_amt_due, Clients.Cur_rec, Clients.Dep_amt_rec,
Clients.Date_fin_due, Clients.Fin_amt_rec, Rates.Eu_rate, Clients.Notes,
[Dep_amt_rec]+[Fin_amt_rec] AS Expr1, [Expr1]*[Eu_rate] AS Expr2,
[Expr1]-[Expr2] AS Expr3 INTO Tclcur
FROM Clients LEFT JOIN Rates ON Clients.Cur_rec = Rates.Country
WHERE (((Clients.Cur_q)="eu") AND ((Clients.Cur_rec)="us") AND
((Clients.Fin_amt_rec)>0));


Any help would be much appreciated.

Maggic
 
You could do something like have a table that has the quote and payment
currencies as the key, and the exchange rate as a value, and join that table
to your main table. Have a row for each case of quote and payment currencies
being the same, with the exchange rate being 1.
 
many thanks for your help .. will give it a try .. Maggic


Douglas J. Steele said:
You could do something like have a table that has the quote and payment
currencies as the key, and the exchange rate as a value, and join that table
to your main table. Have a row for each case of quote and payment currencies
being the same, with the exchange rate being 1.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Abay said:
In a query can I select the data based on one set of criteria and in the
same query do calculations based on a different set of criteria .. I want
to
select all data but only perform the calculations on some data ..

e.g.

My overall criteria .. select all clients or sometimes select only
confirmed
clients, and then (secondary criteria) Calculate currency differences for
those clients who been quoted for a trip (travel agency stuff) in Euro
but
paid in US dollars.

When I try to do this I get only one client (with the mis-matched
currencies) selected.

Is there a way to do this in one query or do I have to do a query for each
set of criteria and somehow merge/append the data?

Sql code:

SELECT Clients.Lname, Clients.Property, Clients.Cur_q, Clients.Amt_q,
Clients.Dep_amt_due, Clients.Cur_rec, Clients.Dep_amt_rec,
Clients.Date_fin_due, Clients.Fin_amt_rec, Rates.Eu_rate, Clients.Notes,
[Dep_amt_rec]+[Fin_amt_rec] AS Expr1, [Expr1]*[Eu_rate] AS Expr2,
[Expr1]-[Expr2] AS Expr3 INTO Tclcur
FROM Clients LEFT JOIN Rates ON Clients.Cur_rec = Rates.Country
WHERE (((Clients.Cur_q)="eu") AND ((Clients.Cur_rec)="us") AND
((Clients.Fin_amt_rec)>0));


Any help would be much appreciated.

Maggic
Click to expand...
Click to expand...
 
Back
Top