To throw in or not? (1 Viewer)

[nucoeus]

Hi all,

Newbie to the forum and pleased to meet you all!

I am considering shifting from Microsoft Excel 2010 to Microsoft Access 2010 to manage a rapidly growing fixed asset register.

I would want to build the database from scratch (rather know it inside and out than use a pre-built one) tailored to my companies needs.

My question is this... is Microsoft Access 2010 suitable for this need?

I would want a register of assets with details, ability to post depreciation (straight line, reducing balance etc.), reports for journal entries etc.

My research suggests yes but I want to run it by a few experts before I start this mission!

I have also bought Access 2010: The Missing Manual by Matthew MacDonald to bring me up to date on using the software since my ECDL days long ago.


Any feedback is greatly appreciated!

nucoeus.

 

[RainLover]

Acess is certainly a much more appropiate tool than excel.

Is there anything you need to know in particular.

 

[nucoeus]

Nothing in particular I'm gonna read this book before attempting to set up the database just wanted to know its not a wasted path!

Oooh thinking about it importing and exporting data from Excel to Access - take it can be done? I imagine you need to input into Excel correct field headers and such and hey presto job done?

Have you ever used it for assets or something similar? Just wondering how depreciation would work in it...


Many thanks for your reply it is much appreciated!

nucoeus.

 

[jdraw]

As Rainlover said, (relational database)Access is a more appropriate tool for this sort of application than Excel(spreasheet). However, expertise in Excel doesn't necessarily mean any expertise in Access. Most issues with
database projects (especially for people new to database) relate to design.
Access is a tool. But it does not "build a database" for you. Spend some time doing research on Normalization. Get all the facts about the proposed application before you start using Access. It isn't a design as you go sort of thing.

Here are a few links to help.

Excellent article on design/relational principles
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

Entity relationship diagramming
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

http://www.rogersaccesslibrary.com/forum/uploads/5/12-Steps_to_Better_Databases.zip
There are a number of free data models here (and some tutorials)
http://www.databaseanswers.org/

Good luck.
The secret to a useful, maintainable database is good design.

 

[gemma-the-husky]

out of interest, it took me just over 2 days to build a plant register, with flexible depreciation calculations including straight line and reducing balances/ with or without a residual value, including preparation of depn journal for posting to a NL, and month end/year end processing

this included facilities to import details from an existing spreadsheet.


like most accounting applications, access is a much better tool than excel fro such projects.

 

[nucoeus]

Wow Gemma that sounds exactly like what I am looking for! I see your a super moderator so id assume a Access pro - would it be hard for a beginner to do?

Given you've done the exact thing Im looking for if your have any tips / planning documents etc. youd be willing to share id much appreciate it!

jdraw - ill research normalization and have downloaded those articles - sounds serious stuff!

Thanks fro the continuing advice,

nucoeus.

 

[RainLover]

Wow Gemma that sounds exactly like what I am looking for! I see your a super moderator so id assume a Access pro - would it be hard for a beginner to do?

Given you've done the exact thing Im looking for if your have any tips / planning documents etc. youd be willing to share id much appreciate it!

jdraw - ill research normalization and have downloaded those articles - sounds serious stuff!

Thanks fro the continuing advice,

nucoeus.

Gemma's advice will be of great assistance to you later on. First and most importantly listen to jdraw. Normalisation is of utmost importance. If you don't get that right forget everything else.

I would like to also suggest that you learn a good Naming Convention. Nothing too complicated, just something simple. You can search this forum for examples.

 

[nucoeus]

Hi Rainlover,

Normalization is on todays agenda after a brew and shower!

I'm going to google it but if you / anyone has any links you think are good would be very interested to read up on them!

I'll be sure to tap Gemma's knowledge when the time comes, parties willing of course

Regards,

nucoeus.

EDIT: No. 1 and 3 on 'The Ten Commandments' - I am going to print these out and respect the knowledge that has been handed down to me

 

[nucoeus]

Okay had a look at Normalization.

My fields for my database should include:
1. Type (vehicle / plant / F&F etc.)
2. Cost
3. UEL
4. Depreciation basis (straight line / reducing balance)
5. Accumulated depreciation
6. NBV
7. Disposal proceeds
8. Profit / loss on sale
9. Description
10. Site

I'm thinking that my database may be too simple for Normalization to hold real meaning - don't shoot me!

Primary key = auto-assigned ID number (no specific ID required)
1st forms: (i) Type and (ii) Cost
2nd forms: (i) Description, site and (ii) UEL and disposal proceeds
3rd forms: (ii) Depreciation basis, accumulated depreciation, NBV, profit / loss on sale.

Given the type of the vehicle has no subsequent implications I have tied the description and site as a 2nd form which also is a dead end but likely to be more relevant to asset type than anything else in my database.

Cost on the other hand drives most other relationships. I wouldn't say UEL is directly related to cost as my 2nd form nor is disposal proceeds but these are the most related out of my other variables. Disposal proceeds does not depend on the UEL thus also listed as a 2nd form.

Once a UEL is assigned all other 3rd forms depend on this i.e. depreciation related variables.


So what do you all think? Good or a bad start?

EDIT: Reading through your post rain: access-programmers.co.uk/forums/showthread.php?t=225837

 

[RainLover]

I'm thinking that my database may be too simple for Normalization to hold real meaning - don't shoot me!

So when is a Database large enough to require Normalisation. Normalisation requires the creation of more than One Table. There are certain Rules that apply to those new tables. So if you think your database only requires one table then I would suggest you use Excel. BANG BANG

There are many writings on Normalisation. Some are very complicated. Try searching for the one written by Microsoft. I find it simple to understand.

You need to understand Naming Conventions also. I will attach something for you to read.

When describing Tables here it is the design not the contents that we are interested in. The follow are examples of two tables.

tblClient
ClientPK (As Autonumber) PK for Primary key
ClientName (Text)
PhoneNumber (Text)
Address (Text)
CityFK (Integer) Foreign Key to tblCities

tblCities
CityPK (Autonumber)
City (Text)
PostalCode (Text)

 

[nucoeus]

Hi Rain,

Thanks for the input - what did you think to my attempt at normalizing my database above - did it make logical sense?

I've read this document on the website (access-programmers.co.uk/forums/showthread.php?t=225837) noted in my above post - very informative!

I will be putting that into strict practice along with the 10 commandments also listed on this website

I've saved your document for reference but as far as what I've done does it make sense to you? Hopefully im heading in the right direction.

Thanks again for everyones help I feel like I'm off to the right start here.

 

[RainLover]

The problem is that I cannot follow what you have done.

Can you post a Pic of your Relationships. You do know what I mean don't you?

If you don't then please say so.

 

[jdraw]

Take a quick read through this article that I mentioned in previous post, it will give you a working example of Normalization and lead you to tables and relationships.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

I agree with Rain, it's hard to determine what you have done.

 

[nucoeus]

Hi everyone,

Ill come back to you on Monday evening - I'll make sure to have everything mapped out and defined rather than ad hoc the way through it today. All my reading should be hopefully done today from your suggestions so seems a good plan

Should make it easier to progress forwards!

Regards,

nucoeus.

 

[nucoeus]

Hi all,

Slight delay due to family issue so hopefully ill get back to you all tomorrow with an outline.

I have read some of the documents today though and have a few questions / outlined some planning for critique to help me tomorrow if possible?

1. The RDBM principles document notes that only raw data should be stored in tables with no independent data. My independent data attributes would be (i) Accumulated depreciation (function of cost, UEL and/or depreciation basis) (ii) NBV (function of cost and accumulated depreciation) and (iii) Gain / loss on sale (dependent upon NBV and proceeds on sale). Am I correct in thinking these will not then be represented in my tables?

2. My model is as simple as: Register of fixed assets.

3. My only entity is the assets as this is the only collection of items. This is what has stalled me a little - if entities drive the no. of tables do I only need one table?

4. My entities attributes (excluding dependents named above) are:
- Description
- Date of acquisition
- Date of disposal
- Cost
- UEL
- Depreciation basis (SL / RB)
- Proceeds on disposal
- Site (which site the asset is allocated to - I expect this may be an entity in itself but am unsure as it would be stand alone with no other attributes)
- Employee name (some assets are assigned employee names - again I expect this to be an entity in itself and would contain (i) first name, (ii) last name and (iii) employee number attributes)
- SLE (short life election) - a simple true or false attribute.

