Import Query to update table 1

[Ajwebb]

Hi all,

I have a database with a query i have to export weekly.

The query has 10 fields:-

ULI
Learn_ID
Provi_id
LSurname
LForenam
and so on.

However when i export the query the ULI field is blank and then three days later i will receive a file which has the following fields:-

ULI
Learn_id
Provi_id

What i would like is that i can import my file i receive back and update all the null ULI fields from my table with the imported query without adding them to the bottom or changing any other existing data.

Is this possible?

Thanks in Advance All

Merry Xmas

Anthony

 

[SuicidED]

Hi Try this

TABLE1 is where you exported from, TABLE2 the data you recieve back.

UPDATE Table1 INNER JOIN Table2 ON (Table1.Provi_id = Table2.Provi_id) AND (Table1.Learn_ID = Table2.Learn_ID) SET Table1.ULI = [TABLE2].[ULI]
WHERE (((Table1.ULI) Is Null));

Eddie

 

[Ajwebb]

Hi Eddie,

Would i have to create another table to import the data into?

Ant

 

[Ajwebb]

Hi Eddie,

Imported the information into a query but it comes cannot find my tables.

My Table Names and Fields are:-

Learner Dataset
Uniqueid
Learn_id
Provi_id

ULI Table
Field1
Field2
Field3

Not sure what im doing wrong.

Thanks

Ant

 

[SuicidED]

Ok try this

UPDATE [Learner Dataset] INNER JOIN [ULI Table] ON ([Learner Dataset].Provi_id = [ULI Table].Field2) AND ([Learner Dataset].Learn_ID = [ULI Table].Field3) SET [Learner Dataset].ULI = [ULI Table].[Field1]
WHERE ((([Learner Dataset].ULI) Is Null));

 

[Ajwebb]

Thanks Eddie.

Have a good Xmas.

Anthony

 

[Ajwebb]

Hi All,

Is it possible to update several tables from one query, such as above?

If so could some explain please.

Thanks

Anthony

 

[SuicidED]

Hi Anthony

For each table I believe you will have to run a separate query for each table you wish to update using the appropiate field and table names. It seems to be a bit pointless to have multiple tables containg the same information. Look in access help under normalisation, I think you may have to have a look at how your database is constructed.

Eddie

 

[Ajwebb]

Thanks for replying Eddie.

However I cant remove that information from all the tables as my database in connected to a national database which tracks students all over the country.

The idea is by having that information in all tables they will be able to track students throughout there whole life.

A Strange way of doing it.

I will create them seperately then.

Thanks Again

Anthony