First time using Access in a long time. I want to make sure what I'd like to do is possible.

[JohnEnglish]

A bit of context and background. The last time I used access was about 15 years ago in an "Introduction to Databases" course in university. I currently work at a financial institution and we have a few of databases in Excel for tracking things. However, they're becoming unwieldy and we can't do any can of analysis or filtering on them, errors can be introduced since we're maintaining 3 separate but related databases, and it's a bit of information overload since you usually dona't want tor need to see all the info at once. This is why I'm thinking of moving everything over to Access.



The databases are as follows:

Database 1 - Listing of all deals that we have participated in and the specifics of the deal (name, sector, price, size, how much we sold, revenue generated, other banks in the deal, etc.)
Database 2 - Listing of all of our retail sales team and deals (who bought what, how much they wanted vs how much they actually got, commissions made, etc.)
Database 3 - Listing of all of the corporate division's revenue (retail and capital markets group) from all sources (deals, advisory, expenses reimbursed, etc) by month and quarter

My question is, with the limited info I've provided, does it sound like I can create something in Access that'll be able to combine all that info? The end goal would be able to put in queries like "Show everyone who bought the Facebook IPO", "Show me everyone who buys tech stocks", "Show me all the oil & gas deals we have participated in", "Show me all the deals where JP Morgan participated", etc.

[CJ_London]

My question is, with the limited info I've provided, does it sound like I can create something in Access that'll be able to combine all that info?

yes - but be aware of access limits of 2gb of data (although you can split the backend into multiple databases but with a better cohesive design) - might be better to use sql server or sql server express for the back end

Another option is to just link access to the three databases and then you can combine data from all 3 existing dbs as required.

Which is the better option, you have not provided sufficient information - what are the other db's? sql server, dbase, mySql, excel? mixture of these?

are they all accessible from one desktop?

what is required by way of new forms etc

[JohnEnglish]

yes - but be aware of access limits of 2gb of data (although you can split the backend into multiple databases but with a better cohesive design) - might be better to use sql server or sql server express for the back end

Another option is to just link access to the three databases and then you can combine data from all 3 existing dbs as required.

Which is the better option, you have not provided sufficient information - what are the other db's? sql server, dbase, mySql, excel? mixture of these?

are they all accessible from one desktop?

what is required by way of new forms etc

The 3 databases combined are well under 2 GB so that won't be an issue. The existing 3 databases are really just big Excel spreadsheets.

[CJ_London]

sounds like access is the way to go

Excel tables are rarely normalised, so look for common elements from each excel table to combine it on one table - you will probably end up with 8 or 10 tables

[JohnEnglish]

Thanks. I had a feeling that would be the case but I wanted to make sure. Yes, a lot of the data is replicated. I'm sure I can shrink it down. I guess what I need to do is to figure out 2hat goes into what Access table and the best way to link them. What do you mean by "normalized"?

[CJ_London]

normalised basically means that, with a few exceptions, data is stored only once and in the same location and fields are calculated when required in a query - there are no 'rules' as such, just principles

Databases store data 'tall and thin', excel is 'short and wide'

so for an invoice, you might have a customer table, an invoice header table, an invoice line table and perhaps a product table. You are also likely to have a customer address table (if invoice and delivery addresses are different or multiple delivery addresses) and maybe a contact table and a tax table of some sort. This all depends on your business - if retail selling you may not have a customer table for example, and if you have products, you will probably want a supplier table and the invoice line table.

an exception for calculations may be in the invoice line - you look up the price from the product table, but store it because you don't want it to change when the product is invoiced.

if you start having headings fld1, fld2, fld3, or year1, year2, year3 etc, then this is not normalised - store the data in a table with basically two columns, year and value

if you have customer and suppliers - they are basically the same record - it may make sense to have them in the same table (with a field to indicate type) or it may not - if a customer can also be a supplier, I would suggest they should be in the same table.

Google normalisation to find out more - you can go too far - employee names for example, to meet the normalisation 'rule' you might have a table for surnames - but doesn't really make sense to do so.

Other things to bear in mind when creating your tables

always have a primary key (PK) - ideally autonumber - Foreign or Family key (FK) to be used to link tables together
avoid spaces in names and make them meaningful (e.g. tradedate, employeename)
avoid reserved words like 'name','date' - google to find a list of reserved words and the implications of using them
avoid using non alphanumeric characters in names (like #) - again, google to find out more
be aware that an autonumber only guarantees to be unique, it does not guarantee to be anything else such as sequential - records are stored randomly so you need to apply order (might be a date, might be a reference number) - and should not be assigned any meaning other than as a unique identifier of the record.

[JohnEnglish]

Thank you, that makes sense. I had googled normalization earlier and just came across a bunch of information about averaging out all the values in a table and putting them on a bell curve which didn't make much sense.

The "rules" you mention at the end sound familiar. I'm going though a few lynda.com courses on Access so hopefully it'll all start to come back.