Access/SQL question

[General Kenobi (ret.)]

I think we've covered this before, but nevertheless:

I have one table with losses by zip by territory.
I have another table with premium by zip by territory.

There can be premium without losses. There shouldn't be losses without premium, but thanks to the vagaries of claims coding, there are. I want a list of all zip/territory combinations that appear in either list. How do I get it?

[General Kenobi (ret.)]

Got it--union queries to the rescue!

[Ron Weasley]

If I understand your problem correctly, you want to write a query with an open join. My hugest peeve with MS Access is that it does not support open joins (at least the versions I use. I've been told that the latest versions don't either).

If this is the case, I and my co-workers have only found two solutions.

1) Write a pass-through query using ODBC and the SQL native to the RDBMS you are passing through to.

2) Create a series of queries that create a table of all combinations that exist in the two tables you're interested in. Next, write a query that joins this new table to the two original tables.

I wish that I had a good answer. :cry:

RW

[Ron Weasley]

If you get Union Queries to do what you want, please let me know how.

[Moderator2]

First, you have to join the Union. And pay dues. :D

[General Kenobi (ret.)]

I used the union query to feed another query linking the premium and the losses. I think that's the same as the workaround you described. I have a couple ideas for doing it in one big happy query, but I'm at the "don't break it" point right now.

[Dr T Non-Fan]

Look at FULL JOIN command.

However, you'll have to use CASE function to define variables when there are. Here, for a small fee:

SELECT
CASE WHEN a.ZIP is null then b.ZIP else a.ZIP end as ZIP,
a.losses,
b.premium
FROM a FULL JOIN b ON a.ZIP = b.ZIP

etc.

[PK]

Why isn't this as simple as

Select a.zip from policy
union
select b.zip from claim;

Assuming this query is called c, then

Select c.zip from c
Group by c.zip;

It's two queries, but so what? Many things run faster in steps than they do in "one big happy query".

[Dr T Non-Fan]

HEY! Make it one happy query, or ELSE!

The easiest way in my mind to do this is to create a third table in access with the following fields from the premium table

Fields

1) Zip
2) Premium
3) Losses: 0 (this just creates a field with 0's in it).

Next use an append query to append the records from the losses table to this new table you created.

Append

1) Zip
2) Losses

Last query - use a select query group by zip and sum(premium) and sum(losses).

No records lost.