linking fields from different tables in same database

[Pillinger]

I have a database (Access 2007) that is used for holding data on and
communicating with FE colleges. Obviously (?) each college has a number of
contacts (up to 14) and these are represented in two tables – one for College
Details and one for Contact Details which are linked by a one to many
relationship. Most colleges have one representative on one of 3 groups, but
not all colleges are represented on all groups and some not on any. The
membership of a group is noted by a field in the Contact details. Getting a
query to run to identify membership of groups is OK. But I can’t figure out
how to get a query with a row per COLLEGE that has no representation on
either one or more of the groups. What I get is a duplication of the college
name (as they identify the number of contacts who are not members of a
group). The only way I can figure it is to have a field in the College Table
which mirrors the one in the Contacts Table and by completing one it
automatically completes the ‘mirror’ field in the other table. I don’t want
to enter the same data twice but cannot link the tables.

 

[Daryl S]

Pillinger -

You should be able to do this with a query using a outer join on the College
table.

If you post your current query (the one that is missing the colleges without
representation on any groups), then we can help you adjust that query. Go
to query design and select the SQL View. Copy/paste that into your reply
posting.

 

[Toppo]

Daryl,
Apologies for any mix up here. I have posted this question several times &
not been able to find it afterwards. I posted the latest one from home
yesterday hence the different 'name'.

The Query that works i.e. identifying membership of groups is as follows:-

SELECT [College Details].[Organisation Name], [Contact Details].[First
Name], [Contact Details].Surname, [Contact Details].[Position in
Organisation], [Contact Details].[Member of Partnership Forum], [Contact
Details].[Member of Strategic Partner Group], [Contact Details].[Member of
Partner Marketing Group]
FROM [College Details] INNER JOIN [Contact Details] ON [College
Details].[Ref No] = [Contact Details].[College Ref No]
WHERE ((([Contact Details].[Member of Partnership Forum])=Yes) AND
(([College Details].[Current Partnership Status])="Active Partner" Or
([College Details].[Current Partnership Status])="In process of
termination")) OR ((([Contact Details].[Member of Strategic Partner
Group])=Yes) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination")) OR ((([Contact Details].[Member of Partner Marketing
Group])=Yes) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination"))
ORDER BY [College Details].[Organisation Name];


The query that gives all contacts for a college when all I want is the
college name that doesn't have a member in any one (or more) of the groups
is:-

SELECT [College Details].[Organisation Name], [Contact Details].Surname,
[Contact Details].[Member of Partnership Forum], [Contact Details].[Member of
Strategic Partner Group], [Contact Details].[Member of Partner Marketing
Group]
FROM [College Details] INNER JOIN [Contact Details] ON [College
Details].[Ref No] = [Contact Details].[College Ref No]
GROUP BY [College Details].[Organisation Name], [Contact Details].Surname,
[Contact Details].[Member of Partnership Forum], [Contact Details].[Member of
Strategic Partner Group], [Contact Details].[Member of Partner Marketing
Group], [College Details].[Current Partnership Status]
HAVING ((([Contact Details].[Member of Partnership Forum])=No) AND
(([College Details].[Current Partnership Status])="Active Partner" Or
([College Details].[Current Partnership Status])="In process of
termination")) OR ((([Contact Details].[Member of Strategic Partner
Group])=No) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination")) OR ((([Contact Details].[Member of Partner Marketing
Group])=No) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination"))
ORDER BY [College Details].[Organisation Name];


Look forward to hearing from you now I know where to look & apologies again
for anyone seeing my query & wondering why I hadn't acknowledged them

 

[Daryl S]

Toppo -

I have altered your SELECT statement as follows:
1. Changed INNER JOIN to LEFT JOIN. This will allow colleges with no
contacts listed to show up.
2. Changed the criteria on any [Contact Details] field to take into account
null values (e.g. where there is no contact). For example, the following
criteria
(([Contact Details].[Member of Partnership Forum])=No)
was changed to
((nz([Contact Details].[Member of Partnership Forum],No))=No)
which allowed for colleges that don't have members to show up as No.
I do wonder about this (No) - is this text or a boolean? If it is boolean,
you may want to change to FALSE. Since your other query (with Yes) works, I
did not change these to text or FALSE).

So, the new query would be this (this is untested, so there could be a
typo...):


SELECT [College Details].[Organisation Name], [Contact Details].Surname,
[Contact Details].[Member of Partnership Forum], [Contact Details].[Member of
Strategic Partner Group], [Contact Details].[Member of Partner Marketing
Group]
FROM [College Details] LEFT JOIN [Contact Details] ON [College
Details].[Ref No] = [Contact Details].[College Ref No]
GROUP BY [College Details].[Organisation Name], [Contact Details].Surname,
[Contact Details].[Member of Partnership Forum], [Contact Details].[Member of
Strategic Partner Group], [Contact Details].[Member of Partner Marketing
Group], [College Details].[Current Partnership Status]
HAVING (((nz([Contact Details].[Member of Partnership Forum],No))=No) AND
(([College Details].[Current Partnership Status])="Active Partner" Or
([College Details].[Current Partnership Status])="In process of
termination")) OR (((nz([Contact Details].[Member of Strategic Partner
Group],No))=No) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination")) OR (((Nz([Contact Details].[Member of Partner Marketing
Group],No))=No) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination"))
ORDER BY [College Details].[Organisation Name];

