MsAccess Query/Report

[petrovlis]

I have a query from several Access-tables to make an offer to my clients.
I like to insert also the LATEST USDollar value,which I have also in
a (not yet linked) table called Forex with USD,EUR,PST,Date,ForexID fields.
I fill in every morning in my MSaccess Program (form) the values with
an automatic todays date (field fill-in)
I have clients name,quantity,Product,Price,Conditions,Payment etc in the
offer,which I then use in CR 8.5 and Visual Cut prog.
The problem I have (not an IT man)to get the lastest USD rate into my offer.
Thanks for any ideas and hlp.
William Rippen

 

[PHV]

Crete a saved query named, say qryLastForex, with this sql code:
SELECT USD, EUR, PST, [Date] As LastDate, ForexID
FROM Forex F
WHERE F.Date=(SELECT Max([Date]) FROM forex)
;
Then you may use qryLastForex as a single row table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[petrovlis]

Att PHV and Thanks for reply.
Have done and it is basically working,with a simple
Company and qryLastForex. (with no Links to each other)

If I continue the "test" query to one to many (e.g.one- company,many-products)and qryLastForex
then I get error *****Ambigous outer joins*** etc.

if I remove QryLastForex then it is without this error.

In my (already present ) query I tried to add the qryLastForex
but similar **ambigous outer joins** error.
Tried to follow Msofts help with subquery but not clear to me.

 

[PHV]

*****Ambigous outer joins***
Can you please post the sql code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[petrovlis]

