quote:

---

Originally posted by mana

I have the following code but i have the error message that the

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "AMPPU_ARTPU.offline_date1" could not be bound."

i have this error message in u

insert into AMPPU_ARTPU(summeofrepairtime,[offline date])
(select SUM(Reparatur_hr)*60,Offline_date from AMPPU_Alle_Fehlteile
group by Offline_date);

update AMPPU_Alle_Fehlteile
set
summe = (select summeofrepairtime from AMPPU_ARTPU)
where AMPPU_Alle_Fehlteile.Offline_date = AMPPU_ARTPU.offline_date1;

please help me
thaks

---

quote:

---

Originally posted by James K

Does the table AMPPU_ARTPU have a column named offline_date1? It is complaining about the part marked in red below

quote:

---

Originally posted by mana

I have the following code but i have the error message that the

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "AMPPU_ARTPU.offline_date1" could not be bound."

i have this error message in u

insert into AMPPU_ARTPU(summeofrepairtime,[offline date])
(select SUM(Reparatur_hr)*60,Offline_date from AMPPU_Alle_Fehlteile
group by Offline_date);

update AMPPU_Alle_Fehlteile
set
summe = (select summeofrepairtime from AMPPU_ARTPU)
where AMPPU_Alle_Fehlteile.Offline_date = AMPPU_ARTPU.offline_date1;

please help me
thaks

---

---

UPDATE f SET 
	summe = summeofrepairtime
FROM
	AMPPU_Alle_Fehlteile f
	INNER JOIN AMPPU_ARTPU a ON
		f.Offline_date = a.offline_date1;

quote:

---

Originally posted by James K

I had missed an extra bracket you had. Assuming you are trying to update the column summe for every row in table AMPPU_Alle_Fehlteile with value in the column summeofrepairtime from table AMPPU_ARTPU that satisfy the condition that AMPPU_Alle_Fehlteile.Offline_date = AMPPU_ARTPU.offline_date1, then you can use the following code

UPDATE f SET 
	summe = summeofrepairtime
FROM
	AMPPU_Alle_Fehlteile f
	INNER JOIN AMPPU_ARTPU a ON
		f.Offline_date = a.offline_date1;

---

quote:

---

Originally posted by mana


thank you
now it works
but when i want to update the table AMPPU_Alle_Fehlteile directly i have the below message:
Msg 207, Level 16, State 1, Line 2
Ungültiger Spaltenname 'summenew1'.
column summenew1 is unvalid
my code is like below


UPDATE AMPPU_Alle_Fehlteile SET
summenew1 = (select SUM(Reparatur_hr)*60
FROM
AMPPU_Alle_Fehlteile
group by Offline_date);

also i want that this update will be done automatically every day do you know what i can do
thank you for your help

quote:

---

Originally posted by James K

I had missed an extra bracket you had. Assuming you are trying to update the column summe for every row in table AMPPU_Alle_Fehlteile with value in the column summeofrepairtime from table AMPPU_ARTPU that satisfy the condition that AMPPU_Alle_Fehlteile.Offline_date = AMPPU_ARTPU.offline_date1, then you can use the following code

UPDATE f SET 
	summe = summeofrepairtime
FROM
	AMPPU_Alle_Fehlteile f
	INNER JOIN AMPPU_ARTPU a ON
		f.Offline_date = a.offline_date1;

---

---

quote:

---

Originally posted by mana


thank you
now it works
but when i want to update the table AMPPU_Alle_Fehlteile directly i have the below message:
Msg 207, Level 16, State 1, Line 2
Ungültiger Spaltenname 'summenew1'.
column summenew1 is unvalid
my code is like below


UPDATE AMPPU_Alle_Fehlteile SET
summenew1 = (select SUM(Reparatur_hr)*60
FROM
AMPPU_Alle_Fehlteile
group by Offline_date);

also i want that this update will be done automatically every day do you know what i can do
thank you for your help

quote:

---

Originally posted by James K

I had missed an extra bracket you had. Assuming you are trying to update the column summe for every row in table AMPPU_Alle_Fehlteile with value in the column summeofrepairtime from table AMPPU_ARTPU that satisfy the condition that AMPPU_Alle_Fehlteile.Offline_date = AMPPU_ARTPU.offline_date1, then you can use the following code

UPDATE f SET 
	summe = summeofrepairtime
FROM
	AMPPU_Alle_Fehlteile f
	INNER JOIN AMPPU_ARTPU a ON
		f.Offline_date = a.offline_date1;

---

---

quote:

---

