Solved

Best strategy when moving from a flat file "database" to an Access 2016 relational database?

Posted on 2016-09-24
11
57 Views
Last Modified: 2016-09-24
Hi,

At the moment we are running our member "database" in Excel (yeah, I know!)... But as the members keep piling up, the member management part becomes very time consuming. Now someone told me to, instead of importing these csv-files to Excel, doing so into a new MS Access 2016 relational database. I am quite familiar with formulas in Excel to manipulate data and to clean up the data in the fields after importing the files into Excel, but I am a bit hesitant of learning a completely new software as I do not know how difficult this might be?

The current flat file version is rather simple. It consists of fields for the parents and their children.  The main focus should be the child. As it is now I have the parents and one child per row, which means that parents who have registred several children, will be on several rows, ("records"). I guess it would make much more sense if I had one table for the parents data and one table with the "children data", as a child can have one or two parents and one or two parents can have on or several children. Whats gets a bit complicated is that we have divorced parents with several children in our current file, which means that we might need to set up some more tables as well such as "household table"???

Anyway, I just wanted first to get some input into if you think it would be feasible for me to make this transition with a good book on Access and maybe some kind help from you guys!? Or should we just stick with the Excel file?

Regards,

Dag
0
Comment
Question by:Dag Wolters
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 18

Expert Comment

by:Roy_Cox
ID: 41813840
I have successfully used Excel for years as relational databases. For convenience, you need the table for parents and the children on separate sheets.

However, Access is built specifically for this. You can import your existing data from Excel into a new database or use a template and modify it.

If you are building from scratch then there are special fields from the More Fields button called Quick Start. These are in the Table Tab displayed when you add a Table. So, if you select Name from these it will add  First Name & Last Name Fields
0
 

Author Comment

by:Dag Wolters
ID: 41813883
Thanks, Roy! I will give it a try.
0
 
LVL 18

Expert Comment

by:Roy_Cox
ID: 41813888
Post back if you need help. I am just getting into Access myself so I will help where I can, or depending on the amount of data that you have you can stick with Excel. I can help you with forms, etc for Excel.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41813897
First, set up Tables, Fields, and Relationships (data structure) in Access. Then, after you know what the data structure should look like, import data from Excel*.  As you import data, you will probably need to modify the structure to accommodate items you didn't previously consider.  Structuring data is an iterative process.

* It is often best to import to different tables than the structure you designed. Start import tables with "import" so you can easily tell them from the rest. Learn how to create APPEND and UPDATE queries in Access.

To help you design the data structure, here is a playlist on YouTube that would be beneficial:
Learn Access playlist
http://www.youtube.com/playlist?p=PL1B2705CCB40CA4CA

and here is a short book that is version independent you can read:

Access Basics
http://www.accessmvp.com/strive4peace
free 100-page book that covers essentials in Access
0
 
LVL 18

Expert Comment

by:Roy_Cox
ID: 41813899
That looks useful Crystal
0
 
LVL 19
ID: 41813902
thanks, Roy. The biggest mistake that beginners make is not enough planning.  It is common to make an Access database look like Excel because that is the easiest way to get the data in.
1
 
LVL 18

Expert Comment

by:Roy_Cox
ID: 41813910
I've been working with Excel & VBA for many years now, but never gotten around to Access. I've just started developing a database for work using Access so I'll check out your videos.
0
 
LVL 19
ID: 41813921
Roy, great! I hope they help you.

Dag, here is a free download for Access that will give you some ideas on how to keep track of your member information.  On the download page are also 2 videos explaining how to use it:

Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments
http://www.msaccessgurus.com/Contacts.htm

To relate children and spouse to a head of household, treat the head of household like a company and the children as company contacts.

Your organization would be a List. Contacts can be members of one or more lists.

This example is elaborate as I spent hundreds of hours building and refining it over the years. Don't let its complexity scare you off from Access! Even seasoned Access developers would need to spend a lot of time to understand it all.
1
 

Author Closing Comment

by:Dag Wolters
ID: 41813960
This is a very good starting point!
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41813968
Access is Access and Excel is Excel...
Access is RAD platform which includes a true relational database and excel is a spreadsheet that you just input whatever you need where you like and almost no restrictions...(except maximum columns-rows)
On the other hand Access needs design ...the better one the best it performs and most resilient to every future demand.Just think that getting the employees that have 2 kids with age greater than 15 years where the one is a boy and the other is a girl it is just a matter of a simple query....
0
 
LVL 19
ID: 41813973
Thank you ~ happy to help
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question