Many-to-many relationship

[Tim1217]

Hi,

I have a table that includes information about client projects that assist
them with finding a new service provider through a bidding process. I have a
field for the winning provider (related to the Providers table) but I'd also
like to have information about the 4-5 finalists, all those that returned
the RFP, etc. I assume I need a many-to-many relationship by creating a
third table called Finalists. If that's correct, my question is; how does
that table get populated? Do you actually need to go into the table and
manually populate it? That doesn't sound efficient. The MS example in the
Step-By-Step book uses a order table and a products table with a
many-to-many relationship with a table called order details. unfortunately,
the book doesn't explain how th etable gets populated.

tim1217

 

[David Cleave]

Hello

Working out how to model work processes is the fun part of
Access and very much down to personal interpretation.

I would personally use three tables. One would be "Bidding
Processes", where each record describes an instance of a
service going to tender. This record would describe the
service which is going to tender, list timescales, people
responsible for managing the process etc.

The second table would be the "Providers" table, which I
assume you have already created.

The third table would be a junction table between the
first two, i.e. it would be on the many side of a one-to-
many relationship with each of the other two. This table
would be called "Bids", and each record would represent a
bid put in for a particular service by a particular
provider. Thus, each "Bidding Processes" record could be
linked to many "Bids", and each Provider could also be
linked to many "Bids". The table would be updated as the
bid process progresses.

Include a field in the "Bids" table called something
like "Successful", which basically says "Yes" or "No" (you
could use the Yes/No data type if you like).

These three tables would then allow you list, for each
bidding process, a number of providers who submitted bids,
and if they were successful.

I hope, after a little thought, this makes sense!

Thanks

David Cleave
www.logistics.nhs.uk

 

[Tim1217]

Hi David,

Thanks for your quick reply. I was with you up until the point where you
say; "The table would be updated as the bid process progresses." I assume
this means I have to update the table manually as new information is
received, correct? If so, it seems a bit tedius. As I stated in my original
note, the MS Access Step-By-Step example uses a company that has customers,
products and orders. The order table can't predict how many products any
customer may buy, so it has a many-to-one relationship with a table called
"order details" (as does the "products" table thus giving the order table
and the products table a many-to-many relationship).

What I'm having trouble believing is that the salesperson completing the
order has to work in one table (order) to record some of the information and
then open another table (order detail) to record the rest of the
information.

One thought I have is that this might be accomplished behind the scenes via
a form that enters some information in one table and some information in
another??

Tim