Submittal Log Database

[rblair11]

I'm having difficulty designing the structure of a datebase that will track product submittals. My submittal table has the following fields:

tblSubmittals:
Submittal_ID (Autonumber) (Primary)
Type
Contractor
Description
Status
Revision#
Comments

Here's my dilemma. If the submittal is approved, great. If it is not, it's status would be "waiting for re-submittal" or "waiting for more info" etc. It would be easy to create a query to identify open submittals (any that are not "approved"), but how would I update the submittal as "approved" without losing any of the revisions.

For example, lets say on the 4th revision the submittal is finally approved. How would I save all 4 revisions and not show 1-3 as open?

My thought is to add another field that is simply Open/Closed. As submittals 2-4 come in I would click on the latest revision, have another record automatically created. The proper revision number would be updated by 1 , the comments and status field would be blank, and the other info would be copied. Once the 4th submittal is approved, it would mark the other 3 revisions as "closed" but all other fields would remain unchanged.

If that seems like a resonable approach, I need help making it happen (i.e. how to have the revision number update, how to have other fields copied, how to eventually mark all revisions closed).

Please let me know if I need to provide the group with additional info. Thanks.

 

[Sergeant]

Maybe you should make another table called tblRevisions and relate it to this table. Then you could use that for your subform and update/add/close out revision records on that.
Move the Status, Revision_No (Don't use that '#' symbol for your field name), and Comments fields to this new table. It would be like:

tblRevisions:
RevisionID (PK, AN)
Submittal_ID (FK on tblSubmittals.Submittal_ID)
Submitted_Date (Date/Time)
Revision_No (Integer)
Status (Text...Perhaps looked up from a status table)
Status_Date (Date/Time)
Comments (Memo?)
Revision_Closed (Yes/No)

That way, you have a nice relational setup for your data which maintains the history of revisions and doesn't break any rules. If you ever need to pull the current status, just query the two tables with criteria of "false" for Revision_Closed. Once you get this structure set up, you'd probably want to make some automated whistles and bells to fill in the Revision_No and such.

BTW, don't use the word 'type' as a field name, either.

 

[rblair11]

Thank you for your suggestion, it is a much better strategy that what I was planning on using.

So would this be the table structure you are suggesting? Basically all the data that doesn't change being in the first table and any data that does change being in the second table. The tables would be related by the submittal_ID?

tblSubmittals:
Submittal_ID (Autonumber) (Primary)
Format
Contractor
Description

tblRevisions:
RevisionID (PK, AN)
Submittal_ID (FK on tblSubmittals.Submittal_ID)
Submitted_Date (Date/Time)
Revision_No (Integer)
Status (Text...Perhaps looked up from a status table)
Status_Date (Date/Time)
Comments (Memo?)
Revision_Closed (Yes/No)

Assuming my example of the 4th revision actually being the one that is approved, I would mark Revision_Closed to "Yes" but wouldn't the first 3 revisions still show as not closed?

 

[rblair11]

How to

What keywords should I be searching on to find similar examples? I can't figure out how to click on a record, have the record duplicated, the revision number incremented, and then have all the revisions show as closed once the final (number 4 in my example) is "approved"

Since I'm pretty new to Access/VBA/SQL I am a bit stumped on what terms I should even be looking for. I don't mind doing the research, but I think I need a little guidence.

Thank you.

 

[Sergeant]

Do you really need to wait until approval to close out ALL of the revision entries? Or, do you want to close each revision entry when a new one is entered?
Thinking of it from a different angle:
If you are going to wait until you get an approval entry and then close out all the sub-records, why don't you just put the "Closed" field in the main table and either let the user click that checkbox or use some code that will close the main record once an approval record is loaded in the subform (tblSubmittals)?
You see, I am trying to 'get' your requirements from a question you asked, so I can't say I know what would be the perfect approach without knowing more about how the data needs to flow.

 

[rblair11]

You're right, each new revision could close out the previous. That seems like a much better approach.

I'm not sure where to stat with setting up the code that would increment the revison number, close out the previous revison, clear the comments field and copy the rest of the record.

Would I create a main form with the info I need to see (maybe from a query) in order to "see" enough data for each submittal to identify it, click on it, and have the actions I discussed previously happen? Then would I open a subform and create the new record there?

Also, is there a good place to read about SQL calls. In the 2 months I've been working with Access (I have another database that I've been pretty successful with) I've had to work real hard to get things like forms![subform] or Me.field, etc. to work. I've been able to do it case by case, but I don't understand the methodology (i.e. "." verses "!" and what does "Me" mean - things like that).

Thank you.