Missing Data in an Outer Join

  • Thread starter Thread starter Noah
  • Start date Start date
N

Noah

I have a query where I am doing an outer join on two
tables. So it picks up everything from Table A and only
the data from Table B where the employee numbers match.

So I get something like this:
Table A Table B
Emp # Data A Data B
123 abc abc
123 zxy
789 iop iop

What I would like to do is to fill in the missing data
from Table B. I am thinking that I might need somekind of
VB script that would look at the Emp # from Table A and if
Table B is missing anything it would fill it in with data
from Table A. So in the above example, 'zxy' is missing
from Table B so I would fill it in with data from Table A.

I hope this makes sence. If anyone has any sample code I
could use please let me know.

Thanks
 
Hi Noah,

You should be able to accomplish this without any code. All you need is an update query. Make a
copy of your current query. Convert it in query design view into an Update Query (Query > Update
Query from the menu in design view). You should see a new Update To: row in the QBE grid. Make
sure that the field you want to update is included in the query. In the Update To: row, enter
something like this:

Field: Data B
Table: B
Update To: [Table A].[Data A]

enclosing the name of the table and field in square brackets, and using a period in-between. I
suggest making a back up of Table B first (or back up the entire database), just in case you get
some unexpected result.

Tom
______________________________________


I have a query where I am doing an outer join on two
tables. So it picks up everything from Table A and only
the data from Table B where the employee numbers match.

So I get something like this:
Table A Table B
Emp # Data A Data B
123 abc abc
123 zxy
789 iop iop

What I would like to do is to fill in the missing data
from Table B. I am thinking that I might need somekind of
VB script that would look at the Emp # from Table A and if
Table B is missing anything it would fill it in with data
from Table A. So in the above example, 'zxy' is missing
from Table B so I would fill it in with data from Table A.

I hope this makes sence. If anyone has any sample code I
could use please let me know.

Thanks
 
Back
Top