--
Daryl S

Daryl,
Apologies for any mix up here. I have posted this question several times &
not been able to find it afterwards. I posted the latest one from home
yesterday hence the different 'name'.

The Query that works i.e. identifying membership of groups is as follows:-

SELECT [College Details].[Organisation Name], [Contact Details].[First
Name], [Contact Details].Surname, [Contact Details].[Position in
Organisation], [Contact Details].[Member of Partnership Forum], [Contact
Details].[Member of Strategic Partner Group], [Contact Details].[Member of
Partner Marketing Group]
FROM [College Details] INNER JOIN [Contact Details] ON [College
Details].[Ref No] = [Contact Details].[College Ref No]
WHERE ((([Contact Details].[Member of Partnership Forum])=Yes) AND
(([College Details].[Current Partnership Status])="Active Partner" Or
([College Details].[Current Partnership Status])="In process of
termination")) OR ((([Contact Details].[Member of Strategic Partner
Group])=Yes) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination")) OR ((([Contact Details].[Member of Partner Marketing
Group])=Yes) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination"))
ORDER BY [College Details].[Organisation Name];


The query that gives all contacts for a college when all I want is the
college name that doesn't have a member in any one (or more) of the groups
is:-

SELECT [College Details].[Organisation Name], [Contact Details].Surname,
[Contact Details].[Member of Partnership Forum], [Contact Details].[Member of
Strategic Partner Group], [Contact Details].[Member of Partner Marketing
Group]
FROM [College Details] INNER JOIN [Contact Details] ON [College
Details].[Ref No] = [Contact Details].[College Ref No]
GROUP BY [College Details].[Organisation Name], [Contact Details].Surname,
[Contact Details].[Member of Partnership Forum], [Contact Details].[Member of
Strategic Partner Group], [Contact Details].[Member of Partner Marketing
Group], [College Details].[Current Partnership Status]
HAVING ((([Contact Details].[Member of Partnership Forum])=No) AND
(([College Details].[Current Partnership Status])="Active Partner" Or
([College Details].[Current Partnership Status])="In process of
termination")) OR ((([Contact Details].[Member of Strategic Partner
Group])=No) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination")) OR ((([Contact Details].[Member of Partner Marketing
Group])=No) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination"))
ORDER BY [College Details].[Organisation Name];


Look forward to hearing from you now I know where to look & apologies again
for anyone seeing my query & wondering why I hadn't acknowledged them

Pillinger -

You should be able to do this with a query using a outer join on the College
table.

If you post your current query (the one that is missing the colleges without
representation on any groups), then we can help you adjust that query. Go
to query design and select the SQL View. Copy/paste that into your reply
posting.

 

[Toppo]

Sorry, Daryl, but this does not appear to have worked. I am still getting a
list of all staff in colleges who are not members of one of the groups. I
think I’m whistling against the wind here & will try to explain why.
Contact Details table contains (amongst other fields)
Contact ID (Auto number PK)
College ID (Number)
Surname
Position
Email
Tel no
Member of Forum group – Yes/No
Member of Marketing group – Yes/No
Member of Strategic Group – Yes/No

College Details table contains (amongst many other fields)
College ID (Auto number PK)
Name of organisation
Tables linked one (college details[College ID]) to many (contact details
–[College ID])

I have a number of queries that list all members of the 3 groups
(forum/strategic/marketing), collectively and separately. These work no
problem.
What I want is a list of colleges only that do not have a member of one or
more of the 3 groups. There are colleges that are not represented on all
three groups, other that are represented only on one or two.

I think that if the Yes/No fields indicating membership of the groups was in
the college details table then I wouldn’t have a problem. However, I would
not then be able to have a list of members of each group. An alternative
would be to stick with what I have plus another set of Yes/No fields in the
College details table, and check those as appropriate at the same time as
doing the same in the Contacts Details table. But this is data duplication &
something I don’t do as a matter of course. Which is where I came in & don’t
think there is an answer: if I could find a way of checking a Yes/No field
(say for membership of the forum group)in the College Database automatically
when a Yes/No field in the contacts table was checked (or visa-versa), then
this would solve the problem. Otherwise I think we are hamstrung.

Many thanks for your time & effort, Daryl. Much appreciated.

Toppo -

I have altered your SELECT statement as follows:
1. Changed INNER JOIN to LEFT JOIN. This will allow colleges with no
contacts listed to show up.
2. Changed the criteria on any [Contact Details] field to take into account
null values (e.g. where there is no contact). For example, the following
criteria
(([Contact Details].[Member of Partnership Forum])=No)
was changed to
((nz([Contact Details].[Member of Partnership Forum],No))=No)
which allowed for colleges that don't have members to show up as No.
I do wonder about this (No) - is this text or a boolean? If it is boolean,
you may want to change to FALSE. Since your other query (with Yes) works, I
did not change these to text or FALSE).

