Query to show all records whether or not there is a matching messa

G

Guest

Hi all,

Not quite sure how to phrase this question! Here's the tables I'm querying:

Caller Role ID (PK)
Caller Role

Caller Role Access ID (PK, FK to [Message Display.Caller Role Access], 1 to n)
Caller Role (FK to Caller Role ID, 1 to n)
Service Offering
Access Allowed (Y/N)
Message Displayed (Y/N)

Message Display ID (PK)
Caller Role Access
Message (FK to Message ID, n to 1)

Message ID (PK)
Message Type
Message Text

I need a query to select a specific Caller Role and display all records
where Access Allowed = Y, and display the message text if there is any.

The problem i'm finding is that if there is no Message match, because
Message Displayed = N, this gets ignored by the query, as there are no
matching references in the Message ID table.

So how can I get the query to show all matches to Access Allowed = Y,
whether Mesage is present or not??

Make any sense??

Thanks

Mark
 
G

Guest

Right click the join (in the design grid) between the tables and select -
include all records from TableCallerRole (not sure of the name you have given
it) and etc etc etc

An arrow will appear to show you have joined them correctly
 
G

Guest

Got it, thanks..

It was giving me ambiquous outer join errors on my previous attempts because
I hadn't gone through all the links in the query to set the join properties,
I was relying on the relationships between the tables..

Very rusty on Access i'm afraid!

Wayne-I-M said:
Right click the join (in the design grid) between the tables and select -
include all records from TableCallerRole (not sure of the name you have given
it) and etc etc etc

An arrow will appear to show you have joined them correctly

--
Wayne
Manchester, England.



Markpud said:
Hi all,

Not quite sure how to phrase this question! Here's the tables I'm querying:

Caller Role ID (PK)
Caller Role

Caller Role Access ID (PK, FK to [Message Display.Caller Role Access], 1 to n)
Caller Role (FK to Caller Role ID, 1 to n)
Service Offering
Access Allowed (Y/N)
Message Displayed (Y/N)

Message Display ID (PK)
Caller Role Access
Message (FK to Message ID, n to 1)

Message ID (PK)
Message Type
Message Text

I need a query to select a specific Caller Role and display all records
where Access Allowed = Y, and display the message text if there is any.

The problem i'm finding is that if there is no Message match, because
Message Displayed = N, this gets ignored by the query, as there are no
matching references in the Message ID table.

So how can I get the query to show all matches to Access Allowed = Y,
whether Mesage is present or not??

Make any sense??

Thanks

Mark
Click to expand...
Click to expand...
 
G

Guest

If i could pick your brains a bit more??

I now need to make another query to display all the Messages for each Caller
Role ID, regardless of Access Allowed.

I have built the following, but it gives me many duplicates due to the
relationships. One Caller Role can have Access to many Service Offerings. For
each Service Offering there can be many messages.

SELECT [Caller Roles].[Caller Role], [Caller Role Access].[Message
Displayed], [Caller Role Messages].[Message ID], [Caller Role
Messages].[Message Type], [Caller Role Messages].[Message Text]
FROM [Caller Roles] LEFT JOIN ([Caller Role Messages] RIGHT JOIN ([Caller
Role Access] LEFT JOIN [Caller Role Message Display] ON [Caller Role
Access].[Caller Role Access ID] = [Caller Role Message Display].[Caller Roll
Access]) ON [Caller Role Messages].[Message ID] = [Caller Role Message
Display].Message) ON [Caller Roles].[Caller Role ID] = [Caller Role
Access].[Caller Role]
WHERE ((([Caller Roles].[Caller Role])=[Enter Caller Role]) AND (([Caller
Role Access].[Message Displayed])=-1));

Thanks again,

Mark

Markpud said:
Got it, thanks..

It was giving me ambiquous outer join errors on my previous attempts because
I hadn't gone through all the links in the query to set the join properties,
I was relying on the relationships between the tables..


Very rusty on Access i'm afraid!

Wayne-I-M said:
Right click the join (in the design grid) between the tables and select -
include all records from TableCallerRole (not sure of the name you have given
it) and etc etc etc

An arrow will appear to show you have joined them correctly

--
Wayne
Manchester, England.



Markpud said:
Hi all,

Not quite sure how to phrase this question! Here's the tables I'm querying:

Caller Role ID (PK)
Caller Role

Caller Role Access ID (PK, FK to [Message Display.Caller Role Access], 1 to n)
Caller Role (FK to Caller Role ID, 1 to n)
Service Offering
Access Allowed (Y/N)
Message Displayed (Y/N)

Message Display ID (PK)
Caller Role Access
Message (FK to Message ID, n to 1)

Message ID (PK)
Message Type
Message Text

I need a query to select a specific Caller Role and display all records
where Access Allowed = Y, and display the message text if there is any.

The problem i'm finding is that if there is no Message match, because
Message Displayed = N, this gets ignored by the query, as there are no
matching references in the Message ID table.

So how can I get the query to show all matches to Access Allowed = Y,
whether Mesage is present or not??

Make any sense??

Thanks

Mark
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Never mind, I found the option in the query properties, to show Unique
Entries :)

Markpud said:
If i could pick your brains a bit more??

I now need to make another query to display all the Messages for each Caller
Role ID, regardless of Access Allowed.

I have built the following, but it gives me many duplicates due to the
relationships. One Caller Role can have Access to many Service Offerings. For
each Service Offering there can be many messages.

