Grouping problem - bit tricky

  • Thread starter Thread starter Piers 2k
  • Start date Start date
P

Piers 2k

Hi folks,

Can't seem to wrap my head around this one... db idea is Companies and
services offered.
Table of data includes loads of Yes/No fields, including cities covered and
facilities offered.
e.g.
Cardiff Yes/No
Bridgend Yes/No
Swansea Yes/No
etc...
Walking Yes/No
Golf Yes/No
etc...

I am trying to create a summarized report. What I CAN do: In report footer,
I can get
Walking 4
Golf 7
etc...

What I CAN'T get: I want is to group the report by the regions that are also
available in the db
All
Walking 4
Golf 7
Cardiff
Walking 3
Golf 5
Bridgend
Walking 4
Golf 7
etc...

I don't mind hard coding the regions - there's about 12 of them, and they
won't change, but how can you group in this manner?
The DB is new, so I can change the design if needs be.

Anyone out there?

TIA,
Piers
 
I can't see this being a normalisation issue, since there is no information
being duplicated for each client. Different clients offer different services
in different areas. Would creating a 1-to-1 relationship for some of the
data enable me to do some grouping? If so, I can't see how.

Any pointers?

Piers
 
I would create tables like the following which would allow for the addition
of services or cities without changing any tables, queries, forms,
reports,... This structure should also provide much greater flexibility in
querying and reporting.

tblCompanies
===============
cmpCmpID autonumber primary key
cmpCompanyName
cmp...other fields....

tblServices
=============
srvSrvID autonumber primary key
srvService (values like Walking, Golf,....)

tblCities
============
citCitID autonumber primary key
citCity city name

tblCompanyServices (one record per service per company)
=============
csrCSrID autonumber primary key
csrCmpID link to tblCompanies.cmpCmpID
csrSrvID link to tblServices.srvSrvID
csrComments

tblCompanyCities (one record per city per company)
============
cciCCiID autonumber primary key
cciCmpID link to tblCompanies.cmpCmpID
cciCitID link to tblCities.citCitID
 
Back
Top