Is access what would be best for what I need? (2 Viewers)

Donury236

New member
Local time
Today, 22:40
Joined
Jan 10, 2021
Messages
14
I am very new to access and I am still trying to work out if it is what is right for what I think I need. I apologise if this rambles - I have just spent the last 8 hours updating files.

Background: My role is administration of stairlifts and hoists for a LAHSCP. We use CES360 but it is not what we need for the admin of these items due to how fast they go out and come back. It also does not allow data to be exported in any useful useable way!

We have 2 companies that do the Install, service and removals. One of which uses a system called Maximus(I think that right). They need us to send them data in an excel table, laid out a specific way so that it uploads correctly.

Because of this we have 11 spreadsheets for the products! We have to track what we install, when a product is serviced and then when it is removed.

I am trying to think about how this would be constructed.

I see it as our clients as one group - that keeps a history of the equipment that they have had.
Then the equipment as another group, and each piece of equipment has its own profile that tracks service history and also tracks a user history.

Right now we have a spreadsheet for what was installed each month that gets sent to the service company, one that tracks who has what-its last service and next service date, one that historically tracks what was removed and when and a monthly one that lists the removals that we sent to the service company.
I would have to be able to pull the required information, in the required format of the servicing company, in excel format from the database.

I want to have one database where I record everything but I can just have different views that pull different data to meet the requirements of the spreadsheets the other company wants.

My brain is rather stuck here. I have watched the guide on transferring info from an excel but I am just struggling to see what would be the relationships.

Years ago (2007-2014) The company I was at used a database in Sharepoint that recorded ALL the info on orders but we could change the view to show the data in different views that each section needed. I am not sure if this is something linked into Access now - as its all advanced so much since I used it.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:40
Joined
Feb 19, 2013
Messages
16,553
Only you can really answer the question of your thread title, but yes access can do this
I want to have one database where I record everything but I can just have different views that pull different data to meet the requirements of the spreadsheets the other company wants.
it can even export the excel files required

I am just struggling to see what would be the relationships.
if you want help on this, provide some examples of what your data looks like as an input to the db and what you require as an output from the db
 

Donury236

New member
Local time
Today, 22:40
Joined
Jan 10, 2021
Messages
14
CJ, Input initially I would want it to save time and just pull the data from existing excel spreadsheets. During use it would be data that would be manually inputted by myself and who ever else uses the database, ideally in a Form format.
I have looked at some templates and there is a maintenance template that sort of has the layout I would be after with tweaks. We do not need to record cost.
In general use I would like to be able to be looking at a client and see all the equipment they have - as many often have different types of hoists and aids - and the service dates for all of these.

I can't show you exactly what is in the spreadsheets due to GDPR, but these are the fields that we use. And you can see that just for that one workbook we have sheets per month (other products have it by alphabet last name as these are 6 monthly services, and those hoists have an asset number as well as an Ident)

1610580130986.png
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:40
Joined
Feb 19, 2013
Messages
16,553
just pull the data from existing excel spreadsheets
databases do not work like spreadsheets and trying to make them do so will ultimately result in an app incredibly complex to manage or at worst failure.

there is no subtext to your screenshot explaining what the fields are and what table they might go in, but as a guess you might need

tblEquipment
EquipmentPK
EquipmentName
AssetNumber

tblCustomers
CustomerPK
FirstName
LastName
Address
Town
PostCode
TelNo

tblCustEquipment
CustEquipmentPK
CustomerFK
EquipmentFK
InstallDate

needless to say you do not store data in different months

Don't know what Ident means, or what Service Due represents (a date? a Y/N?). You mention servicing every 6 months - but is that related to the install date? Or since last service? If so, what happens if a hoist is replaced or a customer gets a second hand one? If it is related to the install date then it is easily calculated but perhaps it is a specified date around the 6 month point.

Similarly, don't know where Service Complete and Comments would be stored - depends on what they relate to, who completes that information etc.

I've done quite a bit of work in the health care industry (for carers) and you normally need to store a lot more information about the customer. Typically the customer is the individual and the client is who pays for the service - may be the customer, might be a relative, a charity or local authority.

Other comments, most people these days have two telephone numbers, landline and mobile.
 

Donury236

New member
Local time
Today, 22:40
Joined
Jan 10, 2021
Messages
14
Apologies. Those fields are what is the header for the spreadsheet currently, and in the format that the external company needs the data.
When I say pull the data I mean that I would like to be able to take the existing data, rearrange the it to match the database fields that it will populate and then upload it once it is constructed so that it imports the data- if that is possible. Mainly because individual entry of over 600 pieces of equipment and about 400 clients is not something that I currently have time for.

I have no idea on how to work this out into what is needed for ACCESS hence why no explanation.

Ident is like an asset number - this is the code the manufacturer gives the equipment. When we register it on our system it is then given an asset number.

