Please help with this query

G

Guest

I need to compare the MCDID from one table to 2 other tables that contain
multiple rows for each member due to effective and expiration date changes
and return a single row with the member information for members whose MCDID
is in the first table and who were not active for october. A member may have
a row for being active in september and a row for november but was missed in
october. I wish I could explain this better so feel free to ask questions.

Thanks in advance,
Matt


Table 1 = LostMembers1005:
MCDID text
HealthPlan text
LastName text
FirstName text
DOB date

Table 2 = tmpLostMembers1005_1:
MCDID text
EffDate text as cyymmdd
ExpDate text as cyymmdd

Table 3 = tmpLostMembers1005_2:
MCDID text
CodeEffDate text as cyymmdd
CodeExpDate text as cyymmdd

Here are the 2 queries that I've tried.
--1
SELECT DISTINCT l.MCDID, l.HealthPlan, l.CarrierCode, l.LastName,
l.FirstName, l.DOB, e.Code, e.CodeEffDate AS [Tier Code EffDate], e.[Tier
Code ExpDate] AS [Tier Code ExpDate], m.EFFDATE AS [TXEN EFFDATE], m.EXPDATE
AS [TXEN EXPDATE] INTO tmpLostMembers1005_3
FROM (LostMembers1005 AS l INNER JOIN tmpLostMembers1005_1 AS m ON m.MCDID =
l.MCDID) INNER JOIN tmpLostMembers1005_2 AS e ON e.MCDID = l.MCDID
WHERE (2051001 NOT BETWEEN m.[TXEN EFFDATE] AND m.[TXEN EXPDAT]) AND
(2051001 NOT BETWEEN e.[Tier Code EffDate] AND e.[Tier Code ExpDate]) AND
(m.[TXEN EXPDAT] < 2051031) AND (e.[Tier Code ExpDate] < 2051031)
GROUP BY l.MCDID, l.HealthPlan, l.CarrierCode, l.LastName, l.FirstName,
l.DOB, e.[Tier Code], e.[Tier Code EffDate], e.[Tier Code ExpDate], m.[TXEN
EFFDAT], m.[TXEN EXPDAT];

--2 Final query to return the results
SELECT DISTINCT l.MCDID, m.HealthPlan, m.CarrierCode, m.LastName,
m.FirstName, m.DOB, m.[Tier Code], FormatDateUS(m.[Tier Code EffDate]) AS
[Tier EffDate], FormatDateUS(m.[Tier Code ExpDate]) AS [Tier ExpDate],
FormatDateUS(m.[TXEN EFFDAT]) AS [TXEN EFFDATE], FormatDateUS(m.[TXEN
EXPDAT]) AS [TXEN EXPDATE] INTO tmpLostMembers1005_4
FROM LostMembers1005 AS l INNER JOIN tmpLostMembers1005_3 AS m ON l.MCDID =
m.MCDID
WHERE ([Tier Code EffDate] = (SELECT MAX([Tier Code EffDate]) FROM
tmpLostMembers1005_3 WHERE tmpLostMembers1005_3.MCDID = l.MCDID)) AND ([TXEN
EXPDAT] = (Select Max([TXEN EXPDAT]) FROM tmpLostMembers1005_3 WHERE
tmpLostMembers1005_3.MCDID = l.MCDID))
GROUP BY l.MCDID, m.HealthPlan, m.CarrierCode, m.LastName, m.FirstName,
m.DOB, m.[Tier Code], m.[Tier Code EffDate], m.[Tier Code ExpDate], m.[TXEN
EFFDAT], m.[TXEN EXPDAT];
 
V

Vincent Johns

This would have been easier if you had posted some example data, maybe a
couple of records, along with a description of what you wanted the
results to be. For example, the records could describe someone who was
active in September and in November but was missed in October, and you
might include what you'd want the Table Datasheet View for the
[LostMembers1005_4] Table to look like for those records.


I haven't done much with this except to correct apparent misspellings
and to guess at your data. Function calls like

FormatDateUS(m.[Tier Code ExpDate])

