Link to home
Start Free TrialLog in
Avatar of WiilingToPaypalAGoodCoder
WiilingToPaypalAGoodCoder

asked on

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

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
Avatar of etech0
etech0
Flag of United States of America image

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
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
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).
Avatar of WiilingToPaypalAGoodCoder
WiilingToPaypalAGoodCoder

ASKER

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
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
SOLUTION
Avatar of MINDSUPERB
MINDSUPERB
Flag of Kuwait image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sarabande
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
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 =)
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
=)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial