A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Report prints data twice on two different pages



 
 
Thread Tools Display Modes
  #21  
Old October 3rd, 2008, 06:21 PM posted to microsoft.public.access.reports
Xfree
external usenet poster
 
Posts: 46
Default Report prints data twice on two different pages

Duane,
I believe I follow you on this but do you have a suggestion on a way to
achieve a report design that will show the main form information and the
subtable information?

"Duane Hookom" wrote:

Your main report should not have a level of detail that is greater than the
level of the Link Master/Child properties unless the subreport is located in
a group header or footer.

For instance if you have a table of Employees and a table of employee's
children, your link master/child would be the employeeID. The main report
would NOT have employee children in its Record Source. I assume your
CertOrderSub table is similar to employee children table and shouldn't be in
the main report.

--
Duane Hookom
Microsoft Access MVP


"Xfree" wrote:

It is Nessa's unless you know of a different way to do it. The goal of the
final report is to produce a material cert that shows the parts that go with
the cert number along with the purchase order issued for those parts.

I have the sub reports made and have them in the main report now but the
issue still remains.



"Duane Hookom" wrote:

I'm not sure why you have the CertOrderSub table in your main report's record
source. Is this table needed in the main report?
--
Duane Hookom
Microsoft Access MVP


"Xfree" wrote:

Duane,
I hope this is what your talking about. Each time I have tried to change
things in the Select statement it makes one or both of the subs not show data
or the whole sub does not show up.

I will begin to make sub-reports to replace the sub-forms now but what
record source do I use? the sub-form or the main form?


SQL record source from Part Certification Report:

SELECT [PartCertification].[PartCertificationID] AS
PartCertification_PartCertificationID, [PartCertSub].[PartCertSub] AS
PartCertSub_PartCertSub, [PartCertSub].[PartCertificationID] AS
PartCertSub_PartCertificationID, [PartCertSub].[ProductID],
[PartCertSub].[PourScheduleID] AS PartCertSub_PourScheduleID,
[PartCertSub].[Pour Wt], [PartCertSub].[NumberOfParts],
[CertMaterailSub].[CertMaterialID], [CertMaterailSub].[PourScheduleID] AS
CertMaterailSub_PourScheduleID, [CertMaterailSub].[PartCertificationID] AS
CertMaterailSub_PartCertificationID, [CertOrderSub].[CertOrderSubID],
[CertOrderSub].[OrderID] AS CertOrderSub_OrderID,
[CertOrderSub].[OrderDetailID], [CertOrderSub].[PartCertificationID] AS
CertOrderSub_PartCertificationID, [PartCertification].[PartCertSub] AS
PartCertification_PartCertSub, [PartCertification].[OrderID] AS
PartCertification_OrderID, [PartCertification].[CertmaterialSubID],
[PartCertification].[PourScheduleID] AS PartCertification_PourScheduleID,
[PartCertification].[PartCertDate], [PartCertification].[Number Certified],
[PartCertification].[Part Number], [PartCertification].[Part Cert Entered
by], [PartCertification].[Part Cert Notes], [PartCertification].[Material],
[PartCertification].[Specification], CertMaterailSub.PourScheduleID,
PourSchedule.[Master Heat Number], PourSchedule.HeatNo,
PourSchedule.PourDate, Products.[Part Number] AS [Part Number_Products],
PartCertification.PartCertificationID FROM Products INNER JOIN (PourSchedule
INNER JOIN (((PartCertification INNER JOIN CertOrderSub ON
PartCertification.PartCertificationID=CertOrderSub .PartCertificationID) INNER
JOIN PartCertSub ON
PartCertification.PartCertificationID=PartCertSub. PartCertificationID) INNER
JOIN CertMaterailSub ON
PartCertification.PartCertificationID=CertMaterail Sub.PartCertificationID) ON
PourSchedule.PourScheduleID=CertMaterailSub.PourSc heduleID) ON
Products.ProductID=PartCertSub.ProductID;


Record Source from Cert Order Sub:
SELECT CertOrderSub.*, Orders.CustomerID, Orders.OrderDate FROM Orders INNER
JOIN CertOrderSub ON Orders.OrderID=CertOrderSub.OrderID;


Record source for Part Cert Sub is PartCertSub appears to be no SQL but
within that sub I am selecting the part number from the ProductID field:
SELECT Products.[Part Number] FROM Products ORDER BY Products.[Part Number];