Query without the FOREX as discussed reads:
SELECT Companies.CompanyID, Companies.Company, Companies.Address1, Companies.City, Companies.Zip, Companies.State, Countries.Country, Countries.Conti, Companies.Fax, Companies.Email, ProdInOut.ProdType, Products.Hot, Companies.Lang, IIf([Lang]="F",[USYNFR],IIf([Lang]="S",[USYNSP],IIf([Lang]="D",[USYNDU],IIf([Lang]="I",[USYNIT],IIf([Lang]="N",[USYNNL],IIf([Lang]="P",[USYNPO],[Product])))))) AS MMProd, IIf([Lang]="F",[UPACKFR],IIf([Lang]="S",[UPACKSP],IIf([Lang]="D",[UPACKDU],IIf([Lang]="I",[UPACKIT],IIf([Lang]="N",[UPACKNL],IIf([Lang]="P",[UPACKPO],[Packing])))))) AS MMPack, Products.FOBUSD, Products.FOBEUR, Products.CHIUSD, Products.Origin, Products.Contweight, Conditions.Conditions, Conditions.RdamAntFreight, Conditions.ChinaFreight, Persons.FirstName, Persons.LastName, Persons.MainPerson, Companies.CommPct, Companies.ExtraProfit, IIf([Origin]<>"EEC",Int(([CHIEUR]+([ChinaFreight]/([Contweight]/1000))+[ExtraProfit])/((100-[CommPct])/100)),Int(([FOBEUR]+([RdamAntFreight]/([Contweight]/1000))+[ExtraProfit])/((100-[CommPct])/100))) AS CFR, IIf([Lang]="F","Nous avons le plaisir de vous offrir, sans engagement:",IIf([Lang]="D","Wir freuen uns Ihnen freibleibend anzubieten:",IIf([Lang]="N","Wij kunnen U vrijblijvend aanbieden:",IIf([Lang]="S","Nos complace offrecerles, sujeto a confirmacion final",IIf([Lang]="I","Siamo soddisfatti di offrire",IIf([Lang]="P","Nós somos satisfeitos oferecê-lo:","We are pleased to offer you, subject final confirmation:")))))) AS MMTxtOff, IIf([Lang]="F","Demandez pour des autres produits !",IIf([Lang]="D","Bitte fragen Sie uns auch andere Produkte !",IIf([Lang]="S","Pida por favor nuestros otros productos !",IIf([Lang]="I","Chieda prego i nostri altri prodotti",IIf([Lang]="P","Peça por favor nossos outros produtos",IIf([Lang]="N","Vraag ook naar onze andere produkten","Please ask for others products !")))))) AS MMoreProd, Attach.Attach, PayAll.Pay, IIf([Lang]="D",[PayD],IIf([Lang]="F",[PayF],IIf([Lang]="N",[PayN],IIf([Lang]="S",[PayS],IIf([Lang]="I",[PayI],IIf([Lang]="P",[PayP],[PayE])))))) AS MMPayX, ProdInOut.Date
FROM (((((((Companies LEFT JOIN ProdInOut ON Companies.CompanyID = ProdInOut.CompanyID) LEFT JOIN Products ON ProdInOut.ProductID = Products.ProductID) LEFT JOIN Conditions ON Companies.ConditionsID = Conditions.ConditionsID) LEFT JOIN Countries ON Companies.CountryID = Countries.CountryID) LEFT JOIN Persons ON Companies.CompanyID = Persons.CompanyID) LEFT JOIN Payments ON Companies.PaymentID = Payments.PaymentID) LEFT JOIN Attach ON Companies.AttachID = Attach.AttachID) LEFT JOIN PayAll ON Companies.PayID = PayAll.PayID
GROUP BY Companies.CompanyID, Companies.Company, Companies.Address1, Companies.City, Companies.Zip, Companies.State, Countries.Country, Countries.Conti, Companies.Fax, Companies.Email, ProdInOut.ProdType, Products.Hot, Companies.Lang, IIf([Lang]="F",[USYNFR],IIf([Lang]="S",[USYNSP],IIf([Lang]="D",[USYNDU],IIf([Lang]="I",[USYNIT],IIf([Lang]="N",[USYNNL],IIf([Lang]="P",[USYNPO],[Product])))))), IIf([Lang]="F",[UPACKFR],IIf([Lang]="S",[UPACKSP],IIf([Lang]="D",[UPACKDU],IIf([Lang]="I",[UPACKIT],IIf([Lang]="N",[UPACKNL],IIf([Lang]="P",[UPACKPO],[Packing])))))), Products.FOBUSD, Products.FOBEUR, Products.CHIUSD, Products.Origin, Products.Contweight, Conditions.Conditions, Conditions.RdamAntFreight, Conditions.ChinaFreight, Persons.FirstName, Persons.LastName, Persons.MainPerson, Companies.CommPct, Companies.ExtraProfit, IIf([Origin]<>"EEC",Int(([CHIEUR]+([ChinaFreight]/([Contweight]/1000))+[ExtraProfit])/((100-[CommPct])/100)),Int(([FOBEUR]+([RdamAntFreight]/([Contweight]/1000))+[ExtraProfit])/((100-[CommPct])/100))), IIf([Lang]="F","Nous avons le plaisir de vous offrir, sans engagement:",IIf([Lang]="D","Wir freuen uns Ihnen freibleibend anzubieten:",IIf([Lang]="N","Wij kunnen U vrijblijvend aanbieden:",IIf([Lang]="S","Nos complace offrecerles, sujeto a confirmacion final",IIf([Lang]="I","Siamo soddisfatti di offrire",IIf([Lang]="P","Nós somos satisfeitos oferecê-lo:","We are pleased to offer you, subject final confirmation:")))))), IIf([Lang]="F","Demandez pour des autres produits !",IIf([Lang]="D","Bitte fragen Sie uns auch andere Produkte !",IIf([Lang]="S","Pida por favor nuestros otros productos !",IIf([Lang]="I","Chieda prego i nostri altri prodotti",IIf([Lang]="P","Peça por favor nossos outros produtos",IIf([Lang]="N","Vraag ook naar onze andere produkten","Please ask for others products !")))))), Attach.Attach, PayAll.Pay, IIf([Lang]="D",[PayD],IIf([Lang]="F",[PayF],IIf([Lang]="N",[PayN],IIf([Lang]="S",[PayS],IIf([Lang]="I",[PayI],IIf([Lang]="P",[PayP],[PayE])))))), ProdInOut.Date, Companies.MailtoS
HAVING (((Companies.Email)<>"0") AND ((ProdInOut.ProdType)="Out") AND ((Persons.MainPerson)=Yes) AND ((Companies.MailtoS)<>"0"))
ORDER BY IIf([Lang]="F",[USYNFR],IIf([Lang]="S",[USYNSP],IIf([Lang]="D",[USYNDU],IIf([Lang]="I",[USYNIT],IIf([Lang]="N",[USYNNL],IIf([Lang]="P",[USYNPO],[Product]))))));

 

