Setting Up a New Database for Projects & Contracts (1 Viewer)

P

plog

Banishment Pending

Local time

Today, 04:14

Joined

May 11, 2011

Messages

11,638

• Dec 11, 2017

• #21

Can a Contract be for multiple reservations?

Can a Project be for multiple reservations?

Can a Contract/Project be for multiple reservations?

 

W

Weekleyba

Registered User.

Local time

Today, 04:14

Joined

Oct 10, 2013

Messages

586

• Dec 11, 2017

• #22

No, for all three questions.

 

P

plog

Banishment Pending

Local time

Today, 04:14

Joined

May 11, 2011

Messages

11,638

• Dec 11, 2017

• #23

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.

 

W

Weekleyba

Registered User.

Local time

Today, 04:14

Joined

Oct 10, 2013

Messages

586

• Dec 11, 2017

• #24

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?

 

P

plog

Banishment Pending

Local time

Today, 04:14

Joined

May 11, 2011

Messages

11,638

• Dec 11, 2017

• #25

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.

 

W

Weekleyba

Registered User.

Local time

Today, 04:14

Joined

Oct 10, 2013

Messages

586

• Dec 11, 2017

• #26

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

 

M

Mark_

Longboard on the internet

Local time

Today, 02:14

Joined

Sep 12, 2017

Messages

2,111

• Dec 11, 2017

• #27

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.

 

W

Weekleyba

Registered User.

Local time

Today, 04:14

Joined

Oct 10, 2013

Messages

586

• Dec 12, 2017

• #28

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.

 

Attachments

• SFC Test 4.zip

  39.2 KB · Views: 59

W

Weekleyba

Registered User.

Local time

Today, 04:14

Joined

Oct 10, 2013

Messages

586

• Dec 14, 2017

• #29

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.