outer join on a subreport

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

Guest

i would like to produce a report that prints all records from the main report
and all records from the sub report. currently all the records from the main
report print and only the matching records from the sub report print. how do
i force all the records from the sub report to print.

SQL from main report:
SELECT [OrderList Cashout].VRCRoute, [OrderList Cashout].VRCCustomer,
[OrderList Cashout].VRCSequence, [OrderList Cashout].VRCCashout, [OrderList
Cashout].VRCChanger, [OrderList Cashout].VRCChangerAmt, Assets.AssetNumber,
AssetType.AssetType, Route.RouteName
FROM (AssetType RIGHT JOIN ([OrderList Cashout] LEFT JOIN Assets ON
[OrderList Cashout].VRCCustomer = Assets.AssetCustomer) ON
AssetType.AssetTypeID = Assets.AssetType) LEFT JOIN Route ON [OrderList
Cashout].VRCRoute = Route.RouteNumber
WHERE (((AssetType.SlipExclude)=No))
ORDER BY [OrderList Cashout].VRCSequence;

SQL From Sub-Report:
SELECT LoadOut.LoadOutRoute, LoadOut.LoadOutItem, LoadOut.LoadOutCount,
Products.ItemBin, Products.ItemNumber, LoadOut.LoadOutDate, Route.RouteName,
Products.ItemCat, LoadOut.LoadOutCustomer, LoadOut.LoadOutAsset,
LoadOut.LoadOutSeq, Products.ItemDesc, [OrderList Cashout].VRCSequence,
[OrderList Cashout].VRCCashout, [OrderList Cashout].VRCChanger, [OrderList
Cashout].VRCChangerAmt, AssetType.AssetType, Assets.AssetRouteNote
FROM (((LoadOut LEFT JOIN Products ON LoadOut.LoadOutItem =
Products.ItemNumber) LEFT JOIN Route ON LoadOut.LoadOutRoute =
Route.RouteNumber) LEFT JOIN [OrderList Cashout] ON LoadOut.LoadOutCustomer =
[OrderList Cashout].VRCCustomer) LEFT JOIN (AssetType RIGHT JOIN Assets ON
AssetType.AssetTypeID = Assets.AssetType) ON LoadOut.LoadOutAsset =
Assets.AssetNumber
WHERE (((LoadOut.LoadOutRoute)=[Forms]![DriverOrderDialogue]![RouteNumber])
AND ((LoadOut.LoadOutCount)>0) AND
((LoadOut.LoadOutDate)=[Forms]![DriverOrderDialogue]![startdate]));
 
cansoft said:
i would like to produce a report that prints all records from the main report
and all records from the sub report. currently all the records from the main
report print and only the matching records from the sub report print. how do
i force all the records from the sub report to print.

SQL from main report:
SELECT [OrderList Cashout].VRCRoute, [OrderList Cashout].VRCCustomer,
[OrderList Cashout].VRCSequence, [OrderList Cashout].VRCCashout, [OrderList
Cashout].VRCChanger, [OrderList Cashout].VRCChangerAmt, Assets.AssetNumber,
AssetType.AssetType, Route.RouteName
FROM (AssetType RIGHT JOIN ([OrderList Cashout] LEFT JOIN Assets ON
[OrderList Cashout].VRCCustomer = Assets.AssetCustomer) ON
AssetType.AssetTypeID = Assets.AssetType) LEFT JOIN Route ON [OrderList
Cashout].VRCRoute = Route.RouteNumber
WHERE (((AssetType.SlipExclude)=No))
ORDER BY [OrderList Cashout].VRCSequence;

