Help with an Asset Reservation/Booking Database- Banner Poles

[raydpp]

Good morning! First, let me say how grateful I am to have found this site. Got some smart people on here and I have really learned a lot! I’ve had some experience with Access, mostly with templates and simple databases. I like to learn through tinkering. But now I’ve actually got a project that is really important for my job, and I’ve hit a wall. Below is a description of what I want my end result to be and what I have so far:

Purpose: I manage street pole banner program. There are fixed assets in the field, POLES, which can be of several different POLE TYPES. Some poles have BANNER HARDWARE, some don’t and some have partial. I get convention groups or other CLIENTS who want to reserve POLES for a date in the future. I need to check POLE AVAILABILITY at a certain point in the future-or sometimes what the situation is like in the field right now- so that I don’t schedule duplicate jobs at the same time. Also I need to identify POLES that need new HARDWARE. And I also need to coordinate INSTALL/REMOVAL, PLACEMENT and COST ESTIMATING, BILLING and INVOICING.

When I book a job, I need to reserve the POLE locations for the DATES the CLIENT selects and whether they are AVAILABLE at that time. Once they are reserved and artwork is approved and sent to printer, I coordinate INSTALLATION. Every BANNER JOB has a LAYOUT SCHEME that I give to the install (ie this individual banner design goes on the Street Side of this specific pole)

The SCHEDULING portion is where I get stuck. I have run some queries where I can find duplicates, or I can see what is up or reserved at a certain time. Ideally, I’d like to be able to have a front end form that I schedule new BANNER JOBS and make RESERVATIONS, but on that form I’d like to filter out POLES that are NOT AVAILABLE based the INSTALL DATE and the REMOVAL DATE that I enter on that form.

There are two other components that I’d like to incorporate in the future. GIS capability- linking to a .dbf of a Shapefile so that I can see geographic database information. And also, COSTS and BILLING. I want COSTS calculated based on the info that I select (ie, number of Small Banners and Large Banners, etc.) for ESTIMATES and INVOICES in order to facilitate better billing processes.

I tried to include everything I could think of, but understand that I’m just at the beginning. I’d like to know if I’m on the right track or if not, what other components I need to learn to get to the next step. I’ve included an Excel spreadsheet that lists what I believe my data tables should be as well as the Relationship Diagram from Access based on what I’ve started.



Tried to be as thorough in this explanation, but please let me know if I’ve missed something. Thanks for sharing all your expertise on this forum, you all have really introduced me to a new powerful tool that I've enjoyed learning thus far.

Ray

[June7]

Looks like a good start.

Be aware MS is cutting support of dbf file type. AFAIK support is completely removed from Access 2013, not sure about 2010, I will have to check on that later. Even support in Excel diminished starting with 2007. Excel can open but no longer save as or export to dbf.

Are you using ESRI products? You might look at using personal geodatabase instead of shapefile.

Have you looked at the MS Asset Management template?

[raydpp]

Hello June7. Thanks so much for your reply. I was not aware of the dbf support. I don't have ArcGIS at work yet, so I just receive a Shapefile from our stakeholders, in this case, the locations of poles throughout our District from the utility company. I use that information for the identifiers and I have to add banner hardware information on my end. I am trying to do this Access Database to get this Banner Program under control as it can be time consuming to verify what poles are reserved at a certain date etc. Not sure about a personal geodatabase? Is that a function of ArcMap or another ESRI product?

The MS Asset Tracking Database is one that I started with, but the scheduling seems alittle different. The assets seem to be assigned to employees indefinitely whereas my issue is reserving that asset for a particular period of time and making it unavailable to another group. Do you feel that I could make some adjustments to that template to deliver what I need?

[June7]

Personal geodatabase is ESRI spatial data storage format based on Access 2003 framework - it's basically an mdb file. Use ArcCatalog to create them and ArcMap displays/adds/edits records.

Modifying templates can sometimes be more difficult than building from scratch but they can certainly be a source for ideas. Another you might find of interest is the Lending Library.

[raydpp]

Yes, I've looked at both those templates in addition to others to understand how those relationships work.

Based on what you see, am I missing a join table that combines Banner Jobs with Poles, so that I can be able to see what poles are with which group for any given date? Is this a query, or macro? Guess that's where I get stuck.

[June7]

Isn't that what linkReservations table is for? Try a query.

What are the Field1 and Field2 fields for? Attachments? Be aware embedded objects can dramatically increase file size.

[raydpp]

Great point about the file sizes. I will remove some of the attachment options and just store the approved artwork, signed contracts etc in another folder, but I'd like to track when those are received per job so I can see what is missing when there are a bunch of open jobs.

-What is the best practice for adding "events" like that to a specific banner job? Do I add date fields and just manually select when it was entered? Or checkboxes? Do those automatically save when the checkbox was selected?

-Also, running into an issue that I didn't think of before. So each banner job, has poles linked to clients by a reservation join. What if there are status changes involving a pole? Say, a banner came down during a storm and needs to be replaced? Or pole hardware broken. Is there another "Maintenance" portion that I'm not anticipating that should be separate from the info I've built thus far?

[June7]

- If you need to document 'events' then have an Events table related to the job record. A record for each 'event'.


- Is downed banner an 'event' for the job? Broken pole is aspect of the pole - maybe need another table - pole events?

[raydpp]

So for things like Application Received, Final Artwork Approved, Deposit Received, i.e. things that happen for each BannerJob but on separate dates, do I create a table BannerJobEventType with every known event type? And a join table for BannerJobEvents to connect them?

Then I could create the same for any event specifically related to poles?

[June7]

That would be a normalized structure. Yes, a table for event types is reasonable. Allows for easily adding new event types, as opposed to modifying table to add a new field. However, it is a balancing act between normalization and ease of data entry/output. Normalize as far as you want to accomplish your requirements.