So I have several tables that I need to pull fields from:
- Companies (main table for information)
- Territory Terms
What I need to do is create a report that pulls fields from each of these tables into ONE report.
I thought maybe creating a new table called Reports that is a connected table would work, so that all my companies would show up. So I created a connected table from the Companies table (which is my main table). This is fine, but I can't seem to figure out how to get the fields from the other tables to show up on the Reports table (well, they show up, but are not connected to the original Companies record, so that all my fields show up on the correct record)
All companies that are:
From: United States,
That are: signed,
But I also want to show information about these companies that live on other tables.
So I want these fields to show but are from different tables:
From the companies table these fields would be referenced/shown:
From Contact table
*when full name's role is equal to Main Business Contact
From the Contract table
Stage (*also one of the filters)
From the Amendment Table
From Territory Terms
So I want this to be able to be shown on one report. I already have relationships built from these tables but can't get fields besides summaries to show up (Companies is usually the parent in my database to these other tables). Any suggestions are welcome, I am a little puzzled on how to solve this.
I can explain the contacts one later. We will use a a reverse lookup.
Please explain what you mean for the Contract table - which contract? what if there are 10 contracts?
same for Amendments, what if there are 10 amendments.
and I don't understand Territory List.
Oh, i guess I will walk you though that now.
So on the Company to Contacts relationship make a Summary field of the of the maximum Record ID of the contacts table where the contact type is Main Business Contact. call it [Record ID# of the Main Business Contact]
Then do a reverse relationships where 1 Contact has many companies, but for the field on the right side of the relationship use that field [Record ID# of the Main Business Contact]. The lookup the contact name to the Companies table.