Thank you for your help on this.

Mark Pierce





"Duane Hookom" wrote:

Again, I would convert the forms to reports and get rid of the code in the
reports.

I wasn't asking for code. I had expected a reply back with the SQL view of
your reports' Record Sources. This would be something like:
SELECT ... FROM...
--
Duane Hookom
Microsoft Access MVP


"Xfree" wrote:

The subs are in the detail section of the report.
I can only see code for the main form, the subs come up blank when I try to
look at code.


SQL for Part Cert Form:

Private Sub Print_Cert_Click()

Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[PartCertificationID] = " & Me.[PartCertificationID]
DoCmd.OpenReport "PartCertification", acViewPreview, , strWhere
End If

End Sub

Private Sub Delete_Heat_Click()
On Error GoTo Err_Delete_Heat_Click


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

Exit_Delete_Heat_Click:
Exit Sub

Err_Delete_Heat_Click:
MsgBox Err.Description
Resume Exit_Delete_Heat_Click

End Sub

Private Sub Command36_Click()
On Error GoTo Err_Command36_Click


Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdFind

Exit_Command36_Click:
Exit Sub

Err_Command36_Click:
MsgBox Err.Description
Resume Exit_Command36_Click

End Sub
Private Sub Print_Cert1_Click()
On Error GoTo Err_Print_Cert1_Click


DoCmd.RunCommand acCmdSelectRecord
DoCmd.PrintOut acSelection

Exit_Print_Cert1_Click:
Exit Sub

Err_Print_Cert1_Click:
MsgBox Err.Description
Resume Exit_Print_Cert1_Click

End Sub








"Duane Hookom" wrote:

I would not print subforms. Take the time to save the subforms as reports and
then use the sub-reports.

Maybe you could provide the actual SQL of the main and sub-reports. What
sections are the sub-reports located in?
--
Duane Hookom
Microsoft Access MVP


"Xfree" wrote:

Yes the setup below is for the main input form "Part Certification" with the
two sub forms. The report is setup just as the main form is but the two Sub
forms are pulled into the form so I didn't make sub reports and then pull
them into the main report, so they are not sub-reports. The record sources in
the report for each Sub is the sub table its self.


"Duane Hookom" wrote:

Can we assume you actually are referencing sub-reports rather than sub-forms?
Is the main report bound only to the Part Certification table and no table
that is detailed in the sub-report record source?

--
Duane Hookom
Microsoft Access MVP


"Xfree" wrote:

I am still unable to resolve this issue here is what I have:

Main form table
Part Certification with the key field of PartCertificationID with a one to
many join to both subtables

Sub form 1 is
PartCertSub with the key field of PartCertSub and the foreign key that joins
the two tables is PartCertificationID
Within this table also it is joined to the Products table by the ProductID
field (many to one)
The Link Master Fields is PartCerificationID and Link Child is
PartCertificationID

Sub form 2 is
CertMaterialSub with the key field of CertMaterialID and the foreign key to
PartCertification table is PartCertificationID
This table also joins with the PourSchedule table (many to one) by way of
the PourScheduleID foreign key.
The Link Master Fields is PartCerificationID and Link Child is
PartCertificationID

The issue starts when I add more than one record in either sub form one or
two. If there is only one record in each of the two sub reports
it prints correctly.


"Duane Hookom" wrote:

Perhaps you should share the record sources of your main report and the 1
sub-report giving the problems as well as the Link Master/Child properties.

--
Duane Hookom
Microsoft Access MVP


"Xfree" wrote:

I tried to remove tables but it kills the report. Now this report has 3 sub
reports in it 2 out of three of the sub reports prints correctly i.e. only
once, just the one sub report is causing me issues of repeating its self on
page 2 of what should be a 1 page report.

"John Spencer" wrote:

Mor Likely you need to remove a table from the query rather than fields.

Of course you can use
SELECT DISTINCT ...

and remove all the fields from the tables that are being used in the sub-report.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Xfree wrote:
Daune,
I am still battling this issue, I have removed fields but I am still
getting the sub-report section printing twice.

Any other ideas?


"Duane Hookom" wrote:

You are somewhat correct. It's more like you have a detail table in your main
report's record source that should not be included.

--
Duane Hookom
Microsoft Access MVP

  #22  
