?
Solved

Migrate to Access from Excel for greater efficiency and better flexibility?

Posted on 2011-10-27
13
Medium Priority
?
314 Views
Last Modified: 2012-05-12
Hi Experts,

With the help from awesome experts from Excel forums (Especially AndrewSSD3 & Kgerb, thank you), they helped me create a macro for the attached spreadsheet.


The basic outline of what the macro does:
1) Test Combination
2) Output "Carmakes" with ZERO count to a separate Sheet.
3) Test next combination
4) 2) Output "Carmakes" with ZERO count to a separate Sheet.
and so on.... until the last combination.

The Detail:
The attached Excel workbook contains 5 sheets."Sheet1", "Sheet2", "sheet3", "Sheet4", "Sheet5".

Each Sheet has 35 columns (A:AI) and data from row2~ row55.

What the macro does is it test combination from each sheet and output the result.

EG:
starting from the
1ST COMBINATION:
1) Out of all 270 names from
Sheet1 - Column A (54 names from A2~A55)
Sheet2 - Column A (54 names from A2~A55)
Sheet3 - Column A (54 names from A2~A55)
Sheet4 - Column A (54 names from A2~A55)
Sheet5 - Column A (54 names from A2~A55)

OUTPUT carmakes that DO NOT show up in the combination. and then move on to 2nd Combination,

Sheet1 - Column A (54 names from A2~A55)
Sheet2 - Column A (54 names from A2~A55)
Sheet3 - Column A (54 names from A2~A55)
Sheet4 - Column A (54 names from A2~A55)
Sheet5 - Column B (54 names from B2~B55)

again, OUTPUT carmakes that DO NOT show up in the combination. and then move on to 3rd Combination,

it goes on until the 52521875 combination.

Sheet1 - Column AI (54 names from T2~T55)
Sheet2 - Column AI (54 names from T2~T55)
Sheet3 - Column AI (54 names from T2~T55)
Sheet4 - Column AI (54 names from T2~T55)
Sheet5 - Column AI (54 names from T2~T55)

and then output carmakes that do not show up for the specific combination.


=================================================================



With the help from experts, the macro has no problem doing that EXCEPT
1) It takes a long time to run the macro to generate result.
2) When the data gets bigger (even more columns from each sheet, meaning even more combinations), out of memory error occurs.




Hence, I was recommended by both expert Akosterkgerb (thanks!) do switch to access.

However, with my ZERO knowledge of Access, I have no idea how to migrate the data from excel to access. Even after watching some Youtube clips, I have only learnt how to create a database for people's contact/address book.

So I am seeking your professional  help on migrating the data to Access and explain to me how to achieve the same task in Access with greater efficiency (generate result faster) and greater flexibility (have the ability to add more data)?

Thank you very much.


p.s. the office version that I have on 2 different machines are 2007 & 2010.

Q-27411797-RevA--Extended-.xlsm
0
Comment
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 37041944
Microsoft has some great courses that teach you how to use access step by step.
http://office.microsoft.com/en-us/training/access-2007-training-courses-HA010274447.aspx?CTT=5&origin=HA010229580#cdmainc
0
 
LVL 12

Expert Comment

by:danishani
ID: 37041983
You might start the learn the basics indeed, so you have good foundation about database design, tables, fields, query etc.

One site I highly recommend is Access Basics by Crystal Long (MVP):
http://www.accessmvp.com/strive4peace/

And for more specific questions you can always ask here in the Forums.

Cheers and goodluck learning,
Daniel
0
 
LVL 10

Expert Comment

by:etech0
ID: 37042118
599cd.com has great courses. For the beginner courses, I like to use Microsoft because they're free, but 599cd has great advanced courses (in many programs).
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:WiilingToPaypalAGoodCoder
ID: 37042480
Hi everyone,

Thanks for the replies~

I will definitely check out the sites that you all have recommended.
.
However, I am sure that it will take me some time to get "acceptably good" at it,

Given that I have a problem that requires solution asap, I would like to know if I am heading the right direction by switching to excel.So hopefully someone can shed some light on the Questions:

With what I am trying to calculate (52521875 combinations, described in the initial post), is Access what I should be heading toward? Is Access substantially faster than Excel with what I am trying to achieve?


To get a better idea, the workbook attached to the initial post contains all the data.

thanks
0
 
LVL 12

Expert Comment

by:danishani
ID: 37042599
Well first I need to understand what you want to calculate and how you want to store the data in Access. But in general Access database is fast in calculations, alltough very complex calculations in querys can consume some time as well.

So if you give us some heads up on your data, we can help you how to sort it out in Access.

Cheers,
Daniel
0
 
LVL 19

Assisted Solution

by:MINDSUPERB
MINDSUPERB earned 664 total points
ID: 37042877
I appreciate your desire to learn Access if it would help you resolve your issue. I may suggest to look into the link below:

http://office.microsoft.com/en-us/training/choose-between-access-and-excel-RZ010253998.aspx

The ideas you get from the training will help you decide which application that serves better with your purpose. The bottom line here is each of the applications (Excel and Access) has its strength more than the other. It depends on what kind of data you are working on.

Sincerely,
Ed
0
 
LVL 35

Expert Comment

by:sarabande
ID: 37043265
can you give a sample what a 'combination' is and how you get the count of that combination.

i think 2 sheets 3 columns and 4 rows should be enough to make the problem clear.

note, with a database you could make indexes such that a search on a combination would go within exponential time.  

Sara
0
 

Author Comment

by:WiilingToPaypalAGoodCoder
ID: 37047306
Hi Ed (MINDSUPERB),

I went through the tutorial that you linked.
I feel like the data that I have is not "flat" data like the excel. I feel it's like "relational" (because there are different sheets). However, they are not relational in a the sense of the relaitonship between a person's "address", "name", "age"..etc etc. I guess I am still confused about how i can input my data in Access.


Hi Daniel  (danishani) &  Sara (sarabande),

Thanks for the reply.
I have uploaded the file to Google Document. You can see the file directly by clicking the link to google Doc below.
https://docs.google.com/spreadsheet/ccc?key=0AlhkkU7CRHI0dExrT2lOVHNSbTBTWURBY292cW5seWc

As I mentioned stated in the initial post, I am testing out 52,521,875 combinations, with the 1st combination being:
Sheet1 - Column A (54 names from A2~A55)
Sheet2 - Column A (54 names from A2~A55)
Sheet3 - Column A (54 names from A2~A55)
Sheet4 - Column A (54 names from A2~A55)
Sheet5 - Column A (54 names from A2~A55)

and 2nd combination being:
Sheet1 - Column A (54 names from A2~A55)
Sheet2 - Column A (54 names from A2~A55)
Sheet3 - Column A (54 names from A2~A55)
Sheet4 - Column A (54 names from A2~A55)
Sheet5 - Column B (54 names from B2~B55)

and so on until the last combination:

Sheet1 - Column AI (54 names from AI2~AI55)
Sheet2 - Column AI (54 names from AI2~AI55)
Sheet3 - Column AI (54 names from AI2~AI55)
Sheet4 - Column AI (54 names from AI2~AI55)
Sheet5 - Column AI (54 names from AI2~AI55)

For each combination, I am looking for CARMAKES that have ZERO occurance and output them on a separate Sheet. I can also choose to ONLY OUTPUT when there are MORE than X amount of CARMAKES with ZERO occurances.
(Eg. if X = 3, then Combination 1: if only 2 carmakes, toyota honda with zero occurance, then don't output.
If combination2: 3 carmakes, ford, bmw, fiat, with zero occurance, then output them on a separate sheet)


This is pretty much what I am looking to do.
However, given that there are 52,521,875 combination (and potentially more, with different amount of data input). I was advised that I was reaching Excel's limit and was recommended to consider Access.

thank you very much for trying to help =)
0
 
LVL 12

Accepted Solution

by:
kgerb earned 672 total points
ID: 37047447
Hello All,
Maybe this will get the ball rolling.  I created a sample database containing seven tables.  These first five tables (tbl1, tbl2, ...) are equivalent to the first five worksheets in the excel file (the “data”).  Each of the these tables contains five fields and ten records.  Obviously the real data sets are much larger than this.  The next table (tblMakes) represents all the car makers.  The last table (tblQryResults) is a static version of what is desired from the Access query.

For this much data the required resulting query will contain 3125 records (5^5).  The first record in the query corresponds to the first combination of fields in the five tables (1-1-1-1-1).  The second record in the query corresponds to the second combination of fields in the five tables (1-1-1-1-2), and so on.

Here’s what we want the query to do.  We take the contents of the first field from all five tables (first combination), put them together, and then return whichever car makes do not exist in this data but do exist in the table of all car makers.  Then we take the contents from the first fields of tables 1-4 and the second field of table5 (second combination), put them together, and return whichever car makes do not exist in this data but do exist in the table of all car makers.  This is then repeated for each combination of fields.  Obviously, since the number of combinations grows exponentially, this takes a while in Excel:).  

As said previously, the desired results from the query are given in tblQryResults.  This was not generated from Access.  I just imported from Excel.

This is the part where I run out of knowledge.  About 5 years ago I did a project for someone and picked up a little SQL.  My knowledge of Access isn’t much better.  I find it hard to *think* correctly when working with Access.  Anyway, if someone who has forgotten more about Access then I will ever learn could pick up the ball from here I’m sure it would be greatly appreciated.

Kyle

Q-27419603-RevA.accdb
0
 

Author Comment

by:WiilingToPaypalAGoodCoder
ID: 37047507
Hi Kyle,

Thank you so much for helping out and for explaining it in the "Access-language" to the experts here.
I truly appreciate your effort and the time you're taking out to help. Thank you very very much.
=)
0
 
LVL 12

Assisted Solution

by:danishani
danishani earned 664 total points
ID: 37057690
I am not sure this is what you after, but I created a merge of all columns to single column in every sheet.
Then import the sheets into Access. Then append all the sheets into a Master Table.

From there you can do a comparison of all CarMakes and count them.

Change the pathname accordingly, and then run the Merge function by opening the frmRun and click on button Run Merge. Everything will go automaticly, so wait till the message appears.

Hope this helps,
Daniel

 CarMakes.accdb
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 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