Can MS Access help me with 3 dimensional data?

[mia...@gmail.com]

Hi,

I am new to MS Access but know some programming languages, like R and Matlab.

I have a 3-dimensional dataset which is presented in several xls files. For each quarter, I have an xls file like this:


Bank A Bank B Bank C
Asset 30 50 60
Loan 70 8 90
Investment 14 12 9

I have 16 such files, each for a quarter. One problem is that sometimes we will have more fields because accounting rules change, e.g.,

Bank A Bank B Bank C
Asset 30 50 60
Loan 70 8 90
Fixed Asset 40 2 3
Investment 14 12 9

Sometimes a new bank emerges

Bank A Bank B Bank C Bank D
Asset 30 50 60 7
Loan 70 8 90 85
Fixed Asset 40 2 3 6
Investment 14 12 9 4

I have some standard textbook for MS Access, but just don't know how to start. Could MS Access to deal with the 3 dimensional data? I would like, at least, to read the data from the existing Excel files, to build a database, and to write all of the data to excel. That means, all resulting xls files should be in the following form, which contains all banks and accounting fields that are present in the 16 quarters. Another issue is that sometimes (not frequently) the bank name might change. For example, Bank C might become Bank CC.


Bank A Bank B Bank C Bank D
Asset
Loan
Fixed Asset
Investment

Could someone tell me which chapters I should read or highlight the process to work with it?

Thanks,

Miao

[David Hare-Scott]

Access can deal with this. You can do it all in two tables, something like:

tblBankDetails
===========
cntID (pk)
lngBankID (fk)
dtmQuarter
curAsset
curLoan
curFixed
curInvestment

tblBank
======
cntBankID (pk)
txtBankName
[other bank attributes]

You have one record in tblBank for each bank, if the name changes it matters
not as the relationship to the data remains the same. You would populate
this table first.

In tblBankDetails you have one record for each bank for each quarter,
lngBankID is a foreign key linked to cntBankID in tblBank.

You would load the data from the spreadsheets using a series of append
querys. You can present it or export in whatever format you like using a
select query that joins the two tables.

I don't what book you are reading chapters of but for this a basic book on
normalisation and database design would be best. Eg "SQL and relational
basics" by Pascal.

David

[Access Developer]

Yes, you will first need to redesign your data structure so it follows
"normalized" structured relational database design priniciples... Access is
not, and not intended to be, a "spreadsheet on steroids" (e.g., identical to
Excel only faster).

Get a good book on relational database design, such as "Designing Effective
Database Systems" by Rebecca Riordan.

"Three dimensional data" is not a relational database term.

If you try to follow the design you use in Excel, you will (sooner rather
than later) find it "rises up to bite you in the tender places". If you
redesign according to relational design principles, you are likely to be
able to accomodate more-or-fewer banks, and more-or-fewer categories, with
little or no change at all.

Chances are, you'll end up with more identifying information, fewer or only
one value per record, and, thus, lots more records. But it will be much more
adaptable and easy to accomodate the changes you describe when they happen.

For the data you describe, each record might contain fields as follows:

Time period
Entity (bank)
Category (type)
Value

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

<mia...@gmail.com> wrote in message
news:bce476fc-4738-4cd9...@googlegroups.com...

[mia...@gmail.com]

Thanks.

R has a few packages related to database like RMySQL , RODBC , Hmisc. Could any R package deal with the problem?

Thanks,

[Access Developer]

Sorry... I don't know what "R" is that you are talking about. MySQL is a
more-or-less relational database, ODBC (Open Database Connection) is a
protocol for one database accessing another kind, but I'm not familiar with
RMySQL, RODBC, nor an "R" that has "packages".

With proper design of your tables, a relational database such as Access
(subject of this newsgroup), MS SQL, MySQL, PostgreSQL, or various products
from Sybase could handle the data -- but, of these, Access is the only one
with user interface ability (though all the others I list can be used to
store data for an Access 'front end').

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

<mia...@gmail.com> wrote in message

news:63b9eb68-8024-4aa0...@googlegroups.com...

[Access Developer]

Oops... sorry, I hadn't reviewed your original post and wasn't thinking
about "programming languages". I've used quite a few different programming
languages over the years, but haven't used "R", so it didn't spring to mind.
Chances are high that R's "flavor" of ODBC will support Access or SQL
databases. Microsoft has an "express" version of SQL that is free, and can
handle up to 4GB of data.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

<mia...@gmail.com> wrote in message

news:63b9eb68-8024-4aa0...@googlegroups.com...

[David Hare-Scott]

mia...@gmail.com wrote:
> Thanks.
>
> R has a few packages related to database like RMySQL , RODBC ,
> Hmisc. Could any R package deal with the problem?
>
> Thanks,
>

Any relational database can handle this. Whether 'R' qualifies I cannot say
knowing nothing about it. Keep in mind that you will probably want more
than just the database to store the data, you may need tools to build it, a
user inteface and reporting capability. Not all products have all those
things.

D

[Aikistan]

On Tuesday, May 7, 2013 10:57:04 PM UTC-4, mia...@gmail.com wrote:
> Hi, I am new to MS Access but know some programming languages, like R and Matlab. I have a 3-dimensional dataset which is presented in several xls files.

Excel can work with multi-dimensional data but it's tedious (it may be less tedious than learning Access, though). You would need a summary workbook to collect by copying from or by linking to the individual quarter workbooks. Using named ranges would make it straightforward.

If I knew that I never needed to worry about anyone changing the source workbooks and that I wouldn't need to run reports repeatedly, I would do it in Excel.

Stan