, which my copy of Access 2000 did not recognize, I changed to

Format(m.[Tier Code ExpDate],"mm/dd/yy") .


Starting with example Tables like these...


[LostMembers1005] Table Datasheet View:

MCDID Health FirstName LastName DOB Carrier
Plan Code
----- ------ --------- -------- -------- -------
x y John Doe 7/7/1977 z


[LostMembers1005_1] Table Datasheet View:

MCDID EffDate ExpDate
----- -------- --------
x 2051011 2051021


[LostMembers1005_2] Table Datasheet View:

MCDID CodeEffDate CodeExpDate Code
----- ----------- ----------- ----
x 2051011 2051021 w


I ran my version of your Queries, getting the results shown here.

[Q--1] SQL:

SELECT DISTINCT l.MCDID, l.HealthPlan,
l.CarrierCode, l.LastName, l.FirstName,
l.DOB, e.Code AS [Tier Code],
e.CodeEffDate AS [Tier Code EffDate],
e.CodeExpDate AS [Tier Code ExpDate],
m.EffDate AS [TXEN EFFDATE],
m.ExpDate AS [TXEN EXPDATE]
INTO tmpLostMembers1005_3
FROM (LostMembers1005 AS l
INNER JOIN tmpLostMembers1005_1 AS m
ON l.MCDID=m.MCDID)
INNER JOIN tmpLostMembers1005_2 AS e
ON l.MCDID=e.MCDID
WHERE ((("2051001")
Not Between m.EffDate And m.ExpDate
And ("2051001") Not Between
e.CodeEffDate And e.CodeExpDate)
And ((m.ExpDate)<"2051031")
And ((e.CodeExpDate)<"2051031"))
GROUP BY l.MCDID, l.HealthPlan, l.CarrierCode,
l.LastName, l.FirstName, l.DOB, e.Code,
e.CodeEffDate, e.CodeExpDate, m.EffDate,
m.ExpDate, e.CodeExpDate, e.Code,
e.CodeEffDate, m.EffDate, m.ExpDate;


[LostMembers1005_3] Table Datasheet View:

MCDID Health Carrier Last First DOB ...
Plan Code Name Name
----- ------ ------- ---- ----- --------
x y z Doe John 7/7/1977

... Tier Tier Code Tier Code TXEN TXEN
Code EffDate ExpDate EFFDATE EXPDATE
---- --------- --------- ------- -------
w 2051011 2051021 2051011 2051021


[Q--2] SQL:

SELECT DISTINCT l.MCDID, m.HealthPlan,
m.CarrierCode, m.LastName, m.FirstName,
m.DOB, m.[Tier Code],
Format(m.[Tier Code EffDate],"mm/dd/yy")
AS [Tier EffDate],
Format(m.[Tier Code ExpDate],"mm/dd/yy")
AS [Tier ExpDate],
Format(m.[TXEN EFFDATE],"mm/dd/yy")
AS [TXEN EFFDAT],
Format(m.[TXEN EXPDATE],"mm/dd/yy")
AS [TXEN EXPDAT]
INTO tmpLostMembers1005_4
FROM LostMembers1005 AS l
INNER JOIN tmpLostMembers1005_3 AS m
ON l.MCDID=m.MCDID
WHERE (((m.[Tier Code EffDate])
=(SELECT MAX([Tier Code EffDate])
FROM tmpLostMembers1005_3
WHERE tmpLostMembers1005_3.MCDID = l.MCDID))
AND ((m.[TXEN EXPDATE])
=(Select Max([TXEN EXPDATE])
FROM tmpLostMembers1005_3
WHERE tmpLostMembers1005_3.MCDID = l.MCDID)))
GROUP BY l.MCDID, m.HealthPlan, m.CarrierCode,
m.LastName, m.FirstName, m.DOB, m.[Tier Code],
m.[Tier Code EffDate], m.[Tier Code ExpDate],
m.[TXEN EFFDATE], m.[TXEN EXPDATE];


[LostMembers1005_4] Table Datasheet View:

MCDID Health Carrier Last First DOB ...
Plan Code Name Name
----- ------ ------- ---- ----- --------
x y z Doe John 7/7/1977

... Tier Tier Tier TXEN TXEN
Code EffDate ExpDate EFFDAT EXPDAT

---- -------- -------- -------- --------
w 06/23/15 07/03/15 06/23/15 07/03/15


I need to compare the MCDID from one table to 2 other tables that contain
multiple rows for each member due to effective and expiration date changes
and return a single row with the member information for members whose MCDID
is in the first table and who were not active for october. A member may have
a row for being active in september and a row for november but was missed in
october. I wish I could explain this better so feel free to ask questions.

Thanks in advance,
Matt


Table 1 = LostMembers1005:
MCDID text
HealthPlan text
LastName text
FirstName text
DOB date

Table 2 = tmpLostMembers1005_1:
MCDID text
EffDate text as cyymmdd
ExpDate text as cyymmdd

Table 3 = tmpLostMembers1005_2:
MCDID text
CodeEffDate text as cyymmdd
CodeExpDate text as cyymmdd

Here are the 2 queries that I've tried.
--1
SELECT DISTINCT l.MCDID, l.HealthPlan, l.CarrierCode, l.LastName,
l.FirstName, l.DOB, e.Code, e.CodeEffDate AS [Tier Code EffDate], e.[Tier
Code ExpDate] AS [Tier Code ExpDate], m.EFFDATE AS [TXEN EFFDATE], m.EXPDATE
AS [TXEN EXPDATE] INTO tmpLostMembers1005_3
FROM (LostMembers1005 AS l INNER JOIN tmpLostMembers1005_1 AS m ON m.MCDID =
l.MCDID) INNER JOIN tmpLostMembers1005_2 AS e ON e.MCDID = l.MCDID
WHERE (2051001 NOT BETWEEN m.[TXEN EFFDATE] AND m.[TXEN EXPDAT]) AND
(2051001 NOT BETWEEN e.[Tier Code EffDate] AND e.[Tier Code ExpDate]) AND
(m.[TXEN EXPDAT] < 2051031) AND (e.[Tier Code ExpDate] < 2051031)
GROUP BY l.MCDID, l.HealthPlan, l.CarrierCode, l.LastName, l.FirstName,
l.DOB, e.[Tier Code], e.[Tier Code EffDate], e.[Tier Code ExpDate], m.[TXEN
EFFDAT], m.[TXEN EXPDAT];

--2 Final query to return the results
SELECT DISTINCT l.MCDID, m.HealthPlan, m.CarrierCode, m.LastName,
m.FirstName, m.DOB, m.[Tier Code], FormatDateUS(m.[Tier Code EffDate]) AS
[Tier EffDate], FormatDateUS(m.[Tier Code ExpDate]) AS [Tier ExpDate],
FormatDateUS(m.[TXEN EFFDAT]) AS [TXEN EFFDATE], FormatDateUS(m.[TXEN
EXPDAT]) AS [TXEN EXPDATE] INTO tmpLostMembers1005_4
FROM LostMembers1005 AS l INNER JOIN tmpLostMembers1005_3 AS m ON l.MCDID =
m.MCDID
WHERE ([Tier Code EffDate] = (SELECT MAX([Tier Code EffDate]) FROM
tmpLostMembers1005_3 WHERE tmpLostMembers1005_3.MCDID = l.MCDID)) AND ([TXEN
EXPDAT] = (Select Max([TXEN EXPDAT]) FROM tmpLostMembers1005_3 WHERE
tmpLostMembers1005_3.MCDID = l.MCDID))
GROUP BY l.MCDID, m.HealthPlan, m.CarrierCode, m.LastName, m.FirstName,
m.DOB, m.[Tier Code], m.[Tier Code EffDate], m.[Tier Code ExpDate], m.[TXEN
EFFDAT], m.[TXEN EXPDAT];
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

please help with clipboard viewer 4
Can somebody help me with this code? 5

Top