Issa S.
asked on
Why this Update SQL not Updating!
Hello,,,
Why this Update SQL not Updating!
UPDATE [PN Details Temp] RIGHT JOIN PNDetails ON [PN Details Temp].Voucher = PNDetails.Voucher SET PNDetails.Voucher = [PN Details Temp]![Voucher]
WHERE ((([PN Details Temp].[PN-No])<>"0"));
Although when viewing the select of it works well and shows the right values and right number of records.
No errors show up and gives the message "n Number of records will e updated . . ."
Thanks
Why this Update SQL not Updating!
UPDATE [PN Details Temp] RIGHT JOIN PNDetails ON [PN Details Temp].Voucher = PNDetails.Voucher SET PNDetails.Voucher = [PN Details Temp]![Voucher]
WHERE ((([PN Details Temp].[PN-No])<>"0"));
Although when viewing the select of it works well and shows the right values and right number of records.
No errors show up and gives the message "n Number of records will e updated . . ."
Thanks
ASKER
Hi Rey,,
It is giving Syntax Error on the JOIN.
It is giving Syntax Error on the JOIN.
there is something wrong with your db
do a compact and repair and try again
can you view the query in design in QBE?
do a compact and repair and try again
can you view the query in design in QBE?
ASKER
I did the Compact and Repair.
Strange now. All expressions on my form are showing #Name?
What does it mean to you as they were working nicely before the compact and the QBE is not showing anything.
Also, with the given SQL up, it still gives the Syntax error!
Strange now. All expressions on my form are showing #Name?
What does it mean to you as they were working nicely before the compact and the QBE is not showing anything.
Also, with the given SQL up, it still gives the Syntax error!
create a new blank db, then
import all the objects from the non working db
test the new db
see if that resolve your problem
import all the objects from the non working db
test the new db
see if that resolve your problem
ASKER
Hi Rey,,,
I did import everything.
It Opens well.
However, it gave me one Error "Search Key is not available anywhere..." before the last step of closing the Import.!!
Thanks
I did import everything.
It Opens well.
However, it gave me one Error "Search Key is not available anywhere..." before the last step of closing the Import.!!
Thanks
what is the size of your db? what version of access are you using?
ASKER
The new imported one is 50 MB while the source is 27 MB.
did you get all the objects from the source db?
do a compact and repair on the new db
can you now run the query correctly?
can you open the query in QBE design view?
do a compact and repair on the new db
can you now run the query correctly?
can you open the query in QBE design view?
ASKER
Yes, I got all the Objects in the Import.
Re-compact is now 48 MB.
Otherwise, it seems to be working fine.
am still worried about the Search Key Error... as I never met this before.
Re-compact is now 48 MB.
Otherwise, it seems to be working fine.
am still worried about the Search Key Error... as I never met this before.
Check all tables for Primary key fields, record counts, see if any are missing.
ASKER
Dear Rey,,,
I feel sorry that am taking a lot of you time on this issue.
I checked all the tables and found that it has created a send copy of each table like this "TableA" there is another one "TableA1" and so on.
Could it be that I imported twice, am not sure. That explains the size, I suppose. Can I delete the ...1?
Otherwise it is working with exception of my Subject Question issue.
In simple few words, what am after is :
1- Table Temp has fields PN-No, and Voucher,DateField (10 Records, say)
2- Table PNDetails has Pn-No, Voucher, and other fields.
3- I want to update the Voucher values in PNDetails with those in the Temp where PN-No on both side are equal.
I feel sorry that am taking a lot of you time on this issue.
I checked all the tables and found that it has created a send copy of each table like this "TableA" there is another one "TableA1" and so on.
Could it be that I imported twice, am not sure. That explains the size, I suppose. Can I delete the ...1?
Otherwise it is working with exception of my Subject Question issue.
In simple few words, what am after is :
1- Table Temp has fields PN-No, and Voucher,DateField (10 Records, say)
2- Table PNDetails has Pn-No, Voucher, and other fields.
3- I want to update the Voucher values in PNDetails with those in the Temp where PN-No on both side are equal.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Rey,,,
"Can I delete the ...1? " . I meant the extra objects suffixed by 1 at the end of their names.
Apparently this must have happened due to mistakenly importing the same twice. . . Sorry.
I removed them and it is OK now.
I used your first SQL (up here) and now all working fine.
Many thanks for help patience.
"Can I delete the ...1? " . I meant the extra objects suffixed by 1 at the end of their names.
Apparently this must have happened due to mistakenly importing the same twice. . . Sorry.
I removed them and it is OK now.
I used your first SQL (up here) and now all working fine.
Many thanks for help patience.
ASKER
Very kind of Rey for his patience and support.
UPDATE PNDetails
Inner JOIN [PN Details Temp] ON PNDetails.Voucher = [PN Details Temp].Voucher
SET PNDetails.Voucher = [PN Details Temp]![Voucher]
WHERE ((([PN Details Temp].[PN-No])<>"0"));