Need help creating query

[rinova]

Good morning everyone,
I need help with creating a query with two tables. Table A contains multiple records with the same ID number and SERVICE DATES (Some service date may not contain a date [BLANK]). Table B contains multiple records with the same ID and STATUS DATES and a STATUS FLAG that indicates if the record was eligible or ineligible for service (“E” = Eligible and “I” = Ineligible).
An Example is TABLE B has multiple records with the same ID number and if a STATUS FLAG is “E” and the STATUS DATE is 12/13/2013 and the same ID number has another STATUS FLAG and this time is “I” and the STATUS DATE is 2/1/2014 it means the record was Eligible from 12/13/2013 to 1/31/2014. Any SERVICE DATES between 12/13/2013 and 1/31/2014 are Eligible.
Another Example is TABLE B has multiple records with the same ID number and if a STATUS FLAG is “E” and the STATUS DATE is 12/13/2013 and the same ID number has another STATUS FLAG and this time it is “I” and the STATUS DATE is 2/1/2014 and then we have another STATUS FLAG and this time it is “E” and the STATUS DATE is 2/15/2014 it means the record was Eligible from 12/13/2013 to 1/31/2014 but Ineligible from 2/1/2014 to 2/14/2014 and then Eligible from 2/15/2014 to present.
There are records in Table A that may not appear in Table B, and there will be entries in Table B that don't appear in the Table A.
I would like to be able to use the SERVICE DATES found in Table A to reference the STATUS DATES and STATUS FLAG in Table B to determine is records in Table A were eligible or ineligible.
Example database is attached.

 

[namliam]

Make a new query, qryI:

SELECT TABLEB.ID, TABLEB.[STATUS DATE], TABLEB.[STATUS FLAG], TABLEB.Rate
FROM TABLEB
WHERE (((TABLEB.[STATUS FLAG])="I"));

New query, qryE:

SELECT TABLEB.ID, TABLEB.[STATUS DATE], TABLEB.[STATUS FLAG], TABLEB.Rate
FROM TABLEB
WHERE (((TABLEB.[STATUS FLAG])="E"));

New query, qryEligablePeriods:

SELECT qryE.ID, qryE.[STATUS DATE] AS EligableDate, Min(qryI.[STATUS DATE]) AS EndEligableDate
FROM qryE 
INNER JOIN qryI ON qryE.ID = qryI.ID
WHERE (((qryE.[STATUS DATE])<[qryI].[Status Date]))
GROUP BY qryE.ID, qryE.[STATUS DATE];

Your requested end result... qryTableAResults:

SELECT TABLEA.[CLIENT-NAME], TABLEA.REL, TABLEA.[SERVICE DATE], TABLEA.ID
FROM TABLEA 
INNER JOIN qryEligablePeriods ON TABLEA.ID = qryEligablePeriods.ID
WHERE (((TABLEA.[SERVICE DATE])>=[EligableDate] 
  And  (TABLEA.[SERVICE DATE])<=[EndEligableDate]));

I hope that gives what you are looking for....
Had some real trouble trying to understand exactly what you are looking for.

 

[rinova]

Thank you for the quick response Namliam,

It is amazing how you were able to create the resulting query close to what I needed but it is not working the way I wished it would. That’s my fault I was not clear on what I needed, sorry about that.

The client should appear on the qryTableAResults query when the STATUS FLAG is "E" and a single SERVICE DATE appears in TABLEB. I believe the client is not appearing on the qryTableAResults query because the client does not have an EligibleDate and EndEligibleDate when I run the qryEligablePeriods query.

Also the client should not appear on the qryTableAResults query when the clients STATUS FLAG is "I" and the STATUS DATE is the same as the SERVICE DATE.

What query changes are needed to fix this?

I'm attaching an Excel File that shows what the results of the Example database should be. I added the STATUS FLAG to the spreadsheet to show what clients are eligible and ineligible based on the data in TABLEB.

I hope this post was clearer then the last one.

Thank you,

Richard

 

[namliam]

Aha you want the last status comparing tableA to tableB

Plus I kindoff assumed that it would always be E >>> I, but I can now see its sometimes I >>>>E too....

That is something completely different, though quite easy to do... Your post didnt help much but the intended end result makes things A LOT clearer!!!

Make a query and save it as qryMostRecentDate:

SELECT TABLEA.ID, TABLEA.[SERVICE DATE], Max(TABLEB.[STATUS DATE]) AS [MaxVanSTATUS DATE]
FROM TABLEA INNER JOIN TABLEB ON TABLEA.ID = TABLEB.ID
WHERE (((TABLEA.[SERVICE DATE])>[tableB].[Status date]))
GROUP BY TABLEA.ID, TABLEA.[SERVICE DATE];

And a second query for your end result:

SELECT TABLEA.ID, TABLEA.[SERVICE DATE], TABLEA.REL, TABLEA.[CLIENT-NAME], TABLEB.[STATUS FLAG]
FROM (TABLEA 
INNER JOIN qryMostRecentDate ON (TABLEA.[SERVICE DATE] = qryMostRecentDate.[SERVICE DATE]) AND (TABLEA.ID = qryMostRecentDate.ID)) 
INNER JOIN TABLEB ON (qryMostRecentDate.[MaxVanSTATUS DATE] = TABLEB.[STATUS DATE]) AND (qryMostRecentDate.ID = TABLEB.ID);

I know your db is just a dummy thing, but I feel obliged to point you to the naming convention thread, even dummies should take this into account IMHO.
If you have no clue what I am on about read this: http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=name+convention

 

[rinova]

I'm still working on this issue namliaM. I was pulled off of it to work on another project for now. I will let you know when I'm working on this issue again.

 

[rinova]

FYI Namliam, I finished my other project and starting to work on this one again.

 

[namliam]

Good luck

 

[rinova]

Thank you finished this part of the project. Couldn't have done it without your help! Thank you!