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
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