New Services?

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

Guest

Hi another rookie question!!!

I have 2 tables, one which records contacts with clients, and another which
records the services which they recieve...

I need to know for each contact whether or not the client is recieving
services at the point of contact.

To make this a little more difficult, i need to be able to do this in one
block of SQL as i am transposing this to business objects.

My Contacts Table (CONTACTS)
CONTACT ID
PERSON ID
DATE OF CONTACT

SERVICE AGREEMENTS
AGREE ID
PERSON ID
START
END

One person may have many services all starting at different times and may
have a few contacts over the year. I need a query that pulls back only
clients that have a contact date which is at a point where that client has no
"open" services.

Any pointers???

Thanks,


Mark
 
You need to define "point of contact", "where that client has no 'open'
services". Convert these phrases into examples/rules based on your tables
and fields.
 
Mark said:
I have 2 tables, one which records contacts with clients, and another which
records the services which they recieve...

I need to know for each contact whether or not the client is recieving
services at the point of contact.

To make this a little more difficult, i need to be able to do this in one
block of SQL as i am transposing this to business objects.

My Contacts Table (CONTACTS)
CONTACT ID
PERSON ID
DATE OF CONTACT

SERVICE AGREEMENTS
AGREE ID
PERSON ID
START
END

One person may have many services all starting at different times and may
have a few contacts over the year. I need a query that pulls back only
clients that have a contact date which is at a point where that client has no
"open" services.
Click to expand...


I think you want a find unmatched records query with a
twist:

SELECT C.[CONTACT ID], C.[PERSON ID], C.[DATE OF CONTACT]
FROM CONTACTS As C LEFT JOIN [SERVICE AGREEMENTS] As S
ON C.[PERSON ID] = S.[PERSON ID]
AND C.[DATE OF CONTACT] >= S.START
AND C.[DATE OF CONTACT] <= S.END
WHERE S.[PERSON ID] Is Null

The twist is that the join is not an = type join. This
means that you can not create the query using the query
design grid, you must use SQL View instead.
 
Hi thanks marshall;

SELECT c.[PER_ID], c.[INITIAL_CONTACT_DATE]
FROM INITIAL_CONTACTS as c LEFT JOIN [AGREEMENTS] as s
ON c.[PER_ID] = s.[SER_PER_ID]
AND c.[INITIAL_CONTACT_DATE] >= s.START
AND c.[INITIAL_CONTACT_DATE] >= s.END
WHERE s.[SER_PER_ID] is Null;

This is what i transposed, from your query, and it doesnt seem to work, when
i hit run it asks me for the value of C.INITIAL_CONTACT_DATE!

Here is an example of my 2 tables

INITIAL CONTACTS (The date when someone gets in touch)
-----------------------------------------------------------------------
PER_ID NAME INITIAL_CONTACT_DATE
F1 John 01/04/2005
F1 John 20/05/2005
F2 Fred 22/06/2006
F3 Bob 12/12/2005

AGREEMENTS (Start and end dates of any services any client recieves)
---------------------------------------------------------------------------------
SER_ID SER_PER_ID START END
1 F1 02/04/05 30/06/05
2 F1 03/05/05 04/05/06
3 F1 04/05/05 05/05/05
4 F2 01/01/03 30/06/06
5 F2 21/07/04 21/07/05
6 F2 22/02/02 23/02/02

In the case of john, For the contact on the 1/4/05 he was a NEW CLIENT, as
he did not have any services which were open (at the date of contact no
services had started or all had finished) to him. For the contact on
20/05/05 he was NOT a new client as there is a service that starts 02/04/05
that doesnt finish until 30/06/05, so at the time of contact, he was
recieving a service.

What i want is a list of Just the contacts where where the client was NEW.

I hope that this is a little clearer now?

MANY thanks,

Mark


Marshall Barton said:
Mark said:
I have 2 tables, one which records contacts with clients, and another which
records the services which they recieve...

I need to know for each contact whether or not the client is recieving
services at the point of contact.

To make this a little more difficult, i need to be able to do this in one
block of SQL as i am transposing this to business objects.

My Contacts Table (CONTACTS)
CONTACT ID
PERSON ID
DATE OF CONTACT

SERVICE AGREEMENTS
AGREE ID
PERSON ID
START
END

One person may have many services all starting at different times and may
have a few contacts over the year. I need a query that pulls back only
clients that have a contact date which is at a point where that client has no
"open" services.
Click to expand...


I think you want a find unmatched records query with a
twist:

SELECT C.[CONTACT ID], C.[PERSON ID], C.[DATE OF CONTACT]
FROM CONTACTS As C LEFT JOIN [SERVICE AGREEMENTS] As S
ON C.[PERSON ID] = S.[PERSON ID]
AND C.[DATE OF CONTACT] >= S.START
AND C.[DATE OF CONTACT] <= S.END
WHERE S.[PERSON ID] Is Null

The twist is that the join is not an = type join. This
means that you can not create the query using the query
design grid, you must use SQL View instead.
Click to expand...
 
I think the query I posted will do what you want.

You have a typo in the END condition, it should be <=

If you are being prompted for INITIAL_CONTACT_DATE, then
that name is not the name of a field in the INITIAL_CONTACTS
table, you probably have another typo here.
--
Marsh
MVP [MS Access]


Mark said:
Hi thanks marshall;

SELECT c.[PER_ID], c.[INITIAL_CONTACT_DATE]
FROM INITIAL_CONTACTS as c LEFT JOIN [AGREEMENTS] as s
ON c.[PER_ID] = s.[SER_PER_ID]
AND c.[INITIAL_CONTACT_DATE] >= s.START
AND c.[INITIAL_CONTACT_DATE] >= s.END
WHERE s.[SER_PER_ID] is Null;

This is what i transposed, from your query, and it doesnt seem to work, when
i hit run it asks me for the value of C.INITIAL_CONTACT_DATE!

Here is an example of my 2 tables

INITIAL CONTACTS (The date when someone gets in touch)
-----------------------------------------------------------------------
PER_ID NAME INITIAL_CONTACT_DATE
F1 John 01/04/2005
F1 John 20/05/2005
F2 Fred 22/06/2006
F3 Bob 12/12/2005

AGREEMENTS (Start and end dates of any services any client recieves)
---------------------------------------------------------------------------------
SER_ID SER_PER_ID START END
1 F1 02/04/05 30/06/05
2 F1 03/05/05 04/05/06
3 F1 04/05/05 05/05/05
4 F2 01/01/03 30/06/06
5 F2 21/07/04 21/07/05
6 F2 22/02/02 23/02/02

In the case of john, For the contact on the 1/4/05 he was a NEW CLIENT, as
he did not have any services which were open (at the date of contact no
services had started or all had finished) to him. For the contact on
20/05/05 he was NOT a new client as there is a service that starts 02/04/05
that doesnt finish until 30/06/05, so at the time of contact, he was
recieving a service.

What i want is a list of Just the contacts where where the client was NEW.

I hope that this is a little clearer now?

MANY thanks,

Mark


Marshall Barton said:
Mark said:
I have 2 tables, one which records contacts with clients, and another which
records the services which they recieve...

I need to know for each contact whether or not the client is recieving
services at the point of contact.

To make this a little more difficult, i need to be able to do this in one
block of SQL as i am transposing this to business objects.

My Contacts Table (CONTACTS)
CONTACT ID
PERSON ID
DATE OF CONTACT

SERVICE AGREEMENTS
AGREE ID
PERSON ID
START
END

One person may have many services all starting at different times and may
have a few contacts over the year. I need a query that pulls back only
clients that have a contact date which is at a point where that client has no
"open" services.
Click to expand...


I think you want a find unmatched records query with a
twist:

