Merging two colums from query into datasheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi and thanks for taking the time to read this.

I have a query based on three linked tables. It returns two sets of
geographical data (amongst other stuff relating to competitor organisations)
one for London Boroughs and one for English Counties where organisations
work. In my datasheet I want to show the various organisational data for a
given organisation, including what they do and where they do it. That's all
fine, however, for a given organisation some work is conducted in London and
other work in an outside county. At the moment I have London Borough and
County as two seperate colums since they derive from two different tables.
How can I put them under one heading in the datasheet, an Area column, for
example, and exclude null values so that I don't have to have both columns
with some empty values showing? I am using Access 2002.

Thank you
 
The best solution here will be to redesign the tables so you have these:

- Company table (one record per organization), with fields:
CompanyID primary key
Company name of company
...

- County table (one record per county), with fields
County primary key
IsLondon Yes/no (indicates if this is a London Borough or
not.)

- CompanyCounty table, with fields:
CompanyID relates to Company.CompanyID
CountyID relates to County.CountyID
So, if a company operates in 4 counties, they have 4 entries in the 3rd
table.

You can now create your query very easily. (This structure with a junction
table is the standard relational solution for resolving the many-to-many
relation between companies and counties into a pair of one-to-many
relations.)

If you can't do that, you will need to use a UNION query to combine the 2
tables into one long list. But since you want to handle the duplicated
records differently, you will need to create 3 more queries first:
1. Create an INNER JOIN query between the 2 tables, based on the company.
This query contains only record that match in both tables.
2. Use the Unmatched query wizard (last option in first dialog when you
create a query) to get a query of all London companies that have no matches
in the other counties.
3. Use the Unmached query wiz to create a query for the other counties that
have no match in London.
4. Create the UNION ALL query to combine the values from the 3 into one long
list.

The UNION query cannot be displayed graphically, but it will be something
like this:

SELECT [Company] AS LondonCo, [Company] AS CountyCo
FROM Query1
UNION ALL
SELECT [Company] AS LondonCo, Null AS CountyCo
FROM Query2
UNION ALL
SELECT Null As LondonCo, [Company] AS CountyCo
FROM Query3

In my view, the work in creating the union query would be better spent in
creating the normalized design.

HTH.
 
Hi Allen

Thanks for your response. I couldn't restructure tables since the county
table was linked to a sub-county (borough) table, so it was not convenient to
mix the London and County information - particularly since users will need to
be able to input this data. However, I used your Union suggestion and it has
worked!

I am very grateful for your help on this matter.

Thank you

Allen Browne said:
The best solution here will be to redesign the tables so you have these:

- Company table (one record per organization), with fields:
CompanyID primary key
Company name of company
...

- County table (one record per county), with fields
County primary key
IsLondon Yes/no (indicates if this is a London Borough or
not.)

- CompanyCounty table, with fields:
CompanyID relates to Company.CompanyID
CountyID relates to County.CountyID
So, if a company operates in 4 counties, they have 4 entries in the 3rd
table.

You can now create your query very easily. (This structure with a junction
table is the standard relational solution for resolving the many-to-many
relation between companies and counties into a pair of one-to-many
relations.)

If you can't do that, you will need to use a UNION query to combine the 2
tables into one long list. But since you want to handle the duplicated
records differently, you will need to create 3 more queries first:
1. Create an INNER JOIN query between the 2 tables, based on the company.
This query contains only record that match in both tables.
2. Use the Unmatched query wizard (last option in first dialog when you
create a query) to get a query of all London companies that have no matches
in the other counties.
3. Use the Unmached query wiz to create a query for the other counties that
have no match in London.
4. Create the UNION ALL query to combine the values from the 3 into one long
list.

The UNION query cannot be displayed graphically, but it will be something
like this:

SELECT [Company] AS LondonCo, [Company] AS CountyCo
FROM Query1
UNION ALL
SELECT [Company] AS LondonCo, Null AS CountyCo
FROM Query2
UNION ALL
SELECT Null As LondonCo, [Company] AS CountyCo
FROM Query3

In my view, the work in creating the union query would be better spent in
creating the normalized design.

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

WilliamP61 said:
Hi and thanks for taking the time to read this.

I have a query based on three linked tables. It returns two sets of
geographical data (amongst other stuff relating to competitor
organisations)
one for London Boroughs and one for English Counties where organisations
work. In my datasheet I want to show the various organisational data for a
given organisation, including what they do and where they do it. That's
all
fine, however, for a given organisation some work is conducted in London
and
other work in an outside county. At the moment I have London Borough and
County as two seperate colums since they derive from two different tables.
How can I put them under one heading in the datasheet, an Area column, for
example, and exclude null values so that I don't have to have both columns
with some empty values showing? I am using Access 2002.

Thank you
Click to expand...
Click to expand...
 
Back
Top