Complex Table Design


G

Guest

I am working on a complex database to store Business information as well as
specific into about that Business... here is the tricky part...

The individual companies are sorted by Business type (i.e. Computer Software
Companies, Child Care Providers, Commercial Building Contractors, etc.) and
each Business type needs to hold a simi-customized set of information (i.e.
Commercial Building Contractors needs Total AZ Billings for Commercial Bldg.,
$ Awarded for Completed Contracts, % of Work Subcontracted, No. of Local
Employees, and Areas of Specialty). I will refer to each type of information
as a Category. Each Business Type has between 4 and 7 Categories. Within a
Business Type there are between 10 and 30 companies listed. There will be
just over 200 Business Types listed within my database structure, as well as
around 100 unique types of Categories.

Primarily, I need to be able to query and format each Business Type, to
include the Companies information as well as each Category that is associated
with the Business in a flat spreadsheet style layout.

Now, I intend to create a unique report (if needed) for each Business Type
as needed to customize the look (i.e. column widths and any other special
formatting needed) of each Business Type,

Here is where I stand currently with this project…

I have 4 tables,
1. Business (it includes basic contact info that is common to all business)
2. Category (is basically a list of all possible categories)
3. BusinessCategory (a Many-to-Many relationship container that links the
BusinessID to the CategoryID and includes the corresponding Value)
4. BusinessType (a list of business types that a business can belong to)

This structure seems to work in that it can handle the customization of
categories for each Business within a BusinessType. The problem I am running
into with this structure is creating a query to handle the data and create a
“flat†table for a BusinessType with each Business and it’s categories.

It may be that I can use this structure to do what I need done, however I
think that there must be a better way to structure my data to work the way I
need it to work. I really appreciate your input on this!
 
Ad

Advertisements

D

Duane Hookom

I like your table structure but not necessarily your business requirements.
Have you looked at creating crosstab queries of unique business types to
display the categories as column headings?

There are some crosstab reports that might help at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The Crosstab.mdb file
has a crosstab report that allows for dynamic column headings.
 
Ad

Advertisements

G

Guest

This seems to work... If I query by a business type I can pull the info into
a flat table with the columns correctly labeled! Thanks! This will work
perfectly!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top