Exporting Records that match one or more than one column

[SanVer]

Hi,

I have stored data in access. The records have name, address and unique Id. I want to be able to extract the unique ID from the Access database for all those records that match the name and address in any new file that I compare my Database with. Hope I have been able to explain the situation.



Thanks

[June7]

What do you mean by 'in any new file'? What exactly are you comparing?

Maybe you want something like: http://www.datapigtechnologies.com/f...earchform.html

[SanVer]

Thanks for the reply. Sorry to be late on this. Wanted to tell you that the site you gave the link for was really useful. I managed to get the Data set up. Stuck on another point though, I have 2 Tables and have set up a relationship ( Inner Join ) for various headers between them since these headers are common to both the files. What I want to do is, get values from a specific column in one of the tables to show in the other one if the values in any of the headers that I have joined, match in both the Tables. So essentially I want to set a Query and put an OR criteria ( may be ) and UPDATE the values in a column in one of the tables if the values for any of the Headers that I have established a relationship for, match in both the Tables. Again, not sure If I have been able to explain the scenario very well. Nevertheless, I'd appreciate any sort of help on this one.

[June7]

An INNER JOIN that links on all of the common fields will return only those records where ALL of the linked fields match. This is like using an AND operator in a WHERE clause. You want OR operator. Never tried an UPDATE like this, not sure it will work. If you want to provide sample data, I will analyze.

[SanVer]

An INNER JOIN that links on all of the common fields will return only those records where ALL of the linked fields match. This is like using an AND operator in a WHERE clause. You want OR operator. Never tried an UPDATE like this, not sure it will work. If you want to provide sample data, I will analyze.

Thanks for the reply. I have attached a sample file. Basically, what I want to achieve is :

Retrieve the Venue Code and Status column values from the Main database Access Table to the Sheet 2 Table based on the below condition :

1) If there is an exact Match found for any of the following headers in both the sheets : Venue Name OR Phone OR any of the last two headers.

2) For all of the above headers the sequence followed should be such that If a unique match is found with Venue Name, the corresponding Venue Code gets assigned and the subsequent headers need not be matched, conversely, if there is no matching value found in the Venue Name header or there is more than 1 match found then query is run on the subsequent header "Phone" and so on and so forth.

This is critical towards achieving the objective that I have in mind, any sort of help would be welcome on this. If there is any other feasible way or method to accomplish this, then please let me know.

Thanks

[June7]

This does something but not sure I would trust the results:

UPDATE [MAIN DATABASE_ACCESS], Sheet2 SET Sheet2.[Venue Code] = [MAIN DATABASE_ACCESS]![Venue Code]
WHERE (((Sheet2.Phone)=Val([MAIN DATABASE_ACCESS]![Phone]))) OR (((Sheet2.[Venue Name])=[MAIN DATABASE_ACCESS]![Venue Name])) OR (((Sheet2.[left(Name,10)&left(Add,5)])=[MAIN DATABASE_ACCESS]![left(Name,10)&left(Add,5)])) OR (((Sheet2.[left(Name,5)&left(Add,5)])=[MAIN DATABASE_ACCESS]![left(Name,5)&left(Add,5)]));

View the SELECT query before switching it to UPDATE then view again after running UPDATE and also review the updated table directly. Are the results acceptable?

[SanVer]

Thanks so much for leading me in the direction which I so much wanted to go earlier with repeat failed attempts and unsuccessful searches on forums and other tutorial websites to unearth something which now that you have told me seems pretty regular and basic stuff. This sure does seems to do the job, so long as I am getting a match for any of the criteria above, I am fine since there is a process in which all these matched records will go through a further verification for correctness. Just one thing though, is it possible to put up a condition that If the match occurs more than once for any of the criteria specified above, then the corresponding Venue Code is not retrieved. So essentially, I only want the Venue Code retrieved for the scenario when there is just one match found, for example, if there are 3 records in my main table with the same Venue name but different Venue Codes and I have a records with this name in Sheet2 as well, I would not want to assign the Venue Code to it from, the man sheet because, i am not sure which one is the correct one and would rather check it manually.

Thanks

[June7]

That was why I said probably would not trust the results.

Build a query that excludes records with same [Venue Name]

SELECT * FROM [MAIN DATABASE_ACCESS] WHERE DCount("*", "[MAIN DATABASE_ACCESS]", "[Venue Name]='" & [Venue Name] & "'") = 1;

Use that query in the earlier query instead of the table.

[SanVer]

That was why I said probably would not trust the results.

Build a query that excludes records with same [Venue Name]

SELECT * FROM [MAIN DATABASE_ACCESS] WHERE DCount("*", "[MAIN DATABASE_ACCESS]", "[Venue Name]='" & [Venue Name] & "'") = 1;

Use that query in the earlier query instead of the table.

Hi, Sample File.accdb

I have been away from work for some time, I tried the above SELECT statement given by you, but for some reason, it keeps giving me the error, "characters found after end of SQL statement". I have attached the file for your reference. Can you please take a look and help.

Thanks

[June7]

I see only UPDATE queries in the db. Don't see the SELECT I suggested. Don't see any attempt to do what I suggested.

I tested the query and the DCount() errors when it encounters words with apostrophe, like "O'Connell'Corne". The following works:

SELECT * FROM [MAIN DATABASE_ACCESS] WHERE DCount("*", "[MAIN DATABASE_ACCESS]", "[Venue Name]='" & Replace([Venue Name],"'", "''") & "'") = 1;

However, there are no duplicates so all 6 records are returned.


Should avoid spaces and special characters/punctuation (underscore is exception) in naming convention. A name like [left(Name,10)&left(Add,5)] could cause issues.

[SanVer]

Thanks one again, I had applied the SELECT query that you suggested earlier but removed it while uploading the file since it was exceeding the permissible size here. This one works for me, I believe from what you have told me above, I need to apply the UPDATE query in the table which I get after applying the select query. The only point at which I am getting stuck now is...

1) I have a huge Database, once I apply the SELECT query, it takes a long while to get me the unique Data after which I need to use the UPDATE query on it. Not sure if something can be done about it or If am doing it the wrong way.

Thanks anyways, youve been a great help for getting me started on my journey to learn Access.