How does access match records?

[johnmerlino]

Hey all,



In excel, let's say you have last name, first name, and middle initials, and addresses in columns A through D, and then you last name and first name in columns E and F. If you want to compare them and only return columns A through D where the first and last name match columns E and F, you might do something like this:

=F1&" "&INDEX(C$1:C$354,MATCH(F1,G$1:G$354,0))

I would like to know how in Access can you import, let's say, 200 records and return all the records already existing in the database where the first and last name match the 200 records you just imported?

Thanks for any response.

[NoellaG]

Hi John,

to find data in Access you have to build queries. As there are a lot of possibilities I would advice to get a basic book or look for an online course to learn the basics.

grNG

[johnmerlino]

But what if the records you import are not in a database table. It's just a list of records that you want to match against a table in a database? Doesn't a query only work on a single database? It's not like a join table can be used because there's no table to join!

Would I need to create a table? And do this query?

SELECT Table1.LastName, Table1.FirstName, Table1.MI, Table1.Address, Table1.City, Table1.State, Table1.Zip FROM Table1 WHERE Table1.LastName=Table2.LastName && Table1.FirstName=Table2.FirstName;

Anyone know how to convert this MySQL to JET in Access?

Thanks for response.

[NoellaG]

Hi,

you can link the excel file to Access and then use it in a query as an Access table. the query can easily be made in the query by example grid.

grNG

[NTC]

Noella's advice is correct. It is very easy.

First either import the excel data or link to it. Import if it is static, link to it if it is continuously being updated.

Assuming your core info is already in an Access table then you now have 2 tables.

In query design mode, embedded is a FindDuplicates wizard - which will step you thru set up of the query.

Hope this helps.