JOIN question

[MarkRCC]

Hi.

I have an Access2000 database that tracks Broker commissions. For example, BrokerA had sales (and commissions) for Ins. CompanyA, Ins. CompanyB and Ins. CompanyC this month. BrokerB had sales (and commissions) for Ins. CompanyA and Ins. CompanyD this month. BrokerC had no sales (or commissions) this month (and is on the verge of being fired!).

I created a crosstab query (and a subsequent report) that's supposed to list ALL brokers, whether they had sales or not, as the "row", and all the Ins. Companies we do business with as the "columns". So for each Broker, the total commissions are listed for each company he/she had sales for. It looks like this:

COMMISSIONS ON SALES
OCTOBER 2003

CompanyA CompanyB CompanyC CompanyD Etc
BrokerA $200.00 $75.00 $40.00
BrokerB $80.00 $100.00
BrokerC
BrokerD
Etc
Ect

There will be totals for both rows and columns as well, but that's not the problem (yet).

What's happening is that ONLY those brokers who had sales (and commissions) for the given month (in this case, October 2003), is listed. I need ALL brokers to be listed. I understand why the ones without sales (commissions) are not listed as I have a table (<tblBrokerMonthlyData>)that saves all transactions data. If a broker has no sales that month then he/she won't be in the table (obviously) The broker info (Name, address, etc) is in a separate table (<tblBrokerInfo>).

I tried to set the query JOIN property to type 2, (Include ALL records from <tblBrokerInfo> and only those records from <tblBrokerMonthlyData> where the joined fields are equal.), but get the following message:

&quot;The SQL statement could not be executed because it contains ambiguous OUTER joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement.&quot;

I've been trying to do what was suggested but can't seem to get it to work. My SQL statement for the query is as follows:


TRANSFORM Sum(tblBrokerMonthlyData.BrokerFYC) AS SumOfBrokerFYC
SELECT ([BrkrLN] & &quot; &quot; & [BrkrFN] & [BrkrCompanyName]) AS LNFN, ([BrkrORRatea] & &quot;/&quot; & [BrkrORRateIA]) AS BrokerORRates
FROM tblCompanyInfo INNER JOIN (tblBrokerInfo LEFT JOIN tblBrokerMonthlyData ON tblBrokerInfo.BrkrID = tblBrokerMonthlyData.BrokerID) ON tblCompanyInfo.CmpnyID = tblBrokerMonthlyData.CompanyID
GROUP BY ([BrkrLN] & &quot; &quot; & [BrkrFN] & [BrkrCompanyName]), ([BrkrORRatea] & &quot;/&quot; & [BrkrORRateIA])
PIVOT tblCompanyInfo.CmpnyName;


Could anyone take a look and point me in the right direction?

Many TIAs!

Mark

 

[dhookom]

Create your crosstab without the Left Join to tblBrokerInfo. Then, create a new query that Left Joins the crosstab to tblBrokerInfo.

Duane
MS Access MVP

 

[MarkRCC]

Hi Duane.

Thanks for the reply.

I hate to sound like an idiot, but could you explain further? I've been playing with the SQL statement and can't seem to get my head around the proper syntax.

One additional question, if I may. I've just been informed that the report needs to calculate monthly and YTD totals as follows:



COMMISSIONS ON SALES
OCTOBER 2003
Tot for Total
CompanyA CompanyB CompanyC Month YTD
BrokerA $200.00 $75.00 $40.00 $315.00 $1,200
BrokerB $80.00
Ect

In my query, I have a parameter that asks for the Month (in this case October) and prints the report based on it. I can total the Month but also need the YTD total (all transactions to date for the respective brokers) on the same report. Is this even possible?

Again, Many TIAs!

Mark

 

[dhookom]

I would create your first crosstab even though it may not include all brokers. Then create a new query based on the crosstab and a table/query of all brokers. Use the join that selects all records from the &quot;all brokers&quot;.

I'm not sure how you limit your query to a single month so I can't suggest how to get the YTD. My guess is to add the YTD to the All Brokers query to combine with your crosstab.

Duane
MS Access MVP