Old October 3rd, 2008, 07:07 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Report prints data twice on two different pages

This is generally how main and subreports are set up. The main report has a
record source based on the table and the subreport has more detailed based on
the related/child table. The main report might need to display aggregated
child information but certainly not detailed child records.

Keep the details out of the main report and limit them to only the subreport.

--
Duane Hookom
Microsoft Access MVP


"Xfree" wrote:

Duane,
I believe I follow you on this but do you have a suggestion on a way to
achieve a report design that will show the main form information and the
subtable information?

"Duane Hookom" wrote:

Your main report should not have a level of detail that is greater than the
level of the Link Master/Child properties unless the subreport is located in
a group header or footer.

For instance if you have a table of Employees and a table of employee's
children, your link master/child would be the employeeID. The main report
would NOT have employee children in its Record Source. I assume your
CertOrderSub table is similar to employee children table and shouldn't be in
the main report.

--
Duane Hookom
Microsoft Access MVP


"Xfree" wrote:

It is Nessa's unless you know of a different way to do it. The goal of the
final report is to produce a material cert that shows the parts that go with
the cert number along with the purchase order issued for those parts.

I have the sub reports made and have them in the main report now but the
issue still remains.



"Duane Hookom" wrote:

I'm not sure why you have the CertOrderSub table in your main report's record
source. Is this table needed in the main report?
--
Duane Hookom
Microsoft Access MVP


"Xfree" wrote:

Duane,
I hope this is what your talking about. Each time I have tried to change
things in the Select statement it makes one or both of the subs not show data
or the whole sub does not show up.

I will begin to make sub-reports to replace the sub-forms now but what
record source do I use? the sub-form or the main form?


SQL record source from Part Certification Report:

SELECT [PartCertification].[PartCertificationID] AS
PartCertification_PartCertificationID, [PartCertSub].[PartCertSub] AS
PartCertSub_PartCertSub, [PartCertSub].[PartCertificationID] AS
PartCertSub_PartCertificationID, [PartCertSub].[ProductID],
[PartCertSub].[PourScheduleID] AS PartCertSub_PourScheduleID,
[PartCertSub].[Pour Wt], [PartCertSub].[NumberOfParts],
[CertMaterailSub].[CertMaterialID], [CertMaterailSub].[PourScheduleID] AS
CertMaterailSub_PourScheduleID, [CertMaterailSub].[PartCertificationID] AS
CertMaterailSub_PartCertificationID, [CertOrderSub].[CertOrderSubID],
[CertOrderSub].[OrderID] AS CertOrderSub_OrderID,
[CertOrderSub].[OrderDetailID], [CertOrderSub].[PartCertificationID] AS
CertOrderSub_PartCertificationID, [PartCertification].[PartCertSub] AS
PartCertification_PartCertSub, [PartCertification].[OrderID] AS
PartCertification_OrderID, [PartCertification].[CertmaterialSubID],
[PartCertification].[PourScheduleID] AS PartCertification_PourScheduleID,
[PartCertification].[PartCertDate], [PartCertification].[Number Certified],
[PartCertification].[Part Number], [PartCertification].[Part Cert Entered
by], [PartCertification].[Part Cert Notes], [PartCertification].[Material],
[PartCertification].[Specification], CertMaterailSub.PourScheduleID,
PourSchedule.[Master Heat Number], PourSchedule.HeatNo,
PourSchedule.PourDate, Products.[Part Number] AS [Part Number_Products],
PartCertification.PartCertificationID FROM Products INNER JOIN (PourSchedule
INNER JOIN (((PartCertification INNER JOIN CertOrderSub ON
PartCertification.PartCertificationID=CertOrderSub .PartCertificationID) INNER
JOIN PartCertSub ON
PartCertification.PartCertificationID=PartCertSub. PartCertificationID) INNER
JOIN CertMaterailSub ON
PartCertification.PartCertificationID=CertMaterail Sub.PartCertificationID) ON
PourSchedule.PourScheduleID=CertMaterailSub.PourSc heduleID) ON
Products.ProductID=PartCertSub.ProductID;


Record Source from Cert Order Sub:
SELECT CertOrderSub.*, Orders.CustomerID, Orders.OrderDate FROM Orders INNER
JOIN CertOrderSub ON Orders.OrderID=CertOrderSub.OrderID;


