So, I have a database where I need to create reports pulling fields from different tables.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

So I have several tables that I need to pull fields from:

  1. Companies (main table for information)
  2. Contacts
  3. Contracts
  4. Amendments
  5. 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)


Suggestions?

Photo of Cecelia

Cecelia

  • 180 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
So, for example, say that 1 company has 20 contacts, 20 contracts,  20 amendments, and 20 territory terms.  What exactly would you expect this report to look like?
Photo of Cecelia

Cecelia

  • 180 Points 100 badge 2x thumb
So for my report I want it to show these filters

All companies that are:
From: United States,
That are: signed,
Type: publishers,
Contracts: 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:
Company Name
Home Country
Company Status
Type

From Contact table
Full name
*when full name's role is equal to Main Business Contact

From the Contract table
Stage (*also one of the filters)
Contract Type

From the Amendment Table
Amendment Type

From Territory Terms
Territory List

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.
The company info is easy because this is gong to be a report off the companies table.
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.
Photo of Cecelia

Cecelia

  • 180 Points 100 badge 2x thumb
So for the contracts table (and same for Amendments).  Each company can have several contracts.    So I want to expose those as choices.  So it could be that I have the same company that is Signed from the US and has a signed contract.  Ignore the Territory List for now.
OK, so 1 company has 20 contracts and I am running a report. How do I know which of the 20 contracts to show on the report?  what do you mean "expose those as choices".  are you looking to list the most recent say 3 conrtacts?  The most recent 3 active contracts?  All 20 or 200 or 2,000 contracts?
sorry in the above I mean "ContRacts"
Photo of Cecelia

Cecelia

  • 180 Points 100 badge 2x thumb
and so when I run a report I want to create a report that says: show me all the Signed Companies that are publishers who have Signed contracts with us.  Now I think I could do the summary for the Contract Signed portion, do you think that is what I have to do with all of them?  Summary tables of the choices?
Yes, you can do a Summary field of the # of signed contacts which are "active".  no doubt your contacts have a starting and ending date.  I will get back to you on how to flat up the main Business contact to the companies table.

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.
Photo of Cecelia

Cecelia

  • 180 Points 100 badge 2x thumb
That is great!  Thanks!