Combining the following queries without getting query too complex error

[ravi]

I have created the following interest to calculate the interest for
the following currency pairs. I have tried to combine them in macros
using conditions but the next query that is run in the macro ends up
deleting the previous interest value that has been generated by the
query. For example if query 1 is run on the table with currency pair
USD/CHF then the interest will be updated without any problem but if
there is another entry in the table with another currency pair for
example EUR/JPY which is calculated say in query two then the interest
of USD/CHF which has been calculated previously will be deleted. I
think the only way to solve this would be to combine the queries
instead of seperating them using macro conditions. Please tell me how
I would appreciate it alot.

This is query1:
UPDATE [Spot Open Positions], [Sell Rate], [Buy Rate] SET [Spot Open
Positions].[INTEREST WED] = IIf([Spot Open Positions].[Sell]>0 And
[Spot Open Positions].[ITEM]='USD/CHF' AND [Spot Open
Positions].[DAY]=4,([Sell Rate].[USD/CHF]*[Spot Open
Positions].[Sell]*100000*3)/36000,IIf([Spot Open Positions].[Sell]>0
And [Spot Open Positions].[ITEM]='EUR/USD' AND [Spot Open
Positions].[DAY]=4,([Sell Rate].[EUR/USD]*[Spot Open
Positions].[Sell]*100000*3)/36000,IIf([Spot Open Positions].[Sell]>0
And [Spot Open Positions].[ITEM]='USD/JPY'AND [Spot Open
Positions].[DAY]=4,([Sell Rate].[USD/JPY]*[Spot Open
Positions].[Sell]*100000*3)/36000,IIf([Spot Open Positions].[Sell]>0
And [Spot Open Positions].[ITEM]='GBP/USD' AND [Spot Open
Positions].[DAY]=4,([Sell Rate].[GBP/USD]*[Spot Open
Positions].[Sell]*100000*3)/36000,IIf([Spot Open Positions].[Sell]>0
And [Spot Open Positions].[ITEM]='AUD/USD' AND [Spot Open
Positions].[DAY]=4,([Sell Rate].[AUD/USD]*[Spot Open
Positions].[Sell]*100000*3)/36000,IIf([Spot Open Positions].[Sell]>0
And [Spot Open Positions].[ITEM]='XAU/USD' AND [Spot Open
Positions].[DAY]=4,([Sell Rate].[XAU/USD]*[Spot Open
Positions].[Sell]*100000)/36000*3,IIf([Spot Open Positions].[Buy]>0
And [Spot Open Positions].[ITEM]='USD/CHF' AND [Spot Open
Positions].[DAY]=4,([Buy Rate].[USD/CHF]*[Spot Open
Positions].[Buy]*100000*3)/36000,IIf([Spot Open Positions].[Buy]>0 And
[Spot Open Positions].[ITEM]='EUR/USD' AND [Spot Open
Positions].[DAY]=4,([Buy Rate].[EUR/USD]*[Spot Open
Positions].[Buy]*100000*3)/36000,IIf([Spot Open Positions].[Buy]>0 And
[Spot Open Positions].[ITEM]='USD/JPY' AND [Spot Open
Positions].[DAY]=4,([Buy Rate].[USD/JPY]*[Spot Open
Positions].[Buy]*100000*3)/36000,IIf([Spot Open Positions].[Buy]>0 And
[Spot Open Positions].[ITEM]='GBP/USD' AND [Spot Open
Positions].[DAY]=4,([Buy Rate].[GBP/USD]*[Spot Open
Positions].[Buy]*100000*3)/36000,IIf([Spot Open Positions].[Buy]>0 And
[Spot Open Positions].[ITEM]='AUD/USD' AND [Spot Open
Positions].[DAY]=4,([Buy Rate].[AUD/USD]*[Spot Open
Positions].[Buy]*100000*3)/36000,IIf([Spot Open Positions].[Buy]>0 And
[Spot Open Positions].[ITEM]='XAU/USD' AND [Spot Open
Positions].[DAY]=4,([Buy Rate].[XAU/USD]*[Spot Open
Positions].[Buy]*100000*3)/36000)))))))))) ));

