Query Problem - Missing blanks

[burrelly]

I have 4 tables as follows:

1. Company Info: Company, Phone Number
2. Source: Company, Source
3. Industry: Company, Industry
4. Location: Company, Country

In the company info table I have approx 5000 records. I am running a query to create a list of the following:

Company, Phone Number, Source, Industry, Country

Table 2, 3 , 4 do not contain information about all the companies in table 1 but only a few of them.

I have set up the relationship with each table using company to the main Table (1)

When I run the query it does do what I want but if a company in table 1 has no information against it in table 2,3,4 is does not show in the query. So basically the query only returns around 1000 records.

How do I set it up so it will show companies from Table 1 even if there is nothin pulled from the other 3 tables?

 

[neileg]

When you join tables in a query the join defaults to an inner join. This requires data on both sides of the join to return anything. If you right click the join line in design view you can change this to a left join (number 2 in the list). That returns all of the records in the left hand table and any from the right that match.

Make sure that the Company Info tables is joined to each of 2, 3 and 4 by a left join, i.e. join 1 to 2, 1 to 3 and 1 to 4.

 

[burrelly]

When you join tables in a query the join defaults to an inner join. This requires data on both sides of the join to return anything. If you right click the join line in design view you can change this to a left join (number 2 in the list). That returns all of the records in the left hand table and any from the right that match.

Make sure that the Company Info tables is joined to each of 2, 3 and 4 by a left join, i.e. join 1 to 2, 1 to 3 and 1 to 4.

I did that and it has now created alot of duplicates.

The original Table 1 hade 4965 contacts the Query returns over 7000.

 

[MStef]

Look at attachment (word, zip).

 

[burrelly]

Look at attachment (word, zip).

Yeah that is what I have:

SELECT [Company List].Company, Industry.Industry, Country.Country, Source.Source, [Company List].Status, [Company List].[Call Comment], [Company List].[Data Change], [Company List].Contact, [Company List].[Job title], [Company List].[Main No], [Company List].ddi, [Company List].Mobile, [Company List].Email
FROM (([Company List] LEFT JOIN Country ON [Company List].Company = Country.Company) LEFT JOIN Source ON [Company List].Company = Source.Company) LEFT JOIN Industry ON [Company List].Company = Industry.Company;

I am getting alot more records in the Query than I start with in the original Table (Company List)

 

[MStef]

It means that you have got more records in (SOURCE, INDUSTRY, LOCATION) tables, for every COMPANY.
I think, your tables not normalized well.

 

[neileg]

To restate what MStef said, if you have more than one record in any of tables 2, 3 or 4 that matches a company, then you will get more than one record returned i9n the query.

 

[MSAccessRookie]

I have 4 tables as follows:

1. Company Info: Company, Phone Number
2. Source: Company, Source
3. Industry: Company, Industry
4. Location: Company, Country

In the company info table I have approx 5000 records. I am running a query to create a list of the following:

Company, Phone Number, Source, Industry, Country

Table 2, 3 , 4 do not contain information about all the companies in table 1 but only a few of them.

I have set up the relationship with each table using company to the main Table (1)

When I run the query it does do what I want but if a company in table 1 has no information against it in table 2,3,4 is does not show in the query. So basically the query only returns around 1000 records.

How do I set it up so it will show companies from Table 1 even if there is nothin pulled from the other 3 tables?

Lack of of Normalization appears to be what is causing much of your grief in this case. Each table has its own Company Information, and it appears that some of the Company Information can be duplicated.

• The Company Table should have one record for each Company, and a PK for use by the other tables.
• Each of the other tables should not contain company information, but instead should contain a FK pointing back to the relevant record in the Company Table.

This kind of approach should make your problem go away.

 

[burrelly]

Yes I understand but the issue is all this data came from different excel sheets.

Table 1 Have some companies duplicated as I have 2 - 3 contacts within that company.

I am just trying to join all the tables to create one to import into the our CRM

 

[neileg]

So that suggests that the query is working correctly. What do you want to do if you have more than one contact? Just pick one or do you want them all? What format do you need to import the data into your CRM system?