2 Replies - 1376 Views - Last Post: 19 September 2007 - 07:23 PM Rate Topic: -----

#1 silverfeathers  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 19-September 07

Manipulating data from two different tables

Post icon  Posted 19 September 2007 - 03:04 PM

Hi everyone!

I'm not really sure what's possible and what's not in Access, so I don't even know if I can actually do this but...

Say that I have two tables. Table1 has a field called "force" and a field called "work" and both are integers, though "work" is left blank. Table2 has a field called "distance" and is also an integer. Now, if I want to multiply "force" from Table1 and "distance" from Table2, and store that value in "work"... how would I do that? Can I do that?

Any help would be appreciated! Thanks in advance!
Xin

Is This A Good Question/Topic? 0
  • +

Replies To: Manipulating data from two different tables

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4318
  • View blog
  • Posts: 12,100
  • Joined: 18-April 07

Re: Manipulating data from two different tables

Posted 19 September 2007 - 04:19 PM

You can do that using a simple update SQL statement as such...

Update table1,table2 set table1.work = (table1.work * table2.distance) Where Table1.id = Table2.id;



This assumes that you do have matching keys. If you don't have id fields, just make sure you have something that links to the two tables together.

I like how you made one of your fields "force". So really you can say that you have made yourself a "force field". :lol:

Enjoy!
Was This Post Helpful? 0
  • +
  • -

#3 silverfeathers  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 19-September 07

Re: Manipulating data from two different tables

Posted 19 September 2007 - 07:23 PM

Hi, and thanks for the quick reply!

View PostMartyr2, on 19 Sep, 2007 - 04:19 PM, said:

You can do that using a simple update SQL statement as such...


Erheheh... I'm a total newbie when it comes to Access... where exactly would I type that code?

View PostMartyr2, on 19 Sep, 2007 - 04:19 PM, said:

This assumes that you do have matching keys. If you don't have id fields, just make sure you have something that links to the two tables together.


Hrm... my tables don't really have a relationship though... I mean, Table1 would be data that's entered by the user, but Table2 is more like a reference table, with values that are set.

For instance, using the same two tables and fields, say that I'm tring to figure out how much work is involved in moving an object a certain distance. The entries in Table1 would represent the different trials where the user knows the force involved but not the work. Then the first entry in Table2 would be like... a tennis court. The second entry may be a football field, and the third may be an Olympic swimming pool. The distance across these wouldn't change, and I want them in a seperate table 'cause later I need to reference those same values with something else.

So is there a way to do that, without really making any sort of relationships between two tables? And sorry if my examples aren't too clear!

Thanks for the help!
Xin
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1