Two Tables - One to Many Relationship - Query Pulls Duplicates

[Guest]

I have two tables that are linked by an account number and encounter date. In the main table, a unique record exists for every account number and encounter date. The other table contains note records and so there can be multiple records in that table that tie back to the unique record in the "main" table.

I am trying to design a query for a report which will allow me to print out "main" records with the notes listed on the print-out. I don't want the "main" records to be printed multiple times when there are multiple notes. However, that is exactly what is happening. If the account has three notes in the notes table then I end up with three occurrences of the "main" record being pulled for my report. I don't know how to go about telling the query not to repeat the "main" record just because there are multiple occurrences in the other table.

For example, suppose I have the the following account:

John Smith

And he has detail surrounding three encounter dates:

1/5/2004
2/23/2004
4/12/2004

For the 1/5/2004 encounter there are two notes in the notes table.
For the 2/23/2004 encounter there are three notes in the notes table.
For the 4/12/2004 encounter there are two notes in the notes table.

Assuming each line below is a page on my report here is what currently end up getting on my report:

John Smith --- 1/5/2004 --- Note 1 and Note 2 data
John Smith --- 1/5/2004 --- Note 1 and Note 2 data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3 data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3 data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3 data
John Smith --- 4/12/2004 --- Note 1 and Note 2 data
John Smith --- 4/12/2004 --- Note 1 and Note 2 data

Basically... a 7 page report when really all I want is a 3 page report with one page per unique encounter for John Smith. Again, assuming each line below is a page on my report here is what I want my report to produce:

John Smith --- 1/5/2004 --- Note 1 and Note 2 data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3 data
John Smith --- 4/12/2004 --- Note 1 and Note 2 data

Any suggestions on how to keep the one for many relationship between my "main" table and my notes table from creating the duplicate report pages?

Thanks,
David

 

[Marshall Barton]

I have two tables that are linked by an account number and encounter date. In the main table, a unique record exists for every account number and encounter date. The other table contains note records and so there can be multiple records in that table that tie back to the unique record in the "main" table.

I am trying to design a query for a report which will allow me to print out "main" records with the notes listed on the print-out. I don't want the "main" records to be printed multiple times when there are multiple notes. However, that is exactly what is happening. If the account has three notes in the notes table then I end up with three occurrences of the "main" record being pulled for my report. I don't know how to go about telling the query not to repeat the "main" record just because there are multiple occurrences in the other table.

For example, suppose I have the the following account:

John Smith

And he has detail surrounding three encounter dates:

1/5/2004
2/23/2004
4/12/2004

For the 1/5/2004 encounter there are two notes in the notes table.
For the 2/23/2004 encounter there are three notes in the notes table.
For the 4/12/2004 encounter there are two notes in the notes table.

Assuming each line below is a page on my report here is what currently end up getting on my report:

John Smith --- 1/5/2004 --- Note 1 and Note 2 data
John Smith --- 1/5/2004 --- Note 1 and Note 2 data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3 data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3 data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3 data
John Smith --- 4/12/2004 --- Note 1 and Note 2 data
John Smith --- 4/12/2004 --- Note 1 and Note 2 data

Basically... a 7 page report when really all I want is a 3 page report with one page per unique encounter for John Smith. Again, assuming each line below is a page on my report here is what I want my report to produce:

John Smith --- 1/5/2004 --- Note 1 and Note 2 data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3 data
John Smith --- 4/12/2004 --- Note 1 and Note 2 data

Any suggestions on how to keep the one for many relationship between my "main" table and my notes table from creating the duplicate report pages?

The problem is in the report's record source query (probably
a missing JOIN operator in the FROM clause).

If you need more help, post a Copy/Paste of the query's SQL
statement.

 

[Guest]

From what I can tell there is no SQL statement. I've never written one. I created the query in the "Design" view. Selected the two tables by using the "Show Tables" button. Then joined the two tables on the matching fields using the select ALL records option for the "main" table and include only those records from the notes table where the fields are equal. That's it. I've never used the "SQL Specific" drop down on the menu bar because I don't know anything about how to code in SQL.

If there is a statement I can use in SQL can you show me an example so that I could plug my actual table names and fields into that statement where appropriate? If it is somewhat straight-forward...

Thanks,
David N.

 

[Marshall Barton]

From what I can tell there is no SQL statement. I've never written one. I created the query in the "Design" view. Selected the two tables by using the "Show Tables" button. Then joined the two tables on the matching fields using the select ALL records option for the "main" table and include only those records from the notes table where the fields are equal. That's it. I've never used the "SQL Specific" drop down on the menu bar because I don't know anything about how to code in SQL.

If there is a statement I can use in SQL can you show me an example so that I could plug my actual table names and fields into that statement where appropriate? If it is somewhat straight-forward...

The query design window just provides a graphical interface
for creating a query, which will be translated to SQL for
you. In your case, the result should be the same so don't
worry about it. The reason everyone uses SQL view in the
newgroups is because it's really difficult to describe the
query design grid in words and it is trivially easy to
Copy/Paste the query's SQL statement.

The comment I made about the JOIN earlier translates to the
lines you used to connect the related fields in the two
tables. I sure sounds like there are no lines, but if you
have them, then something else must be going on.

Let's forget about the report for now and just run the query
by itself. Does it still show multiple records with the
same data? If it does, then Copy/Paste the query's SQL view
along with a description of the records it produces.

 

[Terry]

David,

Are you sure your end result lookjs like this:

John Smith --- 1/5/2004 --- Note 1 and Note 2 data
John Smith --- 1/5/2004 --- Note 1 and Note 2 data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3
data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3
data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3
data
John Smith --- 4/12/2004 --- Note 1 and Note 2 data
John Smith --- 4/12/2004 --- Note 1 and Note 2 data

Or does it look like this:

John Smith --- 1/5/2004 --- Note 1
John Smith --- 1/5/2004 --- Note 2 data
John Smith --- 2/23/2004 --- Note 1
John Smith --- 2/23/2004 --- Note 2
John Smith --- 2/23/2004 --- Note 3 data
John Smith --- 4/12/2004 --- Note 1
John Smith --- 4/12/2004 --- Note 2 data

The difference being that the entire report entry is being
replicated for each "Note" with it's associated "note"
only?

If this is the case you should use grouping within your
report to improve the layout. Please post back and let us
know.

Terry

-----Original Message-----
From what I can tell there is no SQL statement. I've

never written one. I created the query in the "Design"
view. Selected the two tables by using the "Show Tables"
button. Then joined the two tables on the matching fields
using the select ALL records option for the "main" table
and include only those records from the notes table where
the fields are equal. That's it. I've never used
the "SQL Specific" drop down on the menu bar because I
don't know anything about how to code in SQL.

If there is a statement I can use in SQL can you show me

an example so that I could plug my actual table names and
fields into that statement where appropriate? If it is
somewhat straight-forward...

Thanks,
David N.

and encounter date. In the main table, a unique record
exists for every account number and encounter date. The
other table contains note records and so there can be
multiple records in that table that tie back to the unique
record in the "main" table.allow me to print out "main" records with the notes listed
on the print-out. I don't want the "main" records to be
printed multiple times when there are multiple notes.
However, that is exactly what is happening. If the
account has three notes in the notes table then I end up
with three occurrences of the "main" record being pulled
for my report. I don't know how to go about telling the
query not to repeat the "main" record just because there
are multiple occurrences in the other table.a 3 page report with one page per unique encounter for
John Smith. Again, assuming each line below is a page on
my report here is what I want my report to produce:relationship between my "main" table and my notes table
from creating the duplicate report pages?

 

[Guest]

Yes... my results look like the first example. I get all of the notes showing up on the account and the account is repeated for the number of notes that existed. If there are three notes for one account than the one account ends up printed three times with all three notes on every one of those print outs.

Thanks,
David

 

[Guest]

Marshall,

Here is the SQL view...

SELECT [ATB Table].[Patient #], [ATB Table].[Record Status]
FROM [ATB Table] LEFT JOIN [Notes Table] ON ([ATB Table].[Patient #] = [Notes Table].[Patient #]) AND ([ATB Table].[Encounter Date] = [Notes Table].[Encounter Date])
WHERE ((([ATB Table].[Patient #])=[Enter Patient Number:]) AND (([ATB Table].[Record Status])="Active"));

What happens when you run the report is that the user is asked to enter the account number that they want to have printed. Then, the report pulls every encounter for the account number. The account numbers and encounter dates are also contained in each note record. Those two fields are used to link notes to the proper account numbers and on the proper encounters for those account numbers.

Let's say I run the report and type 100006550 as the account number. And, let's say that there was only one encounter dated 1/5/2004 for that account. I would expect the report to print only one occurrence of this account with whatever notes are included shown at the bottom (in the subform that exists in the detail section of the report). Let's suppose that there were three separate note records that were entered against this particular encounter.

Here is a basic example of what I am trying to get the report to print out:

[PAGE 1]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah


What I actually end up with is a three page report as follows:

[PAGE 1]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah

[PAGE 2]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah

[PAGE 3]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah


I have no idea why I get the 2 extra pages with the same data as what is shown on the first page. If there are no notes at all I get one page with the notes section blank, which is what I expected. If there is one note I get one page with the one note, which again is what I expected. If there are multiple notes then I get as many duplicate pages as there are note records, not what I expected. Five note records means getting five duplicate pages in the report, etc. etc.

Thanks for your help.

David

 

[Guest]

It appears that I figured it out...

I changed the SQL statement to the following:

SELECT DISTINCT [ATB Table].*
FROM [ATB Table] LEFT JOIN [Notes Table] ON ([ATB Table].[Patient #] = [Notes Table].[Patient #]) AND ([ATB Table].[Encounter Date] = [Notes Table].[Encounter Date])
WHERE ((([ATB Table].[Patient #])=[Enter Patient Number:]) AND (([ATB Table].[Record Status])="Active"));

The only change is in the "Select Distinct" part. Take a look at my original posting of the SQL statement to see the difference. I am not 100% sure that I understand why that worked but something made me think that my query was selecting distinct records from both tables rather than just the "main" table. Since there are multiple occurrences of the account number and encounter dates in the notes table I suppose that causes the query to make a combined record for each one and my report then shows duplicate data for every additional note that was entered against an account.

In any case, my query seems to be working now. I'm not sure that I know how I could have made this work had I not edited the SQL statement directly. I couldn't figure out a way to fix this working in the Design View.

Thanks,
David N.

Marshall,

Here is the SQL view...

SELECT [ATB Table].[Patient #], [ATB Table].[Record Status]
FROM [ATB Table] LEFT JOIN [Notes Table] ON ([ATB Table].[Patient #] = [Notes Table].[Patient #]) AND ([ATB Table].[Encounter Date] = [Notes Table].[Encounter Date])
WHERE ((([ATB Table].[Patient #])=[Enter Patient Number:]) AND (([ATB Table].[Record Status])="Active"));

What happens when you run the report is that the user is asked to enter the account number that they want to have printed. Then, the report pulls every encounter for the account number. The account numbers and encounter dates are also contained in each note record. Those two fields are used to link notes to the proper account numbers and on the proper encounters for those account numbers.

Let's say I run the report and type 100006550 as the account number. And, let's say that there was only one encounter dated 1/5/2004 for that account. I would expect the report to print only one occurrence of this account with whatever notes are included shown at the bottom (in the subform that exists in the detail section of the report). Let's suppose that there were three separate note records that were entered against this particular encounter.

Here is a basic example of what I am trying to get the report to print out:

[PAGE 1]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah


What I actually end up with is a three page report as follows:

[PAGE 1]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah

[PAGE 2]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah

[PAGE 3]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah


I have no idea why I get the 2 extra pages with the same data as what is shown on the first page. If there are no notes at all I get one page with the notes section blank, which is what I expected. If there is one note I get one page with the one note, which again is what I expected. If there are multiple notes then I get as many duplicate pages as there are note records, not what I expected. Five note records means getting five duplicate pages in the report, etc. etc.

Thanks for your help.

David

From what I can tell there is no SQL statement. I've never written one. I created the query in the "Design" view. Selected the two tables by using the "Show Tables" button. Then joined the two tables on the matching fields using the select ALL records option for the "main" table and include only those records from the notes table where the fields are equal. That's it. I've never used the "SQL Specific" drop down on the menu bar because I don't know anything about how to code in SQL.

If there is a statement I can use in SQL can you show me an example so that I could plug my actual table names and fields into that statement where appropriate? If it is somewhat straight-forward...

The query design window just provides a graphical interface
for creating a query, which will be translated to SQL for
you. In your case, the result should be the same so don't
worry about it. The reason everyone uses SQL view in the
newgroups is because it's really difficult to describe the
query design grid in words and it is trivially easy to
Copy/Paste the query's SQL statement.

The comment I made about the JOIN earlier translates to the
lines you used to connect the related fields in the two
tables. I sure sounds like there are no lines, but if you
have them, then something else must be going on.

Let's forget about the report for now and just run the query
by itself. Does it still show multiple records with the
same data? If it does, then Copy/Paste the query's SQL view
along with a description of the records it produces.
--
Marsh
MVP [MS Access]

"David Newbold" wrote:
I have two tables that are linked by an account number and encounter date. In the main table, a unique record exists for every account number and encounter date. The other table contains note records and so there can be multiple records in that table that tie back to the unique record in the "main" table.

I am trying to design a query for a report which will allow me to print out "main" records with the notes listed on the print-out. I don't want the "main" records to be printed multiple times when there are multiple notes. However, that is exactly what is happening. If the account has three notes in the notes table then I end up with three occurrences of the "main" record being pulled for my report. I don't know how to go about telling the query not to repeat the "main" record just because there are multiple occurrences in the other table.

For example, suppose I have the the following account:

John Smith

And he has detail surrounding three encounter dates:

1/5/2004
2/23/2004
4/12/2004

For the 1/5/2004 encounter there are two notes in the notes table.
For the 2/23/2004 encounter there are three notes in the notes table.
For the 4/12/2004 encounter there are two notes in the notes table.

Assuming each line below is a page on my report here is what currently end up getting on my report:

John Smith --- 1/5/2004 --- Note 1 and Note 2 data
John Smith --- 1/5/2004 --- Note 1 and Note 2 data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3 data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3 data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3 data
John Smith --- 4/12/2004 --- Note 1 and Note 2 data
John Smith --- 4/12/2004 --- Note 1 and Note 2 data

Basically... a 7 page report when really all I want is a 3 page report with one page per unique encounter for John Smith. Again, assuming each line below is a page on my report here is what I want my report to produce:

John Smith --- 1/5/2004 --- Note 1 and Note 2 data
John Smith --- 2/23/2004 --- Note 1, Note 2, and Note 3 data
John Smith --- 4/12/2004 --- Note 1 and Note 2 data

Any suggestions on how to keep the one for many relationship between my "main" table and my notes table from creating the duplicate report pages?


:
The problem is in the report's record source query (probably
a missing JOIN operator in the FROM clause).

If you need more help, post a Copy/Paste of the query's SQL
statement.

 

[Marshall Barton]

Here is the SQL view...

SELECT [ATB Table].[Patient #], [ATB Table].[Record Status]
FROM [ATB Table] LEFT JOIN [Notes Table] ON ([ATB Table].[Patient #] = [Notes Table].[Patient #]) AND ([ATB Table].[Encounter Date] = [Notes Table].[Encounter Date])
WHERE ((([ATB Table].[Patient #])=[Enter Patient Number:]) AND (([ATB Table].[Record Status])="Active"));

What happens when you run the report is that the user is asked to enter the account number that they want to have printed. Then, the report pulls every encounter for the account number. The account numbers and encounter dates are also contained in each note record. Those two fields are used to link notes to the proper account numbers and on the proper encounters for those account numbers.

Let's say I run the report and type 100006550 as the account number. And, let's say that there was only one encounter dated 1/5/2004 for that account. I would expect the report to print only one occurrence of this account with whatever notes are included shown at the bottom (in the subform that exists in the detail section of the report). Let's suppose that there were three separate note records that were entered against this particular encounter.

Here is a basic example of what I am trying to get the report to print out:

[PAGE 1]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah


What I actually end up with is a three page report as follows:

[PAGE 1]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah

[PAGE 2]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah

[PAGE 3]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah


I have no idea why I get the 2 extra pages with the same data as what is shown on the first page. If there are no notes at all I get one page with the notes section blank, which is what I expected. If there is one note I get one page with the one note, which again is what I expected. If there are multiple notes then I get as many duplicate pages as there are note records, not what I expected. Five note records means getting five duplicate pages in the report, etc. etc.

Whoa there, hold up just a minute, we are missing some
critical information here!!!

From what you've said so far, there is no way to get the
output you're seeing. First off, your query does not
include a notes field at all, so how could they show up in
the report at all, much less multiple times????????

I think I can guess what's missing, you're using a subreport
to display the notes, right? If so, that means the main
report's record source query should NOT include the notes
table. Try changing the query by removing the notes table
and see what happens.

You probably do not need to use a subreport, but maybe you
have a good reason for it??

 

[Guest]

Yes, the notes are coming into the main report by way of a sub-report. And, yes, removing the notes table from the selection did correct the problem. Turned out to be an easier fix than I was expecting.

Thanks,
David

Here is the SQL view...

SELECT [ATB Table].[Patient #], [ATB Table].[Record Status]
FROM [ATB Table] LEFT JOIN [Notes Table] ON ([ATB Table].[Patient #] = [Notes Table].[Patient #]) AND ([ATB Table].[Encounter Date] = [Notes Table].[Encounter Date])
WHERE ((([ATB Table].[Patient #])=[Enter Patient Number:]) AND (([ATB Table].[Record Status])="Active"));

What happens when you run the report is that the user is asked to enter the account number that they want to have printed. Then, the report pulls every encounter for the account number. The account numbers and encounter dates are also contained in each note record. Those two fields are used to link notes to the proper account numbers and on the proper encounters for those account numbers.

Let's say I run the report and type 100006550 as the account number. And, let's say that there was only one encounter dated 1/5/2004 for that account. I would expect the report to print only one occurrence of this account with whatever notes are included shown at the bottom (in the subform that exists in the detail section of the report). Let's suppose that there were three separate note records that were entered against this particular encounter.

Here is a basic example of what I am trying to get the report to print out:

[PAGE 1]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah


What I actually end up with is a three page report as follows:

[PAGE 1]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah

[PAGE 2]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah

[PAGE 3]

Account: 100006550
Encounter Date: 1/5/2004

Note 1 --- Blah Blah Blah
Note 2 --- Blah Blah Blah
Note 3 --- Blah Blah Blah


I have no idea why I get the 2 extra pages with the same data as what is shown on the first page. If there are no notes at all I get one page with the notes section blank, which is what I expected. If there is one note I get one page with the one note, which again is what I expected. If there are multiple notes then I get as many duplicate pages as there are note records, not what I expected. Five note records means getting five duplicate pages in the report, etc. etc.

Whoa there, hold up just a minute, we are missing some
critical information here!!!

From what you've said so far, there is no way to get the
output you're seeing. First off, your query does not
include a notes field at all, so how could they show up in
the report at all, much less multiple times????????

I think I can guess what's missing, you're using a subreport
to display the notes, right? If so, that means the main
report's record source query should NOT include the notes
table. Try changing the query by removing the notes table
and see what happens.

You probably do not need to use a subreport, but maybe you
have a good reason for it??