SQL From Sub-Report:
SELECT LoadOut.LoadOutRoute, LoadOut.LoadOutItem, LoadOut.LoadOutCount,
Products.ItemBin, Products.ItemNumber, LoadOut.LoadOutDate, Route.RouteName,
Products.ItemCat, LoadOut.LoadOutCustomer, LoadOut.LoadOutAsset,
LoadOut.LoadOutSeq, Products.ItemDesc, [OrderList Cashout].VRCSequence,
[OrderList Cashout].VRCCashout, [OrderList Cashout].VRCChanger, [OrderList
Cashout].VRCChangerAmt, AssetType.AssetType, Assets.AssetRouteNote
FROM (((LoadOut LEFT JOIN Products ON LoadOut.LoadOutItem =
Products.ItemNumber) LEFT JOIN Route ON LoadOut.LoadOutRoute =
Route.RouteNumber) LEFT JOIN [OrderList Cashout] ON LoadOut.LoadOutCustomer =
[OrderList Cashout].VRCCustomer) LEFT JOIN (AssetType RIGHT JOIN Assets ON
AssetType.AssetTypeID = Assets.AssetType) ON LoadOut.LoadOutAsset =
Assets.AssetNumber
WHERE (((LoadOut.LoadOutRoute)=[Forms]![DriverOrderDialogue]![RouteNumber])
AND ((LoadOut.LoadOutCount)>0) AND
((LoadOut.LoadOutDate)=[Forms]![DriverOrderDialogue]![startdate]));
Click to expand...


Make sure you have the subreport control's Link Master/Child
Fields properties set as you want them, maybe even clear
them.
 
the problem seems to be with the master / child links.
the master report should list all the customers and assets on a pre defined
specific route.
the sub report should then list any orders for the customers by asset for
that route as well as any temporary orders added to the order table but not
in the route/customer/asset table. because the links are set by route by
customer and by asset the report ignores the additions to the order table
that do not have a corresponding customer and asset in the route table. i
need to connect the route info and the oder info by route customer and asset
as well as list any exception orders that are added to the route without a
corresponding customer and asset. when i remove the master/child links i get
way to much info reported.

Marshall Barton said:
cansoft said:
i would like to produce a report that prints all records from the main report
and all records from the sub report. currently all the records from the main
report print and only the matching records from the sub report print. how do
i force all the records from the sub report to print.

SQL from main report:
SELECT [OrderList Cashout].VRCRoute, [OrderList Cashout].VRCCustomer,
[OrderList Cashout].VRCSequence, [OrderList Cashout].VRCCashout, [OrderList
Cashout].VRCChanger, [OrderList Cashout].VRCChangerAmt, Assets.AssetNumber,
AssetType.AssetType, Route.RouteName
FROM (AssetType RIGHT JOIN ([OrderList Cashout] LEFT JOIN Assets ON
[OrderList Cashout].VRCCustomer = Assets.AssetCustomer) ON
AssetType.AssetTypeID = Assets.AssetType) LEFT JOIN Route ON [OrderList
Cashout].VRCRoute = Route.RouteNumber
WHERE (((AssetType.SlipExclude)=No))
ORDER BY [OrderList Cashout].VRCSequence;

SQL From Sub-Report:
SELECT LoadOut.LoadOutRoute, LoadOut.LoadOutItem, LoadOut.LoadOutCount,
Products.ItemBin, Products.ItemNumber, LoadOut.LoadOutDate, Route.RouteName,
Products.ItemCat, LoadOut.LoadOutCustomer, LoadOut.LoadOutAsset,
LoadOut.LoadOutSeq, Products.ItemDesc, [OrderList Cashout].VRCSequence,
[OrderList Cashout].VRCCashout, [OrderList Cashout].VRCChanger, [OrderList
Cashout].VRCChangerAmt, AssetType.AssetType, Assets.AssetRouteNote
FROM (((LoadOut LEFT JOIN Products ON LoadOut.LoadOutItem =
Products.ItemNumber) LEFT JOIN Route ON LoadOut.LoadOutRoute =
Route.RouteNumber) LEFT JOIN [OrderList Cashout] ON LoadOut.LoadOutCustomer =
[OrderList Cashout].VRCCustomer) LEFT JOIN (AssetType RIGHT JOIN Assets ON
AssetType.AssetTypeID = Assets.AssetType) ON LoadOut.LoadOutAsset =
Assets.AssetNumber
WHERE (((LoadOut.LoadOutRoute)=[Forms]![DriverOrderDialogue]![RouteNumber])
AND ((LoadOut.LoadOutCount)>0) AND
((LoadOut.LoadOutDate)=[Forms]![DriverOrderDialogue]![startdate]));
Click to expand...


Make sure you have the subreport control's Link Master/Child
Fields properties set as you want them, maybe even clear
them.
Click to expand...
 
Without linking data fields in these temp orders, I don't
see how you can tell if it belongs to the route or not.

I'm just guessing here, but is there some way you can use
some kind of dummy customer and asset values for the
exception orders?
--
Marsh
MVP [MS Access]

the problem seems to be with the master / child links.
the master report should list all the customers and assets on a pre defined
specific route.
the sub report should then list any orders for the customers by asset for
that route as well as any temporary orders added to the order table but not
in the route/customer/asset table. because the links are set by route by
customer and by asset the report ignores the additions to the order table
that do not have a corresponding customer and asset in the route table. i
need to connect the route info and the oder info by route customer and asset
as well as list any exception orders that are added to the route without a
corresponding customer and asset. when i remove the master/child links i get
way to much info reported.

cansoft said:
i would like to produce a report that prints all records from the main report
and all records from the sub report. currently all the records from the main
report print and only the matching records from the sub report print. how do
i force all the records from the sub report to print.

SQL from main report:
SELECT [OrderList Cashout].VRCRoute, [OrderList Cashout].VRCCustomer,
[OrderList Cashout].VRCSequence, [OrderList Cashout].VRCCashout, [OrderList
Cashout].VRCChanger, [OrderList Cashout].VRCChangerAmt, Assets.AssetNumber,
AssetType.AssetType, Route.RouteName
FROM (AssetType RIGHT JOIN ([OrderList Cashout] LEFT JOIN Assets ON
[OrderList Cashout].VRCCustomer = Assets.AssetCustomer) ON
AssetType.AssetTypeID = Assets.AssetType) LEFT JOIN Route ON [OrderList
Cashout].VRCRoute = Route.RouteNumber
WHERE (((AssetType.SlipExclude)=No))
ORDER BY [OrderList Cashout].VRCSequence;

SQL From Sub-Report:
SELECT LoadOut.LoadOutRoute, LoadOut.LoadOutItem, LoadOut.LoadOutCount,
Products.ItemBin, Products.ItemNumber, LoadOut.LoadOutDate, Route.RouteName,
Products.ItemCat, LoadOut.LoadOutCustomer, LoadOut.LoadOutAsset,
LoadOut.LoadOutSeq, Products.ItemDesc, [OrderList Cashout].VRCSequence,
[OrderList Cashout].VRCCashout, [OrderList Cashout].VRCChanger, [OrderList
Cashout].VRCChangerAmt, AssetType.AssetType, Assets.AssetRouteNote
FROM (((LoadOut LEFT JOIN Products ON LoadOut.LoadOutItem =
Products.ItemNumber) LEFT JOIN Route ON LoadOut.LoadOutRoute =
Route.RouteNumber) LEFT JOIN [OrderList Cashout] ON LoadOut.LoadOutCustomer =
[OrderList Cashout].VRCCustomer) LEFT JOIN (AssetType RIGHT JOIN Assets ON
AssetType.AssetTypeID = Assets.AssetType) ON LoadOut.LoadOutAsset =
Assets.AssetNumber
WHERE (((LoadOut.LoadOutRoute)=[Forms]![DriverOrderDialogue]![RouteNumber])
AND ((LoadOut.LoadOutCount)>0) AND
((LoadOut.LoadOutDate)=[Forms]![DriverOrderDialogue]![startdate]));
Click to expand...
Click to expand...
Marshall Barton said:
Make sure you have the subreport control's Link Master/Child
Fields properties set as you want them, maybe even clear
them.
Click to expand...
Click to expand...
 
i can report on the route/customer table by selecting all customers and
assets for a specific route.
i can report on the order table by selecting all customers and assets by
route for a specific order date.
i then want to merge the two reports into one showing all customers and
assets on a route and any orders the customers may have. the orders table can
have temporary customers added to the route for any specific day without
being added to the route table. the temporary additions do not get selected.
(when the order is added it indicates route customer and asset so i know
which route) Should i create two separate queries and then try to create an
outer join on those queries. i was hoping i could force the outer join on the
subreport??
Marshall Barton said:
Without linking data fields in these temp orders, I don't
see how you can tell if it belongs to the route or not.

I'm just guessing here, but is there some way you can use
some kind of dummy customer and asset values for the
exception orders?
--
Marsh
MVP [MS Access]

