How to build a Microsoft Access MDB Database for Document Control

[irsmalik]

Hello Alls

I have a little know how of Access Database. Presently I am working in a construction company who keeps its Drawings record in Excel format. Our system is like this

QQQ is Client
EEE is Contractor
PPP is Sub-Contractor

Now the situation is that PPP design a Drawing and send it to EEE, then EEE send it to QQQ for Review / Approval. If is approved then OK and no problem. but if QQQ give its comments it reverse to EEE and then to PPP. So our drawing approval cycle is give below:

Step 1 PPP design drawing in Rev 0 and send it to EEE
Step 2 EEE send this drawing to QQQ
Step 3 QQQ send it back to EEE with its comments
Step 4 EEE send it back to PPP
Step 5 PPP incorporate comments and send it to EEE in Rev 1
Step 6 EEE again send it to QQQ
Step 7 QQQ approve it or again make some changes

So this is the story. Our company is using an Excel Sheet which records

Dwg No
Rev
Xmtl No form PPP
Xmtl Date.

Then we send it to QQQ

Dwg No
Rev
Xmtl No to QQQ
Xmtl Date

So it become a LONG ROW to see where the drawings and what is its current Rev and Status (approved or still in approving process).

I want to make a database in Acces. I don't understand how to see all this Drawings incoming and outgoing in ONE SINGLE SCREEN.

Can anybody please help me to guide / advise a way out of this. Or if there is a template of Revision Based Entry / Repeated entry between 3 companies.

I will be thankful to all friends. I want to do something which can improve my status in new company... OK dear... I will wait for your suggestions / advise.

Irshad Ahmad

 

[Steve Prevette]

Sounds like you need a relational database file, with the basic information about the document in a master file, then a daughter file that is a transactional recording of things that happen to the document and the assumption the most recent record is the current status.

There is a report generator in Access where you can do a lot of control of the output. If you've never worked with relational databases before, setting up the relational databases and keeping them index will at first be a challenge. VBA programming is also available in Access if needed.

 

[irsmalik]

Thanks U Steve
Reports option is a task after Data Entry in a Table or Form.
My first task is to make a Form where I could enter my data and view all incoming & outgoing drawings cycle.
I am looking a way how to enter Repeatedly Revisions between 3 companies. I will be thankful if you could proide me some idea.

 

[Steve Prevette]

I'd suggest you flowchart out the document control process for the three companies first. Then you should be able to develop the data structure. Perhaps a structure with a master document file, and then three relational files, one per company with the individual status (and applicability) at each company may be in order.

 

[Woody]

This (attached .zip is Access 2014) is about as simple as it gets. You could refine this basic design to include date the drawing went from one entity to another. If different subcontractors are used, those could be another selection option.

All manner of additions can be built later without having to change existing tables by relating the new data to an existing key. If nothing else, maybe this will get you thinking in a different direction. Good luck!

 

[Hummingbird]

Hi Woody. I have Access 2010 would you be so kind as to save it for 2010 and repost. This database is what I am looking for.

Thanks!

 

[PaulJSmith]

If your own attempts do not net you what you seek, one of the advertisers here on The Cove, Sunday Business Systems (link on the left of this page), offers a simple Access-based document control program for a very reasonable price.