So, the new query would be this (this is untested, so there could be a
typo...):


SELECT [College Details].[Organisation Name], [Contact Details].Surname,
[Contact Details].[Member of Partnership Forum], [Contact Details].[Member of
Strategic Partner Group], [Contact Details].[Member of Partner Marketing
Group]
FROM [College Details] LEFT JOIN [Contact Details] ON [College
Details].[Ref No] = [Contact Details].[College Ref No]
GROUP BY [College Details].[Organisation Name], [Contact Details].Surname,
[Contact Details].[Member of Partnership Forum], [Contact Details].[Member of
Strategic Partner Group], [Contact Details].[Member of Partner Marketing
Group], [College Details].[Current Partnership Status]
HAVING (((nz([Contact Details].[Member of Partnership Forum],No))=No) AND
(([College Details].[Current Partnership Status])="Active Partner" Or
([College Details].[Current Partnership Status])="In process of
termination")) OR (((nz([Contact Details].[Member of Strategic Partner
Group],No))=No) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination")) OR (((Nz([Contact Details].[Member of Partner Marketing
Group],No))=No) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination"))
ORDER BY [College Details].[Organisation Name];

--
Daryl S

Daryl,
Apologies for any mix up here. I have posted this question several times &
not been able to find it afterwards. I posted the latest one from home
yesterday hence the different 'name'.

The Query that works i.e. identifying membership of groups is as follows:-

SELECT [College Details].[Organisation Name], [Contact Details].[First
Name], [Contact Details].Surname, [Contact Details].[Position in
Organisation], [Contact Details].[Member of Partnership Forum], [Contact
Details].[Member of Strategic Partner Group], [Contact Details].[Member of
Partner Marketing Group]
FROM [College Details] INNER JOIN [Contact Details] ON [College
Details].[Ref No] = [Contact Details].[College Ref No]
WHERE ((([Contact Details].[Member of Partnership Forum])=Yes) AND
(([College Details].[Current Partnership Status])="Active Partner" Or
([College Details].[Current Partnership Status])="In process of
termination")) OR ((([Contact Details].[Member of Strategic Partner
Group])=Yes) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination")) OR ((([Contact Details].[Member of Partner Marketing
Group])=Yes) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination"))
ORDER BY [College Details].[Organisation Name];


The query that gives all contacts for a college when all I want is the
college name that doesn't have a member in any one (or more) of the groups
is:-

SELECT [College Details].[Organisation Name], [Contact Details].Surname,
[Contact Details].[Member of Partnership Forum], [Contact Details].[Member of
Strategic Partner Group], [Contact Details].[Member of Partner Marketing
Group]
FROM [College Details] INNER JOIN [Contact Details] ON [College
Details].[Ref No] = [Contact Details].[College Ref No]
GROUP BY [College Details].[Organisation Name], [Contact Details].Surname,
[Contact Details].[Member of Partnership Forum], [Contact Details].[Member of
Strategic Partner Group], [Contact Details].[Member of Partner Marketing
Group], [College Details].[Current Partnership Status]
HAVING ((([Contact Details].[Member of Partnership Forum])=No) AND
(([College Details].[Current Partnership Status])="Active Partner" Or
([College Details].[Current Partnership Status])="In process of
termination")) OR ((([Contact Details].[Member of Strategic Partner
Group])=No) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination")) OR ((([Contact Details].[Member of Partner Marketing
Group])=No) AND (([College Details].[Current Partnership Status])="Active
Partner" Or ([College Details].[Current Partnership Status])="In process of
termination"))
ORDER BY [College Details].[Organisation Name];


Look forward to hearing from you now I know where to look & apologies again
for anyone seeing my query & wondering why I hadn't acknowledged them

Pillinger -

You should be able to do this with a query using a outer join on the College
table.

If you post your current query (the one that is missing the colleges without
representation on any groups), then we can help you adjust that query. Go
to query design and select the SQL View. Copy/paste that into your reply
posting.

--
Daryl S


:

I have a database (Access 2007) that is used for holding data on and
communicating with FE colleges. Obviously (?) each college has a number of
contacts (up to 14) and these are represented in two tables – one for College
Details and one for Contact Details which are linked by a one to many
relationship. Most colleges have one representative on one of 3 groups, but
not all colleges are represented on all groups and some not on any. The
membership of a group is noted by a field in the Contact details. Getting a
query to run to identify membership of groups is OK. But I can’t figure out
how to get a query with a row per COLLEGE that has no representation on
either one or more of the groups. What I get is a duplication of the college
name (as they identify the number of contacts who are not members of a
group). The only way I can figure it is to have a field in the College Table
which mirrors the one in the Contacts Table and by completing one it
automatically completes the ‘mirror’ field in the other table. I don’t want
to enter the same data twice but cannot link the tables.