I'm new to access, have been playing around with access 2013/16 over the last few days to see if I can do what I would like but have barely scratched the surface.
The company division I work for were going to implement Filemaker but as the larger company are going to introduce a wider db in the new year, the FM implementation has been canned.
That leaves us working from an excel sheet - which is shared on a network drive. Although, I'm no expert on excel either, I know that shared excel sheets are asking for trouble. Plus, some of the people who will use the sheet use unreliable wifi connections and mobile internet access. I just know we are asking for trouble.
I would like to set up an access database which I hear is better for sharing.
We have been tasked by our client to carry out works on several hundred of their sites. This work is carried out over several stages, sometimes all by us, sometimes just the latter part by us but these works are tracked by forecast and actual milestones. These are updated on a daily basis by a large tracker that is sent out - Shall call it the ABCT (ABC-Tracker). The ABCT is created by their in-house database system which in turn is manually fed by all their subcontractors (like us).
At the moment the excel sheet we use has four key components or inputs;
1. Static data - info that relates to the site - address, owner, postcode, clients responsible PM etc - at the moment this is VLOOKUP into the ABCT based on the site ID, although this info is obviously fixed so could be set if require, no need to keep the vlookup active.
2. Data from our own internal tracker, but is limited to internal job numbers (for finance tracking), so only two columns of data
3. VLOOKUPS into the ABCT for the milestones - as we and other subbies change this data it can change daily.
4. Manual entry into the sheet from several departments - amount of labour required, survey visits, blockers, RAG status's, equipment required to carry out the works, PO info, handover pack info and more.
We use conditional formatting a lot to show if things are good to go or there is an issue based on drop down boxes - ie if a certain bit of tooling is required to be hired but not yet hired, the cell changes to yellow to highlight there is something still to be actioned. Once we have arranged this, we change to drop down and the colour changes to green (Good to go).
I suppose my questions are;
The ABCT comes with 5 tabs, I only need one, so split that off, rename the tab (to match the vlookup) and then overwrite the predecessor file using the same file name which keeps the vlookup in tact. Although the ABCT is well over 200 columns wide, we only require data from 30 or so. Occasionally, more columns are added to the ABCT so I have to re-do the vlookups.
So, How is best to get the daily updated info from the ABCT into the database? Do I save it to the shared drive, have a separate excel that finds the info I want (the 30 or so columns) and then import that daily into the database table? This info is NOT changed by us, its just for info. If we want to make changes, it is made in our clients system and will be reflected on the following ABCT.
If importing the shared excel tracker into access I assume I would just import the static data and areas that require manual entry, both the info from the ABCT and the internal job numbers could be added as additional tables and set up to link in the forms?
Would i just set this up as a desktop app and then change to webapp (we use sharepoint/onedrive) once complete (if ever i do, doing this in my spare time)?
Am i bitting off more than I can chew here? Ive not worked on access before and have only basic knowledge of excel, although am OK with the basics of Vlookup, if, or, and commands.