Showing All Records in a JOIN of Three Tables?

[Comstreem]

Hi Everyone,
Help please, I've been banging my head against the wall with this for more time than I'd like to admit.
I have three tables, [Policy_Info], [Premium], and [Loss]. They are set up like this:

(composite Primary keys since autonumbers mean nothing )

What I would like to do is have a resulting table/query with all records from the [Premium] and [Loss] where the [Premuim_Year] and [Loss_Year] are related (since they are the same year) and if there is no relation for these it will still show the record. All I could come up with is this:

(a double LEFT OUTER JOINS?)

which gives duplicates of the Premium/Loss Year since there is no relationship between them...

I have no notion of the direction to go in about this. The resulting records are what i need put into a Report doing many calculations of its own (its actually the feed is by 5 layers of queries that do a lot of calculations before it gets to the report but the above example is essentially the same)
I've already put the report together and would rather not have to rebuild it using one main report and two subreports (for each table) The easiest approach i would thought would be to make a relationship to fix this but perhaps it would of been better time spent rebuilding the report from scratch to accomidate for referencing values in the subreports(or the quieries that feed them)

(I've only been writting Access systems for the past 3 months and my boss assumed I could just pop these things out... too bad I didn't go to school for programming of any type.)

Hope someone can give me any idea Thanks

 

[WildHare]

Double dipping, eh Brian?...

all records from the [Premium] and [Loss] where the [Premuim_Year] and [Loss_Year] are related (since they are the same year) and if there is no relation for these it will still show the record.

I was fine until that last part. "Still show the record" - which record?

ALL LOSSes and ALL PREMIUMS, sorted by Year?

78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Comstreem]

HeHe, At least I didn't repost the same thing over there. Like i said I've been banging my head against the wall and its starting to hurt so if I am allowed to I'm going to do what i can to alleviate the pain.

What i meant above was a way in wich "...the data will be returned if it matches in both tables, or if it exist in either one" (ie a Full Outer Joins) which, (the other) Jim quickly and nicely pointed out what was right there in front of me.
"...you will need left/right joins in order to return all of the data requested..."

What i did was break appart each side of the above relationship to make two quieries and it looked like this:

Then i put both of those quieries together with a Left and Right JOINS like this:

and finally I used a UNION query like so:

SELECT[Policy_Number], [Premium_Year] AS [YEAR],[Premium_Paid], [Loss_Amount]
FROM [Qry_Both_Right]

UNION SELECT [Policy_Number], [Loss_Year], [Premium_Paid] , [Loss_Amount]
FROM [Qry_Both_Left];

Hence [Premium_Year] and [Loss_Year] show up where they have a relationship to a record in the other table And where they do not have a relationship to a record in the other table.
Perhaps my way of explaining/thinking about it is not on the same plane but some how I got what i needed. %-)
I really appreciate your dedication to the Forums and helping all of us with Access app. development.

Brian

 

[WildHare]

That's exactly what I was going to suggest..two full outers, a left/o and a right/o and a inner...oy vey...now I've got a headache...

Glad it appears to be coming together.

78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm