I am trying to create column that calculates revenue based off of both data within a query and another table. So far I have: IIf([Product]="CHR",[rate]*[installed]*[activation],0).

The [activation] is where I am having the issue as that data is in another tab and relates to two of the fields in the query--'Month' and 'Country'.

For example: in the USA for the month of June the activation was 50% and the UK was 35%, while July was 60% and 42% respectively.

There are two ways I can think of to set the activation table up:

1. Columns are 'Month', 'Country', and 'activation
2. Columns are 'Country', 'Jan', 'Feb', 'Mar', ... 'Dec' and under each month are the respective activations.

So I guess there are two questions: Which format would be better? And, how would I join the activation so that it is connected to multiple fields in the query?

Thanks,

The first option is the preferred one, however it’s not as simple as that, it always depends.
But 80% of the time it would be the first option, well probably more like 99% of the time.

I thought that might be the case.

Do you know how I could join the query and table together? I tried joining on both month and country but it will not let me.

Looks like Tony's gone to bed, as it's past midnight there. The fields would have to be compatible data types in both tables. It couldn't be a text in one and date/time in the other for instance. FWIW, I agree with Tony about the table layout.

The fields for country are in the same format.

For the month I am not sure. In the query it is pulled out of the date field via: Month([Date]) AS [Month], while the activation table I just entered the numerical value for the month (e.g., 7 for July).

Would I replicate the formula in the activation table?

It works fine for the July data, but the August data uses July's activations for the calculations.

Can you post a sample of the db?

Looks like Tony's gone to bed

Yes... I was K***kered... Hard day yesterday... no lunch... one cup of coffee all day....

Here is a toned down sample of the query and activation table. The two are joined on 'New Country' and 'ActCountry'.

He is the SQL that is currently being used:
SELECT Month([date]) AS [Month], Convert_Query.Date, Convert_Query.Source, Convert_Query.Client, Convert_Query.Product, Convert_Query.[RP Version], Convert_Query.Lang, Convert_Query.Buckets, Convert_Query.Distribution, Convert_Query.Distcode, Convert_Query.MorphedCode, Convert_Query.Presented, Convert_Query.Installed, Convert_Query.[New Country], Convert_Query.Geo, Bounty_Table.*, IIf([Product]="CHR",[bounty]*[installed]*[Activation],0) AS Revenue
FROM (Convert_Query INNER JOIN Bounty_Table ON Convert_Query.[New Country] = Bounty_Table.BountyCountry) INNER JOIN Activation_Table ON Convert_Query.Country = Activation_Table.ActCountry;