Microsoft Access, anyone?

[Yankee Chronicler]

I have a problem: I've been using an Excel spreadsheet at work to track permit applications, submittal dates, response dates, result, and the processing time in days. I have a master worksheet with one row for each project and summary information, with a hyperlink to a worksheet for each application that has entries for the project name, address, application number, application date ... and then a table that lists the date each submittal is received, the date we responded, the action taken, and the number of days elapsed between submittal and response. The corresponding entries on the master worksheet are populated by look-ups from the detail sheets.

The primary purpose of this is to keep track of which submittals that haven't been acted on yet have been in the queue the longest, so I can look at the oldest first. That means every time I update the workbook, either by adding a new project or by changing the status of an existing project, I have to manually run a two-level custom sort to move the longest-pending to the top. The sort is imperfect, so I than have to follow up by manually moving one or two rows up or down to get every open/pending project in correct chronological order.

That wasn't too daunting when the spreadsheet was new and there only a few entries. It's now up to 150 projects, with more coming in virtually every day. I think this whole mess could probably run a lot smoother if it were in an Access database ... but I have no idea how to even start setting it up. Does anyone know Access, or know anyone who can give me some help/coaching on putting together a relational database in Access?

 

[jar546]

I have a problem: I've been using an Excel spreadsheet at work to track permit applications, submittal dates, response dates, result, and the processing time in days. I have a master worksheet with one row for each project and summary information, with a hyperlink to a worksheet for each application that has entries for the project name, address, application number, application date ... and then a table that lists the date each submittal is received, the date we responded, the action taken, and the number of days elapsed between submittal and response. The corresponding entries on the master worksheet are populated by look-ups from the detail sheets.

The primary purpose of this is to keep track of which submittals that haven't been acted on yet have been in the queue the longest, so I can look at the oldest first. That means every time I update the workbook, either by adding a new project or by changing the status of an existing project, I have to manually run a two-level custom sort to move the longest-pending to the top. The sort is imperfect, so I than have to follow up by manually moving one or two rows up or down to get every open/pending project in correct chronological order.

That wasn't too daunting when the spreadsheet was new and there only a few entries. It's now up to 150 projects, with more coming in virtually every day. I think this whole mess could probably run a lot smoother if it were in an Access database ... but I have no idea how to even start setting it up. Does anyone know Access, or know anyone who can give me some help/coaching on putting together a relational database in Access?

Your town needs to get out of the early 90s and buy permitting software. There are many companies out there that cater to small towns at reasonable prices.

 

[Yankee Chronicler]

Your town needs to get out of the early 90s and buy permitting software. There are many companies out there that cater to small towns at reasonable prices.

We have permitting software. It doesn't do what my spreadsheet does.

 

[jar546]

We have permitting software. It doesn't do what my spreadsheet does.

Then you have the wrong software.

 

[Msradell]

Have you thought about using the sort command? You could put another column to indicate that a project was completed and sort close to the bottom. For that matter you might want to even delete them. The sort commanded Is very easy to use and you can sort on any column.

 

[Yankee Chronicler]

Then you have the wrong software.

Not really. We use Municity, which a number of other departments in the state also use. Municity has its quirks, but it does a lot. However, what it can't do is

• Spit out a report of which commercial projects are pending issuance of a permit, showing when the application was filed, what the most recent action was, and how long it has been since the last action was taken
• Spit out a report for a specific project showing when plans were received, when we responded (either with an approval or with a denial letter), how long it took us to respond, when the applicant resubmitted, how long it took them to resubmit, and how long it took us to respond.

We get a lot of interference from Town Hall because of complaints that we don't respond in a timely manner.I started keeping the spreadsheet to document that, typically, we're not the party that's slow in responding. I stole the idea from the shop drawing logs we kept when I was working as an architect.

 

[Yankee Chronicler]

Have you thought about using the sort command? You could put another column to indicate that a project was completed and sort close to the bottom. For that matter you might want to even delete them. The sort commanded Is very easy to use and you can sort on any column.

I am using the Sort command to perform a two-level sort. The problem is that there are two categories (status codes) that may apply to open projects:

• Pending (newly submitted, not yet reviewed)
• Resubmittal