Originally posted by mana


now i have this code and below error;

UPDATE AMPPU_Alle_Fehlteile SET
summenew = (select SUM(Reparatur_hr)*60
FROM
AMPPU_Alle_Fehlteile
where Offline_date is not null
group by Offline_date);

Msg 512, Level 16, State 1, Line 1

The subquery returned more than one value. This is not permitted when the subquery follows =,! =, <, <=,> Or> = follows or is used as an expression.
i'm new to sql server
can you help me please?
thank you

quote:

---

Originally posted by mana


thank you
now it works
but when i want to update the table AMPPU_Alle_Fehlteile directly i have the below message:
Msg 207, Level 16, State 1, Line 2
Ungültiger Spaltenname 'summenew1'.
column summenew1 is unvalid
my code is like below


UPDATE AMPPU_Alle_Fehlteile SET
summenew1 = (select SUM(Reparatur_hr)*60
FROM
AMPPU_Alle_Fehlteile
group by Offline_date);

also i want that this update will be done automatically every day do you know what i can do
thank you for your help

quote:

---

Originally posted by James K

I had missed an extra bracket you had. Assuming you are trying to update the column summe for every row in table AMPPU_Alle_Fehlteile with value in the column summeofrepairtime from table AMPPU_ARTPU that satisfy the condition that AMPPU_Alle_Fehlteile.Offline_date = AMPPU_ARTPU.offline_date1, then you can use the following code

UPDATE f SET 
	summe = summeofrepairtime
FROM
	AMPPU_Alle_Fehlteile f
	INNER JOIN AMPPU_ARTPU a ON
		f.Offline_date = a.offline_date1;

---

---

---

quote:

---

Originally posted by MichaelJSQL

Try the following


UPDATE AMPPU_Alle_Fehlteile SET
summenew1 = B.SUpdateColSource
FROM #AMPPU_Alle_Fehlteile A

This is going to set the summenew1 for a given date to the sum of Reparatur_hr * 60 for all records in that table with the same date. So every date that is the same will have the same value. Is that what you are trying to do?
INNER JOIN (select Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile group by Offline_date) B ON A.Offline_date = B.Offline_date;

---

quote:

---

Originally posted by mana



thank you very much now it works and i want that the following queries will be done automatically every day, should i write a job for them?

update f
set
numberofdevices = Orders_DailyVolume
from AMPPU_Fehlerdaten_CalcVals a
join AMPPU_Alle_Fehlteile f
on f.Offline_date = a.Offline_date
where f.Offline_date is not null;
-------------------------------------------------
UPDATE AMPPU_Alle_Fehlteile SET
summenew = B.SUpdateColSource
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile group by Offline_date) B ON A.Offline_date = B.Offline_date;
----------------------------------------------------
UPDATE AMPPU_Alle_Fehlteile SET
ARTPU = CASE WHEN numberofdevices = 0 THEN 0 ELSE ISNULL(ARTPU, (summenew/numberofdevices)) END

quote:

---

Originally posted by MichaelJSQL

Try the following


UPDATE AMPPU_Alle_Fehlteile SET
summenew1 = B.SUpdateColSource
FROM #AMPPU_Alle_Fehlteile A

This is going to set the summenew1 for a given date to the sum of Reparatur_hr * 60 for all records in that table with the same date. So every date that is the same will have the same value. Is that what you are trying to do?
INNER JOIN (select Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile group by Offline_date) B ON A.Offline_date = B.Offline_date;

---

---

quote:

---

Originally posted by mana



thank you very much now it works and i want that the following queries will be done automatically every day, should i write a job for them?

update f
set
numberofdevices = Orders_DailyVolume
from AMPPU_Fehlerdaten_CalcVals a
join AMPPU_Alle_Fehlteile f
on f.Offline_date = a.Offline_date
where f.Offline_date is not null;
-------------------------------------------------
UPDATE AMPPU_Alle_Fehlteile SET
summenew = B.SUpdateColSource
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile group by Offline_date) B ON A.Offline_date = B.Offline_date;
----------------------------------------------------
UPDATE AMPPU_Alle_Fehlteile SET
ARTPU = CASE WHEN numberofdevices = 0 THEN 0 ELSE ISNULL(ARTPU, (summenew/numberofdevices)) END

quote:

---

Originally posted by MichaelJSQL

Try the following


UPDATE AMPPU_Alle_Fehlteile SET
summenew1 = B.SUpdateColSource
FROM #AMPPU_Alle_Fehlteile A

