PTW Database Problem

[Singapore Sam]

Hi there. I use an Access database to follow and track permits on a daily basis.The trigger for a permit "issued today" is a checkbox and then the checkbox is unchecked last thing at night so we start with no permits issued for the following morning when certain permits will again be "issued today" and marked with a tick in the checkbox.All the permits have a finite life and not all are issued every day for work. My problem is that i need to generate a report each day for all permits issued and all permits not issued. This i have done and it works fine but i now need to be able to say how long it is since a permit has been issued. I am having real trouble and feel i may need to export the data into Excel to be able to do this..Any help would be greatly appreciated

[HiTechCoach]

IMHO, Excel would be stepping in the wrong direction. Access is much more powerful for this type of task.

Hi there. I use an Access database to follow and track permits on a daily basis.The trigger for a permit "issued today" is a checkbox and then the checkbox is unchecked last thing at night so we start with no permits issued for the following morning when certain permits will again be "issued today" and marked with a tick in the checkbox.

The way I handle this is my applications is to a date field for date issued. I would not use a check box for this. A check box does not give you much meaningful information.

I think this will solve a lot of your problems.

My problem is that i need to generate a report each day for all permits issued and all permits not issued.

How do you know if a permit was NOT issued? Do you have a status field?

It would help to know more about your tables. Start with the main table that holds the permit information. What are all the fields?

[Singapore Sam]

Field NameData TypePermitNoTextTaskDescriptionMemoIssueDateDate/TimeCancelledDateDate/TimeExpiryDateDate/TimeIsLiveYes or NoThis runs off VBA code after the Issue date is filled and changes when the Expiry date is reachedIssuedTodayYes/NoHot Work PermitYes/NoCold Work PermitYes/NoOpen Flame PermitYes/NoIsol/De-Isol CertificateTextAreaTextTag NoTextPerforming PartyTextCompanyTextAuthorising PartyTextConstructionYes/NoCommissioningYes/NoSIMOPSYes/NoSIMOPS ConcurringYes/NoProjectTextPTW TypeTextEquipment DescriptionTextMechanicalYes/NoInstrumentYes/NoElectricalYes/NoOthersYes/NoSIMOPS ApprovalYes/No
This is the Main Table and the status is obtained by the Issued Today yes/no checkbox. This is then cleared by using an update Query to clear the yes to no then all permits are back and none issued..We then issue those requested the next day by the same process.By running a query i can make a report of which permits have been issued fortoday ..I may have 300 permits Live in the system but only issue 100 on a daily basis so i can generate reports to say which permits were issued and which ones werent but i want a way to be able to inform me or print a report to tell me when a permit was last issued...
Hope this helps.....

[HiTechCoach]

Field Name DataType
PermitNo Text
TaskDescription Memo
IssueDate Date/Time
Cancelled DateDate/Time
ExpiryDate Date/Time
IsLiveYes or No

This runs off VBA code after the Issue date is filled and changes when the Expiry date is reached

IssuedToday Yes/No
Hot Work Permit Yes/No
Cold Work Permit Yes/No
Open Flame Permit Yes/No
Isol/De-Isol Certificate Text
AreaTextTag No Text
Performing Party Text
Company Text
Authorising Party Text
Construction Yes/No
Commissioning Yes/No
SIMOPS Yes/No
SIMOPS Concurring Yes/No
Project Text
PTW Type Text
Equipment Description Text
Mechanical Yes/No
Instrument Yes/No
Electrical Yes/No
Others Yes/No
SIMOPS Approval Yes/No

This is the Main Table and the status is obtained by the Issued Today yes/no checkbox. This is then cleared by using an update Query to clear the yes to no then all permits are back and none issued..We then issue those requested the next day by the same process.By running a query i can make a report of which permits have been issued fortoday ..I may have 300 permits Live in the system but only issue 100 on a daily basis so i can generate reports to say which permits were issued and which ones werent but i want a way to be able to inform me or print a report to tell me when a permit was last issued...
Hope this helps.....

Since you are issuing the same permit multiple times I would use a separate child/related) table that has a record for each day it is issued.

From looking at your table, it really has some design issues with normalization. You have lots of repeating field that really should be separate records in a child/related table.

It looks more like a spreadsheet that a relational database design. I am guessing that you create this database from an Excel file. Making the leap from designing spreadsheets to design relational databases is not easy. When designing a relations database you need to forget all the stuff you know about designing a good spreadsheet. It rarely applies to a a good relational database. They are just to different.