Question about translating a series of actions in MS Excel to operation in MS Access

[rgrocks]

I have 2 excel files with over 40k records and its gotten to the point that saving the files and running any kind of formula through the set takes a significant amount of time.



What I did in excel was I created a Concatenate to create some sort of primary key to compare between both sheets.
Value1-Value2-Value3 = P-ID

I then used the P-ID to compare a single attribute between the two sheets.
Specifically I used VLOOKUP on sheet1 to to pull in the values from sheet2 I want. Then created another column in sheet1 that checks between both values. The check was simply =if(sheet1value<>sheet2value, "Check", "Matches - OK")
I then get situations where I get "Check", "Matches - OK", "#N/A" and "#Value!". I then take a separate attribute from sheet1, sheet1route, for the cases where I get "Check", "#N/A" and "#Value!"
I then create another three columns in sheet1 and populate in sheet1route for each instance of "Check", "#N/A", "#Value!". This is the data I want

Can someone explain to me how I may be able to do this process or get to the same end result using ACCESS? And would using ACCESS be quicker than using excel for this number of records. I was told that with the size of records Im working with I should use ACCESS. I have to repeat this process regularly and currently taking 30 minutes between each operation is not good. PLEASE HELP!

[Xipooo]

Access is a RDMS which stands for Relational Database Management System. It is also a system which has RAD (Rapid Application Development) features to help produce forms, reports, and organize your data in a short amount of time compared to more traditional software development.

It is NOTHING like Excel in terms of how you relate your information to other information.

Tables contain your data.
Queries perform CRUD actions on your data (Create, Read, Update, Delete)
Reports display your data to your users in printable or PDF format.
Forms are your user interface where a user enters data, changes it, or views it.

VBA is the coding language behind Access which allows you to manipulate all of these things.

Access is far, far, far better at keeping track of your data and managing how users interact with it.

[rgrocks]

Ah, So Access is used to store and move and relate data together, not really used for analysis. I have almost 500K records and it is a bitch to do my analysis but it works I guess. For some reason I had the impression that access was excel just on a larger scale.

[Xipooo]

Ah, So Access is used to store and move and relate data together, not really used for analysis. I have almost 500K records and it is a bitch to do my analysis but it works I guess. For some reason I had the impression that access was excel just on a larger scale.

You can do analysis in Access, you just don't use formulas in the same way you do with Excel. Generally you write up reports or you build a form which do calculations on the data in your tables, then presents them to you. It is possible to see calculations in a Query which LOOKS a lot like an Excel spreadsheet, but the way you achieve the results are quite different.

I too had the impression for the longest time that Access was just a glorified Excel. But now that I've worked on over 100+ projects all with varied scopes, I can safely say the two things are about as different as Apples and Oranges. They may both be fruit, round, and grow on trees, but that's the end of their similarities. Access is MUCH more comprehensive than Excel and has a completely different way of organizing/accessing/displaying your data. Superior in almost every way possible. You just have to go through the process of learning it.