Combining the following queries without getting query too complex error

**MGFoster** · Nov 12 '05, 04:37 PM

Re: Combining the following queries without getting query too complexerror

-----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:
[color=blue]
> 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[/color]

< SNIP >

Combining the following queries without getting query too complex error

Combining the following queries without getting query too complex error

Comment