the problem seems to be with the master / child links.
the master report should list all the customers and assets on a pre defined
specific route.
the sub report should then list any orders for the customers by asset for
that route as well as any temporary orders added to the order table but not
in the route/customer/asset table. because the links are set by route by
customer and by asset the report ignores the additions to the order table
that do not have a corresponding customer and asset in the route table. i
need to connect the route info and the oder info by route customer and asset
as well as list any exception orders that are added to the route without a
corresponding customer and asset. when i remove the master/child links i get
way to much info reported.

cansoft wrote:
i would like to produce a report that prints all records from the main report
and all records from the sub report. currently all the records from the main
report print and only the matching records from the sub report print. how do
i force all the records from the sub report to print.

SQL from main report:
SELECT [OrderList Cashout].VRCRoute, [OrderList Cashout].VRCCustomer,
[OrderList Cashout].VRCSequence, [OrderList Cashout].VRCCashout, [OrderList
Cashout].VRCChanger, [OrderList Cashout].VRCChangerAmt, Assets.AssetNumber,
AssetType.AssetType, Route.RouteName
FROM (AssetType RIGHT JOIN ([OrderList Cashout] LEFT JOIN Assets ON
[OrderList Cashout].VRCCustomer = Assets.AssetCustomer) ON
AssetType.AssetTypeID = Assets.AssetType) LEFT JOIN Route ON [OrderList
Cashout].VRCRoute = Route.RouteNumber
WHERE (((AssetType.SlipExclude)=No))
ORDER BY [OrderList Cashout].VRCSequence;

SQL From Sub-Report:
SELECT LoadOut.LoadOutRoute, LoadOut.LoadOutItem, LoadOut.LoadOutCount,
Products.ItemBin, Products.ItemNumber, LoadOut.LoadOutDate, Route.RouteName,
Products.ItemCat, LoadOut.LoadOutCustomer, LoadOut.LoadOutAsset,
LoadOut.LoadOutSeq, Products.ItemDesc, [OrderList Cashout].VRCSequence,
[OrderList Cashout].VRCCashout, [OrderList Cashout].VRCChanger, [OrderList
Cashout].VRCChangerAmt, AssetType.AssetType, Assets.AssetRouteNote
FROM (((LoadOut LEFT JOIN Products ON LoadOut.LoadOutItem =
Products.ItemNumber) LEFT JOIN Route ON LoadOut.LoadOutRoute =
Route.RouteNumber) LEFT JOIN [OrderList Cashout] ON LoadOut.LoadOutCustomer =
[OrderList Cashout].VRCCustomer) LEFT JOIN (AssetType RIGHT JOIN Assets ON
AssetType.AssetTypeID = Assets.AssetType) ON LoadOut.LoadOutAsset =
Assets.AssetNumber
WHERE (((LoadOut.LoadOutRoute)=[Forms]![DriverOrderDialogue]![RouteNumber])
AND ((LoadOut.LoadOutCount)>0) AND
((LoadOut.LoadOutDate)=[Forms]![DriverOrderDialogue]![startdate]));
Click to expand...
Marshall Barton said:
Make sure you have the subreport control's Link Master/Child
Fields properties set as you want them, maybe even clear
them.
Click to expand...
Click to expand...
Click to expand...
 
The subreport joining mechanism is the Link Master/Child
properties and they are strictly an "inner join" kind of
thing. If that's what you really want, maybe you can use a
second instance of the subreport with only the route
specified in the Link Master/Child properties.

OTOH, maybe you can plug in some dummy customer name in the
exception orders using this kind of thing in the subreport's
query: Nz(LoadOut.LoadOutCustomer,"Exception") and union an
"Exception" customer record in the main report's query.
Similarly for the asset field.
--
Marsh
MVP [MS Access]

i can report on the route/customer table by selecting all customers and
assets for a specific route.
i can report on the order table by selecting all customers and assets by
route for a specific order date.
i then want to merge the two reports into one showing all customers and
assets on a route and any orders the customers may have. the orders table can
have temporary customers added to the route for any specific day without
being added to the route table. the temporary additions do not get selected.
(when the order is added it indicates route customer and asset so i know
which route) Should i create two separate queries and then try to create an
outer join on those queries. i was hoping i could force the outer join on the
subreport??


Marshall Barton said:
Without linking data fields in these temp orders, I don't
see how you can tell if it belongs to the route or not.

I'm just guessing here, but is there some way you can use
some kind of dummy customer and asset values for the
exception orders?

