Vlookup across two database tables

[intrapostal]

Hey all, and apologies if this sounds very 'basic'--I'm new to my access journey!
I have one VERY large database in Access already (over 2mill rowsx 140 columns), and a smaller CSV file.
Is there a way of opening both in access and then doing essentially a Vlookup to retrieve accounts found in both?

Many many thanks in advance

 

[MajP]

In a database you do this by joining the two tables on the account ID or whatever the common field/s is/are using a query.

Create a simple select query - Microsoft Support

Queries help retrieve information from your Access database. Here's how to create a simple select query.

support.microsoft.com

 

[arnelgp]

you can also use Query that Inner Joins both tables and csv on a Common field (Account field?).

 

[intrapostal]

Thanks both. I'll give these a whirl

 

[Gasman]

We call it DLookUp() in Access.

 

[spaLOGICng]

Hi,

You can link to the CSV File in Access. Go to the External Data Tab, then click the New Data Source button from the Ribbon, and under the File Menu, select Text File. You will be taken through the Spec Definitions, i.e. delimiters, data types, etc., and once complete, the CSV Will be linked. The Table Name will be the same as the Filename. You can rename the Linked CSV Table Name in Access to suit your needs.

Once the CSV is Linked, you can create a Query and JOIN your Access Table to your CSV Linked Table on the appropriate Fields.

Tip: Based on your Post, I feel your Table could go through a lot of normalization, so that the Data is split out into separate Tables in the most unique fashion. This is the beauty of an RDBMS. Because you are new to Access, I recommend first using the "Analyze" Table feature under the Data base Tools Tab on the Ribbon to assist in doing this. The tool has its quirks, but I feel you would benefit in understanding of RDBMS's and Data Normalization. This may complicate your existing process, but with a little finesse, you can build out your import process through the use of Queries and VBA to fit the data model.

Best Wishes!

 

[Pat Hartman]

Domain functions do not belong in queries or in VBA loops. The proper method is to join the two tables.

Also with a 2+million row table, I would never join it to a linked flat file. Import the .csv to a table. Add indexes if appropriate. That will make the join as efficient as it can be.

A real world example --
A few years ago, I was employed to teach the analysts at a small startup company how to use Access to make their Excel work more efficient. Every day, they would come in and download yesterday's data from our transaction processing company. They would then import the data into their spreadsheets and start their VLookup()s running and head out for breakfast. 2-3 hours later, the files would be ready to be used for analysis.

I started with one simple change. Import the downloaded data into Access and join the files rather than doing VLookup()s or the Access equivalent which is dLookup(). Less than 5 minutes later, the data was exported to their Excel workbooks ready to go. That is the power of a join vs a domain function when you are dealing with MILLIONS of rows of data as they were and as you are. About 30% of my students "got" Access and were able to move a large part of their work to Access which made long running Excel jobs a thing of the past for them. The others didn't get it so they stuck with the old way but I still saved them a wasted 2 hours a day minimum by just getting rid of the VLookups.

A dLookup() and all the other domain functions as well, performs a query so when you use a dLookup() in a query, you are essentially running a separate query for every single row of your RecordSource. If it contained 1000 rows, you were running 1000 queries. If it was 2 million rows, you were running 2 million queries. Even if there were useful indexes, there is overhead associated with simply running a query and when you do it 2 million times, that adds up to one heck of a lot of overhead.

 

[intrapostal]

you can also use Query that Inner Joins both tables and csv on a Common field (Account field?).

Domain functions do not belong in queries or in VBA loops. The proper method is to join the two tables.

Also with a 2+million row table, I would never join it to a linked flat file. Import the .csv to a table. Add indexes if appropriate. That will make the join as efficient as it can be.

A real world example --
A few years ago, I was employed to teach the analysts at a small startup company how to use Access to make their Excel work more efficient. Every day, they would come in and download yesterday's data from our transaction processing company. They would then import the data into their spreadsheets and start their VLookup()s running and head out for breakfast. 2-3 hours later, the files would be ready to be used for analysis.

I started with one simple change. Import the downloaded data into Access and join the files rather than doing VLookup()s or the Access equivalent which is dLookup(). Less than 5 minutes later, the data was exported to their Excel workbooks ready to go. That is the power of a join vs a domain function when you are dealing with MILLIONS of rows of data as they were and as you are. About 30% of my students "got" Access and were able to move a large part of their work to Access which made long running Excel jobs a thing of the past for them. The others didn't get it so they stuck with the old way but I still saved them a wasted 2 hours a day minimum by just getting rid of the VLookups.

A dLookup() and all the other domain functions as well, performs a query so when you use a dLookup() in a query, you are essentially running a separate query for every single row of your RecordSource. If it contained 1000 rows, you were running 1000 queries. If it was 2 million rows, you were running 2 million queries. Even if there were useful indexes, there is overhead associated with simply running a query and when you do it 2 million times, that adds up to one heck of a lot of overhead

I have the two tables in access, and they're linked via account number, I've applied a query to return all data from table X and acc nu on Table Y so i can now filter out the data, but is there a way to ONLY return the data where acc number match?
Thank you btw - very comprehensive reply

 

[arnelgp]

but is there a way to ONLY return the data where acc number match?

if you created an Inner Join Query, it will show only records that Matched.

 

[GPGeorge]

Here's an explanation of Join Types. As Arnel pointed out, you want an Inner Join.

 

[Isaac]

Like Pat I usually steer away from linked flat files and DEFINITELY steer clear of linked excel, as they tend to cause more problems to solve compared to importing it. Still, never say never ... every case has the potential to be different. Sometimes I've found it easier to temporarily link to a flat file that's large rather than dealing with the ramifications of importing it, but it all depends on what's in the file, how troublesome it is, and how much you care about the specific import errors that do occur, and that's just the start of a long list of considerations. Importing it up-front will be more likely that data problems are fleshed out early (and must be dealt with). Linking it may flesh these problems out when you try to run the query - or wonder why the results are excluding many items.

Many decisions also depend on whether your task is a one-time thing or you are creating a repeatable process that will in fact be oft repeated.
Maybe if you are creating a repeatable process, it's worth it to design an import process that does in fact deal with all possible scenarios and push through the work of coding for all of them.

Or maybe you are lucky enough to be in a role that's so well defined, that if there are data problems (like a string of text on a column that should be date), you are allowed to just tell the source of the file "go back and fix the file, let me know when there are no more bad data".
It just all depends..

 

[Pat Hartman]

Issac, I use linked Excel all the time. The reason I suggested otherwise in this situation is ONLY because of the join to a 2 MILLION row table. That you want to be ACE-ACE, not ACE-Excel

The OP didn't say anything about needing to add the Excel data to an existing table.

 

[RogerCooper]

I hope that the 2 million record table is not an Access native table. Such large table should be in heavy-duty database like SQL Server and linked to Access.

 

[Pat Hartman]

The number of rows in an Access table has nothing to do with anything. Access databases are limited to 2Gig. Access works fine with millions of rows.

Using Access to join a 2 Million row SQL Server table to a .csv file would be awful. Access would first download all 2 million rows and then it would perform the join locally.

 

[Isaac]

Linked Excel is the worst of all the links IMO, I won't touch it unless I have no better options. Excel does way too much interference.
Similar to any ETL situation, the better practice is usually to import it and then act upon it from there. Let the database be the database.