Question on subreports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All

I am frustrated by something that I hope you can help with.
I have a database that calculates an invoice (grouped by ClientID) and a
separate, unrelated, table within the database that monitors payments (again,
grouped by ClientID).
I want to produce a report with both invocing charges and payment history
that works out the difference. I have tried subreports and either get all
data produced in the subreport or none at all. The' none at all' occurs when
I fill in the Master/Child Links. 'All data' is produced when these
properties are cleared.
I have related all tables and made sure linking fields have the same name
and data type. None of this works. Can any one help?

Many Thanks
 
Are you grouping your recordsources by ClientID and then using this as the
Link Master/Child property?
 
Essentially, yes. The main report and subreport are both grouped by ClientID.
ClientID is also used as the Link between them.
 
Can you provide your record source SQL views for the main report and
subreport?
 
The subreport SQL is as follows (this the main query in the database):

SELECT SessionOrders02.OrderID, SessionOrders02.ChildID,
SessionOrders02.Date, SchoolDates.Day, SchoolDates.[Week No],
SchoolDates.[Week Beginning], SchoolDates.[Week Cumulative],
SessionOrders02.SessionID, IIf([Invoice Age]<2 And SessionOrders02!Comments
Not Like "F",[Unit Cost]+1,IIf(SessionOrders02!SessionID Like "Breakfast
Club" And SessionOrders02!Comments Like "AD",0,IIf(SessionOrders02!Comments
Like "F",0,[Unit Cost]))) AS [Unit Costs], SessionOrders02.[Voucher
Discount], CCur([Session Detail]![Unit Cost])*([Voucher Discount]/100) AS
[Discount 1], [Unit Cost]-[Discount 1] AS Cost, [Appletree Application
02].DOB, Round((SessionOrders02!Date-[DOB])/365,3) AS [Invoice Age],
SessionOrders02.Comments
FROM [Session Detail] INNER JOIN (SchoolDates INNER JOIN ([Appletree
Application 02] INNER JOIN SessionOrders02 ON [Appletree Application
02].[Child Full Name] = SessionOrders02.ChildID) ON SchoolDates.Date =
SessionOrders02.Date) ON [Session Detail].[Session Type] =
SessionOrders02.SessionID
WHERE (((SessionOrders02.Date) Between #9/5/2005# And #12/31/2005#))
ORDER BY SessionOrders02.ChildID, SessionOrders02.Date, SchoolDates.[Week No];

The main report is drawn from a table:

SELECT
FROM [Payment Monitor01];

Many thanks
 
You stated "The main report and subreport are both grouped by ClientID.
ClientID is also used as the Link between them."

I don't see any ClientID field in the record sources.

--
Duane Hookom
MS Access MVP
--

JonK said:
The subreport SQL is as follows (this the main query in the database):

SELECT SessionOrders02.OrderID, SessionOrders02.ChildID,
SessionOrders02.Date, SchoolDates.Day, SchoolDates.[Week No],
SchoolDates.[Week Beginning], SchoolDates.[Week Cumulative],
SessionOrders02.SessionID, IIf([Invoice Age]<2 And
SessionOrders02!Comments
Not Like "F",[Unit Cost]+1,IIf(SessionOrders02!SessionID Like "Breakfast
Club" And SessionOrders02!Comments Like
"AD",0,IIf(SessionOrders02!Comments
Like "F",0,[Unit Cost]))) AS [Unit Costs], SessionOrders02.[Voucher
Discount], CCur([Session Detail]![Unit Cost])*([Voucher Discount]/100) AS
[Discount 1], [Unit Cost]-[Discount 1] AS Cost, [Appletree Application
02].DOB, Round((SessionOrders02!Date-[DOB])/365,3) AS [Invoice Age],
SessionOrders02.Comments
FROM [Session Detail] INNER JOIN (SchoolDates INNER JOIN ([Appletree
Application 02] INNER JOIN SessionOrders02 ON [Appletree Application
02].[Child Full Name] = SessionOrders02.ChildID) ON SchoolDates.Date =
SessionOrders02.Date) ON [Session Detail].[Session Type] =
SessionOrders02.SessionID
WHERE (((SessionOrders02.Date) Between #9/5/2005# And #12/31/2005#))
ORDER BY SessionOrders02.ChildID, SessionOrders02.Date, SchoolDates.[Week
No];

The main report is drawn from a table:

SELECT
FROM [Payment Monitor01];

Many thanks



--
JonK


Duane Hookom said:
Can you provide your record source SQL views for the main report and
subreport?
Click to expand...
Click to expand...
 
It is "ChildID" in this case - I was paraphrasing! Sorry!
--
JonK


Duane Hookom said:
You stated "The main report and subreport are both grouped by ClientID.
ClientID is also used as the Link between them."

I don't see any ClientID field in the record sources.

--
Duane Hookom
MS Access MVP
--

JonK said:
The subreport SQL is as follows (this the main query in the database):

SELECT SessionOrders02.OrderID, SessionOrders02.ChildID,
SessionOrders02.Date, SchoolDates.Day, SchoolDates.[Week No],
SchoolDates.[Week Beginning], SchoolDates.[Week Cumulative],
SessionOrders02.SessionID, IIf([Invoice Age]<2 And
SessionOrders02!Comments
Not Like "F",[Unit Cost]+1,IIf(SessionOrders02!SessionID Like "Breakfast
Club" And SessionOrders02!Comments Like
"AD",0,IIf(SessionOrders02!Comments
Like "F",0,[Unit Cost]))) AS [Unit Costs], SessionOrders02.[Voucher
Discount], CCur([Session Detail]![Unit Cost])*([Voucher Discount]/100) AS
[Discount 1], [Unit Cost]-[Discount 1] AS Cost, [Appletree Application
02].DOB, Round((SessionOrders02!Date-[DOB])/365,3) AS [Invoice Age],
SessionOrders02.Comments
FROM [Session Detail] INNER JOIN (SchoolDates INNER JOIN ([Appletree
Application 02] INNER JOIN SessionOrders02 ON [Appletree Application
02].[Child Full Name] = SessionOrders02.ChildID) ON SchoolDates.Date =
SessionOrders02.Date) ON [Session Detail].[Session Type] =
SessionOrders02.SessionID
WHERE (((SessionOrders02.Date) Between #9/5/2005# And #12/31/2005#))
ORDER BY SessionOrders02.ChildID, SessionOrders02.Date, SchoolDates.[Week
No];

The main report is drawn from a table:

SELECT
FROM [Payment Monitor01];

Many thanks



--
JonK


Duane Hookom said:
Can you provide your record source SQL views for the main report and
subreport?

--
Duane Hookom
MS Access MVP
--

Essentially, yes. The main report and subreport are both grouped by
ClientID.
ClientID is also used as the Link between them.
--
JonK


:

Are you grouping your recordsources by ClientID and then using this as
the
Link Master/Child property?

--
Duane Hookom
MS Access MVP
--

Hi All

I am frustrated by something that I hope you can help with.
I have a database that calculates an invoice (grouped by ClientID)
and
a
separate, unrelated, table within the database that monitors
payments
(again,
grouped by ClientID).
I want to produce a report with both invocing charges and payment
history
that works out the difference. I have tried subreports and either
get
all
data produced in the subreport or none at all. The' none at all'
occurs
when
I fill in the Master/Child Links. 'All data' is produced when these
properties are cleared.
I have related all tables and made sure linking fields have the same
name
and data type. None of this works. Can any one help?

Many Thanks
Click to expand...
Click to expand...
Click to expand...
 
Ok, can I assume that ChildID is in both record sources and when the
datasheet of the record sources is displayed, both columns are aligned to
the left or both to the right?

Do both record sources display the expected records?

--
Duane Hookom
MS Access MVP
--

JonK said:
It is "ChildID" in this case - I was paraphrasing! Sorry!
--
JonK


Duane Hookom said:
You stated "The main report and subreport are both grouped by ClientID.
ClientID is also used as the Link between them."

I don't see any ClientID field in the record sources.

--
Duane Hookom
MS Access MVP
--

JonK said:
The subreport SQL is as follows (this the main query in the database):

SELECT SessionOrders02.OrderID, SessionOrders02.ChildID,
SessionOrders02.Date, SchoolDates.Day, SchoolDates.[Week No],
SchoolDates.[Week Beginning], SchoolDates.[Week Cumulative],
SessionOrders02.SessionID, IIf([Invoice Age]<2 And
SessionOrders02!Comments
Not Like "F",[Unit Cost]+1,IIf(SessionOrders02!SessionID Like
"Breakfast
Club" And SessionOrders02!Comments Like
"AD",0,IIf(SessionOrders02!Comments
Like "F",0,[Unit Cost]))) AS [Unit Costs], SessionOrders02.[Voucher
Discount], CCur([Session Detail]![Unit Cost])*([Voucher Discount]/100)
AS
[Discount 1], [Unit Cost]-[Discount 1] AS Cost, [Appletree Application
02].DOB, Round((SessionOrders02!Date-[DOB])/365,3) AS [Invoice Age],
SessionOrders02.Comments
FROM [Session Detail] INNER JOIN (SchoolDates INNER JOIN ([Appletree
Application 02] INNER JOIN SessionOrders02 ON [Appletree Application
02].[Child Full Name] = SessionOrders02.ChildID) ON SchoolDates.Date =
SessionOrders02.Date) ON [Session Detail].[Session Type] =
SessionOrders02.SessionID
WHERE (((SessionOrders02.Date) Between #9/5/2005# And #12/31/2005#))
ORDER BY SessionOrders02.ChildID, SessionOrders02.Date,
SchoolDates.[Week
No];

The main report is drawn from a table:

SELECT
FROM [Payment Monitor01];

Many thanks



--
JonK


:

Can you provide your record source SQL views for the main report and
subreport?

--
Duane Hookom
MS Access MVP
--

Essentially, yes. The main report and subreport are both grouped by
ClientID.
ClientID is also used as the Link between them.
--
JonK


:

Are you grouping your recordsources by ClientID and then using this
as
the
Link Master/Child property?

--
Duane Hookom
MS Access MVP
--

Hi All

I am frustrated by something that I hope you can help with.
I have a database that calculates an invoice (grouped by
ClientID)
and
a
separate, unrelated, table within the database that monitors
payments
(again,
grouped by ClientID).
I want to produce a report with both invocing charges and payment
history
that works out the difference. I have tried subreports and
either
get
all
data produced in the subreport or none at all. The' none at all'
occurs
when
I fill in the Master/Child Links. 'All data' is produced when
these
properties are cleared.
I have related all tables and made sure linking fields have the
same
name
and data type. None of this works. Can any one help?

Many Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top