This is query2:
UPDATE [Spot Open Positions], [Sell Rate], [Buy Rate] SET [Spot Open
Positions].INTEREST = IIf([Spot Open Positions].[Buy]>0 And [Spot Open
Positions].[ITEM]='EUR/JPY' AND NOT [Spot Open
Positions].[DAY]=4,((([Buy Rate].[EUR/USD]+[Buy
Rate].[USD/JPY])/2)*[Spot Open
Positions].[Buy]*100000)/36000,IIf([Spot Open Positions].[Sell]>0 And
[Spot Open Positions].[ITEM]='EUR/JPY' AND NOT [Spot Open
Positions].[DAY]=4,((([Sell Rate].[EUR/USD]+[Sell
Rate].[USD/JPY])/2)*[Spot Open Positions].[Sell]*100000)/36000,
IIf([Spot Open Positions].[Buy]>0 And [Spot Open
Positions].[ITEM]='GBP/JPY' AND NOT [Spot Open
Positions].[DAY]=4,((([Buy Rate].[GBP/USD]+[Buy
Rate].[USD/JPY])/2)*[Spot Open
Positions].[Buy]*100000)/36000,IIf([Spot Open Positions].[Sell]>0 And
[Spot Open Positions].[ITEM]='GBP/JPY' AND NOT [Spot Open
Positions].[DAY]=4,((([Sell Rate].[GBP/USD]+[Sell
Rate].[USD/JPY])/2)*[Spot Open
Positions].[Sell]*100000)/36000,IIf([Spot Open Positions].[Buy]>0 And
[Spot Open Positions].[ITEM]='AUD/JPY' AND NOT [Spot Open
Positions].[DAY]=4,((([Buy Rate].[AUD/USD]+[Buy
Rate].[USD/JPY])/2)*[Spot Open
Positions].[Buy]*100000)/36000,IIf([Spot Open Positions].[Sell]>0 And
[Spot Open Positions].[ITEM]='AUD/JPY' AND NOT [Spot Open
Positions].[DAY]=4,((([Sell Rate].[AUD/USD]+[Sell
Rate].[USD/JPY])/2)*[Spot Open
Positions].[Sell]*100000)/36000,IIf([Spot Open Positions].[Buy]>0 And
[Spot Open Positions].[ITEM]='EUR/CHF' AND NOT [Spot Open
Positions].[DAY]=4,((([Buy Rate].[EUR/USD]+[Buy
Rate].[USD/CHF])/2)*[Spot Open
Positions].[Buy]*100000)/36000,IIf([Spot Open Positions].[Sell]>0 And
[Spot Open Positions].[ITEM]='EUR/CHF' AND NOT [Spot Open
Positions].[DAY]=4,((([Sell Rate].[EUR/USD]+[Sell
Rate].[USD/CHF])/2)*[Spot Open
Positions].[Sell]*100000)/36000,IIf([Spot Open Positions].[Buy]>0 And
[Spot Open Positions].[ITEM]='USD/CAD' AND NOT [Spot Open
Positions].[DAY]=4,([Buy Rate].[USD/CAD]*[Spot Open
Positions].[Buy]*100000)/36000,IIf([Spot Open Positions].[Sell]>0 And
[Spot Open Positions].[ITEM]='USD/CAD' AND NOT [Spot Open
Positions].[DAY]=4,([Sell Rate].[USD/CAD]*[Spot Open
Positions].[Sell]*100000)/36000)))))))))) ;

