easy question

[hongbin81]

I'm fairly new at this so....
how do I link a table that has a Number field with another table that has a Number field. I'm trying to update the first table that has actual numbers with the second table that has a number field with no numbers.

 

[kerplunkitt]

Have i got this right?
You want the second table - with no numbers in it, to have the same numbers in it as the first?
If this is the case then use an Update query.
If you need a 'how to' for this then let me know - i know how hard i found access first but it gets better.I will not explain how to do it now as i might have the wrong idea and don't want to waste your time.
Regards
Ian

 

[hongbin81]

I'm trying to use Update Query....but can't manage to get it working...

 

[valkyrie69]

Send me the database I will look at the query and see if i can ammend it. A better description of the problem would help as it is a little confusing. Are yo trying to populate the empty number fields.? Send it to sap-possystems@dnet.co.uk Regards Steve

It is better to ask for for directions rather than wander blindly... Where am I and why did I ever get into I.T.

 

[hongbin81]

it's a HUGE file....almost 8MEGS

 

[JeremyNYC]

In any case, it's best to keep all solutions here on the forum, so that others can learn.

Post the sql in your query and we'll see what we can do with it.

Also, do you compact that database regularly? You should.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[kerplunkitt]

I have done a simple update query that updates table 2 with the contents of table 1.
Just paste this SQL statement into a test query to see how it is setup. To do this you will need to select SQL under the 'view' tab and then paste this in. Then you can change the view back to design view as normal and see the effect.

UPDATE Table1 LEFT JOIN Table2 ON Table1.Number = Table2.Number1 SET Table2.Number1 = [Table1]![Number];

This works fine on my access 2000. Note the join properties between the two tables?

I hope this helps.
Regards
Ian

 

[JeremyNYC]

I'm not sure how this could work. You're attempting to update a field when you've linked on that field. All the records you get will have Table1.Number = Table2.Number1, so setting Table2.Number1 = Table1.Number won't do anything.

Also, I would change the name of the field Number, because that's an Access reserved word, and it will give you trouble at some point.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[hongbin81]

I got it to work.

Thanks guys~!