By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,490 Members | 1,417 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,490 IT Pros & Developers. It's quick & easy.

How to report on three tables in Microsoft Access

P: 3
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.
1 Week Ago #1

✓ answered by 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.

Share this Question
Share on Google+
6 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,282
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.
1 Week Ago #2

NeoPa
Expert Mod 15k+
P: 31,476
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.
1 Week Ago #3

P: 3
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
1 Week Ago #4

NeoPa
Expert Mod 15k+
P: 31,476
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.
1 Week Ago #5

twinnyfo
Expert Mod 2.5K+
P: 3,282
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!
6 Days Ago #6

P: 3
Thanks Twinnyfo! That worked beautifully and it was a better approach. Thanks for your help!!
5 Days Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.