This is query3:
UPDATE [Spot Open Positions], [Sell Rate], [Buy Rate] SET [Spot Open
Positions].INTEREST = IIf([Spot Open Positions].[Buy]>0 And [Spot Open
Positions].[ITEM]='EUR/GBP' AND NOT [Spot Open
Positions].[DAY]=4,((([Buy Rate].[EUR/USD]+[Buy
Rate].[GBP/USD])/2)*[Spot Open
Positions].[Buy]*100000)/36000,IIf([Spot Open Positions].[Sell]>0 And
[Spot Open Positions].[ITEM]='EUR/GBP' AND NOT [Spot Open
Positions].[DAY]=4,((([Sell Rate].[EUR/USD]+[Sell
Rate].[GBP/USD])/2)*[Spot Open Positions].[Sell]*100000)/36000,
IIf([Spot Open Positions].[Buy]>0 And [Spot Open
Positions].[ITEM]='EUR/AUD' AND NOT [Spot Open
Positions].[DAY]=4,((([Buy Rate].[EUR/USD]+[Buy
Rate].[AUD/USD])/2)*[Spot Open
Positions].[Buy]*100000)/36000,IIf([Spot Open Positions].[Sell]>0 And
[Spot Open Positions].[ITEM]='EUR/AUD' AND NOT [Spot Open
Positions].[DAY]=4,((([Sell Rate].[EUR/USD]+[Sell
Rate].[AUD/USD])/2)*[Spot Open
Positions].[Sell]*100000)/36000,IIf([Spot Open Positions].[Buy]>0 And
[Spot Open Positions].[ITEM]='NZD/USD' AND NOT [Spot Open
Positions].[DAY]=4,([Buy Rate].[NZD/USD]*[Spot Open
Positions].[Buy]*100000)/36000,IIf([Spot Open Positions].[Sell]>0 And
[Spot Open Positions].[ITEM]='NZD/USD' AND NOT [Spot Open
Positions].[DAY]=4,([Sell Rate].[NZD/USD]*[Spot Open
Positions].[Sell]*100000)/36000,IIf([Spot Open Positions].[Buy]>0 And
[Spot Open Positions].[ITEM]='GBP/CHF' AND NOT [Spot Open
Positions].[DAY]=4,((([Buy Rate].[GBP/USD]+[Buy
Rate].[USD/CHF])/2)*[Spot Open
Positions].[Buy]*100000)/36000,IIf([Spot Open Positions].[Sell]>0 And
[Spot Open Positions].[ITEM]='GBP/CHF' AND NOT [Spot Open
Positions].[DAY]=4,((([Sell Rate].[GBP/USD]+[Sell
Rate].[USD/CHF])/2)*[Spot Open
Positions].[Sell]*100000)/36000,IIf([Spot Open Positions].[Buy]>0 And
[Spot Open Positions].[ITEM]='CHF/JPY' AND NOT [Spot Open
Positions].[DAY]=4,((([Buy Rate].[USD/CHF]+[Buy
Rate].[USD/JPY])/2)*[Spot Open
Positions].[Buy]*100000)/36000,IIf([Spot Open Positions].[Sell]>0 And
[Spot Open Positions].[ITEM]='CHF/JPY' AND NOT [Spot Open
Positions].[DAY]=4,((([Sell Rate].[USD/CHF]+[Sell
Rate].[USD/JPY])/2)*[Spot Open
Positions].[Sell]*100000)/36000)))))))))) ;

Under the conditions column I inserted the the following statements to
open the following queries:
Query1
("[ITEM]","Spot Open Positions")='EU R/USD' Or 'USD/JPY' Or 'GBP/USD'
Or 'USD/CHF' Or 'AUD/USD'
Query2
("[ITEM]","Spot Open Positions")='EU R/JPY' Or 'GBP/JPY' Or 'AUD/JPY'
Or 'EUR/CHF' Or 'USD/CAD'
Query3
("[ITEM]","Spot Open Positions")='EU R/GBP' Or 'EUR/AUD' Or 'NZD/USD'
Or 'GBP/CHF' Or 'CHF/JPY'

Please tell me if just changing the conditions statements would help
or would I have to combine the sql statements using vba. Please tell
me how because I keep getting the query to complex when tryin to
combine them.

Thank You Very Much,
Ravi

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You might simplify it by using the Switch() function instead of the
IIf() function. E.g. (extended for clarity):

UPDATE [Spot Open Positions], [Sell Rate]
SET [Spot Open Positions].[INTEREST WED] =
((100000*3)/36000) *
Switch(
[Spot Open Positions].[ITEM]='USD/CHF', [Sell Rate].[USD/CHF],
[Spot Open Positions].[ITEM]='EUR/USD', [Sell Rate].[EUR/USD],
[Spot Open Positions].[ITEM]='USD/JPY', [Sell Rate].[USD/JPY],

<... other currencies ... >

)
WHERE [Spot Open Positions].[DAY]=4

Do the Sell Rates and Buy Rates in 2 different queries.

See the Access Help article on the Switch() function (in VBA help -
Access 2K & greater).

==

All this would not be necessary if your db design were better. You
might want to redesign some of your tables like this:

