Multiple lines from different tables in a report detail
in [Reports]
|
Prev: Prevent users from accessing report design mode
Next: Need help: table with all locations AND just one location
From: Ben Slater Ben on 12 May 2010 10:14 I'm not sure if I can easily describe what I'm trying to do here, but here goes... Let's say I'm trying to make a report that describes the vehicles each person in my database owns. So I have a table for each person, a table for cars, and a table for pickup trucks. There's a one to many relationship from each person to the car table, and from person to trucks. The car and truck tables don't have the same fields, so I need them to be different tables. I want my report to look like this: Person Car Name Car Mileage Truck Name Cargo Capacity Ben Dodge Colt 50mpg Chevy S-10 40 cubic ft Ben Ford Aspire 34mpg Mary Ford F-150 100 cubic ft Mary Dodge Ram 90 cubic ft All this data is in SQL Server, I'm just using Access to generate a report, so I can use stored procedures, views or anything to get my results. Does anyone have any advice for me?
From: Duane Hookom on 12 May 2010 10:45 I would use a main report based on the person table and subreports for the other tables. I am going to take your word for "so I need them to be different tables" since I doubt this is actually about cars and trucks :-) -- Duane Hookom Microsoft Access MVP "Ben Slater" wrote: > I'm not sure if I can easily describe what I'm trying to do here, but here > goes... > > Let's say I'm trying to make a report that describes the vehicles each > person in my database owns. So I have a table for each person, a table for > cars, and a table for pickup trucks. There's a one to many relationship from > each person to the car table, and from person to trucks. The car and truck > tables don't have the same fields, so I need them to be different tables. I > want my report to look like this: > > Person Car Name Car Mileage Truck Name Cargo Capacity > Ben Dodge Colt 50mpg Chevy S-10 40 cubic ft > Ben Ford Aspire 34mpg > Mary Ford F-150 100 > cubic ft > Mary Dodge Ram 90 cubic > ft > > All this data is in SQL Server, I'm just using Access to generate a report, > so I can use stored procedures, views or anything to get my results. Does > anyone have any advice for me?
From: Ben Slater on 12 May 2010 12:16 Ah, subreports look promising. Thanks. The easiest way is to make a seperate subreports for the car and truck tables, right? And you're correct about the data not really being cars and trucks, but I wanted to keep my example simple. :) "Duane Hookom" wrote: > I would use a main report based on the person table and subreports for the > other tables. > > I am going to take your word for "so I need them to be different tables" > since I doubt this is actually about cars and trucks :-) > -- > Duane Hookom > Microsoft Access MVP > > > "Ben Slater" wrote: > > > I'm not sure if I can easily describe what I'm trying to do here, but here > > goes... > > > > Let's say I'm trying to make a report that describes the vehicles each > > person in my database owns. So I have a table for each person, a table for > > cars, and a table for pickup trucks. There's a one to many relationship from > > each person to the car table, and from person to trucks. The car and truck > > tables don't have the same fields, so I need them to be different tables. I > > want my report to look like this: > > > > Person Car Name Car Mileage Truck Name Cargo Capacity > > Ben Dodge Colt 50mpg Chevy S-10 40 cubic ft > > Ben Ford Aspire 34mpg > > Mary Ford F-150 100 > > cubic ft > > Mary Dodge Ram 90 cubic > > ft > > > > All this data is in SQL Server, I'm just using Access to generate a report, > > so I can use stored procedures, views or anything to get my results. Does > > anyone have any advice for me?
From: Duane Hookom on 12 May 2010 20:02
Yes, you could create separate subreports for cars and trucks. If you include cars in your main report, the trucks would be a subreport in the person group header or footer section. -- Duane Hookom Microsoft Access MVP "Ben Slater" wrote: > Ah, subreports look promising. Thanks. The easiest way is to make a > seperate subreports for the car and truck tables, right? > > And you're correct about the data not really being cars and trucks, but I > wanted to keep my example simple. :) > > "Duane Hookom" wrote: > > > I would use a main report based on the person table and subreports for the > > other tables. > > > > I am going to take your word for "so I need them to be different tables" > > since I doubt this is actually about cars and trucks :-) > > -- > > Duane Hookom > > Microsoft Access MVP > > > > > > "Ben Slater" wrote: > > > > > I'm not sure if I can easily describe what I'm trying to do here, but here > > > goes... > > > > > > Let's say I'm trying to make a report that describes the vehicles each > > > person in my database owns. So I have a table for each person, a table for > > > cars, and a table for pickup trucks. There's a one to many relationship from > > > each person to the car table, and from person to trucks. The car and truck > > > tables don't have the same fields, so I need them to be different tables. I > > > want my report to look like this: > > > > > > Person Car Name Car Mileage Truck Name Cargo Capacity > > > Ben Dodge Colt 50mpg Chevy S-10 40 cubic ft > > > Ben Ford Aspire 34mpg > > > Mary Ford F-150 100 > > > cubic ft > > > Mary Dodge Ram 90 cubic > > > ft > > > > > > All this data is in SQL Server, I'm just using Access to generate a report, > > > so I can use stored procedures, views or anything to get my results. Does > > > anyone have any advice for me? |