Service due - in the table we currently use that has a calculation so that if I put in a date in the service complete field it will tell me the next service date - Stairlifts = 1 yr between services, Hoists - 6 months between servicing. In a database this would be something that would change when I would input the date that the service was carried out.
If a mobile hoist is removed from a client then the service date does not change - it is related to the hoist, no the client. So when the hoist is then passed to a new client the last service date still stands.
If a Ceiling track hoist or Stairlift is removed then that resets to due given the nature of what is required to remove them and risk of damage. Often though on removal they are scrapped.

Currently we just put all the numbers in one cell on the spreadsheet - but yes we do need to be able to store more than one number as there are sometimes also proxys who we contact for access.

We use CareFirst to store the majority of the information on the client and CES360 is the main equipment database - it just doesn't meet our needs for amassing monthly info to send to the servicing company. We are the LA that does the admin and pays for the servicing.

My main desire is to have one thing that I can edit to show changes in servicing, install, removal, client all in just 1 place. But with the ability to generate a report in the excel format that the service company require.

This is something I am trying to do outwith my normal 'duties' as I have recently taken over the role and noticed that due to the having to move info from one spread sheet, based on data that you get in an pdf invoice and from the main system to various other ones that stuff is often missed - which is costly when engineers are then sent to addresses folk are not at, or they go to do a service twice.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:40
Joined
Feb 19, 2013
Messages
16,553
I would like to be able to take the existing data, rearrange the it to match the database fields that it will populate and then upload it once it is constructed so that it imports the data- if that is possible.
so what is your existing data? If you mean the spreadsheets you send out is that also your source? If so, given you have admitted there are errors the data it should be possible to import from there to provide a 'base' dataset. But you should be working on what your 'ideal' application would look like, then import the data and resolve issues

Think you need to research 'data normalisation' to get an understanding pf how databases work.
 

George-Bowyer

Registered User.
Local time
Today, 22:40
Joined
Dec 21, 2012
Messages
177
Hi. If I may offer something from the perspective of another relative beginner - and in order to manage your expectations a bit here, whilst it seems from what you say above that Access would be perfectly capable of handling the task that you envisage, actually making it do so is a lot more complicated. "Figuring out a wee database", as you mention in your other message, is unfortunately a lot more complicated than it might originally appear.

(There are people in here who could probably build something of the sort that you want in a few hours - but that's because it's their job and they have years of experience and that's what they get paid for. CJ_London talks about data normalisation above - he probably does it automatically now, without even thinking about it. I still have to use pencil and paper and endless cups of coffee to do it badly...)

If you can adapt one of the template databases to handle the data you use (which seems unlikely as the functions you desire appear to be quite specific) then that would make it a lot, lot easier.

Using one of the template databases is like buying a car from a dealer. All you have to do is figure out how to drive it.

Building your own database is more like starting from scratch in a giant car-parts warehouse. First you have to design your car, then you have to pick out the right parts and put them together in the right way and then you have to learn how to drive it.

Don't get me wrong, building databases can be very enjoyable and very rewarding - I have been doing it as a hobby for years and the people here are really helpful and unbelievably kind and generous with their time.

So, you can absolutely learn how to build what you want and you will get lots of help here to do it, but, if, as you say, you are doing this in addition to your other duties, and with a lot to do at home, I would guess that unless you're at the super-genius level, if you're starting pretty much from zero knowledge or experience, you're going to need to be thinking in terms of months of learning and experimenting and tinkering before you can put together something that works well enough for you to be confident to use it in a work environment.

If it's a hobby, or if, like in my case, it's your own business, then that's one thing (I'm not likely to fire myself if my db fouls up crucial data; plus, I, hopefully, have the knowledge and experience to regenerate it if I need to). Taking risks with your employer's data, on the other hand, especially off your own bat, is a rather different thing.

You said in your other message that this is just a stop-gap until your work gets a new system that meets its needs. I would be more inclined to persuade your work that they need to push that process themselves, not put yourself at risk trying to do it for them.

Unless, as I say, you want a really fascinating, satisfying and, at times, utterly infuriating new hobby - in which case welcome to the club...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:40
Joined
Feb 28, 2001
Messages
27,001
Time to unpack my cape and tights. I think I accidentally left my mask with the dry-cleaners.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Jan 23, 2006
Messages
15,364
@Doc
Get that mask before anything else.... a COVID prevention priority 😷!

@Donury236
To get some experience with designing a database from a description of the facts about the business, work through this tutorial (ZYX Laboratories) from RogersAccessLibrary. It will take less than 1 hour and you will learn. And what you learn/experience can be used with any database.

There are several articles in the Database Planning and Design link in my signature.

Good luck with your project.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:40
Joined
Feb 28, 2001
Messages
27,001
@Donury236 - we might joke around here sometimes, but I'll get serious for a moment.

You are trying to decide how to tackle this project. IF you go in the direction of designing a new database for this, remember that the upfront design phase is THE SINGLE MOST IMPORTANT part of the process. A bad design now will eat your lunch later - and dinner and breakfast, too. Sleepless nights and inadequate food are your future unless you spend the time up front to do a thorough analysis of needs and goals for this project. "Skull sweat" expended here will pay itself back tenfold later. I don't want you to get paranoid about it, but starting too soon leads to a LOT of redesign and retrofit operations. You CANNOT get it right the first time (presuming you are human) - but you can certainly come very close to right with enough attention to design.
 
  • Like
Reactions: WAB

CJ_London

Super Moderator
Staff member
Local time
Today, 22:40
Joined
Feb 19, 2013
Messages
16,553
CJ_London talks about data normalisation above - he probably does it automatically now, without even thinking about it
Thanks for the comment, I admit normalisation has become second nature to me (the principles are not difficult) but I do think about it and that often requires searching questions about how the app is intended to work, the objectives and what defines the app as being a success. Normalisation is a set of rules used by methods, they are not a method in themselves.

Producing valid/correct reports of whatever flavour (access report, excel/csv file, email, etc) is a clear requirement as is understanding where the data comes from and how it is transferred into the app. But time/performance also plays a part as does future proofing, ease of maintenance, levels of security to name a few others. These define methods.

I might spend a couple of solid days with the client, meeting with the sponsor and potential users to get a good understanding of where the app fits into the business. Sometimes longer
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Feb 19, 2002
Messages
42,976
You've used a lot of jargon. I'm guessing that most of the acronyms are red herrings and we really don't need to know anything about them. But don't expect people to just "know" what random letters mean
LAHSCP, CES360, GDPR, LA
LA is a city in the US:) outside of that you could beat me with a wet noodle and I couldn't even guess about the others. This is a technical forum so you can get away with using technical acronyms but not business specific ones.

Looks like CES360 is some kind of software. Doesn't it have export capabilities? Can it export the data and you reformat it to the spreadsheet the client wants?

I've built a lot of Access applications to take "off the shelf" systems the last mile to actually work for the client's business. Is there really an app you can buy that does everything you need? If so, don't waste your time building something in Access. If the software you are looking at still isn't quite right, don't waste your time and money on it, just build out what you need using Access.

If you end up building in Access, we can help you but you'll have to make a start and come back with questions. Someone suggested reading about normalization. That will help you to convert your existing spreadsheets into properly designed relational tables. I doubt that any of your spreadsheets will end up as a single table. Most of them will end up having to be several tables. The one you posted will be at least four tables.
 

Donury236

New member
Local time
Today, 22:40
Joined
Jan 10, 2021
Messages
14
You've used a lot of jargon. I'm guessing that most of the acronyms are red herrings and we really don't need to know anything about them. But don't expect people to just "know" what random letters mean
LAHSCP, CES360, GDPR, LA
LA is a city in the US:) outside of that you could beat me with a wet noodle and I couldn't even guess about the others. This is a technical forum so you can get away with using technical acronyms but not business specific ones.

Looks like CES360 is some kind of software. Doesn't it have export capabilities? Can it export the data and you reformat it to the spreadsheet the client wants?

I've built a lot of Access applications to take "off the shelf" systems the last mile to actually work for the client's business. Is there really an app you can buy that does everything you need? If so, don't waste your time building something in Access. If the software you are looking at still isn't quite right, don't waste your time and money on it, just build out what you need using Access.

If you end up building in Access, we can help you but you'll have to make a start and come back with questions. Someone suggested reading about normalization. That will help you to convert your existing spreadsheets into properly designed relational tables. I doubt that any of your spreadsheets will end up as a single table. Most of them will end up having to be several tables. The one you posted will be at least four tables.

Apologies...I am just so used to shortening it all. Not that anyone needs to use them...LAHSCP(Local authority health and social care partnership), CES360 (the MSOFT program we use for equipment issuing....its a but like an online shop), GDPR (The data protection regulations), LA (Local Authority).

I spoke to my supervisor and they are spoken to the IT dept as she also likes the idea of anything that makes things run better and means we are working smarter and not harder. I am still really keen to understand how it all works, and still have to be involved in the design process as I am the one that will use it the most.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Feb 19, 2002
Messages
42,976
OK, does the application you are considering purchasing do EVERYTHING the users need? If it does, don't waste your time building a bespoke Access application. If the purchased product would still leave holes, either don't buy the product at all and build the whole solution with Access or just use Access to fill the gaps.
 

Donury236

New member
Local time
Today, 22:40
Joined
Jan 10, 2021
Messages
14
OK, does the application you are considering purchasing do EVERYTHING the users need? If it does, don't waste your time building a bespoke Access application. If the purchased product would still leave holes, either don't buy the product at all and build the whole solution with Access or just use Access to fill the gaps.
No idea. As far as we are all aware they have not even started looking at prospective solutions. Given that we work in conjunction with NHS this is something that I think might be on the back burner for the moment as we are in 'emergency mode'. Though management have a time frame of 12 months to see whats out there, tender and then start the migration....IT dept laughed and said try 3 years....

So this is out best (and free) solution in the mean time.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Feb 19, 2002
Messages
42,976
OK, then take a stab at implementing the suggestions and we'll help you through it:)
 

Users who are viewing this thread

Top Bottom