Record source for Part Cert Sub is PartCertSub appears to be no SQL but
within that sub I am selecting the part number from the ProductID field:
SELECT Products.[Part Number] FROM Products ORDER BY Products.[Part Number];



Thank you for your help on this.

Mark Pierce





"Duane Hookom" wrote:

Again, I would convert the forms to reports and get rid of the code in the
reports.

I wasn't asking for code. I had expected a reply back with the SQL view of
your reports' Record Sources. This would be something like:
SELECT ... FROM...
--
Duane Hookom
Microsoft Access MVP


"Xfree" wrote:

The subs are in the detail section of the report.
I can only see code for the main form, the subs come up blank when I try to
look at code.


SQL for Part Cert Form:

Private Sub Print_Cert_Click()

Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[PartCertificationID] = " & Me.[PartCertificationID]
DoCmd.OpenReport "PartCertification", acViewPreview, , strWhere
End If

End Sub

Private Sub Delete_Heat_Click()
On Error GoTo Err_Delete_Heat_Click


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

Exit_Delete_Heat_Click:
Exit Sub

Err_Delete_Heat_Click:
MsgBox Err.Description
Resume Exit_Delete_Heat_Click

End Sub

Private Sub Command36_Click()
On Error GoTo Err_Command36_Click


Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdFind

Exit_Command36_Click:
Exit Sub

Err_Command36_Click:
MsgBox Err.Description
Resume Exit_Command36_Click

End Sub
Private Sub Print_Cert1_Click()
On Error GoTo Err_Print_Cert1_Click


DoCmd.RunCommand acCmdSelectRecord
DoCmd.PrintOut acSelection

Exit_Print_Cert1_Click:
Exit Sub

Err_Print_Cert1_Click:
MsgBox Err.Description
Resume Exit_Print_Cert1_Click

End Sub








"Duane Hookom" wrote:

I would not print subforms. Take the time to save the subforms as reports and
then use the sub-reports.

Maybe you could provide the actual SQL of the main and sub-reports. What
sections are the sub-reports located in?
--
Duane Hookom
Microsoft Access MVP


"Xfree" wrote:

Yes the setup below is for the main input form "Part Certification" with the
two sub forms. The report is setup just as the main form is but the two Sub
forms are pulled into the form so I didn't make sub reports and then pull
them into the main report, so they are not sub-reports. The record sources in
the report for each Sub is the sub table its self.


"Duane Hookom" wrote:

Can we assume you actually are referencing sub-reports rather than sub-forms?
Is the main report bound only to the Part Certification table and no table
that is detailed in the sub-report record source?

--
Duane Hookom
Microsoft Access MVP


"Xfree" wrote:

I am still unable to resolve this issue here is what I have:

Main form table
Part Certification with the key field of PartCertificationID with a one to
many join to both subtables

Sub form 1 is
PartCertSub with the key field of PartCertSub and the foreign key that joins
the two tables is PartCertificationID
Within this table also it is joined to the Products table by the ProductID
field (many to one)
The Link Master Fields is PartCerificationID and Link Child is
PartCertificationID

Sub form 2 is
CertMaterialSub with the key field of CertMaterialID and the foreign key to
PartCertification table is PartCertificationID
This table also joins with the PourSchedule table (many to one) by way of
the PourScheduleID foreign key.
The Link Master Fields is PartCerificationID and Link Child is
PartCertificationID

The issue starts when I add more than one record in either sub form one or
two. If there is only one record in each of the two sub reports
it prints correctly.


"Duane Hookom" wrote:

Perhaps you should share the record sources of your main report and the 1
sub-report giving the problems as well as the Link Master/Child properties.

--
Duane Hookom
Microsoft Access MVP


"Xfree" wrote:

I tried to remove tables but it kills the report. Now this report has 3 sub
reports in it 2 out of three of the sub reports prints correctly i.e. only
once, just the one sub report is causing me issues of repeating its self on
page 2 of what should be a 1 page report.

"John Spencer" wrote:

Mor Likely you need to remove a table from the query rather than fields.

Of course you can use
SELECT DISTINCT ...

and remove all the fields from the tables that are being used in the sub-report.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Xfree wrote:
Daune,
I am still battling this issue, I have removed fields but I am still
getting the sub-report section printing twice.

Any other ideas?