[PHV]

And where is the problem with the sql code you posted ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[petrovlis]

with the qryLastForex is reads: (see in the middle:
SELECT Companies.CompanyID, Companies.Company, Companies.Address1, Companies.City, Companies.Zip, Companies.State, Countries.Country, Countries.Conti, Companies.Fax, Companies.Email, ProdInOut.ProdType, Products.Hot, Companies.Lang, IIf([Lang]="F",[USYNFR],IIf([Lang]="S",[USYNSP],IIf([Lang]="D",[USYNDU],IIf([Lang]="I",[USYNIT],IIf([Lang]="N",[USYNNL],IIf([Lang]="P",[USYNPO],[Product])))))) AS MMProd, IIf([Lang]="F",[UPACKFR],IIf([Lang]="S",[UPACKSP],IIf([Lang]="D",[UPACKDU],IIf([Lang]="I",[UPACKIT],IIf([Lang]="N",[UPACKNL],IIf([Lang]="P",[UPACKPO],[Packing])))))) AS MMPack, Products.FOBUSD, Products.FOBEUR, Products.CHIUSD, Products.Origin, Products.Contweight, Conditions.Conditions, Conditions.RdamAntFreight, Conditions.ChinaFreight, Persons.FirstName, Persons.LastName, Persons.MainPerson, Companies.CommPct, Companies.ExtraProfit, IIf([Origin]<>"EEC",Int(([CHIEUR]+([ChinaFreight]/([Contweight]/1000))+[ExtraProfit])/((100-[CommPct])/100)),Int(([FOBEUR]+([RdamAntFreight]/([Contweight]/1000))+[ExtraProfit])/((100-[CommPct])/100))) AS CFR, IIf([Lang]="F","Nous avons le plaisir de vous offrir, sans engagement:",IIf([Lang]="D","Wir freuen uns Ihnen freibleibend anzubieten:",IIf([Lang]="N","Wij kunnen U vrijblijvend aanbieden:",IIf([Lang]="S","Nos complace offrecerles, sujeto a confirmacion final",IIf([Lang]="I","Siamo soddisfatti di offrire",IIf([Lang]="P","Nós somos satisfeitos oferecê-lo:","We are pleased to offer you, subject final confirmation:")))))) AS MMTxtOff, IIf([Lang]="F","Demandez pour des autres produits !",IIf([Lang]="D","Bitte fragen Sie uns auch andere Produkte !",IIf([Lang]="S","Pida por favor nuestros otros productos !",IIf([Lang]="I","Chieda prego i nostri altri prodotti",IIf([Lang]="P","Peça por favor nossos outros produtos",IIf([Lang]="N","Vraag ook naar onze andere produkten","Please ask for others products !")))))) AS MMoreProd, Attach.Attach, PayAll.Pay, IIf([Lang]="D",[PayD],IIf([Lang]="F",[PayF],IIf([Lang]="N",[PayN],IIf([Lang]="S",[PayS],IIf([Lang]="I",[PayI],IIf([Lang]="P",[PayP],[PayE])))))) AS MMPayX, ProdInOut.Date, qryLastForex.USD
FROM qryLastForex, (((((((Companies LEFT JOIN ProdInOut ON Companies.CompanyID = ProdInOut.CompanyID) LEFT JOIN Products ON ProdInOut.ProductID = Products.ProductID) LEFT JOIN Conditions ON Companies.ConditionsID = Conditions.ConditionsID) LEFT JOIN Countries ON Companies.CountryID = Countries.CountryID) LEFT JOIN Persons ON Companies.CompanyID = Persons.CompanyID) LEFT JOIN Payments ON Companies.PaymentID = Payments.PaymentID) LEFT JOIN Attach ON Companies.AttachID = Attach.AttachID) LEFT JOIN PayAll ON Companies.PayID = PayAll.PayID
GROUP BY Companies.CompanyID, Companies.Company, Companies.Address1, Companies.City, Companies.Zip, Companies.State, Countries.Country, Countries.Conti, Companies.Fax, Companies.Email, ProdInOut.ProdType, Products.Hot, Companies.Lang, IIf([Lang]="F",[USYNFR],IIf([Lang]="S",[USYNSP],IIf([Lang]="D",[USYNDU],IIf([Lang]="I",[USYNIT],IIf([Lang]="N",[USYNNL],IIf([Lang]="P",[USYNPO],[Product])))))), IIf([Lang]="F",[UPACKFR],IIf([Lang]="S",[UPACKSP],IIf([Lang]="D",[UPACKDU],IIf([Lang]="I",[UPACKIT],IIf([Lang]="N",[UPACKNL],IIf([Lang]="P",[UPACKPO],[Packing])))))), Products.FOBUSD, Products.FOBEUR, Products.CHIUSD, Products.Origin, Products.Contweight, Conditions.Conditions, Conditions.RdamAntFreight, Conditions.ChinaFreight, Persons.FirstName, Persons.LastName, Persons.MainPerson, Companies.CommPct, Companies.ExtraProfit, IIf([Origin]<>"EEC",Int(([CHIEUR]+([ChinaFreight]/([Contweight]/1000))+[ExtraProfit])/((100-[CommPct])/100)),Int(([FOBEUR]+([RdamAntFreight]/([Contweight]/1000))+[ExtraProfit])/((100-[CommPct])/100))), IIf([Lang]="F","Nous avons le plaisir de vous offrir, sans engagement:",IIf([Lang]="D","Wir freuen uns Ihnen freibleibend anzubieten:",IIf([Lang]="N","Wij kunnen U vrijblijvend aanbieden:",IIf([Lang]="S","Nos complace offrecerles, sujeto a confirmacion final",IIf([Lang]="I","Siamo soddisfatti di offrire",IIf([Lang]="P","Nós somos satisfeitos oferecê-lo:","We are pleased to offer you, subject final confirmation:")))))), IIf([Lang]="F","Demandez pour des autres produits !",IIf([Lang]="D","Bitte fragen Sie uns auch andere Produkte !",IIf([Lang]="S","Pida por favor nuestros otros productos !",IIf([Lang]="I","Chieda prego i nostri altri prodotti",IIf([Lang]="P","Peça por favor nossos outros produtos",IIf([Lang]="N","Vraag ook naar onze andere produkten","Please ask for others products !")))))), Attach.Attach, PayAll.Pay, IIf([Lang]="D",[PayD],IIf([Lang]="F",[PayF],IIf([Lang]="N",[PayN],IIf([Lang]="S",[PayS],IIf([Lang]="I",[PayI],IIf([Lang]="P",[PayP],[PayE])))))), ProdInOut.Date, Companies.MailtoS, qryLastForex.USD
HAVING (((Companies.Email)<>"0") AND ((ProdInOut.ProdType)="Out") AND ((Persons.MainPerson)=Yes) AND ((Companies.MailtoS)<>"0"))
ORDER BY IIf([Lang]="F",[USYNFR],IIf([Lang]="S",[USYNSP],IIf([Lang]="D",[USYNDU],IIf([Lang]="I",[USYNIT],IIf([Lang]="N",[USYNNL],IIf([Lang]="P",[USYNPO],[Product]))))));

 

[petrovlis]

This is the mad qryLastForex:
SELECT F.USD, F.EUR, F.GBP, F.Date AS LastDate, F.ForexID
FROM Forex AS F
WHERE (((F.Date)=(Select Max([Date]) from Forex)));

 

[PHV]

Have you tried to replace this:
, qryLastForex.USD
FROM qryLastForex,
By this ?
, (SELECT USD FROM qryLastForex) As USD
FROM
You may have to amend the group by clause too.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[petrovlis]

Att PHV (MIS)
Thanks for the help, the (for me)difficult issue seems to be
quite simple,with your last remark,I changed the sql view with:
(select USD from qryLastforex) as USD from..
and it worked!!
The design-query is even more simple:

USDSelect USD from qryLastForex)
Total: Expression.

THANKS YOU VERY , VERY MUCH FOR ALL THE HELP.!!!!!
William Rippen.