Find Matched Records

[hstreff]

I have 2 tables. I want to find all the records between the two that match and then replace the contents of another field with a value such as "*" to signal the matched records.

If I were using dBase I would set a relationship between the two and repl all "fieldname" with "*" for seal = seal.

I have no idea where to start with this in Access. I was thinking it may be a query but I am not sure. It seems that if there is an Unmatched Query there may be a matched query. The replace seems to be more what I am trying to figure out how to do. I have searched on Replace but all I come up with is the basic Find/Replace.

Any help would be greatly appreciated.

Heather

 

[Michael J Ross]

You need an update query.

Add your two tables to the query grid and create a join on the field names that may contain matches. Click on Query Type on the toolbar and select Update Query.

Add the field you want to update and in the Update To Box enter "*"

This is the SQL

UPDATE Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1 SET Table2.Field2 = "*";

 

[hstreff]

Thank You! I have been searching all day for an answer. But now I have one more question. The field I want to update is in Table1 rather than Table2

SET Table2.Field2 = "*";

I made the changes accordingly. When I ran the query, I only see the dup field. If I go back to design view and add other field that I would like to see along with these results do I have to run it again? It seemed yesterday when I ran a query twice I got double the results. Is that true?

When I go back to my table "Permit" will I be able to see these results in there as well or will they only be in the query?

Thank you for your patience. I am pretty new to this and really appreciate your help.

Heather

 

[hstreff]

Actually I figured it out, it did update in the correct table, I was just viewing all the records and didn't see the value.

Thank you so very much for your help. This ended up being really quite simple. I was thinking it was going to be very complex possibly requiring some VBA. As I learn these things it is really starting to seem that switching from dBase is going to be a good move.

Thanks again!

Heather

 

[Michael J Ross]

You're welcome!

You'll learn a lot from these forums as you go along, I promise you.

Glad to help

 

[hstreff]

My boss and I were just talking about our route with Access. We are coming from dBase and are trying to find where we fit into Access.

He was just saying to me that VBA may not be the real power but SQL may be the way to go. I told him that you gave me the SQL command for what we were trying to do.

We understand the basic concepts of relationships and such but like I said we are just trying to find where we fit in. He posted a message in the VBA area. We're just wondering if SQL is like a boyscout trained in firtst aid trying to be a doctor, without going to school.

If you think maybe we should be doing this, not knowing how long you've been around. But when we started we got a lot of mileage out of a book called "The dBase Language Handbook." It did a side by side comparison of different languages everything from clipper, foxbase, quicksilver, dbase and everything in between. It was great. It was written by David M. Kalman. If there was something like that out there now that would be great. If not someone could get rich.

Heather
(and Bob)