Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Msg 4104

Author  Topic 

mana
Posting Yak Master

102 Posts

Posted - 2014-07-22 : 06:32:27
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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-22 : 08:12:55
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

Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-07-22 : 08:27:26

yes it has this column. yes the problem is related to red part




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



Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-22 : 10:15:04
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;
Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-22 : 10:22:44
You are trying to access a table that is aliased inside the set .(select summeofrepairtime from AMPPU_ARTPU).

Try writing your statement like this and you should not get the error.

update AMPPU_Alle_Fehlteile
set summe =AMPPU_ARTP.summeofrepairtime
FROM AMPPU_Alle_Fehlteile
INNER JOIN AMPPU_ARTPU ON AMPPU_Alle_Fehlteile.Offline_date = AMPPU_ARTPU.offline_date1;

Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-07-23 : 03:20:48

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;


Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-07-23 : 03:22:11

thank you
now it works
but i have the below problem

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;




Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-07-23 : 04:30:20

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;




Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-07-23 : 05:28:50


and this time i get this error:

Msg 164, Level 15, State 1, Line 5Each GROUP BY expression must contain at least one column that is not an outer reference.


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;






Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-23 : 06:17:52
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;
Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-07-23 : 07:26:47


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;


Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-07-23 : 07:30:00

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?

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;




Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-07-23 : 07:35:47

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;




Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-07-23 : 07:40:38


i want that ARTPU will be equal to
summenew/numberofdevices



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;






Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-23 : 07:55:41
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?
Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-07-23 : 08:33:19
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?


Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-07-23 : 09:04:26

I mean i added a column in sql server but it is not added to access database.



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?




Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-23 : 09:11:05
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?
Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-07-23 : 09:40:43

thank you, i checked it and now it works.
do you know how can i export the changes in sql to access( i chnged the table in sql server for example added some columns in sql server but i can't see them in access database's table.





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?

Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-23 : 12:44:48
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
Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-07-24 : 03:29:46

thank you very much,
i tried it and it works.
sorry for bothering.
I have the queries that i sent to you, i want that this queries will be done automatically every hour or minute. i added some columns in sql server and i want to fill them. also filling in access database. If i write a job for them, will it be ok? and if yes the access database will be also updated?





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

Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -