DLookup? 1

[Kobayashi]

Hi,

Apologies, it's been a while since I've done anything in Access and my brain has turned to mush! I'm sure this is very simple....

I have a table with some amounts with differing local currencies. I have another table with the currencies and the GBP equivalents.

I need to run a query to convert the local currencies in table A to the GBP equivalents in table B.

Seems very straightforward but I just can't seem to achieve it. The queries I'm writing that do achieve it are sooooooo slow so there must be a better way?

Thanks,

Adrian

 

[PHV]

The queries I'm writing that do achieve it
Any chance you can post the SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Kobayashi]

As you can see, if it was just one field conversion I could make an inner join and be done but because there are 3 fields that all have to be converted I have the below. It was working, albeit, taking forever to run, but now it's not even doing that...

You'll also see that I have an expression to calcualte the total of the 3 'converted' sums. Again, not sure if this is efficient?

Appreciate the help...



SELECT tblBrokerageFeeDataAll.prodtype AS [Product Type], Mid(["Site],2,1) AS Site, tblBrokerageFeeDataAll.exch, [tblbrokeragefeedataall].[commission]*[fx].[gbp monthly average rate] AS [Commission GBP], [tblbrokeragefeedataall].[brokeragefee]*[fx].[gbp monthly average rate] AS [BrokerageFee GBP], [tblbrokeragefeedataall].[miscellaneous]*[fx].[gbp monthly average rate] AS [Misc GBP], Sum([commission gbp]+[misc gbp]+[brokeragefee gbp]) AS [Total Fee 2010]
FROM tblBrokerageFeeDataAll, FX, [Broker Group]
WHERE (((tblBrokerageFeeDataAll.[Commission Currency])=[fx].[ccy])) OR (((tblBrokerageFeeDataAll.[BrokerageFee Currency])=[fx].[ccy])) OR (((tblBrokerageFeeDataAll.[Miscellaneous Currency])=[fx].[ccy]))
GROUP BY tblBrokerageFeeDataAll.prodtype, Mid(["Site],2,1), tblBrokerageFeeDataAll.exch, [tblbrokeragefeedataall].[commission]*[fx].[gbp monthly average rate], [tblbrokeragefeedataall].[brokeragefee]*[fx].[gbp monthly average rate], [tblbrokeragefeedataall].[miscellaneous]*[fx].[gbp monthly average rate]
HAVING (((tblBrokerageFeeDataAll.prodtype)="Equity"))
ORDER BY Mid(["Site],2,1), tblBrokerageFeeDataAll.exch;

 

[dhookom]

A totals query is not updateable. You would need to lose the GROUP BY.

Are you attempting to update some values or simply display calculated values.

Duane
Hook'D on Access
MS Access MVP

 

[Kobayashi]

I just want to display them via the calculated expressions in the select query, I don't need to update any values in a table.

 

[PHV]

What is [Broker Group] ?
It appears in the FROM clause but without any join condition ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Kobayashi]

Ah, that was the next step.... I've taken it out and the adjusted code (1st set) below works again.

However, the second set of code is what I'm trying to do; add another criteria but this returns ambiguos outer joins. Which is why I was reversing my steps to try and get it to work.

So, in addition to the 1st code below, which now works, I also have a field (cptycode) in the tblbrokeragefeedataall table. I then have the Broker group table which has the same field. I am trying to exclude records whereby another field in the Broker Group table (Group Broker field) equals a certain value but clearly joining on the cptycode fields, adding the 'Group Broker' field to the query and then filtering out <>"Value" clearly doesn't work?

Again, really appreciate your help with this.



SELECT tblBrokerageFeeDataAll.prodtype AS [Product Type], Mid(["Site],2,1) AS Site, tblBrokerageFeeDataAll.exch, [tblbrokeragefeedataall].[commission]*[fx].[gbp monthly average rate] AS [Commission GBP], [tblbrokeragefeedataall].[brokeragefee]*[fx].[gbp monthly average rate] AS [BrokerageFee GBP], [tblbrokeragefeedataall].[miscellaneous]*[fx].[gbp monthly average rate] AS [Misc GBP], Sum([commission gbp]+[misc gbp]+[brokeragefee gbp]) AS [Total Fee 2010]
FROM tblBrokerageFeeDataAll, FX
WHERE (((tblBrokerageFeeDataAll.[Commission Currency])=[fx].[ccy])) OR (((tblBrokerageFeeDataAll.[BrokerageFee Currency])=[fx].[ccy])) OR (((tblBrokerageFeeDataAll.[Miscellaneous Currency])=[fx].[ccy]))
GROUP BY tblBrokerageFeeDataAll.prodtype, Mid(["Site],2,1), tblBrokerageFeeDataAll.exch, [tblbrokeragefeedataall].[commission]*[fx].[gbp monthly average rate], [tblbrokeragefeedataall].[brokeragefee]*[fx].[gbp monthly average rate], [tblbrokeragefeedataall].[miscellaneous]*[fx].[gbp monthly average rate]
HAVING (((tblBrokerageFeeDataAll.prodtype)="Equity"))
ORDER BY Mid(["Site],2,1), tblBrokerageFeeDataAll.exch;




SELECT tblBrokerageFeeDataAll.prodtype AS [Product Type], Mid(["Site],2,1) AS Site, tblBrokerageFeeDataAll.exch, [tblbrokeragefeedataall].[commission]*[fx].[gbp monthly average rate] AS [Commission GBP], [tblbrokeragefeedataall].[brokeragefee]*[fx].[gbp monthly average rate] AS [BrokerageFee GBP], [tblbrokeragefeedataall].[miscellaneous]*[fx].[gbp monthly average rate] AS [Misc GBP], Sum([commission gbp]+[misc gbp]+[brokeragefee gbp]) AS [Total Fee 2010], [Broker Group].[Group Broker]
FROM FX, tblBrokerageFeeDataAll LEFT JOIN [Broker Group] ON tblBrokerageFeeDataAll.cptyname = [Broker Group].cptyname
WHERE (((tblBrokerageFeeDataAll.[Commission Currency])=[fx].[ccy])) OR (((tblBrokerageFeeDataAll.[BrokerageFee Currency])=[fx].[ccy])) OR (((tblBrokerageFeeDataAll.[Miscellaneous Currency])=[fx].[ccy]))
GROUP BY tblBrokerageFeeDataAll.prodtype, Mid(["Site],2,1), tblBrokerageFeeDataAll.exch, [tblbrokeragefeedataall].[commission]*[fx].[gbp monthly average rate], [tblbrokeragefeedataall].[brokeragefee]*[fx].[gbp monthly average rate], [tblbrokeragefeedataall].[miscellaneous]*[fx].[gbp monthly average rate], [Broker Group].[Group Broker]
HAVING (((tblBrokerageFeeDataAll.prodtype)="Equity") AND (([Broker Group].[Group Broker])<>"value"))
ORDER BY Mid(["Site],2,1), tblBrokerageFeeDataAll.exch;

 

[PHV]

What about this ?

SELECT A.prodtype AS [Product Type], Mid(["Site],2,1) AS Site, A.exch
, A.commission*FC.[gbp monthly average rate] AS [Commission GBP]
, A.brokeragefee*FB.[gbp monthly average rate] AS [BrokerageFee GBP]
, A.miscellaneous*FM.[gbp monthly average rate] AS [Misc GBP]
, A.commission*FC.[gbp monthly average rate]+A.brokeragefee*FB.[gbp monthly average rate]+A.miscellaneous*FM.[gbp monthly average rate] AS [Total Fee 2010]
FROM ((tblBrokerageFeeDataAll A
INNER JOIN FX FC ON A.[Commission Currency]=FC.ccy)
INNER JOIN FX FB ON A.[BrokerageFee Currency]=FB.ccy)
INNER JOIN FX FM ON A.[Miscellaneous Currency]=FM.ccy
WHERE A.prodtype='Equity'
AND A.cptyname NOT IN (SELECT cptyname FROM [Broker Group] WHERE [Group Broker]='value')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Kobayashi]

I'm only getting two records? The inner joins work when I have just one value to calculate but they seem to filter out more and more records?
I've added your condition with the previous code and it seems to be more like what I'd expect?

Thank you very much for your help!




SELECT tblBrokerageFeeDataAll.prodtype AS [Product Type], Mid(["Site],2,1) AS Site, tblBrokerageFeeDataAll.cptyname, tblBrokerageFeeDataAll.exch, [tblbrokeragefeedataall].[commission]*[fx].[gbp monthly average rate] AS [Commission GBP], [tblbrokeragefeedataall].[brokeragefee]*[fx].[gbp monthly average rate] AS [BrokerageFee GBP], [tblbrokeragefeedataall].[miscellaneous]*[fx].[gbp monthly average rate] AS [Misc GBP], Sum([commission gbp]+[misc gbp]+[brokeragefee gbp]) AS [Total Fee 2010]
FROM tblBrokerageFeeDataAll, FX
WHERE (((tblBrokerageFeeDataAll.[Commission Currency])=[fx].[ccy]) AND ((tblBrokerageFeeDataAll.cptyname) Not In (SELECT cptyname FROM [Broker Group] WHERE [Group Broker]='RBS'))) OR (((tblBrokerageFeeDataAll.[BrokerageFee Currency])=[fx].[ccy])) OR (((tblBrokerageFeeDataAll.[Miscellaneous Currency])=[fx].[ccy]))
GROUP BY tblBrokerageFeeDataAll.prodtype, Mid(["Site],2,1), tblBrokerageFeeDataAll.cptyname, tblBrokerageFeeDataAll.exch, [tblbrokeragefeedataall].[commission]*[fx].[gbp monthly average rate], [tblbrokeragefeedataall].[brokeragefee]*[fx].[gbp monthly average rate], [tblbrokeragefeedataall].[miscellaneous]*[fx].[gbp monthly average rate]
HAVING (((tblBrokerageFeeDataAll.prodtype)="Equity"))
ORDER BY Mid(["Site],2,1), tblBrokerageFeeDataAll.exch;

 

[PHV]

And this ?

SELECT A.prodtype AS [Product Type], Mid(["Site],2,1) AS Site, A.exch
, A.commission*Nz(FC.[gbp monthly average rate],1) AS [Commission GBP]
, A.brokeragefee*Nz(FB.[gbp monthly average rate],1) AS [BrokerageFee GBP]
, A.miscellaneous*Nz(FM.[gbp monthly average rate],1) AS [Misc GBP]
, Nz(A.commission*Nz(FC.[gbp monthly average rate],1),0)+Nz(A.brokeragefee*Nz(FB.[gbp monthly average rate],1),0)+Nz(A.miscellaneous*Nz(FM.[gbp monthly average rate],1),0) AS [Total Fee 2010]
FROM ((tblBrokerageFeeDataAll A
LEFT JOIN FX FC ON A.[Commission Currency]=FC.ccy)
LEFT JOIN FX FB ON A.[BrokerageFee Currency]=FB.ccy)
LEFT JOIN FX FM ON A.[Miscellaneous Currency]=FM.ccy
WHERE A.prodtype='Equity'
AND A.cptyname NOT IN (SELECT cptyname FROM [Broker Group] WHERE [Group Broker]='value')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Kobayashi]

I need to validate but that seems perfect!

I tried using left joins (honest) but couldn't use more than one of them? Is it your variables that has allowed this to work or did I just miss something big time?
Would be good to know so that I can, hopefully, understand where I went wrong so as to try and not make the same mistakes again?

Thanks again, it never ceases to amaze me how people like you can give up your time to help others! Hopefully one day I'll be in a position to do the same!

Many thanks indeed once again!!!

 

[Kobayashi]

To try and gain more understanding I've rebuilt the query without the variables and I've done the left joins from the 3 ccy fields all to the one CCY field in the FX table. I now have different results? Again, just for my understanding, why is this? Here is the code if it helps:

SELECT tblBrokerageFeeDataAll.prodtype AS [Product Type], Mid(["Site],2,1) AS Site, tblBrokerageFeeDataAll.exch, [tblBrokerageFeeDataAll].[commission]*Nz([Fx].[gbp monthly average rate],1) AS [Commission GBP], [tblBrokerageFeeDataAll].[brokeragefee]*Nz([Fx].[gbp monthly average rate],1) AS [BrokerageFee GBP], [tblBrokerageFeeDataAll].[miscellaneous]*Nz([Fx].[gbp monthly average rate],1) AS [Misc GBP], Nz([tblBrokerageFeeDataAll].[commission]*Nz([Fx].[gbp monthly average rate],1),0)+Nz([tblBrokerageFeeDataAll].[brokeragefee]*Nz([Fx].[gbp monthly average rate],1),0)+Nz([tblBrokerageFeeDataAll].[miscellaneous]*Nz([Fx].[gbp monthly average rate],1),0) AS [Total Fee 2010]
FROM tblBrokerageFeeDataAll LEFT JOIN FX ON (tblBrokerageFeeDataAll.[BrokerageFee Currency] = FX.CCY) AND (tblBrokerageFeeDataAll.[Miscellaneous Currency] = FX.CCY) AND (tblBrokerageFeeDataAll.[Commission Currency] = FX.CCY)
WHERE (((tblBrokerageFeeDataAll.prodtype)='Equity') AND ((tblBrokerageFeeDataAll.cptyname) Not In (SELECT cptyname FROM [Broker Group] WHERE [Group Broker]='value')));

 

[PHV]

I now have different results
Are they correct ?
I guess NO...

query without the variables
My suggestion had NO variable but ALIAS.
Aliases are mandatory to differentiate the 3 instances of the FX table.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Kobayashi]

Is there a way to now group by the 'Exchange: exch' field? Tried to query this query from another query using totals but getting an 'overflow' error?

 

[Kobayashi]

Apologies, getting lazy!

Had a play around and have resolved it.

Thanks.

 

[Kobayashi]

I would now like to add another field that shows the 'Broker Group' name that has the largest 'Total Fee 2010 (excl RBS)' total? I've looked through lots of threads and can achieve this with a simple query on it's own but not nesting into the sql string below?
Any ideas?
Below is the current, working sql, without the new field.



SELECT A.prodtype AS [Product Type], Mid(["Site],2,1) AS Site, A.exch AS Exchange, [Broker Group].[Group Broker], Sum(A.commission/Nz(FC.[gbp monthly average rate],1)) AS [Commission GBP (excl BBS)], Sum(A.brokeragefee/Nz(FB.[gbp monthly average rate],1)) AS [BrokerageFee GBP (excl BBS)], Sum(A.miscellaneous/Nz(FM.[gbp monthly average rate],1)) AS [Miscellaneous GBP (excl BBS)], Sum(A.tradingfee/Nz(Ft.[gbp monthly average rate],1)) AS [Trading GBP (excl BRBS)], Sum(A.executionfee/Nz(Fe.[gbp monthly average rate],1)) AS [Execution GBP (excl BBS)], Sum(A.ubsexecution/Nz(FU.[gbp monthly average rate],1)) AS [UBS Execution GBP (excl BBS)], Sum(Nz(A.commission/Nz(FC.[gbp monthly average rate],1),0)+Nz(A.brokeragefee/Nz(FB.[gbp monthly average rate],1),0)+Nz(A.miscellaneous/Nz(FM.[gbp monthly average rate],1),0)+Nz(A.executionfee/Nz(Fe.[gbp monthly average rate],1),0)+Nz(A.ubsexecution/Nz(Fu.[gbp monthly average rate],1),0)+Nz(A.tradingfee/Nz(Ft.[gbp monthly average rate],1),0)) AS [Total Fee 2010 (excl BBS)], Sum(a.bgnqty*a.bgnprice*a.confact) AS [Notional(qtyxpricexcf)]
FROM ((((((tblBrokerageFeeDataAll AS A LEFT JOIN FX AS FC ON A.[Commission Currency] = FC.CCY) LEFT JOIN FX AS FB ON A.[BrokerageFee Currency] = FB.CCY) LEFT JOIN FX AS FM ON A.[Miscellaneous Currency] = FM.CCY) LEFT JOIN FX AS FE ON A.[Executionfee Currency] = FE.CCY) LEFT JOIN FX AS FT ON A.[TradingFee Currency] = FT.CCY) LEFT JOIN FX AS FU ON A.[UBSExecution Currency] = FU.CCY) LEFT JOIN [Broker Group] ON A.cptyname = [Broker Group].cptyname
WHERE (((A.cptyname) Not In (SELECT cptyname FROM [Broker Group] WHERE [Group Broker]='BBS')))
GROUP BY A.prodtype, Mid(["Site],2,1), A.exch, [Broker Group].[Group Broker]
HAVING (((A.prodtype)='Equity'))
ORDER BY Mid(["Site],2,1), A.exch, [Broker Group].[Group Broker];

 

[Kobayashi]

If anybody can help then I should probably mention that I'm also trying to add the columns:

Top1, Top2, Top3, Top4 etc. each of which will show the 1st, 2nd, 3rd etc. largest 'Broker Group' totals. It will look something like:

Exchange Name Total sum.... Top1 Top 2
Exchange X ex. 1000 Broker x Broker y
Exchange Y ex. 2000 Broker z Broker x

etc..