"Duane Hookom" wrote:

  #23  
Old April 22nd, 2009, 01:38 AM posted to microsoft.public.access.reports
perplexed
external usenet poster
 
Posts: 70
Default Report prints data twice on two different pages

This scenario is pretty close to what I have been struggling with. I posted
in both the reports and queries categories and have received several helpful
suggestions; however, none resolved my problem. I did learn a lot about sql
code though. In the meantime, I have based my report on the main table
selecting just the fields that I need so that I have only one instance of
each record. Then I added subreports for the two other tables which are
joined by the CustomerID field in each table. The report LOOKS great;
however, I only want to see the data for records in the main table based on
criteria in the second table; AND, I want to see the third subreport for
those same records even though there is no data in the third table to
correspond. Some of the suggestions received previously didn't work. I
followed the suggestion for making a query to select the records from the
main table based on the second table criteria which would return only the
records I wanted and got a query with exactly what I wanted; however, when I
made a report from the query, the records repeat based on the number of
records in the second table that apply to the record in the main table. Is
there any way to send my file for analyzing the codes to Duane? Any
suggestions will be greatly appreciated!!!!

Regards!

"Xfree" wrote:

I have a report that contains three sub reports everything is working
correctly as far as the output except the one page report repeats its self on
a second page with all the same info except the header. Is this a setting I
have wrong or a format issue?

Let me know if more info is needed for this issue.

Thanks

  #24  
Old April 22nd, 2009, 03:08 AM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Report prints data twice on two different pages

Apparently your main report's record source query should be a totals query
that outputs only the main tables records.
--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

This scenario is pretty close to what I have been struggling with. I posted
in both the reports and queries categories and have received several helpful
suggestions; however, none resolved my problem. I did learn a lot about sql
code though. In the meantime, I have based my report on the main table
selecting just the fields that I need so that I have only one instance of
each record. Then I added subreports for the two other tables which are
joined by the CustomerID field in each table. The report LOOKS great;
however, I only want to see the data for records in the main table based on
criteria in the second table; AND, I want to see the third subreport for
those same records even though there is no data in the third table to
correspond. Some of the suggestions received previously didn't work. I
followed the suggestion for making a query to select the records from the
main table based on the second table criteria which would return only the
records I wanted and got a query with exactly what I wanted; however, when I
made a report from the query, the records repeat based on the number of
records in the second table that apply to the record in the main table. Is
there any way to send my file for analyzing the codes to Duane? Any
suggestions will be greatly appreciated!!!!

Regards!

"Xfree" wrote:

I have a report that contains three sub reports everything is working
correctly as far as the output except the one page report repeats its self on
a second page with all the same info except the header. Is this a setting I
have wrong or a format issue?

Let me know if more info is needed for this issue.

Thanks

  #25  
Old April 22nd, 2009, 08:52 PM posted to microsoft.public.access.reports
perplexed
external usenet poster
 
Posts: 70
Default Report prints data twice on two different pages

If I am understanding your statement --
My main report is based on a query that is based on the main table only. It
is pulling selected fields (name, address & phone number) only. This results
in my report listing the customer only once. This is great except that I get
all customers and I only want to see the ones that have appts in table 2 that
have reasons greater than '1'. The suggestion from another person to make a
query with the following code provided the correct results; however, once
again when I created a report from this query, the customers appear multiple
times based on the number of appts in table 2 that meet the criteria.

SELECT ... Customers column list ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1

I seem to be going in circles.

"Duane Hookom" wrote:

Apparently your main report's record source query should be a totals query
that outputs only the main tables records.
--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

This scenario is pretty close to what I have been struggling with. I posted
in both the reports and queries categories and have received several helpful
suggestions; however, none resolved my problem. I did learn a lot about sql
code though. In the meantime, I have based my report on the main table
selecting just the fields that I need so that I have only one instance of
each record. Then I added subreports for the two other tables which are
joined by the CustomerID field in each table. The report LOOKS great;
however, I only want to see the data for records in the main table based on
criteria in the second table; AND, I want to see the third subreport for
those same records even though there is no data in the third table to
correspond. Some of the suggestions received previously didn't work. I
followed the suggestion for making a query to select the records from the
main table based on the second table criteria which would return only the
records I wanted and got a query with exactly what I wanted; however, when I
made a report from the query, the records repeat based on the number of
records in the second table that apply to the record in the main table. Is
there any way to send my file for analyzing the codes to Duane? Any
suggestions will be greatly appreciated!!!!

