Question Newbie Help - relationships between tables

[Bloodrayne]

Hi all,

I've fallen horrible out of practice with Access over the past few years (and only had a basic grasp of it when I knew what to do); but I think that this is a basic question about database relationships; and if it becomes too complex I'll end up digging out the old books to re-educate myself.

Basically, I want to make a database of competitors and the projects they've tendered for and won/lost - so I was thinking that I would have three separate tables:

• Company details - name, address, projects won/lost
• Project details - name, value, sector
• Consortium details - details of any consortium of companies that bid for a particular piece of work, and who were the lead, and who were the members - fields would be lead firm and consortium members.

So, I'd think that I can have a relationship from Company details to Project details in order to populate the project details in that table, and then I would link the consortium details to relevant projects again; so that when viewing the projects you could see who had won.

Does that make sense and is it logical? Is there already any gaping holes that someone can spot?

I've not got access to Access until this weekend, so wanted to think this through as much as possible to make good use of my time.

Thanks!

 

[spikepl]

You have Companies, Consortia, Projects, and Bids.

A Consortium consists of one or more companies, of which one is the lead
A Bid on a specific Project is done by one or more consortia, of which one is the winner

Enough hints?

 

[Bloodrayne]

In my head, I can see how that would work now - however, when I sit down at Access it may all become a horrible confused mess.

But certainly it gives me a start - thanks for this!

 

[spikepl]

perhaps a slight rephrasing to avoid confusion:

For a specific project, one or more bids exist, each provided by one consortium. One bid is the winner

 

[Bloodrayne]

Not sure if I should start a new topic for this or not, but thought I'd ask here first so people can see the history.

The suggestion is perfect; works fine - I'm just curious as to how flexible things will be for reporting, I'd like to generate reports that comment on how often a firm partners with another (both as a lead and as a consortium member) and wondered if this would be feasible with my current set up.

Basically, I've set up my tables as:

Company as just that, details about the Company that would only be updated when contact details changed.

Contracts - title, value, who it was awarded to and who was shortlisted

Consortium - used it to tie projects into consortiums so each field is a look-up to another table (lead firm, consortium members, project) - only the consortium members option would allow multiple values.

Bid - matched projects to consortiums; not sure if this is a duplicate of the above.

Any thoughts/guidance appreciated!

 

[spikepl]

I thnk you intermingle data instead of keeping separate data separately.


tblCompanies
--------------
CompanyID
CompanyName
other details

tblConsortia (here you can have many companies belonging to one consortium The table lists all consortia)
------------
ConsortiumID
CompanyID
perhaps a Boolean to mark the Lead company in the consortium

tblContracts
------------
ContractID
other contract data

tblBids (for each ContractID, you can list one or more consortia)
--------
BidID
ContractID - so for oemn contarct you can have many bids
ConsortiumID so each bid is offered for a specific contarct by a specific consortium
perhaps a Boolean marking the Winner for a given Contract

 

[spikepl]

If a company can belong to more than one consortium then we need more tables:


tblCompanies
--------------
CompanyID
CompanyName
other details

tblConsortia
------------
ConsortiumID
other details about the consortium

tblConsortiaCompanies (so you can define which companies make up which consortium)
------------------------
CCId
ConsortiumID
CompanyID

tblContracts
------------
ContractID
other contract data

tblBids (for each ContractID, you can list one or more consortia)
--------
BidID
ContractID - so for one contract you can have many bids
ConsortiumID so each bid is offered for a specific contarct by a specific consortium
perhaps a Boolean marking the Winner for a given Contract

 

[Bloodrayne]

Sorry - only just been able to get back online today; this looks good will give it a go and see if I can work things out