using access to make appoints w/wo excel?

[merlin777]

I've tried to create an excel workbook to record the appoints for up to 15 therapists and 25 patients and then to produce a weekly timetable for each.I got 95% of the way and it all fell apart when we realised all the therapists would need to keep it open on their pcs for most of the time. Excel doesn't really like sharing like this.

So, I've decided to learn access...

I realise this isn't an easy task for a newbie so I have 2 questions:

1) is there a template for this i can use to start with
2) could i just use access to record all the appointments and let my excel workbook import that data to do everything else?

Other considerations:

• my excel level is advanced but my access is non existant at the moment
• one of my weak areas is sharing across networks
• i'm doing this for my local hospital's physio department so there's no budget, I have to develop it at home on my laptop with 2007 and i wont be able to test it on their network (they have 2010) or have access to patient data

My preference is to keep and modify my excel sheet to use data input through access to cope the the sharing issues but i'm open to all suggestions!

 

[JHB]

Do you only want MS-Access to keep track of the data?
Who do inputting new appointments, and how are they input and how often?
Why do you want to make a hybrid, Excel and Ms-Access, can't it be all in MS-Access?
How would you like to implement it, (questions like where should the database stay, users etc. )?
If you only want MS-Access to keep track of the data, why not jump directly to use an SQL-Server?

 

[merlin777]

Thanks JHB - answers below.

Do you only want MS-Access to keep track of the data? I'm open minded
Who do inputting new appointments, and how are they input and how often?10 different people all of whom have the application open for long periods of time so they can see and make appointments ad-hoc (like they use an outlook shared calendar now) on 10 different networked PCs on and off all day, peaking at lunch times
Why do you want to make a hybrid, Excel and Ms-Access, can't it be all in MS-Access? It could be but there's quite a lot of processing to do and i haven't got the skills in access but i do have them in excel
How would you like to implement it, (questions like where should the database stay, users etc. )? in a network folder with up to 10 users as above
If you only want MS-Access to keep track of the data, why not jump directly to use an SQL-Server?i have no experience of that and i suspect it will need more involvement with the hospital's network than i either have the opportunity for or their IT dept will allow. I'm thinking of using access because the shared simultaneous data-entry needs don't fit excel but do fit access - how would i get data into an sql server without access or another kind of front end?

 

[JHB]

...i have no experience of that and i suspect it will need more involvement with the hospital's network than i either have the opportunity for or their IT dept will allow. I'm thinking of using access because the shared simultaneous data-entry needs don't fit excel but do fit access ...

But as you write you don't have MS-Access skills either so ... !
You'll also need to get involved with the hospital's network, when you place a MS-Access database in it.

....how would i get data into an sql server without access or another kind of front end?

It's a little contradictions in what you write because you will of course not use MS-Access as a front-end, but only as a database where you can have the data in, so it's a Back-end solution as I see it ..
So how would you get the data in the MS-Access database (I'm only joking)?
It is more or less the same if you put the data in an MS-Access database or in a SQL-Server database.
Google for "MS-Sql server+excel" on the Internet, you'll get a lot of description/examples how to do it.
But ofcause it is up to you, which solution you choose.

 

[merlin777]

I appreciate the advice. As you can see I'm slightly out of my comfort zone.

I thought it was possible to have a relatively simple access database to record data, which is input using access forms created in access. This for me would have been much more do-able for a beginner than doing all the processing and reports in access.

AFAI knew, its was just a case of placing this database in a network folder everyone can get to and it could have been used simultaneously.

From what you've written is seems I've greatly underestimated whats involved so I think I'm going to give up on it and put it down to experience.

 

[Rx_]

http://www.access-programmers.co.uk/forums/showthread.php?t=269948
No idea if this could help or not.
It is a new feature in Excel for shared web view. I haven't tried it yet.
End users don't need Excel installed.
If you are new to Access there is a learning curve.
If this took you 4 hours to check out and it worked, it might be something to consider.

 

[JHB]

..From what you've written is seems I've greatly underestimated whats involved so I think I'm going to give up on it and put it down to experience.

I feel bad that I have taken your courage to use databases for storig your data, it was really not my intention.

..
I thought it was possible to have a relatively simple access database to record data, which is input using access forms created in access. This for me would have been much more do-able for a beginner than doing all the processing and reports in access.

It is possible, but having 10 users to interact with the same "Front-end database" would only cause your big problems, and you also need to have MS-Access installed on all users computer.
A normally way is to split it in a Front-end and a Back-end:
Below is a link to "Ways to share an Access database":
http://office.microsoft.com/en-gb/access-help/ways-to-share-an-access-database-HA010279159.aspx

I would suggest an other solution:

• Store the data in MS-Access Back-end database.
• Create User-forms in Excel, (like those forms you would create in MS-Access), and store the input data in the Back-end database using these User-forms and queries.

But don't lose the mood working with MS-Access, only because of my opinion, I see RX_ has an other solution.

 

[merlin777]

That's a very useful link. Your suggestion of a frontend/backend database is mentioned here and looks to be a way to go for me but I need to investigate more.

I must admit to being surprised that the shared folder option not being reliable with multiple simultaneous users. I wonder if that means just a few adding the odd piece of data every few minutes or if it has to be dozens of people all adding data at the same time? The info on the link says the shared folder option is 'Adequate for small groups with light data-editing' but there's no explanation of what that is. I might post this in a separate thread and see what people say.