Regards!

"Xfree" wrote:

I have a report that contains three sub reports everything is working
correctly as far as the output except the one page report repeats its self on
a second page with all the same info except the header. Is this a setting I
have wrong or a format issue?

Let me know if more info is needed for this issue.

Thanks

  #26  
Old April 22nd, 2009, 10:02 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Report prints data twice on two different pages

Yes, base your report on a query like:
SELECT ... Customers column list ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1

But as I stated, make this into a totals query and don't include any field
in the output from the appointments table. This might be like:

SELECT CustomerID, CustomerName, Address, Phone, ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1
GROUP BY CustomerID, CustomerName, Address, Phone, ...;


--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

If I am understanding your statement --
My main report is based on a query that is based on the main table only. It
is pulling selected fields (name, address & phone number) only. This results
in my report listing the customer only once. This is great except that I get
all customers and I only want to see the ones that have appts in table 2 that
have reasons greater than '1'. The suggestion from another person to make a
query with the following code provided the correct results; however, once
again when I created a report from this query, the customers appear multiple
times based on the number of appts in table 2 that meet the criteria.

SELECT ... Customers column list ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1

I seem to be going in circles.

"Duane Hookom" wrote:

Apparently your main report's record source query should be a totals query
that outputs only the main tables records.
--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

This scenario is pretty close to what I have been struggling with. I posted
in both the reports and queries categories and have received several helpful
suggestions; however, none resolved my problem. I did learn a lot about sql
code though. In the meantime, I have based my report on the main table
selecting just the fields that I need so that I have only one instance of
each record. Then I added subreports for the two other tables which are
joined by the CustomerID field in each table. The report LOOKS great;
however, I only want to see the data for records in the main table based on
criteria in the second table; AND, I want to see the third subreport for
those same records even though there is no data in the third table to
correspond. Some of the suggestions received previously didn't work. I
followed the suggestion for making a query to select the records from the
main table based on the second table criteria which would return only the
records I wanted and got a query with exactly what I wanted; however, when I
made a report from the query, the records repeat based on the number of
records in the second table that apply to the record in the main table. Is
there any way to send my file for analyzing the codes to Duane? Any
suggestions will be greatly appreciated!!!!

Regards!

"Xfree" wrote:

I have a report that contains three sub reports everything is working
correctly as far as the output except the one page report repeats its self on
a second page with all the same info except the header. Is this a setting I
have wrong or a format issue?

Let me know if more info is needed for this issue.

Thanks

  #27  
Old April 23rd, 2009, 10:13 PM posted to microsoft.public.access.reports
perplexed
external usenet poster
 
Posts: 70
Default Report prints data twice on two different pages

DUANE! You are the greatest!! It works!!!!!
I'm not sure I fully understand what a "totals query" is nor do I totally
understand why adding the GROUP BY line made the difference in the sql code
for the query but none the less I got exactly what I wanted. I had to tweak
the query a little bit and add the appt date and criteria and then also make
the same change to my appointments query but I resolved the issue by making
them both the same reference (=now()-90) so that I get a rolling 90 day
period and don't have to manually change the date reference each month to
drop off the previous 30 day period. It will work for what I need to use it
for.

Again, thanks to everyone who has given me help with this report. You are
all wonderful and I've learned way more than I ever knew Access could do.
This goes way deeper than the Microsoft Certification course.

My next hurtle is to determine if it's possible to have the third table
subreport print out blank fields even if there is no corresponding data
related to the main table. If this is too complicated, I think I can live
with out it but I do love a challenge.

Thanks again Duane! I can see from other posts that you are a great
resource to the group. Take care.
================
"Duane Hookom" wrote:

Yes, base your report on a query like:
SELECT ... Customers column list ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1

But as I stated, make this into a totals query and don't include any field
in the output from the appointments table. This might be like:

SELECT CustomerID, CustomerName, Address, Phone, ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1
GROUP BY CustomerID, CustomerName, Address, Phone, ...;


--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

If I am understanding your statement --
My main report is based on a query that is based on the main table only. It
is pulling selected fields (name, address & phone number) only. This results
in my report listing the customer only once. This is great except that I get
all customers and I only want to see the ones that have appts in table 2 that
have reasons greater than '1'. The suggestion from another person to make a
query with the following code provided the correct results; however, once
again when I created a report from this query, the customers appear multiple
times based on the number of appts in table 2 that meet the criteria.

SELECT ... Customers column list ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1

I seem to be going in circles.

"Duane Hookom" wrote:

Apparently your main report's record source query should be a totals query
that outputs only the main tables records.
--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

This scenario is pretty close to what I have been struggling with. I posted
in both the reports and queries categories and have received several helpful
suggestions; however, none resolved my problem. I did learn a lot about sql
code though. In the meantime, I have based my report on the main table
selecting just the fields that I need so that I have only one instance of
each record. Then I added subreports for the two other tables which are
joined by the CustomerID field in each table. The report LOOKS great;
however, I only want to see the data for records in the main table based on
criteria in the second table; AND, I want to see the third subreport for
those same records even though there is no data in the third table to
correspond. Some of the suggestions received previously didn't work. I
followed the suggestion for making a query to select the records from the
main table based on the second table criteria which would return only the
records I wanted and got a query with exactly what I wanted; however, when I
made a report from the query, the records repeat based on the number of
records in the second table that apply to the record in the main table. Is
there any way to send my file for analyzing the codes to Duane? Any
suggestions will be greatly appreciated!!!!

Regards!

"Xfree" wrote:

I have a report that contains three sub reports everything is working
correctly as far as the output except the one page report repeats its self on
a second page with all the same info except the header. Is this a setting I
have wrong or a format issue?

Let me know if more info is needed for this issue.

Thanks

  #28  
Old April 23rd, 2009, 10:52 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Report prints data twice on two different pages

A subreport won't display if there are no related records. There are times
when it can be "fooled". Start a new thread if you have questions.

--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

DUANE! You are the greatest!! It works!!!!!
I'm not sure I fully understand what a "totals query" is nor do I totally
understand why adding the GROUP BY line made the difference in the sql code
for the query but none the less I got exactly what I wanted. I had to tweak
the query a little bit and add the appt date and criteria and then also make
the same change to my appointments query but I resolved the issue by making
them both the same reference (=now()-90) so that I get a rolling 90 day
period and don't have to manually change the date reference each month to
drop off the previous 30 day period. It will work for what I need to use it
for.

Again, thanks to everyone who has given me help with this report. You are
all wonderful and I've learned way more than I ever knew Access could do.
This goes way deeper than the Microsoft Certification course.

My next hurtle is to determine if it's possible to have the third table
subreport print out blank fields even if there is no corresponding data
related to the main table. If this is too complicated, I think I can live
with out it but I do love a challenge.

Thanks again Duane! I can see from other posts that you are a great
resource to the group. Take care.
================
"Duane Hookom" wrote:

Yes, base your report on a query like:
SELECT ... Customers column list ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1

But as I stated, make this into a totals query and don't include any field
in the output from the appointments table. This might be like:

SELECT CustomerID, CustomerName, Address, Phone, ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1
GROUP BY CustomerID, CustomerName, Address, Phone, ...;


--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

If I am understanding your statement --
My main report is based on a query that is based on the main table only. It
is pulling selected fields (name, address & phone number) only. This results
in my report listing the customer only once. This is great except that I get
all customers and I only want to see the ones that have appts in table 2 that
have reasons greater than '1'. The suggestion from another person to make a
query with the following code provided the correct results; however, once
again when I created a report from this query, the customers appear multiple
times based on the number of appts in table 2 that meet the criteria.

SELECT ... Customers column list ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1

I seem to be going in circles.

"Duane Hookom" wrote:

Apparently your main report's record source query should be a totals query
that outputs only the main tables records.
--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

This scenario is pretty close to what I have been struggling with. I posted
in both the reports and queries categories and have received several helpful
suggestions; however, none resolved my problem. I did learn a lot about sql
code though. In the meantime, I have based my report on the main table
selecting just the fields that I need so that I have only one instance of
each record. Then I added subreports for the two other tables which are
joined by the CustomerID field in each table. The report LOOKS great;
however, I only want to see the data for records in the main table based on
criteria in the second table; AND, I want to see the third subreport for
those same records even though there is no data in the third table to
correspond. Some of the suggestions received previously didn't work. I
followed the suggestion for making a query to select the records from the
main table based on the second table criteria which would return only the
records I wanted and got a query with exactly what I wanted; however, when I
made a report from the query, the records repeat based on the number of
records in the second table that apply to the record in the main table. Is
there any way to send my file for analyzing the codes to Duane? Any
suggestions will be greatly appreciated!!!!

