get the sum of a field from table to table (1 Viewer)

B

basilyos

Registered User.

Local time

Yesterday, 18:12

Joined

Jan 13, 2014

Messages

252

• Nov 10, 2014

• #1

hello

i have two tables
1- stuff (contains personal information and a field named days off)
2- days_off

in the second table i just put the id of the customer with the number of day off
for example
1 4
1 2
4 1
3 10

so what i want is to get the total of days off from days_off table in the days_off field in the stuff table


any help??

 

Minty

AWF VIP

Local time

Today, 02:12

Joined

Jul 26, 2013

Messages

10,371

• Nov 10, 2014

• #2

In theory you shouldn't need to store this as you can always calculate it...
However in a query something like
UPDATE StuffTable1
SET Days_Off = SUM(Table2.DaysOFF)
WHERE StuffTable1.ID_Field = Table2.ID_Field

 

B

basilyos

Registered User.

Local time

Yesterday, 18:12

Joined

Jan 13, 2014

Messages

252

• Nov 10, 2014

• #3

bro this is what i wrote in the onclick event using vba
Update Table2
Set numberr = SUM(Table1.numberr)
WHERE Table2.SID = Table1.SID

but i get an error message
Compile error:
Sub or Function not defined

i tried to make an update query it does not work

could you give some hint

 

Minty

AWF VIP

Local time

Today, 02:12

Joined

Jul 26, 2013

Messages

10,371

• Nov 10, 2014

• #4

You can't run a SQL update query like that in VBA.

To simplify things do this in 2 stages;
a) Create an aggregate query that sums your days off for each ID from the second table only.
b) Use this query to make the update to your main table.