This is going to set the summenew1 for a given date to the sum of Reparatur_hr * 60 for all records in that table with the same date. So every date that is the same will have the same value. Is that what you are trying to do?
INNER JOIN (select Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile group by Offline_date) B ON A.Offline_date = B.Offline_date;

---

---

quote:

---

Originally posted by mana


the below code also doesn't have error but i think it doesn'T work correctly

am i right in this code?
UPDATE AMPPU_Alle_Fehlteile SET
ARTPU = CASE WHEN numberofdevices = 0 THEN 0 ELSE ISNULL(ARTPU, (summenew/numberofdevices)) END
thank you

quote:

---

Originally posted by mana



thank you very much now it works and i want that the following queries will be done automatically every day, should i write a job for them?

update f
set
numberofdevices = Orders_DailyVolume
from AMPPU_Fehlerdaten_CalcVals a
join AMPPU_Alle_Fehlteile f
on f.Offline_date = a.Offline_date
where f.Offline_date is not null;
-------------------------------------------------
UPDATE AMPPU_Alle_Fehlteile SET
summenew = B.SUpdateColSource
FROM AMPPU_Alle_Fehlteile A
INNER JOIN (select Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile group by Offline_date) B ON A.Offline_date = B.Offline_date;
----------------------------------------------------
UPDATE AMPPU_Alle_Fehlteile SET
ARTPU = CASE WHEN numberofdevices = 0 THEN 0 ELSE ISNULL(ARTPU, (summenew/numberofdevices)) END

quote:

---

Originally posted by MichaelJSQL

Try the following


UPDATE AMPPU_Alle_Fehlteile SET
summenew1 = B.SUpdateColSource
FROM #AMPPU_Alle_Fehlteile A

This is going to set the summenew1 for a given date to the sum of Reparatur_hr * 60 for all records in that table with the same date. So every date that is the same will have the same value. Is that what you are trying to do?
INNER JOIN (select Offline_date,SUM(Reparatur_hr)*60 SUpdateColSource FROM
AMPPU_Alle_Fehlteile group by Offline_date) B ON A.Offline_date = B.Offline_date;

---

---

---

quote:

---

Originally posted by MichaelJSQL

and the tables in my sql server are linked to the access database i think.
How can i update the changes in sql server in access daatbase as well?

Microsoft access ahs a pass through query - you might be able to copy your SQL in that or you could set up a job via sql agent on your sql server to run at a scheduled date/time


am i right in this code?
UPDATE AMPPU_Alle_Fehlteile SET
ARTPU = CASE WHEN numberofdevices = 0 THEN 0 ELSE ISNULL(ARTPU, (summenew/numberofdevices)) END


Is this the complete update statement?

---

quote:

---

Originally posted by mana

hello,

thank you,
yes this is the whole code.
I changed some in sql server but i don't have them in access databse as well.
i want to update the changes in access as well ans also want to execute the above code that i write will be run automatially every day.

thank you

quote:

---

Originally posted by MichaelJSQL

and the tables in my sql server are linked to the access database i think.
How can i update the changes in sql server in access daatbase as well?

Microsoft access ahs a pass through query - you might be able to copy your SQL in that or you could set up a job via sql agent on your sql server to run at a scheduled date/time


am i right in this code?
UPDATE AMPPU_Alle_Fehlteile SET
ARTPU = CASE WHEN numberofdevices = 0 THEN 0 ELSE ISNULL(ARTPU, (summenew/numberofdevices)) END


Is this the complete update statement?

---

---

quote:

---

Originally posted by MichaelJSQL

UPDATE AMPPU_Alle_Fehlteile SET
ARTPU = CASE WHEN numberofdevices = 0 THEN 0 ELSE ISNULL(ARTPU, (summenew/numberofdevices)) END

So here is what your statement will be doing:

Your table AMPPU_Alle_Fehlteile will have at least these columns ARTPU (your target) , numberofdevices , summenew
Your update statement will act on each row.
For each row it will test if numberofdevices = 0 and if it is , it will update the ARTPU column to 0.
If numberofdevices is not 0, it will update ARTPU to ARTPU if ARTPU is not null. ( make sure you the records you want to update are null and do not have a value or they will not get updated)

If ARTPU is null, it will update ARTPU to summenew divided by the numberofdevices.

Is this what you are trying to do? If so, what is or is not happening?

---

quote:

---

Originally posted by MichaelJSQL

If you using linked tables, you should be able to use the link table manager to refresh Or just drop the linked table in access and re-establish

If you are importing - just drop and reimport

---