Regards!

"Xfree" wrote:

I have a report that contains three sub reports everything is working
correctly as far as the output except the one page report repeats its self on
a second page with all the same info except the header. Is this a setting I
have wrong or a format issue?

Let me know if more info is needed for this issue.

Thanks

  #29  
Old April 24th, 2009, 12:42 AM posted to microsoft.public.access.reports
perplexed
external usenet poster
 
Posts: 70
Default Report prints data twice on two different pages

Okay, I just started a new thread "Blank Subreport for table with no related
records"
Thanks again -- You're terrific!

"Duane Hookom" wrote:

A subreport won't display if there are no related records. There are times
when it can be "fooled". Start a new thread if you have questions.

--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

DUANE! You are the greatest!! It works!!!!!
I'm not sure I fully understand what a "totals query" is nor do I totally
understand why adding the GROUP BY line made the difference in the sql code
for the query but none the less I got exactly what I wanted. I had to tweak
the query a little bit and add the appt date and criteria and then also make
the same change to my appointments query but I resolved the issue by making
them both the same reference (=now()-90) so that I get a rolling 90 day
period and don't have to manually change the date reference each month to
drop off the previous 30 day period. It will work for what I need to use it
for.

Again, thanks to everyone who has given me help with this report. You are
all wonderful and I've learned way more than I ever knew Access could do.
This goes way deeper than the Microsoft Certification course.

My next hurtle is to determine if it's possible to have the third table
subreport print out blank fields even if there is no corresponding data
related to the main table. If this is too complicated, I think I can live
with out it but I do love a challenge.

Thanks again Duane! I can see from other posts that you are a great
resource to the group. Take care.
================
"Duane Hookom" wrote:

Yes, base your report on a query like:
SELECT ... Customers column list ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1

But as I stated, make this into a totals query and don't include any field
in the output from the appointments table. This might be like:

SELECT CustomerID, CustomerName, Address, Phone, ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1
GROUP BY CustomerID, CustomerName, Address, Phone, ...;


--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

If I am understanding your statement --
My main report is based on a query that is based on the main table only. It
is pulling selected fields (name, address & phone number) only. This results
in my report listing the customer only once. This is great except that I get
all customers and I only want to see the ones that have appts in table 2 that
have reasons greater than '1'. The suggestion from another person to make a
query with the following code provided the correct results; however, once
again when I created a report from this query, the customers appear multiple
times based on the number of appts in table 2 that meet the criteria.

SELECT ... Customers column list ...
FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id =
A.customer_id
WHERE A.cancelled = True
AND A.reason 1

I seem to be going in circles.

"Duane Hookom" wrote:

Apparently your main report's record source query should be a totals query
that outputs only the main tables records.
--
Duane Hookom
Microsoft Access MVP


"Perplexed" wrote:

This scenario is pretty close to what I have been struggling with. I posted
in both the reports and queries categories and have received several helpful
suggestions; however, none resolved my problem. I did learn a lot about sql
code though. In the meantime, I have based my report on the main table
selecting just the fields that I need so that I have only one instance of
each record. Then I added subreports for the two other tables which are
joined by the CustomerID field in each table. The report LOOKS great;
however, I only want to see the data for records in the main table based on
criteria in the second table; AND, I want to see the third subreport for
those same records even though there is no data in the third table to
correspond. Some of the suggestions received previously didn't work. I
followed the suggestion for making a query to select the records from the
main table based on the second table criteria which would return only the
records I wanted and got a query with exactly what I wanted; however, when I
made a report from the query, the records repeat based on the number of
records in the second table that apply to the record in the main table. Is
there any way to send my file for analyzing the codes to Duane? Any
suggestions will be greatly appreciated!!!!

Regards!

"Xfree" wrote:

I have a report that contains three sub reports everything is working
correctly as far as the output except the one page report repeats its self on
a second page with all the same info except the header. Is this a setting I
have wrong or a format issue?

Let me know if more info is needed for this issue.

Thanks

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:23 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.