Access or Excel - which for an application and help required

[MorneDJ]

Hi All,




I am trying to develop an application that can calculate the average annual solar irradiance that can reach ground level for certain areas. This is to calculate potential PV electricity that can be generated in a certain area. The calculation part is rather easy, but the reference to data kept somewhere is a problem to me. I initially though of using Excel as I know nothing of databases or Access, but after significant difficulties I realize that I have to make use of a database system (unless some-one can tell me how I can do this all in Excel).


In short I need to create a database for various areas (Cities, suburbs, towns etc) containing data such as average monthly rainfall, cloud cover, irradiance, temperature etc. I think that with the help of the net that would be quite easy. Then I can use Excel to reference that data ... how I do not know.


I would prefer to use Excel as I know it very well. How to select a city in Excel (dropdown list ?) and allow it to select the correct data from the database however is a problem.


Alternatively I should perhaps use Access, but I have been scared of Access since ... actually forever. I assume that one can do some calculations in a simple spreadsheet-like form in Access. I would prefer not to do anything, or the minimal using any VB.


Can anyone assist me in the best way on how to do this. I will in the mean time do a few tutorials on Access tonight, perhaps get an ebook or something ... the Access for dummies might be worth looking into.

[CJ_London]

Simply, Excel is not good at databases, you can use dropdowns to find your city then complex formula using lookups and indexes but it becomes very unreadable, difficult to maintain and unless you lock cells off, prone to error when someone changes a formula to a value in error or on purpose and forgets to change it back.

I need to create a database for various areas (Cities, suburbs, towns etc) containing data such as average monthly rainfall, cloud cover, irradiance, temperature etc.

Would need to see some sample data and how you would use it to determine the best table structure.

Calculations in Access are done either in a query or in a form or report. If you are investigating access, your life will be a lot simpler if you do not use calculated, lookup or multivalue fields in tables which a relatively new features - on the face of it very useful, in practice fraught with problems if used inappropriately. There are some differences in calculation syntax between Access and Excel, but they are generally small and both intuitive (e.g. in Excel IF(..., in Access IIF(...)

Other tips - do not use spaces or special characters in table and field names (e.g. #-+) - use an underscore if you must.

It helps if your write a description of your business/process - what happens first, what triggers an event, what happens when an event is triggered etc Most processes can be described with 'if...then...else...'

With Excel the thinking around data is 'short and wide', in databases 'Tall and Thin'. During your investigations research Normalisation - in essence, in a database you store data once e.g. in Excel, a city will appear many times in a city column, in a database, you would have a table for cities, and the name appears once - but you can recreate the 'excel look' with a query.