Submittal Tacking Table

[andy1968]

I have a database that I am using to track submittals on construction projects.



I am struggling with how to handle resubmittals.


If the submittal is not accepted, there would need to be a resubmittal related to the original.


Attached is a shot of the relationships. The Status field is what I use to record if the submittal is "Approved" or "Rejected".



How would I link a new submittal to a rejected one from the same table?

 

[NauticalGent]

Are you using a form with a linked sub form to record these transactions? I assume you are...

Also, is there a reason you have two primary keys in tblSubmittals?

 

[MajP]

You can have a self referencing table. Lets assume for simplicity you had a single Primary Key called SubmittalID. In the same table you could have PreviousSubmittalID_FK. It is a foreign key in the same table relating to a SubmittalID. This is how you can build hierarchical data.

 

[andy1968]

Thanks!



I like that idea, however, most submittals will not need a resubmittal, so most of the PreviousSubmittalID_FK would be blank. Not the worst, I guess.

 

[andy1968]

Yes, I use a subform.


The 2 key fields are needed because I have multiple jobs that I am tracking.

 

[NauticalGent]

I see what you are trying to do, but I disagree with the way you are going about it. MajP’s advice is your best bet at this time...

 

[MarkK]

If the submittal is not accepted, there would need to be a resubmittal related to the original.

This sounds to me like a One-To-Many relationship between a "Project" object and a "Submission" object in which one Project might have one or more Submissions.

It is not clear how the second submission, from your description, is related to the previous one, but would it be sufficient for it to be related via the parent Project as a sibling?

But to me you have one Project (parent), and many Submissions (children).

hth
Mark

 

[bastanu]

I would split the tblSubmittals in two, leaving the fields pertaining to the submittal itself such as description, notes, number and move the rest (reviewer, dateof submittal, status,reviewDate, link to file) to a new table tblSubmittalActivity or something similar. That way you can track multiple submittal attempts along with their associated files; to get the latest status you would use a totals query to get the max of the submittal date. I would use a SubmittalID autonumber as the primary key for tblSubmittal and SubmittalID_FK in the second one to link them.

Cheers,
Vlad