Table-SubTable-Form-Query Quandary

I'm working in Access 2003 but will need to convert this back to Access 97
for some users.

I put this question in Database Design, because I'm all tied up in tables,
forms, subforms and queries, and I'm not sure which one is exactly involved
here. I'm sure this is a novice question, but it's hard to know where to
look.

I have a database for contracts.

I have tables for contractors, projects, Contracts, Details, bids, and
detailed bids.

I need to create a form which will let me show a project and enter the
detailed bids for each contract.

I'm getting confused, because Contracts is a subtable to projects, and
Details is a subtable to Contracts. It makes sense conceptually to me that
DetailedBids is a subtable to BidDetails (Each detail receives a bid from
each contractor bidding.)

So it would be straightforward to design this so that someone could take
each Detail, and go through each bidder, entering the assorted detailed bids.
But that's not what happens in real life. In real life, one complete bid
from a bidder is read, with all of the details coming one after another. So
my form has to show the previously entered details, and let someone enter
values for each bid, one at a time. I'm sure this is do-able, but I'm
floundering.

What am I missing here?

Thanks

Contractors
Projects
Contracts
Details
Bids
DetailedBids

Sounds like you need:

frmContractors

frmProjects
+---subform frmContracts (ProjectID as foreign key)
+-----subform frmDetails (ContractID as foreign key)

frmBids
+---subform frmDetailedBids (BidID as foreign key)



"r. howell" wrote:

I'm working in Access 2003 but will need to convert this back to Access 97
for some users.

I put this question in Database Design, because I'm all tied up in tables,
forms, subforms and queries, and I'm not sure which one is exactly involved
here. I'm sure this is a novice question, but it's hard to know where to
look.

I have a database for contracts.

I have tables for contractors, projects, Contracts, Details, bids, and
detailed bids.

I need to create a form which will let me show a project and enter the
detailed bids for each contract.

I'm getting confused, because Contracts is a subtable to projects, and
Details is a subtable to Contracts. It makes sense conceptually to me that
DetailedBids is a subtable to BidDetails (Each detail receives a bid from
each contractor bidding.)

So it would be straightforward to design this so that someone could take
each Detail, and go through each bidder, entering the assorted detailed bids.
But that's not what happens in real life. In real life, one complete bid
from a bidder is read, with all of the details coming one after another. So
my form has to show the previously entered details, and let someone enter
values for each bid, one at a time. I'm sure this is do-able, but I'm
floundering.

What am I missing here?

Thanks