Update Table

[sandrao]

How would I go about retrieving data from one table into another?

One Table "A" has three fields "Subject#", "Subject" and " "Date"

Another table "B" has a form attached that is used in scheduling these subjects with the same three fields mentioned above. (plus other fields that need not be updated) What I would like to do is just periodically retrieve information from the "Date" field from table "B" and insert it in the date field inTable "A".

This will keep track of the information so that duplication of subject will now occur at a too frequent occurance.

 

[Uncle Gizmo]

Could you append the data?

 

[sandrao]

I tried to use append but it didn't work.
The problem is that the Date has to match the Subject# when the information is transfered.

 

[jdraw]

I tried to use append but it didn't work.
The problem is that the Date has to match the Subject# when the information is transfered.

The general format of what you are trying to do is along these lines:

Update TableA
Set TableA.Date =
(SELECT TableB.Date from TableB, TableA
WHERE TableB.Subject#= TableA.Subject# .......)

You will have to have criteria that ensure you get the exact Subject#, Date you need.

 

[sandrao]

What I eventually did was similiar to what suggested above. I created a Update Query and linked the TableA and TableB by the (Subject#) then in the Update To: I placed
[TableA].[Date]. I attach Macro to the form to run the query on unload.

This is working fine.

Thanks for the suggestions.