food for thought PeoplePlacesThings

  • Thread starter Thread starter me
  • Start date Start date
M

me

Here's some food for thought.

If a person can have places and things, what's a good way to summarize both
for a printed report? The union select I made below works if report grouping
is used. This type of solution works well when the items have a similar data
structure like in this case the places and things all just have a name
field.

Here's the problem, what if the things you are summarizing are very
different. What if places has say 10 fields and things has say 5 fields and
they're all different?

I suppose just two different queries without the union between them is
better then but then we need a reporting system that can show two recordsets
in one report. The report also looses some clarity because its hard to see
all the places and things that go with each person because you have to look
at two different lists.

What do you think?

I realize this is an access area but I've posted some SQL Server code to
help illustrate my point.

Darcy



--------------------------
use testDW

if exists (select * from sysobjects where id = object_id(N'[dbo].[aPeople]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aPeople]
if exists (select * from sysobjects where id = object_id(N'[dbo].[aPlaces]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aPlaces]
if exists (select * from sysobjects where id = object_id(N'[dbo].[aThings]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aThings]
GO

CREATE TABLE [dbo].[aPeople] (
[intID] [int] IDENTITY (1, 1) NOT NULL ,
[vchName] [varchar] (50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[aPlaces] (
[intID] [int] IDENTITY (1, 1) NOT NULL ,
[intPerson] [int] NULL ,
[vchName] [varchar] (50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[aThings] (
[intID] [int] IDENTITY (1, 1) NOT NULL ,
[intPerson] [int] NULL ,
[vchName] [varchar] (50) NULL
) ON [PRIMARY]
GO

insert into aPeople values("Darcy")
insert into aPeople values("Caroline")
insert into aPeople values("Joseph")

insert into aPlaces values(1, "Dana")
insert into aPlaces values(1, "Ottawa")
insert into aPlaces values(1, "Moisie")
insert into aPlaces values(2, "England")
insert into aPlaces values(3, "London")
insert into aPlaces values(3, "Vancouver")
insert into aPlaces values(3, "Ottawa")

insert into aThings values(1, "Planes")
insert into aThings values(1, "Traines")
insert into aThings values(1, "Automobiles")
insert into aThings values(2, "Golf Clubs")
insert into aThings values(3, "Tennis Racket")
insert into aThings values(3, "Gliders")

--select * from aPeople
--select * from aPlaces
--select * from aThings

--////////////////////////
--Show peoples places
--////////////////////////
select
aPeople.vchName as Person
,aPlaces.vchName as Place
from aPeople
left join aPlaces on aPlaces.intPerson = aPeople.intID

--////////////////////////
--Show peoples things
--////////////////////////
select
aPeople.vchName as Person
,aThings.vchName as Thing
from aPeople
left join aThings on aThings.intPerson = aPeople.intID

--////////////////////////
-- Show peoples places AND things
-- This doesn't work well since it
-- implies a hierarchy that doesn't exist
--////////////////////////
select
aPeople.vchName as Person
,aPlaces.vchName as Place
,aThings.vchName as Thing
from aPeople
left join aPlaces on aPlaces.intPerson = aPeople.intID
left join aThings on aThings.intPerson = aPeople.intID

--////////////////////////
-- Show peoples places AND things
-- This works but requires each entity
-- to have the same fields
--////////////////////////
select
aPeople.vchName as Person
,"Place" as RowType
,aPlaces.vchName as Item
from aPeople
left join aPlaces on aPlaces.intPerson = aPeople.intID
union select all
aPeople.vchName as Person
,"Thing" as RowType
,aThings.vchName as Item
from aPeople
left join aThings on aThings.intPerson = aPeople.intID

order by person, RowType
 
Use a subreport for at least one of the types of things a person can have.

--
Duane Hookom
MS Access MVP


me said:
Here's some food for thought.

If a person can have places and things, what's a good way to summarize both
for a printed report? The union select I made below works if report grouping
is used. This type of solution works well when the items have a similar data
structure like in this case the places and things all just have a name
field.

Here's the problem, what if the things you are summarizing are very
different. What if places has say 10 fields and things has say 5 fields and
they're all different?

I suppose just two different queries without the union between them is
better then but then we need a reporting system that can show two recordsets
in one report. The report also looses some clarity because its hard to see
all the places and things that go with each person because you have to look
at two different lists.

What do you think?

I realize this is an access area but I've posted some SQL Server code to
help illustrate my point.

Darcy



--------------------------
use testDW

if exists (select * from sysobjects where id = object_id(N'[dbo].[aPeople]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aPeople]
if exists (select * from sysobjects where id = object_id(N'[dbo].[aPlaces]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aPlaces]
if exists (select * from sysobjects where id = object_id(N'[dbo].[aThings]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aThings]
GO

CREATE TABLE [dbo].[aPeople] (
[intID] [int] IDENTITY (1, 1) NOT NULL ,
[vchName] [varchar] (50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[aPlaces] (
[intID] [int] IDENTITY (1, 1) NOT NULL ,
[intPerson] [int] NULL ,
[vchName] [varchar] (50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[aThings] (
[intID] [int] IDENTITY (1, 1) NOT NULL ,
[intPerson] [int] NULL ,
[vchName] [varchar] (50) NULL
) ON [PRIMARY]
GO

insert into aPeople values("Darcy")
insert into aPeople values("Caroline")
insert into aPeople values("Joseph")

insert into aPlaces values(1, "Dana")
insert into aPlaces values(1, "Ottawa")
insert into aPlaces values(1, "Moisie")
insert into aPlaces values(2, "England")
insert into aPlaces values(3, "London")
insert into aPlaces values(3, "Vancouver")
insert into aPlaces values(3, "Ottawa")

insert into aThings values(1, "Planes")
insert into aThings values(1, "Traines")
insert into aThings values(1, "Automobiles")
insert into aThings values(2, "Golf Clubs")
insert into aThings values(3, "Tennis Racket")
insert into aThings values(3, "Gliders")

--select * from aPeople
--select * from aPlaces
--select * from aThings

--////////////////////////
--Show peoples places
--////////////////////////
select
aPeople.vchName as Person
,aPlaces.vchName as Place
from aPeople
left join aPlaces on aPlaces.intPerson = aPeople.intID

--////////////////////////
--Show peoples things
--////////////////////////
select
aPeople.vchName as Person
,aThings.vchName as Thing
from aPeople
left join aThings on aThings.intPerson = aPeople.intID

--////////////////////////
-- Show peoples places AND things
-- This doesn't work well since it
-- implies a hierarchy that doesn't exist
--////////////////////////
select
aPeople.vchName as Person
,aPlaces.vchName as Place
,aThings.vchName as Thing
from aPeople
left join aPlaces on aPlaces.intPerson = aPeople.intID
left join aThings on aThings.intPerson = aPeople.intID

--////////////////////////
-- Show peoples places AND things
-- This works but requires each entity
-- to have the same fields
--////////////////////////
select
aPeople.vchName as Person
,"Place" as RowType
,aPlaces.vchName as Item
from aPeople
left join aPlaces on aPlaces.intPerson = aPeople.intID
union select all
aPeople.vchName as Person
,"Thing" as RowType
,aThings.vchName as Item
from aPeople
left join aThings on aThings.intPerson = aPeople.intID

order by person, RowType
Click to expand...
 
Back
Top