How To Avoid Cartesian Products?

[nimajneb]

Hello! This is my first post on to Access Monster. I have a specific
question with general implications. I've simplified it, since the real code
is pretty gargantuan.

The database... The Access database I set up has a table called Agents,
another table called Auto_NB, and another one called Fire_NB. Auto_NB has
various fields, such as Agent_Code, [Year], [Month], [Day], and NB (new
business). Fire_NB has the same fields, just with different data.

The query... I'm making a query in which Agents is joined to both Auto_NB and
Fire_NB on the field Agent_Code. The joins are left joins, to capture all
the agents even if there was no production. I perform a simple grouped query:


SELECT Agents.Agent_Code, Sum(Auto_NB.NB) AS Auto_NB, Sum(Fire_NB.NB) AS
Fire_NB
FROM ... and the left join statements...
WHERE Auto_NB.[Year]=2008 AND Auto_NB.[Month]=3 AND Fire_NB.[Year]=2008 AND
Fire_NB.[Month]=3
GROUP BY Agents.Agent_Code;

The problem... Let's say Agents James Joyce sold 5 Auto policies on the first
of the month and 10 on the second, and that he sold 6 Fire policies on the
third and 12 on the fourth of the month. The dataset my query returns tells
me that Jim sold 30 Auto policies instead of 15 -- apparently using Cartesian
products. Yet I can't do any kind of join between the Auto and Fire tables
on their [Year] and [Month] fields, because it causes ambiguous outer joins.

The question: Is there an easy way around this? I've done so by using
subqueries for now, but they are unbearably slow.

 

[George Nicholson]

Several approaches. Here are some:

Create 2 queries (Fire and Auto) to give you a one-record monthly sum per
agent. Left Join those queries to your Agents table.

Alternatively, Create a Union Query that combines the Auto and Fire Info
(adding a Policy Type field), and join that to Agents.

Alternatively, Combine Auto and Fire into one NB table (which they probably
should have been from the start). Since you say they contain the same
fields, you probably would only need to add one additional field for
PolicyType (i.e., Fire, Auto). On a similar note, I question whether NB
really belongs in a separate table from Existing business. NB/Existing
sounds more like an data Attribute to me than data of a different type.

 

[nimajneb via AccessMonster.com]

Thanks, George!

I'll be taking your first suggestion, by process of elimination. The second
seems impractical given the number of records in each table, and the third is
something I will consider for the next time I refactor the database.

I will also be looking for ways to include the fire and auto queries you
mentioned as subqueries, rather than independently saved queries. I find it
clutters my database to have all these separate saved queries lying around.

Several approaches. Here are some:

Create 2 queries (Fire and Auto) to give you a one-record monthly sum per
agent. Left Join those queries to your Agents table.

Alternatively, Create a Union Query that combines the Auto and Fire Info
(adding a Policy Type field), and join that to Agents.

Alternatively, Combine Auto and Fire into one NB table (which they probably
should have been from the start). Since you say they contain the same
fields, you probably would only need to add one additional field for
PolicyType (i.e., Fire, Auto). On a similar note, I question whether NB
really belongs in a separate table from Existing business. NB/Existing
sounds more like an data Attribute to me than data of a different type.

Hello! This is my first post on to Access Monster. I have a specific
question with general implications. I've simplified it, since the real

[quoted text clipped - 34 lines]

The question: Is there an easy way around this? I've done so by using
subqueries for now, but they are unbearably slow.