SELECT [Caller Roles].[Caller Role], [Caller Role Access].[Message
Displayed], [Caller Role Messages].[Message ID], [Caller Role
Messages].[Message Type], [Caller Role Messages].[Message Text]
FROM [Caller Roles] LEFT JOIN ([Caller Role Messages] RIGHT JOIN ([Caller
Role Access] LEFT JOIN [Caller Role Message Display] ON [Caller Role
Access].[Caller Role Access ID] = [Caller Role Message Display].[Caller Roll
Access]) ON [Caller Role Messages].[Message ID] = [Caller Role Message
Display].Message) ON [Caller Roles].[Caller Role ID] = [Caller Role
Access].[Caller Role]
WHERE ((([Caller Roles].[Caller Role])=[Enter Caller Role]) AND (([Caller
Role Access].[Message Displayed])=-1));

Thanks again,

Mark

Markpud said:
Got it, thanks..

It was giving me ambiquous outer join errors on my previous attempts because
I hadn't gone through all the links in the query to set the join properties,
I was relying on the relationships between the tables..


Very rusty on Access i'm afraid!

Wayne-I-M said:
Right click the join (in the design grid) between the tables and select -
include all records from TableCallerRole (not sure of the name you have given
it) and etc etc etc

An arrow will appear to show you have joined them correctly

--
Wayne
Manchester, England.



:

Hi all,

Not quite sure how to phrase this question! Here's the tables I'm querying:

Caller Role ID (PK)
Caller Role

Caller Role Access ID (PK, FK to [Message Display.Caller Role Access], 1 to n)
Caller Role (FK to Caller Role ID, 1 to n)
Service Offering
Access Allowed (Y/N)
Message Displayed (Y/N)

Message Display ID (PK)
Caller Role Access
Message (FK to Message ID, n to 1)

Message ID (PK)
Message Type
Message Text

I need a query to select a specific Caller Role and display all records
where Access Allowed = Y, and display the message text if there is any.

The problem i'm finding is that if there is no Message match, because
Message Displayed = N, this gets ignored by the query, as there are no
matching references in the Message ID table.

So how can I get the query to show all matches to Access Allowed = Y,
whether Mesage is present or not??

Make any sense??

Thanks

Mark
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

OK... so my needs are greatly less than these... all I need is to find ALL
customers that have a particular zip code. I have the zips in a separate
table & based on the zip, they can have a particular "plan"... my trouble is
it matches just 1 then it moves on... How can I get ALL of the matches to
output to a table?

Markpud said:
Never mind, I found the option in the query properties, to show Unique
Entries :)

Markpud said:
If i could pick your brains a bit more??

I now need to make another query to display all the Messages for each Caller
Role ID, regardless of Access Allowed.

I have built the following, but it gives me many duplicates due to the
relationships. One Caller Role can have Access to many Service Offerings. For
each Service Offering there can be many messages.

SELECT [Caller Roles].[Caller Role], [Caller Role Access].[Message
Displayed], [Caller Role Messages].[Message ID], [Caller Role
Messages].[Message Type], [Caller Role Messages].[Message Text]
FROM [Caller Roles] LEFT JOIN ([Caller Role Messages] RIGHT JOIN ([Caller
Role Access] LEFT JOIN [Caller Role Message Display] ON [Caller Role
Access].[Caller Role Access ID] = [Caller Role Message Display].[Caller Roll
Access]) ON [Caller Role Messages].[Message ID] = [Caller Role Message
Display].Message) ON [Caller Roles].[Caller Role ID] = [Caller Role
Access].[Caller Role]
WHERE ((([Caller Roles].[Caller Role])=[Enter Caller Role]) AND (([Caller
Role Access].[Message Displayed])=-1));

Thanks again,

Mark

Markpud said:
Got it, thanks..

It was giving me ambiquous outer join errors on my previous attempts because
I hadn't gone through all the links in the query to set the join properties,
I was relying on the relationships between the tables..


Very rusty on Access i'm afraid!

:

Right click the join (in the design grid) between the tables and select -
include all records from TableCallerRole (not sure of the name you have given
it) and etc etc etc

An arrow will appear to show you have joined them correctly

--
Wayne
Manchester, England.



:

Hi all,

Not quite sure how to phrase this question! Here's the tables I'm querying:

Caller Role ID (PK)
Caller Role

Caller Role Access ID (PK, FK to [Message Display.Caller Role Access], 1 to n)
Caller Role (FK to Caller Role ID, 1 to n)
Service Offering
Access Allowed (Y/N)
Message Displayed (Y/N)

Message Display ID (PK)
Caller Role Access
Message (FK to Message ID, n to 1)

Message ID (PK)
Message Type
Message Text

I need a query to select a specific Caller Role and display all records
where Access Allowed = Y, and display the message text if there is any.

The problem i'm finding is that if there is no Message match, because
Message Displayed = N, this gets ignored by the query, as there are no
matching references in the Message ID table.

So how can I get the query to show all matches to Access Allowed = Y,
whether Mesage is present or not??

Make any sense??

Thanks

Mark
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUBQUERIES in the FROM clause with Access query 7
Query will not allow me to add new records 1
Complex for me but may be a very simple query for you experts. 2
query help 1
Counting survey responses, 4 queries or 1 query?? 1
Display message if specific query returns a value 4
Embedded subforms and data-entry on M:M relationships 1
Create Multiple Table Rows From Single Table Row 2

Top