the problem seems to be with the master / child links.
the master report should list all the customers and assets on a pre defined
specific route.
the sub report should then list any orders for the customers by asset for
that route as well as any temporary orders added to the order table but not
in the route/customer/asset table. because the links are set by route by
customer and by asset the report ignores the additions to the order table
that do not have a corresponding customer and asset in the route table. i
need to connect the route info and the oder info by route customer and asset
as well as list any exception orders that are added to the route without a
corresponding customer and asset. when i remove the master/child links i get
way to much info reported.


:
Make sure you have the subreport control's Link Master/Child
Fields properties set as you want them, maybe even clear
them.


cansoft wrote:
i would like to produce a report that prints all records from the main report
and all records from the sub report. currently all the records from the main
report print and only the matching records from the sub report print. how do
i force all the records from the sub report to print.

SQL from main report:
SELECT [OrderList Cashout].VRCRoute, [OrderList Cashout].VRCCustomer,
[OrderList Cashout].VRCSequence, [OrderList Cashout].VRCCashout, [OrderList
Cashout].VRCChanger, [OrderList Cashout].VRCChangerAmt, Assets.AssetNumber,
AssetType.AssetType, Route.RouteName
FROM (AssetType RIGHT JOIN ([OrderList Cashout] LEFT JOIN Assets ON
[OrderList Cashout].VRCCustomer = Assets.AssetCustomer) ON
AssetType.AssetTypeID = Assets.AssetType) LEFT JOIN Route ON [OrderList
Cashout].VRCRoute = Route.RouteNumber
WHERE (((AssetType.SlipExclude)=No))
ORDER BY [OrderList Cashout].VRCSequence;

SQL From Sub-Report:
SELECT LoadOut.LoadOutRoute, LoadOut.LoadOutItem, LoadOut.LoadOutCount,
Products.ItemBin, Products.ItemNumber, LoadOut.LoadOutDate, Route.RouteName,
Products.ItemCat, LoadOut.LoadOutCustomer, LoadOut.LoadOutAsset,
LoadOut.LoadOutSeq, Products.ItemDesc, [OrderList Cashout].VRCSequence,
[OrderList Cashout].VRCCashout, [OrderList Cashout].VRCChanger, [OrderList
Cashout].VRCChangerAmt, AssetType.AssetType, Assets.AssetRouteNote
FROM (((LoadOut LEFT JOIN Products ON LoadOut.LoadOutItem =
Products.ItemNumber) LEFT JOIN Route ON LoadOut.LoadOutRoute =
Route.RouteNumber) LEFT JOIN [OrderList Cashout] ON LoadOut.LoadOutCustomer =
[OrderList Cashout].VRCCustomer) LEFT JOIN (AssetType RIGHT JOIN Assets ON
AssetType.AssetTypeID = Assets.AssetType) ON LoadOut.LoadOutAsset =
Assets.AssetNumber
WHERE (((LoadOut.LoadOutRoute)=[Forms]![DriverOrderDialogue]![RouteNumber])
AND ((LoadOut.LoadOutCount)>0) AND
((LoadOut.LoadOutDate)=[Forms]![DriverOrderDialogue]![startdate]));
Click to expand...
Click to expand...
Click to expand...
 
Seems to be a short coming in the master/child linkage. I think I'll create a
temporary table and merge the records with some VBA code then report on the
result. Was hoping for an easier solution. Oh well not the end of the world.
Thanks for your help.

Marshall Barton said:
The subreport joining mechanism is the Link Master/Child
properties and they are strictly an "inner join" kind of
thing. If that's what you really want, maybe you can use a
second instance of the subreport with only the route
specified in the Link Master/Child properties.

OTOH, maybe you can plug in some dummy customer name in the
exception orders using this kind of thing in the subreport's
query: Nz(LoadOut.LoadOutCustomer,"Exception") and union an
"Exception" customer record in the main report's query.
Similarly for the asset field.
--
Marsh
MVP [MS Access]

i can report on the route/customer table by selecting all customers and
assets for a specific route.
i can report on the order table by selecting all customers and assets by
route for a specific order date.
i then want to merge the two reports into one showing all customers and
assets on a route and any orders the customers may have. the orders table can
have temporary customers added to the route for any specific day without
being added to the route table. the temporary additions do not get selected.
(when the order is added it indicates route customer and asset so i know
which route) Should i create two separate queries and then try to create an
outer join on those queries. i was hoping i could force the outer join on the
subreport??


