Joining to multiple fields

[clintthorn]

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,

 

[Uncle Gizmo]

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.

 

[clintthorn]

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.

 

[pbaldy]

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.

 

[clintthorn]

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?

 

[clintthorn]

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

 

[pbaldy]

Can you post a sample of the db?

 

[Uncle Gizmo]

Looks like Tony's gone to bed

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

 

[clintthorn]

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;