Sharepoint best practices

We got thrown into sharepoint as part of a company reorganization.  It was introduced as a filesharing solution.  We knew of it but not all the ins and outs and to be honest we have done the best we could with what we knew and what we could read to get it up and functional as fast as possible.

Now we have an issue we cant just work around.

We have 14 offices that share 28 spreadsheets.  They were able to make this work before with some options in excel and the fact that they were all on the same network.  However, now these spreadsheets are hosted in sharepoint and there is no way to co-author them.  I mean co-author in the literal sense of the word, multiple people entering data simultaneously.   The It department had very little knowledge of what they were actually doing until today and its too far to turn back.

So now we need to build a better solution quickly.  What we really have is about 6 true datapoints.  However, what they have done is build a spreadsheet for with a sheet for each month and a column for each day to track income for each line item for each day.

I was looking for a better way and started looking at custom lists and while they will give us what we need in the end what we really need is probably and info path form to collect the data and then something to report on the lists.  I could create an access database pretty quick but if we are really trying to get them off of file based solutions then this is really not the right answer.

Just looking for a good idea to move forward.  BTW we are tracking rental income. So a record would look like
company, location, start date, end date, 8/1, 8/2, 8/3.....
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

As you are familiar with Access;
The equivalent of an access table is a list. Unfortunately list are not relational.  Thus in Access you can have a parent child relationship and delete a parent and the children will automatically be deleted.  In a SharePoint list you can have a related list to be able to retrieve information but it does not work the same.  Although if you add workflows you can programmatically do it. Ultimately what this means is that you will most likely build a single list (table) that contains all the fields that you need.  You will also need to add others to control the form or processing. For example; using a list field (lets say status) you can control what your form looks like (using InfoPath) based on the status (New, In Process, Returned, etc). You can also build Calculated fields that use other fields to get their value. Once you have your data entry form and list you can add workflows to do more.  You can also build different views of the data and even control what views people see based on their department (you can put different people into SharePoint Groups and manage them that way).

I would start by making a list with all the fields that are in you spreadsheet. Set the datatype, validations and Calculated fields.  Then work on how you would like the data entered (InfoPath).  How many views (forms) do you need (ie. New Entry Form, Update Form, Completed, etc). What fields should be visible and editable on each. Then determine what you need to report. Does it need to be grouped? Restrict view access to specific departments, etc.

These kind of projects seem impossible at first, but if you take it apart piece by piece you will have a solution.
I hope this helps some.

Good Luck!
I think InfoPath has been deprecated so I would not build a new application using it.  Also, unless you have the latest version of SharePoint and Office 2013 or O365, I wouldn't build an Access web form since A2010 and earlier web forms have also been deprecated.

If you have only old versions of SharePoint and/or Access, go with a list.  You can link the list to Excel or Access for reporting.

As I reviewed your description of the problem and your experience with Access; It occured to me that when you have a spreadsheet that has more than one person editing it at the same time, this should be changed to an Access database.  You could initially create an access database to collect and update the data and then display the results in an Excel Web Part in SharePoint.  Once you have that process developed you can look at replacing the Access Database with SharePoint list, InfoPath Forms and workflows.

So you could take a phased approach; Starting with a partial SharePoint solution and migrating to a full SharePoint solution as you gain more user input and requirements. This would allow you to use your experience in Access to build a strong back end and refine the requirements for the front end (SharePoint Groups, Permissions and different views).

Finally, What version of SharePoint are you using? Do you know your SharePoint Architecture (1 Tier, 2 Tier or 3 Tier)? Do you have a separate App Server?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
john15nltAuthor Commented:
I think the overall consensus is this needs to be a database, and since we have sql server available I would lean in that direction rather than create an access database that would eventually need to be migrated to sql.
I believe we currently have 2 tier architecture with the app and webserver running on the same box and database server on its own box.

I appreciate all your help in this matter.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.