Marshall Barton said:
Without linking data fields in these temp orders, I don't
see how you can tell if it belongs to the route or not.

I'm just guessing here, but is there some way you can use
some kind of dummy customer and asset values for the
exception orders?


cansoft wrote:
the problem seems to be with the master / child links.
the master report should list all the customers and assets on a pre defined
specific route.
the sub report should then list any orders for the customers by asset for
that route as well as any temporary orders added to the order table but not
in the route/customer/asset table. because the links are set by route by
customer and by asset the report ignores the additions to the order table
that do not have a corresponding customer and asset in the route table. i
need to connect the route info and the oder info by route customer and asset
as well as list any exception orders that are added to the route without a
corresponding customer and asset. when i remove the master/child links i get
way to much info reported.


:
Make sure you have the subreport control's Link Master/Child
Fields properties set as you want them, maybe even clear
them.


cansoft wrote:
i would like to produce a report that prints all records from the main report
and all records from the sub report. currently all the records from the main
report print and only the matching records from the sub report print. how do
i force all the records from the sub report to print.

SQL from main report:
SELECT [OrderList Cashout].VRCRoute, [OrderList Cashout].VRCCustomer,
[OrderList Cashout].VRCSequence, [OrderList Cashout].VRCCashout, [OrderList
Cashout].VRCChanger, [OrderList Cashout].VRCChangerAmt, Assets.AssetNumber,
AssetType.AssetType, Route.RouteName
FROM (AssetType RIGHT JOIN ([OrderList Cashout] LEFT JOIN Assets ON
[OrderList Cashout].VRCCustomer = Assets.AssetCustomer) ON
AssetType.AssetTypeID = Assets.AssetType) LEFT JOIN Route ON [OrderList
Cashout].VRCRoute = Route.RouteNumber
WHERE (((AssetType.SlipExclude)=No))
ORDER BY [OrderList Cashout].VRCSequence;

SQL From Sub-Report:
SELECT LoadOut.LoadOutRoute, LoadOut.LoadOutItem, LoadOut.LoadOutCount,
Products.ItemBin, Products.ItemNumber, LoadOut.LoadOutDate, Route.RouteName,
Products.ItemCat, LoadOut.LoadOutCustomer, LoadOut.LoadOutAsset,
LoadOut.LoadOutSeq, Products.ItemDesc, [OrderList Cashout].VRCSequence,
[OrderList Cashout].VRCCashout, [OrderList Cashout].VRCChanger, [OrderList
Cashout].VRCChangerAmt, AssetType.AssetType, Assets.AssetRouteNote
FROM (((LoadOut LEFT JOIN Products ON LoadOut.LoadOutItem =
Products.ItemNumber) LEFT JOIN Route ON LoadOut.LoadOutRoute =
Route.RouteNumber) LEFT JOIN [OrderList Cashout] ON LoadOut.LoadOutCustomer =
[OrderList Cashout].VRCCustomer) LEFT JOIN (AssetType RIGHT JOIN Assets ON
AssetType.AssetTypeID = Assets.AssetType) ON LoadOut.LoadOutAsset =
Assets.AssetNumber
WHERE (((LoadOut.LoadOutRoute)=[Forms]![DriverOrderDialogue]![RouteNumber])
AND ((LoadOut.LoadOutCount)>0) AND
((LoadOut.LoadOutDate)=[Forms]![DriverOrderDialogue]![startdate]));
Click to expand...
Click to expand...
Click to expand...
 
It may be a shortcoming, but in defense of the people that
created these capabilities, I'm not sure that the
complications involved in providing the kind thing you want
would not make a lot more trouble in more normal situations.

I think your idea should work. However, since I have a
serious aversion to temp tables, I would continue to look
for another approach, but that's just me.

If you do use a temp table, be sure to look into putting it
in a temp database as described at:
http://www.granite.ab.ca/access/temptables.htm

Good luck,
--
Marsh
MVP [MS Access]

Seems to be a short coming in the master/child linkage. I think I'll create a
temporary table and merge the records with some VBA code then report on the
result. Was hoping for an easier solution. Oh well not the end of the world.
Thanks for your help.

Marshall Barton said:
The subreport joining mechanism is the Link Master/Child
properties and they are strictly an "inner join" kind of
thing. If that's what you really want, maybe you can use a
second instance of the subreport with only the route
specified in the Link Master/Child properties.

OTOH, maybe you can plug in some dummy customer name in the
exception orders using this kind of thing in the subreport's
query: Nz(LoadOut.LoadOutCustomer,"Exception") and union an
"Exception" customer record in the main report's query.
Similarly for the asset field.

i can report on the route/customer table by selecting all customers and
assets for a specific route.
i can report on the order table by selecting all customers and assets by
route for a specific order date.
i then want to merge the two reports into one showing all customers and
assets on a route and any orders the customers may have. the orders table can
have temporary customers added to the route for any specific day without
being added to the route table. the temporary additions do not get selected.
(when the order is added it indicates route customer and asset so i know
which route) Should i create two separate queries and then try to create an
outer join on those queries. i was hoping i could force the outer join on the
subreport??


:
Without linking data fields in these temp orders, I don't
see how you can tell if it belongs to the route or not.

I'm just guessing here, but is there some way you can use
some kind of dummy customer and asset values for the
exception orders?


cansoft wrote:
the problem seems to be with the master / child links.
the master report should list all the customers and assets on a pre defined
specific route.
the sub report should then list any orders for the customers by asset for
that route as well as any temporary orders added to the order table but not
in the route/customer/asset table. because the links are set by route by
customer and by asset the report ignores the additions to the order table
that do not have a corresponding customer and asset in the route table. i
need to connect the route info and the oder info by route customer and asset
as well as list any exception orders that are added to the route without a
corresponding customer and asset. when i remove the master/child links i get
way to much info reported.


:
Make sure you have the subreport control's Link Master/Child
Fields properties set as you want them, maybe even clear
them.


cansoft wrote:
i would like to produce a report that prints all records from the main report
and all records from the sub report. currently all the records from the main
report print and only the matching records from the sub report print. how do
i force all the records from the sub report to print.

SQL from main report:
SELECT [OrderList Cashout].VRCRoute, [OrderList Cashout].VRCCustomer,
[OrderList Cashout].VRCSequence, [OrderList Cashout].VRCCashout, [OrderList
Cashout].VRCChanger, [OrderList Cashout].VRCChangerAmt, Assets.AssetNumber,
AssetType.AssetType, Route.RouteName
FROM (AssetType RIGHT JOIN ([OrderList Cashout] LEFT JOIN Assets ON
[OrderList Cashout].VRCCustomer = Assets.AssetCustomer) ON
AssetType.AssetTypeID = Assets.AssetType) LEFT JOIN Route ON [OrderList
Cashout].VRCRoute = Route.RouteNumber
WHERE (((AssetType.SlipExclude)=No))
ORDER BY [OrderList Cashout].VRCSequence;

SQL From Sub-Report:
SELECT LoadOut.LoadOutRoute, LoadOut.LoadOutItem, LoadOut.LoadOutCount,
Products.ItemBin, Products.ItemNumber, LoadOut.LoadOutDate, Route.RouteName,
Products.ItemCat, LoadOut.LoadOutCustomer, LoadOut.LoadOutAsset,
LoadOut.LoadOutSeq, Products.ItemDesc, [OrderList Cashout].VRCSequence,
[OrderList Cashout].VRCCashout, [OrderList Cashout].VRCChanger, [OrderList
Cashout].VRCChangerAmt, AssetType.AssetType, Assets.AssetRouteNote
FROM (((LoadOut LEFT JOIN Products ON LoadOut.LoadOutItem =
Products.ItemNumber) LEFT JOIN Route ON LoadOut.LoadOutRoute =
Route.RouteNumber) LEFT JOIN [OrderList Cashout] ON LoadOut.LoadOutCustomer =
[OrderList Cashout].VRCCustomer) LEFT JOIN (AssetType RIGHT JOIN Assets ON
AssetType.AssetTypeID = Assets.AssetType) ON LoadOut.LoadOutAsset =
Assets.AssetNumber
WHERE (((LoadOut.LoadOutRoute)=[Forms]![DriverOrderDialogue]![RouteNumber])
AND ((LoadOut.LoadOutCount)>0) AND
((LoadOut.LoadOutDate)=[Forms]![DriverOrderDialogue]![startdate]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top