Setting Up a New Database for Projects & Contracts

[plog]

Can a Contract be for multiple reservations?

Can a Project be for multiple reservations?

Can a Contract/Project be for multiple reservations?

 

[Weekleyba]

No, for all three questions.

 

[plog]

Since a project must be attached to a contract, I believe that the Reservation should be attached to a Contract. So ReservationID would only be in that table.

 

[Weekleyba]

The project is created first for a reservation. Only after a project is fully designed, will a contract be awarded. So, does it make sense for the reservation to be attached to the project table?

 

[plog]

Sorry, I misspoke in #23.

Since a contract must be attached to a project then I believe a Reservation should be attached to a Project. ReservationID would only be in the Project table.

 

[Weekleyba]

Roger that. I'll play around with this tonight. Time for my other job.
Thanks for all the help!

 

[Mark_]

In relations
ADD ReservationT.
Create 1 to many linkes between Reservations (one) and project/contract (many)

Remove "ReservationID" from JUNCT_ProjectContractT

Suggestion for your naming conventions, rather than putting the type of object at the end (ReservationT), you will find it often more useful to have it at the front and separated (T_Reservation) so that you can immediately and easily distinguise which type of object you are working with.

This would allow your junction table to be
TJ_ProjectContract (T for Table, J for Junction)

Being consistent in how you name objects reduces a LOT of confusion later.

 

[Weekleyba]

Ok, I've made the revisions and came up with the attached design.
This works but adding a ContractNumer is a bit cluncky.
Since PK for ContractT is the ContractID (autonumber), if I add a duplicate ContractNumber, it generates another ContractID. Not what I want.
So, converted the ContractNumber text box into a combo box and added a List Items Edit Form.
Now the user can either select an existing ContractNumber from the dropdown or enter a new ContractNumber via the edit form.

Does anyone have a better way of doing this?

I was hoping to add a ContractNumber without the edit form but, since the combo is bound to the ID and the ContractNumber, Access won't let me.
I could have changed the bound column to the ContractNumber, but I think there is a chance that a ContractNumber could be repeated in the distant future.

Does my design, look solid so far?
Thanks for any advice.

 

[Weekleyba]

The current design has Reservations as the main form (F_Main) and projects and contracts as subforms. (F_Project, F_Contract)
I would like to create two combo boxes to search for records for the all projects and one for all contracts and place the command button on the main form.
Can someone give me some help achieving that?
I'm thinking it has to do with the After Update event and in the Where Condition but, I'm stubbed as to what to put.
Thanks for any help.