We currently have a spreadsheet to track all of a clients medical's bills and keep a running total? I'm trying to decide whether to continue to track these medical bills in the spreadsheet or create a table and make it a part of the client db. I'm leaning toward keeping the spreadsheet. It seems to be a task a spreadsheet was designed for and I can link it to the db or import it as needed. Any opinions on which is better? What would be the reason to to give up the spreadsheet and make it a part of the db?
Excel vs Access?
- Thread starter Gilrucht
- Start date
- Local time
- Yesterday, 23:47
- Joined
- Feb 19, 2002
- Messages
- 47,090
Access is a relational database and relational databases are far better at working with data than a spreadsheet. It sounds like right now you have a separate spreadsheet for each client. With a database, you would have one table that holds the data for all clients which would make consolidated reporting infinitely easier. You can also consolidate your data entry since you already have a database that is used for tracking other client information.
Pat,
Thank you for the reply. Let me clarify a few things that might impact your opinion.
In this particular instance there would be never be a situation where we we would want a consolidated report. Unlike my other tables where we might want a report of all clients or all cases there would never be a reason to want a report of all clients medical bills. So I was actually looking at your issue as as an arguement in favor of Excel. If we will alalways work with the data by client my thinking had been this was the rare situation wher I was better off storing the data by client rather than in one central container.
I was looking at two issues. The first was the advantage of Excel over Access in keeping a running total. Unless I have misunderstood some of your prior posts, Access' ability to keep a rumming total at the form level is mot an area where Acess shines. It is really important for us to keep a running total so we will know when a client has exhausted their Insurance Benefits. This seems to be an area where Excel would have an advantage.
The second, and here I may I be exposing my ignorance of Access, is Excel's ability to increase rowheight and use wordwrap. This allows lenghthy data entry'sin fairly narrow columns which makes it easy to insure all your columns fit on one page when printing. I have not been able to find a similar function in Access.
Now, having said all this, I wouldn't have asked the question if I was that committed to Excel and that certain it was better than Access in this particular situation. I am not disagreeing with you. I merely wanted to let you know the issues I was looking at. I may very well be, and probably am wrong. Would I still be better off with Access?
`
Thank you for the reply. Let me clarify a few things that might impact your opinion.
In this particular instance there would be never be a situation where we we would want a consolidated report. Unlike my other tables where we might want a report of all clients or all cases there would never be a reason to want a report of all clients medical bills. So I was actually looking at your issue as as an arguement in favor of Excel. If we will alalways work with the data by client my thinking had been this was the rare situation wher I was better off storing the data by client rather than in one central container.
I was looking at two issues. The first was the advantage of Excel over Access in keeping a running total. Unless I have misunderstood some of your prior posts, Access' ability to keep a rumming total at the form level is mot an area where Acess shines. It is really important for us to keep a running total so we will know when a client has exhausted their Insurance Benefits. This seems to be an area where Excel would have an advantage.
The second, and here I may I be exposing my ignorance of Access, is Excel's ability to increase rowheight and use wordwrap. This allows lenghthy data entry'sin fairly narrow columns which makes it easy to insure all your columns fit on one page when printing. I have not been able to find a similar function in Access.
Now, having said all this, I wouldn't have asked the question if I was that committed to Excel and that certain it was better than Access in this particular situation. I am not disagreeing with you. I merely wanted to let you know the issues I was looking at. I may very well be, and probably am wrong. Would I still be better off with Access?
`
Last edited:
Gilrucht,
As for which is better, I can only state my own opinion, which with my level of knowledge is not all that important...but I would choose Access. Why? Convenience, organization of your data, and an easy-to-use interface for your users. This is especially true if you already have a database...why not keep everything organized in one place?
However, I can say that Access can do what you want it to do. It can calculate running totals quickly enough with a little code. You can create reports to show you who is close to their limits and who is over. If different people have different benefits you can compare the used benefit to the total available benefits and get a list of those who are over. This could be done in Excel, but...well...I know I wouldn't want to mess with it.
Column sizing - you can use the "Can Grow" property to do word wrapping on your reports.
Again, I don't know your situation as well as you do, but I would choose Access.
As for which is better, I can only state my own opinion, which with my level of knowledge is not all that important...but I would choose Access. Why? Convenience, organization of your data, and an easy-to-use interface for your users. This is especially true if you already have a database...why not keep everything organized in one place?
However, I can say that Access can do what you want it to do. It can calculate running totals quickly enough with a little code. You can create reports to show you who is close to their limits and who is over. If different people have different benefits you can compare the used benefit to the total available benefits and get a list of those who are over. This could be done in Excel, but...well...I know I wouldn't want to mess with it.
Column sizing - you can use the "Can Grow" property to do word wrapping on your reports.
Again, I don't know your situation as well as you do, but I would choose Access.
mresann
Registered User.
- Local time
- Yesterday, 20:47
- Joined
- Jan 11, 2005
- Messages
- 357
The main purpose of an Excel Spreadsheet is, well, to work as a spreadsheet. They are far quicker for analysts to quickly create different scenarios with a base amount of data, to quickly create calculations which involve relative locations to other cells, and it has a much more robust reporting functionality.
However, the ease and convenience of use that the Excel Spreadsheet provides are the key NEGATIVES to its functionality as a database. Data stored in Excel databases are prone to misplacing, deletion, and the only way to save the data is to save the worksheet. With databases, once the data transaction has been conducted, the data is "saved"; no file saving is necessary.
It comes down to this: Use the spreadsheet for mathematical and financial calculatons using scenarios and and goal seek options, and use Access for data storage and transactions.
However, the ease and convenience of use that the Excel Spreadsheet provides are the key NEGATIVES to its functionality as a database. Data stored in Excel databases are prone to misplacing, deletion, and the only way to save the data is to save the worksheet. With databases, once the data transaction has been conducted, the data is "saved"; no file saving is necessary.
It comes down to this: Use the spreadsheet for mathematical and financial calculatons using scenarios and and goal seek options, and use Access for data storage and transactions.
- Local time
- Yesterday, 23:47
- Joined
- Feb 19, 2002
- Messages
- 47,090
You are confusing a running sum with a balance. A running sum is what you would see in a check register - each record's amount is added to the previous balance showing balances for each individual record. What you need is the current balance which is very different and quite simple to get. Just add a footer to your subform (you may have to adjust the height of the control on the main form in order to make the footer visible) and add a control with the ControlSource of:
=Sum(YourField)
As to varying heights, forms can't automatically resize to accomodate extra data but reports can. Use the CanShrink/CanGrow properties.
- Local time
- Yesterday, 22:47
- Joined
- Feb 28, 2001
- Messages
- 30,177
Gilrucht, your question implies your answer because you are using the language of spreadsheets. Not surprising, since you are also using spreadsheets. Why WOULDN'T you use their language?
However, the issue with spreadsheets is GROWTH. You are describing a small business enterprise. To be honest, a spreadsheet is perfect for very small businesses. But if you ever want to grow, spreadsheets will be limiting.
When you use language such as "we will never want to do..." - odds are you will find a need within 6 months. (Murphy's law in action.)
I'll toss one your way. In the USA, a spreadsheet probably doesn't meet the requirements of HIPAA regarding security of patient records. OK, maybe you don't care about that - but someone does.
Let me give you some very important advice. Learn to do regular, almost excruciatingly regular, backups. Spreadsheets are an all-or-nothing-at-all thing. If you lost the sheet, you lost it all. Databases can be more easily repaired. There are ways to devise archives.
In general, the greater flexibility for RECORD management lies with Access. The greater flexibility with data ALTERATION lies with Excel.
However, the issue with spreadsheets is GROWTH. You are describing a small business enterprise. To be honest, a spreadsheet is perfect for very small businesses. But if you ever want to grow, spreadsheets will be limiting.
When you use language such as "we will never want to do..." - odds are you will find a need within 6 months. (Murphy's law in action.)
I'll toss one your way. In the USA, a spreadsheet probably doesn't meet the requirements of HIPAA regarding security of patient records. OK, maybe you don't care about that - but someone does.
Let me give you some very important advice. Learn to do regular, almost excruciatingly regular, backups. Spreadsheets are an all-or-nothing-at-all thing. If you lost the sheet, you lost it all. Databases can be more easily repaired. There are ways to devise archives.
In general, the greater flexibility for RECORD management lies with Access. The greater flexibility with data ALTERATION lies with Excel.
Similar threads
