Relationships that may or may not occur

[madctch]

I am having trouble with how I should relate these tables:
Table One contains general information about a company and table two contains insurance enrollment information. Sometimes there are multiple entries of the same company in the insurance table, but only a single can exist in the general information. Also, at times there can be companies on the insurance without having an record in the general information. As of now I am entering the name multiple times causing redundancy.

I need to make a report that groups by the fields in the general information and sums the field in the insurance information where the record's company name matches. How should I alter my current structure in order to make this possible?

 

[rac2]

First write the query which sums the enrollment figures by company. Give this query a name like CompanyEnrollment.

SELECT company_id, 
       SUM(enrollment) AS "TotalEnrolled"
FROM Insurances
GROUP BY company_id

With a decently defined database and well-mannered data this would yield the total enrollment by company. It will show each company id once with the sum of the enrollments for that company.

It will not have any enrollment for a company that did not have a row in the Insurances table.

The TotalEnrolled would be correct if the several rows for a single company in Insurances were counting different people. If the different rows count some of the same people, then you are sunk. The problem cannot be solved. The data is bad. Throw it away.

A decently defined database will have an ID column in both tables which uniquely identifies a company. It may be that the company name can be used if there is no ID column.

The potential problem with company name will be that the same company has different spellings for the name in different rows in Insurance or in the Companies table. In this case the data must cleaned or you must add an ID column to both tables. At this point you may as well also add rows to the Companies table for companies in the Insurances table which are missing.


Now JOIN this query to the Companies table to obtain the additional information about each company.

SELECT c.company_name, 
       c.address,
       ce.TotalEnrolled
FROM Companies c
JOIN CompanyEnrollment ce ON ce.company_id = c.company_id
ORDER BY c.company_name

If it is not practical to fill the Companies table with the missing company rows you could do this.

SELECT IIF(IsNull(c.company_name), 
           'Unknown', 
           c.company_name) AS "CompanyName"
       IIF(IsNull(c.address, 
           'Unknown', 
           c.address) AS "Address",
       ce.TotalEnrolled
FROM Companies c
RIGHT JOIN CompanyEnrollment ce 
        ON ce.company_id = c.company_id
ORDER BY c.company_name

If the company name is used to identify the company in both tables instead of an ID column then you could do this.

SELECT ce.company_name, 
       IIF(IsNull(c.address, 
           'Unknown', 
           c.address) AS "Address",
       ce.TotalEnrolled
FROM Companies c
RIGHT JOIN CompanyEnrollment ce 
        ON ce.company_name = c.company_name
ORDER BY ce.company_name

 

[fneily]

Sometimes there are multiple entries of the same company in the insurance table".
"there can be companies on the insurance without having an record in the general information"

It seems that a company can have many different insurances. Obviously, an insurance company can insure many companies. So you have a many-to-many relationship.
The second quote above doesn't make any sense since the only description of a company in the insurance table would be the primary key of the company table. ALL companies should have their info in the ONE table. Period.
So you would have two tables. tblCompany and tblinsurance containing their respective info. You would then create a junction table that would hold the primary keys of tblcompany and tblinsurance. This combination would be the primary key of the junction table. Also in this table are any COMMON fields, such as premiums, coverage, etc. Then any query or report you want will fall right out.
Study Normalization and see:
The Fundamentals of Relational Database Design

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc