Submittal Tacking Table (1 Viewer)

andy1968

Registered User.
Local time
Yesterday, 23:11
Joined
May 9, 2018
Messages
131
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?
 

Attachments

  • Capture.JPG
    Capture.JPG
    36.7 KB · Views: 89

NauticalGent

Ignore List Poster Boy
Local time
Today, 02:11
Joined
Apr 27, 2015
Messages
6,286
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've got your good things, and you've got mine.
Local time
Today, 02:11
Joined
May 21, 2018
Messages
8,463
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

Registered User.
Local time
Yesterday, 23:11
Joined
May 9, 2018
Messages
131
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

Registered User.
Local time
Yesterday, 23:11
Joined
May 9, 2018
Messages
131
Yes, I use a subform.


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

NauticalGent

Ignore List Poster Boy
Local time
Today, 02:11
Joined
Apr 27, 2015
Messages
6,286
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

bit cruncher
Local time
Yesterday, 23:11
Joined
Mar 17, 2004
Messages
8,178
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

AWF VIP
Local time
Yesterday, 23:11
Joined
Apr 13, 2010
Messages
1,401
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
 

Users who are viewing this thread

Top Bottom