[Spot Open Positions] should have the [ITEM] (aka CurrencyType) as an
integer (Long in JET), instead of a string expression, that would
reference a table designed like the following. This assumes that the
[ITEM] column always contains a currency type. The design is correct
in any event - it would need minor modifications for other ITEM types.

CREATE TABLE CurrencyTypes (
CurrencyType Counter NOT NULL , -- AutoNumber
TypeDesc VARCHAR(25) PRIMARY KEY -- "USD" "JPY" "GBP", "USD/JPY",
etc.
)

Create a unique index on the CurrencyType column for linking/relating
purposes. The Primary Key on TypeDesc is to prevent duplicate entries
of currency types descriptions, e.g., there can only be one entry for
"USD/JPY."

Then change the Sell Rate table to something like this - to take
advantage of the data integrity capability of the CurrencyTypes table:

CREATE TABLE [Sell Rate] (
CurrencyType Long NOT NULL,
Rate CURRENCY NOT NULL,
CONSTRAINT PK_SellRate PRIMARY KEY (CurrencyType, Rate),
CONSTRAINT FK_CType FOREIGN KEY (CurrencyType)
REFERENCES CurrencyTypes (CurrencyType)
)

The Primary Key constraint prevents more than one Rate per
CurrencyType. The Foreign Key constraint looks in the table
CurrencyTypes to verify the various Currency Types.
This design would make the control of currencies much easier (you
could add new ones without having to add a new column to the Sell Rate
table). It would also make the design of queries much easier. Your
UPDATE query would be like this (for ALL currency types):

UPDATE [Spot Open Positions] AS SOP INNER JOIN [Sell Rate] AS SR
ON SOP.CurrencyTyp e = SR.CurrencyType
SET SOP.[INTEREST WED] = SR.Rate * ((100000*3)/36000)
WHERE SOP.[DAY]=4

HTH,

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP7n1uIechKq OuFEgEQKJzACgqm c7KD5Zf+fe/26S4Mjc+ks/yk0AoMrc
u2FyiIlNylQJ01x 2l+4JMO2j
=cDze
-----END PGP SIGNATURE-----
ravi wrote:

I have created the following interest to calculate the interest for
the following currency pairs. I have tried to combine them in macros
using conditions but the next query that is run in the macro ends up
deleting the previous interest value that has been generated by the
query. For example if query 1 is run on the table with currency pair
USD/CHF then the interest will be updated without any problem but if
there is another entry in the table with another currency pair for
example EUR/JPY which is calculated say in query two then the interest
of USD/CHF which has been calculated previously will be deleted. I
think the only way to solve this would be to combine the queries
instead of seperating them using macro conditions. Please tell me how
I would appreciate it alot.

This is query1:
UPDATE [Spot Open Positions], [Sell Rate], [Buy Rate] SET [Spot Open
Positions].[INTEREST WED] = IIf([Spot Open Positions].[Sell]>0 And
[Spot Open Positions].[ITEM]='USD/CHF' AND [Spot Open
Positions].[DAY]=4,([Sell Rate].[USD/CHF]*[Spot Open
Positions].[Sell]*100000*3)/36000,IIf([Spot Open Positions].[Sell]>0
And [Spot Open Positions].[ITEM]='EUR/USD' AND [Spot Open
Positions].[DAY]=4,([Sell Rate].[EUR/USD]*[Spot Open
Positions].[Sell]*100000*3)/36000,IIf([Spot Open Positions].[Sell]>0
And [Spot Open Positions].[ITEM]='USD/JPY'AND [Spot Open
Positions].[DAY]=4,([Sell Rate].[USD/JPY]*[Spot Open
Positions].[Sell]*100000*3)/36000,IIf([Spot Open Positions].[Sell]>0
And [Spot Open Positions].[ITEM]='GBP/USD' AND [Spot Open
Positions].[DAY]=4,([Sell Rate].[GBP/USD]*[Spot Open
Positions].[Sell]*100000*3)/36000,IIf([Spot Open Positions].[Sell]>0
And [Spot Open Positions].[ITEM]='AUD/USD' AND [Spot Open
Positions].[DAY]=4,([Sell Rate].[AUD/USD]*[Spot Open
Positions].[Sell]*100000*3)/36000,IIf([Spot Open Positions].[Sell]>0
And [Spot Open Positions].[ITEM]='XAU/USD' AND [Spot Open

< SNIP >