5. The normalized functions I am going to have to read over again as it didn't take the first few times! New day should hopefully prove illuminating but any recommended examples (or a working of my fixed asset register) would again prove useful! :banghead:

6. My primary key will be an autonumber serving as the foreign key in any other entity tables.

7. Naming conventions - basically I am going to follow your guidelines to the letter Rainlover as I plan / build the database.

8. Relationships as follows:
- Each asset will have a site, description, date of acquisition, cost, UEL, depreciation basis and statement of SLE.
- Depreciation basis will either be SL or RB basis.
- Assets may have a date of disposal and proceeds on disposal (generating gain / loss on sale).
- Assets may have an employee name.


Hopefully I'm on the right track with this but very open to constructive critique!


Regards all,

Coeus.

 

[RainLover]

One thing I need to poit out.

Calculate Fields should not be stored.

e.g. NoOfUnits * CostPerUnit = $X.XX

Store NoOfUnits and CostPerUnit but Calculate $X.XX as and when you need it. Know as Runtime. There are some very rare occassions for breaking this rule. In general always follow the rule.

Once you have done your research create your tables and their relationships, then post it here for us to look at and give some better feedback.

 

[nucoeus]

Hi Rain and all,

Thanks for the continuing support!

I have made a ERP though I seems to have alot of one-to-one relationships suggesting I may not have my entities correct.

In my previous post I identified the asset, employee and potentially site as the only collections of items deemed as 'entities' but i'm not sure if my understanding is correct.

In my ERP I have included comments to try to explain my thought process but I have assumed that HP agreements (of which there may be many), UELs (which there may be many) and SLE (short life elections which will be a simple yes or no) are entities as they do have collections of items (though not collections of attributes ).

As you can probably see I'm hitting a bit of a wall... :banghead:


If you can give me any feedback would be much appreciated!

Regards,

nucoeus.

 

[Beetle]

Looking at your ERP, it appears you are only considering one side of the relationship in some cases. For example, you have a one-to-one relationship between Asset and Site because your interpretation of the relationship is that each Asset can only belong to one Site. But that's only one side of the relationship. Can a Site have more than one Asset? I would certainly think so in most cases. Also, you need to be sure that your interpretation of the one Asset per Site rule is correct to begin with. For instance, could an Asset ever be moved from one Site to another? If so, then the true nature of the relationship is that an Asset can belong to more than one Site, just not at the same time. These same questions might be asked about your other one-to-one relationships as well.

You need to really think through these things before hand. You could expend a lot of time and effort designing tables, queries, forms and reports, then realize your table structure does not allow for some necessary functionality and you're going to have to rethink your structure and subsequently redesign a multitude of objects (queries, forms, reports) that were based on the faulty table structure. When you decide that you have a relationship between two entities, be sure that you are analyzing both sides of the relationship so you can determine its true nature. It's quite possible that your Asset <--> Site relationship is actually many-to-many, not one-to-one, but it needs further analysis to be sure.

BTW - one-to-one relationships are not common, so it's unlikely that you have that many.

 

[RainLover]

In your Pic you have a note that says, " Each Asset has only one UEL" The point is which UEL. This is why you need a One to Many.

What exactly is a UEL. Is it an actual unique thing or is iy one of many. Example if it was a Town in an address it would only be used once in the Parent Record. But it could be used in many Records even though it is unique. So entering Towns you look up which Town is applicable for each record.

Hope I did not confuse you.

Your post count is such that you can post a copy of your Database with all the relationships set.

Please post a Copy using Access 2003.

Not everyone has the newer versions of 2007 and 2010.

 

[nucoeus]

Hi all,

I have updated and attached a new ERP based on your comments RainLover and Beetle. Keep them coming please I will perfect this before moving on!

As in the last image I have included my thoughts on the relationships so you can understand my approach to these (and correct if necessary :S).

I would be very interested to compare to Gemma's ERP that took two days if willing? Would help my understanding alot to compare and contrast.

Hope you all had a good day,

nucoeus.