find not equal fields

[roley]

If I have a table that I built off a master datasource (Not in ACCESS) and need to update my table with any new data from the master source how do I do it. I'm kinda of newbie that is self taught with no visual basic skills. I tried the search but could not find or understand what was being said.

I know that in a query I can find the matches by joining the fields. I need to take the unmatches and view them then update my table with that info. It's probably really easy. Any suggestions.

 

[dcx693]

Let's say you have table A (the master) and table B (your Access table), do you need to see (1) what exists on table A, but not on table B and/or (2) what exists on table B but not on table A?

Or do you just need to update tableB to synch it with updates from the tableA? Meaning there will never be records on tableB that don't already exist on tableA.

 

[roley]

I need to see what is on table A but not on Table B. Meaning table A has expanded and there are new records. I would like to find what those new records are and then add them to Table B. Some of the records on Table A may have changed as well and I'd like to see what they changed to before I update that field.

 

[roley]

I would like to synch the two tables but the only way i get the info from table A is thru CSV. It is an old DOS system that is very user unfriendly.

 

[dcx693]

Take a look at this thread . It has a link to an article from Microsoft on how you can append and update records in one step.

 

[roley]

I'm sorry DCX maybe I'm not making myself clear. The thread made no sense with what i'm tryng to do. I know how to do an update query and/or append query. What I'm having trouble doing is finding which what has changed in table A in relation to table B.

In other words Table A has 20000 records, and table B has 18000 records so 2000 records have been created. Now I could add a yes/no field to Table A and in an update query make all the records that are the same, be equal to yes in that field, leaving me 2000 no's, but is there an easier way.

 

[dcx693]

If you just want to see which records have changed without updating them or appending them to another table, then can't you just run the query as a plain select query and not as an update query? Sorry if I'm missing the point here.

 

[roley]

no if you just run it, it shows all the records that are equal and not the ones that are not.

I would like to see if the address is different which is right. If the phone is different. If there is a new entry to Table A is he already in Table B as a Temp and then should I change his name in Table b to that of Table A or Keep it. I may have already posted 15 different records on the temp name and updating those records may take some time. So I would note it and at a later date update it. If I put the new name in My database it would not retrieve the 15 records. I just want to view the unequals.

 

[dcx693]

Hmm... I suspect we are just not communicating properly here, but I'll try to get you to where you want to go.

Try this type of query:

SELECT TableA.ID, TableB.ID, TableA.Firstname, TableA.Lastname
FROM TableB RIGHT JOIN TableA ON TableB.ID = TableA.ID
WHERE (((TableA.Firstname)<>[TableB].[Firstname])) OR (((TableB.ID) Is Null)) OR (((TableA.Lastname)<>[TableB].[Lastname]));


In the text above, ID is a primary key field. This query tests to see if there are records on tableA that don't exist on tableB, and if there have been any changes for any records in the Lastname or Firstname for records with the same primary key (ID).

 

[roley]

DCX, I appreciate your stick-to-itness. I seen this type of wording in my searches:

SELECT TableA.ID, TableB.ID, TableA.Firstname, TableA.Lastname
FROM TableB RIGHT JOIN TableA ON TableB.ID = TableA.ID
WHERE (((TableA.Firstname)<>[TableB].[Firstname])) OR (((TableB.ID) Is Null)) OR (((TableA.Lastname)<>[TableB].[Lastname]));
I understand the Select and the From, but is the Where in the criteria of the query. I feel so stupid.
I actually have a very large multi user database controlling calls in, calls on the road, Equip. inventory, Equip sold, Outside Equip sold, where the tables are linked so the rental Dept dircetly communicates with sales and management. It pretty elaborate for a self taught. Being that it probably not done right.
I'd like to send a sample Database.
I've learned alot from this forum.

 

[dcx693]

Yes, the Where part control what gets "picked" from the tables in the "FROM" part of the query.

In the SQL posted, you can probably figure out this part: TableA.Firstname)<>[TableB].[Firstname] and the same for the Lastname field. It simply compares the Firstname field between matching records in the two tables. This part: (TableB.ID) Is Null finds those records where there is an ID field entry in TableA, but non in TableB. Null means "non-existent".

Post a sample you want and we will take a look at it. You should not feel stupid. There is much to learn regarding databases. The best you can do is to try and solve the problem, then ask questions, just as you are doing.

 

[WayneRyan]

roley,

I hate to butt in, but ...

Select YourField
From   YourTable
Where  YourField Not In (Select YourField
                         From   YourOtherTable)

Wayne