But we never know when the administration will kvetch about a project we have already issued a permit for, so I don't drop approved projects from the list, and there's also an "R&R" status code, for projects which we have rejected and for which we are awaiting revised plans or additional documentation. So it's a tug-of-war whether the first level sort should be by date and the second by status, or if the first sort level should be status and the next level should be date. In Excel would allow me to save multiple custom sorts, it would be easy -- but it doesn't allow that. (Or, if it does, I don't know how to do it.)

If I sort first by status, it sorts according to the order in which I list the status codes. Thus, it will put all "Pending" projects first, then all "Resubmittals," then all "R&R," etc., and then it will sort by date within each of those groups. That's the way I run it. But if there's a resubmittal that's older than the oldest new application, we should review that next so it should be at the top of the list.

But if I sort first by date and then by status, I get a mixed mess of everything. I don't want the "R&Rs" mixed in with the "Pending" and "Resubmittal" projects, because I don't control the R&Rs. I don't care how long they take to resubmit -- I need to know which project is die to be reviewed next, how long it has been waiting, and how many projects are in the queue waiting to be reviewed.

 

[wwhitney]

You could create a separate page for the R&R status entries. Also a separate page for the issued permits.

Or, and this is a total hack, if you really want Pending, Resubmittals, and R&R on all one page, sorted so that all the R&R are behind everything else, and then sorted by date within both groups, you could create a new column with a custom value of the form "date + a fixed large number if Pending or Resubmittal" and just sort by that custom value. Dates are just stored as the number of days since December 31, 1899, so if you make your fixed large number say 1000*365.2425, you won't get anything hinky for another 875 years.

Cheers, Wayne

 

[Yankee Chronicler]

You could create a separate page for the R&R status entries. Also a separate page for the issued permits.

Thanks for the suggestion, but it makes no sense. R&R is just one of seven status codes. Putting each on a separate page defeats the whole purpose of the spreadsheet.

 

[jar546]

which a number of other departments in the state also use

Not relevant if it does not do what you need it to.

• Spit out a report of which commercial projects are pending issuance of a permit, showing when the application was filed, what the most recent action was, and how long it has been since the last action was taken
• Spit out a report for a specific project showing when plans were received, when we responded (either with an approval or with a denial letter), how long it took us to respond, when the applicant resubmitted, how long it took them to resubmit, and how long it took us to respond.

Most software companies today will customize any report you need. Maybe you should call Municity. They might just do it for you.

 

[jar546]

We get a lot of interference from Town Hall because of complaints that we don't respond in a timely manner.I started keeping the spreadsheet to document that, typically, we're not the party that's slow in responding. I stole the idea from the shop drawing logs we kept when I was working as an architect

The right software will handle all of this for you with not just the report but the audit trail. For all the time the town pays you to create spreadsheets and manually enter data, they could buy the right software.

 

[wwhitney]

If I sort first by status, it sorts according to the order in which I list the status codes. Thus, it will put all "Pending" projects first, then all "Resubmittals," then all "R&R," etc., and then it will sort by date within each of those groups. That's the way I run it. But if there's a resubmittal that's older than the oldest new application, we should review that next so it should be at the top of the list.

So is that the only problem, that when you do that sort you want Pending and Resubmittal to be treated as one group?

Then a less hacky method is to add a new "alternate status" column which is computed from the status column, and which uses the same code for Pending and Resubmittal. Then you can sort on alternate status as the first key, date as the second key.

Cheers, Wayne

 

[wwhitney]

But we never know when the administration will kvetch about a project we have already issued a permit for, so I don't drop approved projects from the list

Those, at least, could go on a separate page so you don't have to deal with them regularly.

That means every time I update the workbook, either by adding a new project or by changing the status of an existing project, I have to manually run a two-level custom sort to move the longest-pending to the top. The sort is imperfect, so I than have to follow up by manually moving one or two rows up or down to get every open/pending project in correct chronological order.

As the second step, instead of manually moving rows up or down, you could just resort using only date and restricting the range to the rows with the Pending and Resubmittal statuses (which you put next to each other in your custom sort list). Of course, since you say that Excel doesn't let you save multiple custom sorts, changing to a different set of sort criteria would be annoying.

Cheers, Wayne

 

[Yankee Chronicler]

The right software will handle all of this for you with not just the report but the audit trail. For all the time the town pays you to create spreadsheets and manually enter data, they could buy the right software.

The town didn't pay me to create the spreadsheet, or the database I created in Access (for another purpose that Municity also doesn't track). I do that on my own time, at home. Likewise, the town doesn't pay me to lurk on this forum. That's also entirely on my own time.

 

[tbz]

YC,

you asked for suggestions so;

• If the software company does not have the report as standard you are looking for and getting a custom report written is not an option from them, or you are not able to do custom reports within your software, then
  • My first question is, does their software at least have the data you are looking for in the report?
    • If it does look for a third party software that can look at the data like zapier and just write the zaps that will pull the report.
    • Not sure if Zapier works with your software, but there might be one that does.
• Might want to look at a cloud based database program, rather than MS Access that you can input the data and run the reports
  • What you have is not that bad to import the data from the excel file and then just re-write the reports
• If you already wrote the excel sheets, then an access database is pretty much done.
  • The data and the report structures are their, just need user input screens.
    • Just find a entity to convert it or write from scratch
    • Might want to populate a sample excel file with dummy data and get a quote from someone on Fiverr.com
• As to the current software not being the right one, sounds like you have to use what you got, town choice, not building department choice, and as thus, you work with what you have, good or bad.

The question as I get older, as we all do, I keep asking is do we really need that information?

I was in a clients facility last Thursday and the owner was showing me around their new old facility. They had just moved, as they purchased a company that was closing and moved into the older but large location.

There were 2 estimators sitting next to each other now, one old school all paper no electronics working from prints and the other embraced electronics. Both estimators have been doing the same type of estimating for over 20 years.

The estimator that worked with all the new tech and electronically spent 11.5 hours to review and produce the proposal portfolio. The old school paper pusher spent 3.75 hours to produce their proposal portfolio. In the end both proposals looked the same for presentation and there was less than a $300.00 difference between them on a $250,000.00 +/- project.

There is such a thing as too much time spent to get information you would like to have, that looks nice, but do you really need to know it to get the job done correctly?

 

[tmurray]

The town didn't pay me to create the spreadsheet, or the database I created in Access (for another purpose that Municity also doesn't track). I do that on my own time, at home. Likewise, the town doesn't pay me to lurk on this forum. That's also entirely on my own time.

I am going to answer this as a department manager.

1. If the work you are doing benefits your employer, you are working for them.
2. This type of work should not be done on your own time. This provides a skewed view of resourcing needs to the senior management of your organization and negatively impacts managements ability to request additional FTEs.

I understand that you might be in a situation where you need to do this for your own sanity/ethics/etc., however, these additional hours should be tracked, even if they are unpaid. Additionally, the department manager will face challenges in justifying additional staffing to senior leadership if all the work is getting done.

 

[Inspector Gadget]

I am going to answer this as a department manager.

1. If the work you are doing benefits your employer, you are working for them.
2. This type of work should not be done on your own time. This provides a skewed view of resourcing needs to the senior management of your organization and negatively impacts managements ability to request additional FTEs.

I understand that you might be in a situation where you need to do this for your own sanity/ethics/etc., however, these additional hours should be tracked, even if they are unpaid. Additionally, the department manager will face challenges in justifying additional staffing to senior leadership if all the work is getting done.

^^^^

This.

Never work for free.

 

[Yankee Chronicler]

I am going to answer this as a department manager.

1. If the work you are doing benefits your employer, you are working for them.
2. This type of work should not be done on your own time. This provides a skewed view of resourcing needs to the senior management of your organization and negatively impacts managements ability to request additional FTEs.

I understand that you might be in a situation where you need to do this for your own sanity/ethics/etc., however, these additional hours should be tracked, even if they are unpaid. Additionally, the department manager will face challenges in justifying additional staffing to senior leadership if all the work is getting done.

I am aware of all this. I do this because I choose to do it. I'm a senior citizen widower, so I have nothing better to do with my time outside of the office. It doesn't skew anything, because if I didn't generate the Excel spreadsheets I've created and the Access database I created, they simply wouldn't happen and the department would continue using paper and pen to track these things manually.

Please don't go off topic and delve into office management and/or office politics. Every office is different and unique and there is nothing to be gained by trying to examine why I want to do this. I need help with Access. If you can't provide the help I need -- thanks for reading, but comments about not working on my own time are not helpful.

 

[BN4537]

I have a problem: I've been using an Excel spreadsheet at work to track permit applications, submittal dates, response dates, result, and the processing time in days. I have a master worksheet with one row for each project and summary information, with a hyperlink to a worksheet for each application that has entries for the project name, address, application number, application date ... and then a table that lists the date each submittal is received, the date we responded, the action taken, and the number of days elapsed between submittal and response. The corresponding entries on the master worksheet are populated by look-ups from the detail sheets.

The primary purpose of this is to keep track of which submittals that haven't been acted on yet have been in the queue the longest, so I can look at the oldest first. That means every time I update the workbook, either by adding a new project or by changing the status of an existing project, I have to manually run a two-level custom sort to move the longest-pending to the top. The sort is imperfect, so I than have to follow up by manually moving one or two rows up or down to get every open/pending project in correct chronological order.

That wasn't too daunting when the spreadsheet was new and there only a few entries. It's now up to 150 projects, with more coming in virtually every day. I think this whole mess could probably run a lot smoother if it were in an Access database ... but I have no idea how to even start setting it up. Does anyone know Access, or know anyone who can give me some help/coaching on putting together a relational database in Access?

I set up an Access database to try to keep contemporaneous notes on a file for each contractor as I spoke with, searchable by name, company, address, and permit number. It automatically emails a copy of the note to me when I enter it so that I have a timestamped copy , so later I can prove when the note was written. The goal was to be able to pull all the files on an individual, even if they swap companies or LLCs, and be able to show why I am less inclined to be lenient for the 20th time with this individual... Or that I warned them x amount of times not to do what they went ahead and did...

I am not an expert, don't really remember how I did it, and I would have to start over from scratch to re-build it. I YouTubed my way through it until I got a somewhat functional database. It mostly works. The notes go in well, but the search function leaves a lot to be desired. Not sure if it is something I did wrong or a limitation of the system. All I can say is that YouTube is your friend...

 

[tmurray]

I am aware of all this. I do this because I choose to do it. I'm a senior citizen widower, so I have nothing better to do with my time outside of the office. It doesn't skew anything, because if I didn't generate the Excel spreadsheets I've created and the Access database I created, they simply wouldn't happen and the department would continue using paper and pen to track these things manually.

Please don't go off topic and delve into office management and/or office politics. Every office is different and unique and there is nothing to be gained by trying to examine why I want to do this. I need help with Access. If you can't provide the help I need -- thanks for reading, but comments about not working on my own time are not helpful.

My apologies. I will be of no help to you then. I've always used SQL for my databases.