Building peer groups from a form?

[Kevin M.]

I have financial and demographic information for ~10,000 companies for the
last 6 years. My goal is for a user to be able to make custom peer groups of
companies for further analysis. It appears that the best way to be able to
set up a form so that a user could choose options on a form and then run a
query based on the selections made on the form. I'd like to first confirm
that this is possible?

Peer groups could be based on a few factors (State or states, Asset size,
years of data available). Currently, the data is in 6 tables, one for each
year. The 6 tables have the same number of fields. However, the number of
records changes as companies are formed and go out of business. Each table
has the same primary key (Tax ID). Check boxes on the form would designate
which factors to include or exclude. The form would be the criteria for a
make-table query.

My big question at this point is whether this set up (6 tables of annual
data) and the same field being the primary key is a workable one for the
project I'm looking to do. I'm also wondering what type of relationships I
should set up between the tables. It's been almost ten years since I've last
used Access so I'm trying to remember what it's capable of.

 

[Allen Browne]

Well, I have to agree that's a big question, Kevin.

A separate table for each year is not ideal. Better to have them all in one
table, with date fields to indicate when the company joined/left the group.
So you will have these 3 tables:
- Company table, with CompanyID primary key

- PeerGroup table, with PeerGroupID primary key

- CompanyInPeerGroup table, with fields:
CompanyInPeerGroupID AutoNumber (pk)
CompanyID relates to Company.CompanyID
PeerGroupID relates to PeerGroup.PeerGroupID
JoinDate when the company was added to this group
DepartDate when the company left this group
(Blank if still current.)

The interface will be a main form bound to the Company table, with a subform
bound to the CompanyInPeerGroup table. The subform will be in continuous
view, so you can add as many rows as you need. It will have a combo box for
PeerGroupID.

If you need a more involved structure, where companies can have different
kinds of grouping within them (BOM, employees, mailing lists), and the
groupings can contain both companies and persons, there's a sample database
illustrating how to do that here:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html

 

[Kevin M.]

Allen,
Thanks for your prompt response. I think my database is of the "more
involved structure" variety. Here's an update:

-Sadly, I might be forced to maintain the seperate table format because of
the way the data comes in.
-I figured out how to create a query which lists all the unique TaxIDs from
all the years, along with the Asset size from each year, and some expressions
which return 1 or 0 if the company had assets > 0 (was in existence) for that
particular year.
-I'm in the early stages of conceiving a form to toggle which groups of data
to include in the query, which should probably output to a table of peers.

I'll keep you posted. Thanks again for your help! I'll probably be back.
Kevin