many to many relationships

G

Guest

Is there a way to take two sets of data that have a many to many relationship
and create a one to many relationship using a query?

For example: I have1 person that has 15 Certifications that happen every 30
days and during those 30 days I have many visits coming in randomly. So I
have the one to many relationship from the person to the certification and
then the certification to the futurevisit is a many to many. Is there a way
to run an iif statement that would look at a beginning date and an ending
date, if the visit is between them would it give it say the beginning date or
else move on to continue looking through the dates in a query? Or is this
too complex?

I am using Version 2002
 
G

Guest

and then the certification to the future visit is a many to many.
I did not follow this and everything there after.

Can you post a data sample of what is in your tables?
 
G

Guest

Here is a try at a data sample:

Table 1: Episode: (field names are) MRN(Keyed field), Name,DOS,EpisodeId.
Table 2: Certification: (field names are) EpisodeID, CertificationName,
DateNeeded -Note one Episode has up to 16 certifications per person.
Table 3: FutureVisit: (field names are) MRN, Provider, FutureVisit.

My end goal is to make a report that has: a persons episode displaying a
certification and listing below it all of the dates for future visits that
correspond to just that certification. So in other words: a certification
date begins on the DOS, from then on every 30 days a new certification is
obtained and in-between a person has visits going on. Is that more clear?
Thanks for your effort!
 
G

Guest

Trying to understand. Is this the table structures and relations?
Episode ---
MRN – primary key
Name - text
DOS - datetime
EpisodeId

Certification ---
EpisodeID
MRN – foreign field related to Episode.MRN - ADDED
CertificationName
DateNeeded – datetime

-Note one Episode has up to 16 certifications per person – so you need a
one-to-many relationship between the Episode and Certification table using
the MRN field.

FutureVisit --- does this table relate directly to the Episode table?
MRN
Provider - text
FutureVisit - datetime
 
G

Guest

Karl,
your structure is correct. The Future Visit table relates to episode on the
MRN. It is a one to many relationship too. The MRN and the Episode are just
text fields.

One Episode also has many future visits tied to it by the MRN.

Does that help?
 
G

Guest

Without some data I still am not understanding what you want but here is a
stab at it.

SELECT Episode.Name, Episode.DOS, Certification.CertificationName,
Certification.DateNeeded, FutureVisit.Provider, FutureVisit.FutureVisit
FROM (Episode INNER JOIN Certification ON Episode.MRN = Certification.MRN)
INNER JOIN FutureVisit ON Episode.MRN = FutureVisit.MRN
WHERE (((FutureVisit.FutureVisit) Between [DOS] And [DOS]+30));
 
G

Guest

Here is some data:

Episode:
EpisodeID MRN DOS Name Dept
1234562 123456789 2/28/2007 Rudolph Clarence IAM-Rob
11374 32150 1/16/2007 Gym Nasium
IAM BUR

Certification:
EpisodeID WhatRect MRN DateNeeded
11374 Certification 32150 1/16/2007
11374 ReCert 1 32150 2/15/2007
11374 ReCert 10 32150 11/12/2007
11374 ReCert 11 32150 12/12/2007
11374 ReCert 12 32150 1/11/2008
11374 ReCert 13 32150 2/10/2008
11374 ReCert 14 32150 3/11/2008
11374 ReCert 15 32150 4/10/2008
11374 ReCert 16 32150 5/10/2008
11374 ReCert 2 32150 3/17/2007
11374 ReCert 3 32150 4/16/2007
11374 ReCert 4 32150 5/16/2007
11374 ReCert 5 32150 6/15/2007
11374 ReCert 6 32150 7/15/2007
11374 ReCert 7 32150 8/14/2007
11374 ReCert 8 32150 9/13/2007
11374 ReCert 9 32150 10/13/2007
1234562 Certification 123456789 2/28/2007
1234562 ReCert 1 123456789 3/30/2007
1234562 ReCert 10 123456789 12/25/2007
1234562 ReCert 11 123456789 1/24/2008
1234562 ReCert 12 123456789 2/23/2008
1234562 ReCert 13 123456789 3/24/2008
1234562 ReCert 14 123456789 4/23/2008
1234562 ReCert 15 123456789 5/23/2008
1234562 ReCert 16 123456789 6/22/2008
1234562 ReCert 2 123456789 4/29/2007
1234562 ReCert 3 123456789 5/29/2007
1234562 ReCert 4 123456789 6/28/2007
1234562 ReCert 5 123456789 7/28/2007
1234562 ReCert 6 123456789 8/27/2007
1234562 ReCert 7 123456789 9/26/2007
1234562 ReCert 8 123456789 10/26/2007

FutureVisits:
MRN FutureDOS DeptName Provider
32150 2/26/2007 IAM BUR TOM, MARILYN
32150 3/12/2007 IAM BUR TOM, MARILYN
32150 3/26/2007 IAM BUR TOM, MARILYN
32150 4/9/2007 IAM BUR TOM, MARILYN
32150 4/23/2007 IAM BUR TOM, MARILYN
32150 5/21/2007 IAM BUR TOM, MARILYN
32150 6/4/2007 IAM BUR TOM, MARILYN
32150 6/18/2007 IAM BUR TOM, MARILYN
32150 7/2/2007 IAM BUR TOM, MARILYN
32150 7/16/2007 IAM BUR TOM, MARILYN
32150 7/30/2007 IAM BUR TOM, MARILYN

123456789 3/1/2007 IAM ROB RUNNING, KELLY S
123456789 3/5/2007 IAM ROB RUNNING, KELLY S
123456789 3/9/2007 IAM ROB RUNNING, KELLY S
123456789 3/13/2007 IAM ROB RUNNING, KELLY S
123456789 3/17/2007 IAM ROB RUNNING, KELLY S
123456789 3/21/2007 IAM ROB RUNNING, KELLY S
123456789 3/25/2007 IAM ROB RUNNING, KELLY S
123456789 3/29/2007 IAM ROB RUNNING, KELLY S
123456789 4/2/2007 IAM ROB RUNNING, KELLY S
123456789 4/6/2007 IAM ROB RUNNING, KELLY S
123456789 4/10/2007 IAM ROB RUNNING, KELLY S
123456789 4/14/2007 IAM ROB RUNNING, KELLY S
123456789 4/18/2007 IAM ROB RUNNING, KELLY S
123456789 5/2/2007 IAM ROB RUNNING, KELLY S
123456789 5/15/2007 IAM ROB RUNNING, KELLY S

So in the end what I want to see is a report that looks like this:
Dept: IAM-Rob

EpisodeID MRN DOS Name
1234562 123456789 2/28/2007 Rudolph Clarence
Certification 2/28/2007
Future Visits:
3/1/2007 IAM ROB RUNNING, KELLY S
3/5/2007 IAM ROB RUNNING, KELLY S
3/9/2007 IAM ROB RUNNING, KELLY S
3/13/2007 IAM ROB RUNNING, KELLY S
3/17/2007 IAM ROB RUNNING, KELLY S
3/21/2007 IAM ROB RUNNING, KELLY S
3/25/2007 IAM ROB RUNNING, KELLY S
3/29/2007 IAM ROB RUNNING, KELLY S

ReCert 1 3/30/2007
4/2/2007 IAM ROB RUNNING, KELLY S
4/6/2007 IAM ROB RUNNING, KELLY S
4/10/2007 IAM ROB RUNNING, KELLY S
4/14/2007 IAM ROB RUNNING, KELLY S
4/18/2007 IAM ROB RUNNING, KELLY S
ReCert 2 123456789 4/29/2007
5/2/2007 IAM ROB RUNNING, KELLY S
5/15/2007 IAM ROB RUNNING, KELLY S

And then on to the next Person etc.

I think I need to use a query first to group the Futurevisits into the
Certifications.
But maybe you know of another way???

There hopefully this helps clear things up.

KARL DEWEY said:
Without some data I still am not understanding what you want but here is a
stab at it.

SELECT Episode.Name, Episode.DOS, Certification.CertificationName,
Certification.DateNeeded, FutureVisit.Provider, FutureVisit.FutureVisit
FROM (Episode INNER JOIN Certification ON Episode.MRN = Certification.MRN)
INNER JOIN FutureVisit ON Episode.MRN = FutureVisit.MRN
WHERE (((FutureVisit.FutureVisit) Between [DOS] And [DOS]+30));

--
KARL DEWEY
Build a little - Test a little


KAT said:
Karl,
your structure is correct. The Future Visit table relates to episode on the
MRN. It is a one to many relationship too. The MRN and the Episode are just
text fields.

One Episode also has many future visits tied to it by the MRN.

Does that help?
Click to expand...
Click to expand...
 
G

Guest

NOTE – you used different labels in different places for your fields.
So I may be a little mixed up in which fields I should have used but I think
you will get the drift.

Paste this SQL statement in a query and then in design view you can move the
fields around as necessary.
SELECT Episode.EpisodeId, Episode.MRN, Episode.DOS, Episode.Name,
Certification.WhatRect, Certification.DateNeeded, FutureVisit.Provider,
FutureVisit.DeptName, FutureVisit.FutureDOS
FROM (Episode INNER JOIN Certification ON Episode.MRN = Certification.MRN)
INNER JOIN FutureVisit ON Episode.MRN = FutureVisit.MRN
WHERE (((FutureVisit.FutureDOS) Between [DOS] And [DOS]+30));

Create a report using the above query as source and in design view click on
VIEW – Sorting and Grouping. In the new window select EpisodeId, WhatRect,
and FutureDOS.
Set Group Header (in grid below field selection) to YES for EpisodeId and
WhatRect.
Drag the fields into the correct sections of the report. Position the fields
and labels as you in the headers.
Open the header for WhatRect so as to position the detail labels at the
bottom of the header space.

--
KARL DEWEY
Build a little - Test a little


KAT said:
Here is some data:

Episode:
EpisodeID MRN DOS Name Dept
1234562 123456789 2/28/2007 Rudolph Clarence IAM-Rob
11374 32150 1/16/2007 Gym Nasium
IAM BUR

Certification:
EpisodeID WhatRect MRN DateNeeded
11374 Certification 32150 1/16/2007
11374 ReCert 1 32150 2/15/2007
11374 ReCert 10 32150 11/12/2007
11374 ReCert 11 32150 12/12/2007
11374 ReCert 12 32150 1/11/2008
11374 ReCert 13 32150 2/10/2008
11374 ReCert 14 32150 3/11/2008
11374 ReCert 15 32150 4/10/2008
11374 ReCert 16 32150 5/10/2008
11374 ReCert 2 32150 3/17/2007
11374 ReCert 3 32150 4/16/2007
11374 ReCert 4 32150 5/16/2007
11374 ReCert 5 32150 6/15/2007
11374 ReCert 6 32150 7/15/2007
11374 ReCert 7 32150 8/14/2007
11374 ReCert 8 32150 9/13/2007
11374 ReCert 9 32150 10/13/2007
1234562 Certification 123456789 2/28/2007
1234562 ReCert 1 123456789 3/30/2007
1234562 ReCert 10 123456789 12/25/2007
1234562 ReCert 11 123456789 1/24/2008
1234562 ReCert 12 123456789 2/23/2008
1234562 ReCert 13 123456789 3/24/2008
1234562 ReCert 14 123456789 4/23/2008
1234562 ReCert 15 123456789 5/23/2008
1234562 ReCert 16 123456789 6/22/2008
1234562 ReCert 2 123456789 4/29/2007
1234562 ReCert 3 123456789 5/29/2007
1234562 ReCert 4 123456789 6/28/2007
1234562 ReCert 5 123456789 7/28/2007
1234562 ReCert 6 123456789 8/27/2007
1234562 ReCert 7 123456789 9/26/2007
1234562 ReCert 8 123456789 10/26/2007

FutureVisits:
MRN FutureDOS DeptName Provider
32150 2/26/2007 IAM BUR TOM, MARILYN
32150 3/12/2007 IAM BUR TOM, MARILYN
32150 3/26/2007 IAM BUR TOM, MARILYN
32150 4/9/2007 IAM BUR TOM, MARILYN
32150 4/23/2007 IAM BUR TOM, MARILYN
32150 5/21/2007 IAM BUR TOM, MARILYN
32150 6/4/2007 IAM BUR TOM, MARILYN
32150 6/18/2007 IAM BUR TOM, MARILYN
32150 7/2/2007 IAM BUR TOM, MARILYN
32150 7/16/2007 IAM BUR TOM, MARILYN
32150 7/30/2007 IAM BUR TOM, MARILYN

123456789 3/1/2007 IAM ROB RUNNING, KELLY S
123456789 3/5/2007 IAM ROB RUNNING, KELLY S
123456789 3/9/2007 IAM ROB RUNNING, KELLY S
123456789 3/13/2007 IAM ROB RUNNING, KELLY S
123456789 3/17/2007 IAM ROB RUNNING, KELLY S
123456789 3/21/2007 IAM ROB RUNNING, KELLY S
123456789 3/25/2007 IAM ROB RUNNING, KELLY S
123456789 3/29/2007 IAM ROB RUNNING, KELLY S
123456789 4/2/2007 IAM ROB RUNNING, KELLY S
123456789 4/6/2007 IAM ROB RUNNING, KELLY S
123456789 4/10/2007 IAM ROB RUNNING, KELLY S
123456789 4/14/2007 IAM ROB RUNNING, KELLY S
123456789 4/18/2007 IAM ROB RUNNING, KELLY S
123456789 5/2/2007 IAM ROB RUNNING, KELLY S
123456789 5/15/2007 IAM ROB RUNNING, KELLY S

So in the end what I want to see is a report that looks like this:
Dept: IAM-Rob

EpisodeID MRN DOS Name
1234562 123456789 2/28/2007 Rudolph Clarence
Certification 2/28/2007
Future Visits:
3/1/2007 IAM ROB RUNNING, KELLY S
3/5/2007 IAM ROB RUNNING, KELLY S
3/9/2007 IAM ROB RUNNING, KELLY S
3/13/2007 IAM ROB RUNNING, KELLY S
3/17/2007 IAM ROB RUNNING, KELLY S
3/21/2007 IAM ROB RUNNING, KELLY S
3/25/2007 IAM ROB RUNNING, KELLY S
3/29/2007 IAM ROB RUNNING, KELLY S

ReCert 1 3/30/2007
4/2/2007 IAM ROB RUNNING, KELLY S
4/6/2007 IAM ROB RUNNING, KELLY S
4/10/2007 IAM ROB RUNNING, KELLY S
4/14/2007 IAM ROB RUNNING, KELLY S
4/18/2007 IAM ROB RUNNING, KELLY S
ReCert 2 123456789 4/29/2007
5/2/2007 IAM ROB RUNNING, KELLY S
5/15/2007 IAM ROB RUNNING, KELLY S

And then on to the next Person etc.

I think I need to use a query first to group the Futurevisits into the
Certifications.
But maybe you know of another way???

There hopefully this helps clear things up.

KARL DEWEY said:
Without some data I still am not understanding what you want but here is a
stab at it.

SELECT Episode.Name, Episode.DOS, Certification.CertificationName,
Certification.DateNeeded, FutureVisit.Provider, FutureVisit.FutureVisit
FROM (Episode INNER JOIN Certification ON Episode.MRN = Certification.MRN)
INNER JOIN FutureVisit ON Episode.MRN = FutureVisit.MRN
WHERE (((FutureVisit.FutureVisit) Between [DOS] And [DOS]+30));

--
KARL DEWEY
Build a little - Test a little


KAT said:
Karl,
your structure is correct. The Future Visit table relates to episode on the
MRN. It is a one to many relationship too. The MRN and the Episode are just
text fields.

One Episode also has many future visits tied to it by the MRN.

Does that help?



:

Trying to understand. Is this the table structures and relations?
Episode ---
MRN – primary key
Name - text
DOS - datetime
EpisodeId

Certification ---
EpisodeID
MRN – foreign field related to Episode.MRN - ADDED
CertificationName
DateNeeded – datetime

-Note one Episode has up to 16 certifications per person – so you need a
one-to-many relationship between the Episode and Certification table using
the MRN field.

FutureVisit --- does this table relate directly to the Episode table?
MRN
Provider - text
FutureVisit - datetime


--
KARL DEWEY
Build a little - Test a little


:

Here is a try at a data sample:

Table 1: Episode: (field names are) MRN(Keyed field), Name,DOS,EpisodeId.
Table 2: Certification: (field names are) EpisodeID, CertificationName,
DateNeeded -Note one Episode has up to 16 certifications per person.
Table 3: FutureVisit: (field names are) MRN, Provider, FutureVisit.

My end goal is to make a report that has: a persons episode displaying a
certification and listing below it all of the dates for future visits that
correspond to just that certification. So in other words: a certification
date begins on the DOS, from then on every 30 days a new certification is
obtained and in-between a person has visits going on. Is that more clear?
Thanks for your effort!

:

and then the certification to the future visit is a many to many.
I did not follow this and everything there after.

Can you post a data sample of what is in your tables?
--
KARL DEWEY
Build a little - Test a little


:

Is there a way to take two sets of data that have a many to many relationship
and create a one to many relationship using a query?

For example: I have1 person that has 15 Certifications that happen every 30
days and during those 30 days I have many visits coming in randomly. So I
have the one to many relationship from the person to the certification and
then the certification to the futurevisit is a many to many. Is there a way
to run an iif statement that would look at a beginning date and an ending
date, if the visit is between them would it give it say the beginning date or
else move on to continue looking through the dates in a query? Or is this
too complex?

I am using Version 2002
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It all looks good except when I do the Between [DOS] and [DOS]+30 it just
takes the first date of each persons futuredate and repeats it for each
certification. So rather than having each of the actual dates it has 2/26/07
for Certification, Recert 1, Recert 2, etc.

Karl I have to say I will be leaving out of town for 1 week starting early
am tomorrow. I will look for any other wisdom you can send my way - please.





KARL DEWEY said:
NOTE – you used different labels in different places for your fields.
So I may be a little mixed up in which fields I should have used but I think
you will get the drift.

Paste this SQL statement in a query and then in design view you can move the
fields around as necessary.
SELECT Episode.EpisodeId, Episode.MRN, Episode.DOS, Episode.Name,
Certification.WhatRect, Certification.DateNeeded, FutureVisit.Provider,
FutureVisit.DeptName, FutureVisit.FutureDOS
FROM (Episode INNER JOIN Certification ON Episode.MRN = Certification.MRN)
INNER JOIN FutureVisit ON Episode.MRN = FutureVisit.MRN
WHERE (((FutureVisit.FutureDOS) Between [DOS] And [DOS]+30));

Create a report using the above query as source and in design view click on
VIEW – Sorting and Grouping. In the new window select EpisodeId, WhatRect,
and FutureDOS.
Set Group Header (in grid below field selection) to YES for EpisodeId and
WhatRect.
Drag the fields into the correct sections of the report. Position the fields
and labels as you in the headers.
Open the header for WhatRect so as to position the detail labels at the
bottom of the header space.

--
KARL DEWEY
Build a little - Test a little


KAT said:
Here is some data:

Episode:
EpisodeID MRN DOS Name Dept
1234562 123456789 2/28/2007 Rudolph Clarence IAM-Rob
11374 32150 1/16/2007 Gym Nasium
IAM BUR

Certification:
EpisodeID WhatRect MRN DateNeeded
11374 Certification 32150 1/16/2007
11374 ReCert 1 32150 2/15/2007
11374 ReCert 10 32150 11/12/2007
11374 ReCert 11 32150 12/12/2007
11374 ReCert 12 32150 1/11/2008
11374 ReCert 13 32150 2/10/2008
11374 ReCert 14 32150 3/11/2008
11374 ReCert 15 32150 4/10/2008
11374 ReCert 16 32150 5/10/2008
11374 ReCert 2 32150 3/17/2007
11374 ReCert 3 32150 4/16/2007
11374 ReCert 4 32150 5/16/2007
11374 ReCert 5 32150 6/15/2007
11374 ReCert 6 32150 7/15/2007
11374 ReCert 7 32150 8/14/2007
11374 ReCert 8 32150 9/13/2007
11374 ReCert 9 32150 10/13/2007
1234562 Certification 123456789 2/28/2007
1234562 ReCert 1 123456789 3/30/2007
1234562 ReCert 10 123456789 12/25/2007
1234562 ReCert 11 123456789 1/24/2008
1234562 ReCert 12 123456789 2/23/2008
1234562 ReCert 13 123456789 3/24/2008
1234562 ReCert 14 123456789 4/23/2008
1234562 ReCert 15 123456789 5/23/2008
1234562 ReCert 16 123456789 6/22/2008
1234562 ReCert 2 123456789 4/29/2007
1234562 ReCert 3 123456789 5/29/2007
1234562 ReCert 4 123456789 6/28/2007
1234562 ReCert 5 123456789 7/28/2007
1234562 ReCert 6 123456789 8/27/2007
1234562 ReCert 7 123456789 9/26/2007
1234562 ReCert 8 123456789 10/26/2007

FutureVisits:
MRN FutureDOS DeptName Provider
32150 2/26/2007 IAM BUR TOM, MARILYN
32150 3/12/2007 IAM BUR TOM, MARILYN
32150 3/26/2007 IAM BUR TOM, MARILYN
32150 4/9/2007 IAM BUR TOM, MARILYN
32150 4/23/2007 IAM BUR TOM, MARILYN
32150 5/21/2007 IAM BUR TOM, MARILYN
32150 6/4/2007 IAM BUR TOM, MARILYN
32150 6/18/2007 IAM BUR TOM, MARILYN
32150 7/2/2007 IAM BUR TOM, MARILYN
32150 7/16/2007 IAM BUR TOM, MARILYN
32150 7/30/2007 IAM BUR TOM, MARILYN

123456789 3/1/2007 IAM ROB RUNNING, KELLY S
123456789 3/5/2007 IAM ROB RUNNING, KELLY S
123456789 3/9/2007 IAM ROB RUNNING, KELLY S
123456789 3/13/2007 IAM ROB RUNNING, KELLY S
123456789 3/17/2007 IAM ROB RUNNING, KELLY S
123456789 3/21/2007 IAM ROB RUNNING, KELLY S
123456789 3/25/2007 IAM ROB RUNNING, KELLY S
123456789 3/29/2007 IAM ROB RUNNING, KELLY S
123456789 4/2/2007 IAM ROB RUNNING, KELLY S
123456789 4/6/2007 IAM ROB RUNNING, KELLY S
123456789 4/10/2007 IAM ROB RUNNING, KELLY S
123456789 4/14/2007 IAM ROB RUNNING, KELLY S
123456789 4/18/2007 IAM ROB RUNNING, KELLY S
123456789 5/2/2007 IAM ROB RUNNING, KELLY S
123456789 5/15/2007 IAM ROB RUNNING, KELLY S

So in the end what I want to see is a report that looks like this:
Dept: IAM-Rob

EpisodeID MRN DOS Name
1234562 123456789 2/28/2007 Rudolph Clarence
Certification 2/28/2007
Future Visits:
3/1/2007 IAM ROB RUNNING, KELLY S
3/5/2007 IAM ROB RUNNING, KELLY S
3/9/2007 IAM ROB RUNNING, KELLY S
3/13/2007 IAM ROB RUNNING, KELLY S
3/17/2007 IAM ROB RUNNING, KELLY S
3/21/2007 IAM ROB RUNNING, KELLY S
3/25/2007 IAM ROB RUNNING, KELLY S
3/29/2007 IAM ROB RUNNING, KELLY S

ReCert 1 3/30/2007
4/2/2007 IAM ROB RUNNING, KELLY S
4/6/2007 IAM ROB RUNNING, KELLY S
4/10/2007 IAM ROB RUNNING, KELLY S
4/14/2007 IAM ROB RUNNING, KELLY S
4/18/2007 IAM ROB RUNNING, KELLY S
ReCert 2 123456789 4/29/2007
5/2/2007 IAM ROB RUNNING, KELLY S
5/15/2007 IAM ROB RUNNING, KELLY S

And then on to the next Person etc.

I think I need to use a query first to group the Futurevisits into the
Certifications.
But maybe you know of another way???

There hopefully this helps clear things up.

KARL DEWEY said:
Without some data I still am not understanding what you want but here is a
stab at it.

SELECT Episode.Name, Episode.DOS, Certification.CertificationName,
Certification.DateNeeded, FutureVisit.Provider, FutureVisit.FutureVisit
FROM (Episode INNER JOIN Certification ON Episode.MRN = Certification.MRN)
INNER JOIN FutureVisit ON Episode.MRN = FutureVisit.MRN
WHERE (((FutureVisit.FutureVisit) Between [DOS] And [DOS]+30));

--
KARL DEWEY
Build a little - Test a little


:

Karl,
your structure is correct. The Future Visit table relates to episode on the
MRN. It is a one to many relationship too. The MRN and the Episode are just
text fields.

One Episode also has many future visits tied to it by the MRN.

Does that help?



:

Trying to understand. Is this the table structures and relations?
Episode ---
MRN – primary key
Name - text
DOS - datetime
EpisodeId

Certification ---
EpisodeID
MRN – foreign field related to Episode.MRN - ADDED
CertificationName
DateNeeded – datetime

-Note one Episode has up to 16 certifications per person – so you need a
one-to-many relationship between the Episode and Certification table using
the MRN field.

FutureVisit --- does this table relate directly to the Episode table?
MRN
Provider - text
FutureVisit - datetime


--
KARL DEWEY
Build a little - Test a little


:

Here is a try at a data sample:

Table 1: Episode: (field names are) MRN(Keyed field), Name,DOS,EpisodeId.
Table 2: Certification: (field names are) EpisodeID, CertificationName,
DateNeeded -Note one Episode has up to 16 certifications per person.
Table 3: FutureVisit: (field names are) MRN, Provider, FutureVisit.

My end goal is to make a report that has: a persons episode displaying a
certification and listing below it all of the dates for future visits that
correspond to just that certification. So in other words: a certification
date begins on the DOS, from then on every 30 days a new certification is
obtained and in-between a person has visits going on. Is that more clear?
Thanks for your effort!

:

and then the certification to the future visit is a many to many.
I did not follow this and everything there after.

Can you post a data sample of what is in your tables?
--
KARL DEWEY
Build a little - Test a little


:

Is there a way to take two sets of data that have a many to many relationship
and create a one to many relationship using a query?

For example: I have1 person that has 15 Certifications that happen every 30
days and during those 30 days I have many visits coming in randomly. So I
have the one to many relationship from the person to the certification and
then the certification to the futurevisit is a many to many. Is there a way
to run an iif statement that would look at a beginning date and an ending
date, if the visit is between them would it give it say the beginning date or
else move on to continue looking through the dates in a query? Or is this
too complex?

I am using Version 2002
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Oh my gosh, you are incredible Karl! I was just messing with what date I
was using and it works! Amazing! Magnificent work Karl!

Thank you so very much!!!!!!!



KAT said:
It all looks good except when I do the Between [DOS] and [DOS]+30 it just
takes the first date of each persons futuredate and repeats it for each
certification. So rather than having each of the actual dates it has 2/26/07
for Certification, Recert 1, Recert 2, etc.

Karl I have to say I will be leaving out of town for 1 week starting early
am tomorrow. I will look for any other wisdom you can send my way - please.





KARL DEWEY said:
NOTE – you used different labels in different places for your fields.
So I may be a little mixed up in which fields I should have used but I think
you will get the drift.

Paste this SQL statement in a query and then in design view you can move the
fields around as necessary.
SELECT Episode.EpisodeId, Episode.MRN, Episode.DOS, Episode.Name,
Certification.WhatRect, Certification.DateNeeded, FutureVisit.Provider,
FutureVisit.DeptName, FutureVisit.FutureDOS
FROM (Episode INNER JOIN Certification ON Episode.MRN = Certification.MRN)
INNER JOIN FutureVisit ON Episode.MRN = FutureVisit.MRN
WHERE (((FutureVisit.FutureDOS) Between [DOS] And [DOS]+30));

Create a report using the above query as source and in design view click on
VIEW – Sorting and Grouping. In the new window select EpisodeId, WhatRect,
and FutureDOS.
Set Group Header (in grid below field selection) to YES for EpisodeId and
WhatRect.
Drag the fields into the correct sections of the report. Position the fields
and labels as you in the headers.
Open the header for WhatRect so as to position the detail labels at the
bottom of the header space.

--
KARL DEWEY
Build a little - Test a little


KAT said:
Here is some data:

Episode:
EpisodeID MRN DOS Name Dept
1234562 123456789 2/28/2007 Rudolph Clarence IAM-Rob
11374 32150 1/16/2007 Gym Nasium
IAM BUR

Certification:
EpisodeID WhatRect MRN DateNeeded
11374 Certification 32150 1/16/2007
11374 ReCert 1 32150 2/15/2007
11374 ReCert 10 32150 11/12/2007
11374 ReCert 11 32150 12/12/2007
11374 ReCert 12 32150 1/11/2008
11374 ReCert 13 32150 2/10/2008
11374 ReCert 14 32150 3/11/2008
11374 ReCert 15 32150 4/10/2008
11374 ReCert 16 32150 5/10/2008
11374 ReCert 2 32150 3/17/2007
11374 ReCert 3 32150 4/16/2007
11374 ReCert 4 32150 5/16/2007
11374 ReCert 5 32150 6/15/2007
11374 ReCert 6 32150 7/15/2007
11374 ReCert 7 32150 8/14/2007
11374 ReCert 8 32150 9/13/2007
11374 ReCert 9 32150 10/13/2007
1234562 Certification 123456789 2/28/2007
1234562 ReCert 1 123456789 3/30/2007
1234562 ReCert 10 123456789 12/25/2007
1234562 ReCert 11 123456789 1/24/2008
1234562 ReCert 12 123456789 2/23/2008
1234562 ReCert 13 123456789 3/24/2008
1234562 ReCert 14 123456789 4/23/2008
1234562 ReCert 15 123456789 5/23/2008
1234562 ReCert 16 123456789 6/22/2008
1234562 ReCert 2 123456789 4/29/2007
1234562 ReCert 3 123456789 5/29/2007
1234562 ReCert 4 123456789 6/28/2007
1234562 ReCert 5 123456789 7/28/2007
1234562 ReCert 6 123456789 8/27/2007
1234562 ReCert 7 123456789 9/26/2007
1234562 ReCert 8 123456789 10/26/2007

FutureVisits:
MRN FutureDOS DeptName Provider
32150 2/26/2007 IAM BUR TOM, MARILYN
32150 3/12/2007 IAM BUR TOM, MARILYN
32150 3/26/2007 IAM BUR TOM, MARILYN
32150 4/9/2007 IAM BUR TOM, MARILYN
32150 4/23/2007 IAM BUR TOM, MARILYN
32150 5/21/2007 IAM BUR TOM, MARILYN
32150 6/4/2007 IAM BUR TOM, MARILYN
32150 6/18/2007 IAM BUR TOM, MARILYN
32150 7/2/2007 IAM BUR TOM, MARILYN
32150 7/16/2007 IAM BUR TOM, MARILYN
32150 7/30/2007 IAM BUR TOM, MARILYN

123456789 3/1/2007 IAM ROB RUNNING, KELLY S
123456789 3/5/2007 IAM ROB RUNNING, KELLY S
123456789 3/9/2007 IAM ROB RUNNING, KELLY S
123456789 3/13/2007 IAM ROB RUNNING, KELLY S
123456789 3/17/2007 IAM ROB RUNNING, KELLY S
123456789 3/21/2007 IAM ROB RUNNING, KELLY S
123456789 3/25/2007 IAM ROB RUNNING, KELLY S
123456789 3/29/2007 IAM ROB RUNNING, KELLY S
123456789 4/2/2007 IAM ROB RUNNING, KELLY S
123456789 4/6/2007 IAM ROB RUNNING, KELLY S
123456789 4/10/2007 IAM ROB RUNNING, KELLY S
123456789 4/14/2007 IAM ROB RUNNING, KELLY S
123456789 4/18/2007 IAM ROB RUNNING, KELLY S
123456789 5/2/2007 IAM ROB RUNNING, KELLY S
123456789 5/15/2007 IAM ROB RUNNING, KELLY S

So in the end what I want to see is a report that looks like this:
Dept: IAM-Rob

EpisodeID MRN DOS Name
1234562 123456789 2/28/2007 Rudolph Clarence
Certification 2/28/2007
Future Visits:
3/1/2007 IAM ROB RUNNING, KELLY S
3/5/2007 IAM ROB RUNNING, KELLY S
3/9/2007 IAM ROB RUNNING, KELLY S
3/13/2007 IAM ROB RUNNING, KELLY S
3/17/2007 IAM ROB RUNNING, KELLY S
3/21/2007 IAM ROB RUNNING, KELLY S
3/25/2007 IAM ROB RUNNING, KELLY S
3/29/2007 IAM ROB RUNNING, KELLY S

ReCert 1 3/30/2007
4/2/2007 IAM ROB RUNNING, KELLY S
4/6/2007 IAM ROB RUNNING, KELLY S
4/10/2007 IAM ROB RUNNING, KELLY S
4/14/2007 IAM ROB RUNNING, KELLY S
4/18/2007 IAM ROB RUNNING, KELLY S
ReCert 2 123456789 4/29/2007
5/2/2007 IAM ROB RUNNING, KELLY S
5/15/2007 IAM ROB RUNNING, KELLY S

And then on to the next Person etc.

I think I need to use a query first to group the Futurevisits into the
Certifications.
But maybe you know of another way???

There hopefully this helps clear things up.

:

Without some data I still am not understanding what you want but here is a
stab at it.

SELECT Episode.Name, Episode.DOS, Certification.CertificationName,
Certification.DateNeeded, FutureVisit.Provider, FutureVisit.FutureVisit
FROM (Episode INNER JOIN Certification ON Episode.MRN = Certification.MRN)
INNER JOIN FutureVisit ON Episode.MRN = FutureVisit.MRN
WHERE (((FutureVisit.FutureVisit) Between [DOS] And [DOS]+30));

--
KARL DEWEY
Build a little - Test a little


:

Karl,
your structure is correct. The Future Visit table relates to episode on the
MRN. It is a one to many relationship too. The MRN and the Episode are just
text fields.

One Episode also has many future visits tied to it by the MRN.

Does that help?



:

Trying to understand. Is this the table structures and relations?
Episode ---
MRN – primary key
Name - text
DOS - datetime
EpisodeId

Certification ---
EpisodeID
MRN – foreign field related to Episode.MRN - ADDED
CertificationName
DateNeeded – datetime

-Note one Episode has up to 16 certifications per person – so you need a
one-to-many relationship between the Episode and Certification table using
the MRN field.

FutureVisit --- does this table relate directly to the Episode table?
MRN
Provider - text
FutureVisit - datetime


--
KARL DEWEY
Build a little - Test a little


:

Here is a try at a data sample:

Table 1: Episode: (field names are) MRN(Keyed field), Name,DOS,EpisodeId.
Table 2: Certification: (field names are) EpisodeID, CertificationName,
DateNeeded -Note one Episode has up to 16 certifications per person.
Table 3: FutureVisit: (field names are) MRN, Provider, FutureVisit.

My end goal is to make a report that has: a persons episode displaying a
certification and listing below it all of the dates for future visits that
correspond to just that certification. So in other words: a certification
date begins on the DOS, from then on every 30 days a new certification is
obtained and in-between a person has visits going on. Is that more clear?
Thanks for your effort!

:

and then the certification to the future visit is a many to many.
I did not follow this and everything there after.

Can you post a data sample of what is in your tables?
--
KARL DEWEY
Build a little - Test a little


:

Is there a way to take two sets of data that have a many to many relationship
and create a one to many relationship using a query?

For example: I have1 person that has 15 Certifications that happen every 30
days and during those 30 days I have many visits coming in randomly. So I
have the one to many relationship from the person to the certification and
then the certification to the futurevisit is a many to many. Is there a way
to run an iif statement that would look at a beginning date and an ending
date, if the visit is between them would it give it say the beginning date or
else move on to continue looking through the dates in a query? Or is this
too complex?

I am using Version 2002
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

Access Indeterminate relationship in many-to-many table (Windows 10, Access 2016) 5
many to many relationship with dates 1
multiple one to many 2
Query Help One-to-Many 4
Multiple Many-to-Many relationships 8
Limit relationships for a field 2
Return all records many to many 1
Group By, Max, many to many 1

Top