How to report on three tables in Microsoft Access

[joithomas]

I have three tables. One contains ALL funds(Funds). Then I have a table that has the Representatives of the fund(FundReps)and the third table has the Recipients of the Funds(FundRecipients). I need an Access report to show all the Funds regardless of rather they have a Rep or a Receipients. So the report needs to show the Fund, the Rep and the Recipients but if the rep, recipient or both is missing, the fund still needs to populate. Right now it is either show the funds with all the reps or the funds with all the recipients.

[twinnyfo]

joithomas,

Welcome to Bytes!

This is a query issue. You will have to create a LEFT JOIN query with your tables, in which you display all the records from Table Funds and only the records from FundReps and FundRecipients that match. This is common in query building. If you provide your current query, we may be able to assist you in this.

[NeoPa]

As Twinny says, it sounds very much like you have a query that uses INNER JOINs instead of LEFT JOINs or Right JOINs.

If you look at your Query in the design view then you'll see the various tables with lines linking them together on one or more Fields. If you switch to the SQL view you'll see more easily what Twinny's talking about. On the other hand, if you can double-click on one of the lines that joins two of your tables together you'll see options of the type of links available.

1. #1 is equivalent to the SQL INNER JOIN.
2. #2 is equivalent to the SQL LEFT JOIN.
3. #3 is equivalent to the SQL RIGHT JOIN.

If you ensure all the links are #2 or #3, depending on which of the tables is on which side, then you should see all of your data - even those without matching entries.

Best of luck.

[joithomas]

Thank you guys!! Changing the joins worked. I had the joins correct on the Relationship but had to change it on the query also!!

Now I'm dealing with dupes...ugh

[NeoPa]

Hi Jo.

Glad to hear that helped.

Dupes are a different issue. Feel free to post a question about them, but in a separate thread. You can link to this one if you feel it may be helpful.

I'd start though, by considering exactly what you want in the data. The way you have it described now it actually makes sense that you would get duplicates in any situation where you have multiple [FundReps] records AND multiple [FundRecipients] records. Think about why expecting anything else doesn't really make any sense.

I'll leave that with you and look out for another thread when you're ready to post it.

[twinnyfo]

Also, if I understnad your "bigger picture", you want to create a report on this data. Well, then, your Report should be based upon Table Funds, and then you should have two sub-reports on that report: one for FundReps and one for FundRecipients. These two sub-reports would have a Parent-Child relationship on the same fields you are joining now--but you eliminate the need for LEFT/RIGHT JOINs, and will not produce duplicates in the Funds Table records.

This is actually the "better" approach to what I "think" you are doing.

Hope this hepps!

[joithomas]

Thanks Twinnyfo! That worked beautifully and it was a better approach. Thanks for your help!!

[sunitapr]

Thank you. It is very helpful answer.