absolutely dumbfounded with how to structure these two tables

[Gina]

I need to create a database that will track a variety of
information on our customers and competitors. (I work for
a software developer- how I got stuck doing this is truly
a mystery- except I think the developers believe creating
a little access database is beneath them, ugh).

I have created an table that will list all of the
companies, and has a checkbox to indicate whether they
are a customer of ours or not.

Where I am becoming confused is on the "vendor"
information. Each of these companies, regardless of
whether they are a customer of ours or not, uses vendors
for specific processes (7 or so that we'll track- and
each process will only have one vendor-product-version
combination). We could be their vendor, and they could
have purchased any one of three products, in any of up to
5 release versions each. OR they could have purchased
products for each of their processes from any other
vendor.


1. I'm not sure how to set this up. I've created
a "Vendor" table, and have assigned the fields:

CompanyID Process Vendor Product Version

It would be really nice if every time a new company was
added to the company table, the second table would have
their company ID, and 7 rows with Process 1 - 7
automatically showing, so that only the vendor, product,
and version had to show.

I'm absolutely stuck. I don't know what to do from
here. Please help?

Gina

 

[Lynn Trapp]

how I got stuck doing this is truly

a mystery- except I think the developers believe creating
a little access database is beneath them, ugh).

Gina,
What it reallly means is that developing an Access database is quite ABOVE
your development team and they are unwilling to admit it. They gave the task
to the person in the company that they knew would get the job done, no
matter what it takes.

It would be really nice if every time a new company was
added to the company table, the second table would have
their company ID, and 7 rows with Process 1 - 7
automatically showing, so that only the vendor, product,
and version had to show.

If I understand what you want, I don't really think you want that to happen
unless you are going to be tracking 7 processes for EVERY Company ID. That
would cause you to end up storing unnecessary information in your vendor
table (which I would actually rename to Processes because you are really
storing information about processes, not vendors). It's pretty easy to
create a subform that would be tied to the Company table and have a combo
box to select the Processes that you need to track for each company.

Does that all make sense? If not, feel free to write back for more
information.

 

[Guest]

Unfortunately, I do have to track this information for
each of the seven processes at each of the facilities.
And, they've decided that they want to see the nulls, as
well as when we know the inforamation about which
vendors/products/etc. are known about these processes.

Gina

 

[Lynn Trapp]

In that case, you will probably need to write something in the AfterInsert
event of your form to insert a record in the CompanyProcesses table for each
of the 7 Processes.