I am trying to build a query in my MS Access database.  I am operating on Office 2000.  I have 2 tables that I am working with.  Both tables contain columns with identical information.  These identical fields are currently linked together as a subdatasheet under tables.  What I would like to do is create a query where, if the data from one table is identical to data in another table, then values are copied and pasted in to the main table.

Here is what I am talking about:

I have 2 tables:
1 = ISMCompany
2 = BPIncCompany

Both tables contain the identical fields "DepartmentName."  I am trying to bring data from table 2 over to table 1 and paste information from table 2 in the columns if the "DepartmentName" fields in both tables are identical.  I would think this would be a simple task:

Create an append query and append data to table 1 where the criteria is as follows:

"If [ISMCompany]![DepartmentName] = [BPIncCompany]![DepartmentName]"

I am pulling my hair out and have been trying to figure this out for over 12 hours now.  Someone, please please help!  It seems like it would be a simple problem to fix, but I just can't figure it out and I am going crazy! 

Your explanation of your problem leaves much to be desired.  I have no idea what you are asking.

Do you wish to:

1) Update the other columns of rows in Table1 with values from rows in Table2 where DepartmentName matches?  Note that this will not add any new rows to Table1, only change what is already there.

2) Copy rows from Table 2 to Table1, but only if rows containing the same DepartmentName value already exist in Table1?

3) Something else?

Yes to #1...sorry, I should be a little more clear.  I am so confused at this point!

Ok.  Right click in Query Designer, select SQL view, and type the following (adjusting for your table and field names):

UPDATE Table1 INNER JOIN Table2 ON Table2.DepartmentName = Table1.DepartmentName SET Table1.Col1 = Table2.Col1, Table1.Col2 = Table2.Col2

You may, of course, have more than two columns you want to update.  I quickly tested it (since I normally work with SQL Server and Access only supports a subset of T-SQL) and it works.

I don't know whether or how you can create such a query in the drag-n-drop designer mode.  I noticed that after having it saved, Query Designer doesn't seem to be able to display it otherwise than just straight SQL.

I tried it and I can't get it to work.  Any thoughts?

As noted, I tested the query because I wanted to be certain I don't give you SQL that is beyond what Access supports.  I'd suspect you made some mistake while altering it for your situation (table names and columns).

What mistake specifically I couldn't even begin to guess.  If you can't spot it yourself you can cut and paste your version of the query here and I will look it over.  Also, it'd help to know how it fails (what error message you get).