Access Vs Excel....which one for this situation

[Mike375]

A friend of mine who is in real estate is looking to learn about Excel. My knowledge is very limited. I can Conditional Format, IF, VLOOKUP etc but I have no idea what can be done with different Sheets in a file.

For example, in one set of data he has it is details on properties for rent. Sheet1, Sheet2 and Sheet3 are for 1, 2 or 3bedroom properties. I assume if a 4 bedroom property is for rent then Sheet4 would be done.

With Excel how easy (or hard) would it be to link data between the sheets. For example, show the properties where the lease is about to end (or whatever) and whereby properties that met the search criteria would comprise 1, 2 and 3 bedroom units and hence be in all 3 sheets.

If what is in 3 sheets was placed in one sheet can Excel do much with that or are you stuck with data that is spread over what appears to be several acres

If you can do this in Excel would you need to learn a lot about Excel. To put it another way, this is very easy for anyone here to do what he wants in Access. However, for someone starting from scratch would Excel allow him to get up to speed and functioning easier and quicker than Access

Thanks for any advice

 

[tehNellie]

From what you're describing, I'd personally use Access over Excel if nothing else because I can picture how to simply achieve the sort of thing you're trying to do in Access and I'm struggling to think how I'd make this easily manageable in Excel especially in the manner that you describe.

Is there a reason why properties with different bedrooms need to be split over multiple worksheets over just using one worksheet and having a "No Of Bedrooms" column which would simplify any searches/calculations etc to a single worksheet?

While you can run calculations etc in Excel that span multiple worksheets (and workbooks for that matter) I've tended to find that where you have fluid columns,row and worksheet counts that Excel starts to get tricky to manage especially when it comes to tracking down an error in a [nested] calculated cell, though maybe that's just a failing on my part when it comes to Excel.

 

[Mike375]

I believe the data is different sheets for different number of bedrooms because that is how he receives the data. There is a column in each sheet for number of bedrooms.

What I have said to him so far (but have qualified it) is I think Access takes longer to get up to any speed and you need to know more about Access before you can do much than is the case with Excel. However, with Access you will be able to do more and the finished product will be much better to look at and use.

In Excel if you had 700 rows in a sheet and 5 of those rows met a search criteria then where and how would the 5 rows appear. Would they all come to the top?? or is some type of form made.

 

[tehNellie]

In some respects this is one of those "depends on what you want to do and how" type questions.

You can search multiple sheets using the default Find/Replace option from the tool bar, this might well be flexible enough to give what you want without having to resort to anything more complicated in the shorter term. Where it isn't any good is trying to do something like "show me all 1 or 2 bedroom flats that cost less than $500 a month that will be free in 2 months time". If you want that kind of thing then I'd still recommend getting to grips with Access to begin with because it's designed to handle that kind of data filtering and while you can sort of guff around in Excel to do this you're not really playing to its strengths.

If he was an excel whizz to begin with then fair enough, but if he's going to learn stuff from scratch anyway, especially if this is for his/limited use only, then you might as well stick your nose in a book learning the right tool for the job. I've taken the approach of trying to use Excel "because it's easier" in the first instance only to end up abandoning it anyway because I'm trying to fudge a spreadsheet application to act like a database instead of just using a database.

 

[Mike375]

I tend to agree with you. When I first looked at the sample of what he had I said to him that the 3 sheets screamed out Access.

I just had a bit of a play with his sample in Access. In Excel if Iw ant to add the rent payments for properties with water views then I can sort and thus make the formula for G37 to G89. But if I want to then add the rents for a different sort the cell numbers will be different. In Access an unbound text box summing the "rent paid" fleld in combination with the Access toolbar filter and I can add everything in sight

Although there is more to learn when you have to do form design etc and etc. I think one of the attractions with Excel for many people is that it is like Word in the sense you open it and start typing.

To be 100% honest I am trying to get a few comments so as to remove any responsibility from my shoulders

 

[tehNellie]

I think the important distinction to my mind is whether it is just going to be him, at least in the first instance, using the application.

Sure forms and reports are great, look pretty and make the application much easier for people who perhaps don't really [want to] know what is going on behind the scenes to operate, but ultimately a bunch of pre-written queries (here's where you get to charge him consultancy fees ) that take criteria can be used to query and update the information with very little messing around required beside understanding how the query designer works.

I don't disagree with your analogy with Word tbh and in a lot of cases Excel is preferable to using Access and indeed is what I try and tell end users unless there is a clear case for using Access, I'm sure anyone who's tried to support a Corporate network is familiar with the 1000's of unmaintained .mdb files that crop up when you make Access available, by default 99.99% of them could have been achieved just as easily in Excel.

 

[Mike375]

This bloke is elf employed and his work is with real estate agencies as opposed to buyer or renters.

I am an insurance agent and you are right about institutions and Excel/Access. Most of the employees in an insurance company using Excel are mainly doing it because it is easier for them to put column type data together than it is in Word. They might have some simple calculation. It is also used a lot to send information to people like me where their data base has out put policy holder policy/details etc.

There quote system that is supplied fee to agents is often based on Access tables and the quoite system of some companies also includes the agent's policy holder details.

The Actuaries live on Excel

I find with Access if your own data base is very extensive and been there for years then if you want to make something tomorrow the chances are very good that you already has something very similar in your data base.

A couple of weeks ago I made a little data base for a mate of mine for monitoring his diet....progressive calories totals, food type, time of day, date etc and etc. I already had something almost identical for insurance premiums. I left the field names the same and showed him. He was puzzled how Premiums, Commissions etc could do his diet

 

[Rich]

Just make sure the bloke doesn't get hooked on Macros like yourself

 

[Mike375]

Just make sure the bloke doesn't get hooked on Macros like yourself

He will be going to one of the courses, whether it be Excel or Access. I looked at a couple and Excel generally had 3 one day courses and Access was 3 three day courses and fourth day for VBA and with each course dependent on having done a previous one.

I have not looked at any big Access books since Access 95 and 97 and those books had heaps of stuff on macros.

So won't that mean he will need to go through macros.

 

[Rich]

So won't that mean he will need to go through macros.

Only an auto exec if he's got any sense, Access has moved on since 95, get him to learn to just say no to macros, or quickly convert them to vba

 

[boblarson]

Access has moved on since 95,

Have you been in Access 2007 recently? The move is back to macros (not that I'm thrilled with that idea, but that's what they've done, including adding error handling to macros).

 

[Rich]

The error handling is a move in the right direction, but what about the general inefficiency of macros?

 

[Mike375]

Rich,

My point is that courses and books will have lots on macros, so how does new person avoid them.

I read that Access 2007 has enhanced macros and one of the enhancements was having the macro attached directly to the form.

From a practical point of view I think the main plus of VBA is because of forums such as this. Easy to copy/paste code and in addition the vast majority of forum members who have extensive experience with Access use code.

 

[Mike375]

Rich,

Opening line of the start of the VB section from a big Access 95 book

"Most Access applications you create do not require you to write a single line of of Access VBA code. A few commercial Access applications rely primarily on macros rathert ahn Access VBA code for automating applications."

Special Edition Using Access 95

Written by Roger Jennings

Contributors

Matthew Harris
Susann Novalis
Stan Leszynski

 

[tehNellie]

Have you been in Access 2007 recently? The move is back to macros (not that I'm thrilled with that idea, but that's what they've done, including adding error handling to macros).

NoooooooooooOOOOOOOOOOOOOOooooooooOOooOOoooo!

While I can see the benefit of Macros for someone who just wants to knock up something quite powerful quickly without having to learn vba, trying to figure out what a macro driven db application (with no documentation) does is a royal pain in the behind.

My point is that courses and books will have lots on macros, so how does new person avoid them.

Start using VBA from the beginning. I know that's easy to say coming from a scripting background where VBA isn't really that difficult to get your head around but I hate macros almost as much as Robbie Williams.