SELECT C.[CONTACT ID], C.[PERSON ID], C.[DATE OF CONTACT]
FROM CONTACTS As C LEFT JOIN [SERVICE AGREEMENTS] As S
ON C.[PERSON ID] = S.[PERSON ID]
AND C.[DATE OF CONTACT] >= S.START
AND C.[DATE OF CONTACT] <= S.END
WHERE S.[PERSON ID] Is Null

The twist is that the join is not an = type join. This
means that you can not create the query using the query
design grid, you must use SQL View instead.
Click to expand...
Click to expand...
 
Ahhhhhh!!!!

I spent about 40 minutes thinking i was going insane; i checked and double
checked the query, then realised that it was the tesing table i had created
that had the typo!!!

Thanks for your help!



Marshall Barton said:
I think the query I posted will do what you want.

You have a typo in the END condition, it should be <=

If you are being prompted for INITIAL_CONTACT_DATE, then
that name is not the name of a field in the INITIAL_CONTACTS
table, you probably have another typo here.
--
Marsh
MVP [MS Access]


Mark said:
Hi thanks marshall;

SELECT c.[PER_ID], c.[INITIAL_CONTACT_DATE]
FROM INITIAL_CONTACTS as c LEFT JOIN [AGREEMENTS] as s
ON c.[PER_ID] = s.[SER_PER_ID]
AND c.[INITIAL_CONTACT_DATE] >= s.START
AND c.[INITIAL_CONTACT_DATE] >= s.END
WHERE s.[SER_PER_ID] is Null;

This is what i transposed, from your query, and it doesnt seem to work, when
i hit run it asks me for the value of C.INITIAL_CONTACT_DATE!

Here is an example of my 2 tables

INITIAL CONTACTS (The date when someone gets in touch)
-----------------------------------------------------------------------
PER_ID NAME INITIAL_CONTACT_DATE
F1 John 01/04/2005
F1 John 20/05/2005
F2 Fred 22/06/2006
F3 Bob 12/12/2005

AGREEMENTS (Start and end dates of any services any client recieves)
---------------------------------------------------------------------------------
SER_ID SER_PER_ID START END
1 F1 02/04/05 30/06/05
2 F1 03/05/05 04/05/06
3 F1 04/05/05 05/05/05
4 F2 01/01/03 30/06/06
5 F2 21/07/04 21/07/05
6 F2 22/02/02 23/02/02

In the case of john, For the contact on the 1/4/05 he was a NEW CLIENT, as
he did not have any services which were open (at the date of contact no
services had started or all had finished) to him. For the contact on
20/05/05 he was NOT a new client as there is a service that starts 02/04/05
that doesnt finish until 30/06/05, so at the time of contact, he was
recieving a service.

What i want is a list of Just the contacts where where the client was NEW.

I hope that this is a little clearer now?

MANY thanks,

Mark


Marshall Barton said:
Mark Stephenson wrote:
I have 2 tables, one which records contacts with clients, and another which
records the services which they recieve...

I need to know for each contact whether or not the client is recieving
services at the point of contact.

To make this a little more difficult, i need to be able to do this in one
block of SQL as i am transposing this to business objects.

My Contacts Table (CONTACTS)
CONTACT ID
PERSON ID
DATE OF CONTACT

SERVICE AGREEMENTS
AGREE ID
PERSON ID
START
END

One person may have many services all starting at different times and may
have a few contacts over the year. I need a query that pulls back only
clients that have a contact date which is at a point where that client has no
"open" services.


I think you want a find unmatched records query with a
twist:

SELECT C.[CONTACT ID], C.[PERSON ID], C.[DATE OF CONTACT]
FROM CONTACTS As C LEFT JOIN [SERVICE AGREEMENTS] As S
ON C.[PERSON ID] = S.[PERSON ID]
AND C.[DATE OF CONTACT] >= S.START
AND C.[DATE OF CONTACT] <= S.END
WHERE S.[PERSON ID] Is Null

The twist is that the join is not an = type join. This
means that you can not create the query using the query
design grid, you must use SQL View instead.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top