|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 22nd, 2007, 01:12 AM
posted to microsoft.public.access.queries
|
|||
|
|||
Update Query Question Need Help
I have an update query that I use to update a table with data from another
table. However, my "Finish" table is a checkbox (0 and -1). When I run the query it does not update. How can I get the Finish field to update? Thank you in advance for any help will be greatly appreciated. field: Finish Table: Tracking Table Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish]) field: Finish Table: Tracking Table1 Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish]) 
|
|
#2
February 22nd, 2007, 02:58 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Update Query Question Need Help
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships would be a nice touch too. Also any indexes. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "TotallyConfused" wrote: I have an update query that I use to update a table with data from another table. However, my "Finish" table is a checkbox (0 and -1). When I run the query it does not update. How can I get the Finish field to update? Thank you in advance for any help will be greatly appreciated. field: Finish Table: Tracking Table Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish]) field: Finish Table: Tracking Table1 Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])
|
|
#3
February 22nd, 2007, 04:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Update Query Question Need Help
NZ only works with Nulls, not 0's and -1's.
NZ looks at a value and if it's not null uses it, otherwise it uses the other value. Since your value is never null it always uses that one. Try this instead of your nz statement: T1 is the alias I used for [tbl tracking table1] T is the alias I used for [tbl tracking table] IIf([T1].[Finish][T].[Finish],[T1].[Finish],[T].[Finish]) Note: I assumed that you always required the "checked" value over the "non- checked". If that's not the case then change the '' to ''. Cheers, Jason On Feb 21, 8:12 pm, TotallyConfused wrote: I have an update query that I use to update a table with data from another table. However, my "Finish" table is a checkbox (0 and -1). When I run the query it does not update. How can I get the Finish field to update? Thank you in advance for any help will be greatly appreciated. field: Finish Table: Tracking Table Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish]) field: Finish Table: Tracking Table1 Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])
|
|
#4
February 22nd, 2007, 05:41 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Update Query Question Need Help
Could I use this same statement to update existing data? Or would I need
something else to update existing data. thank you. "Jason Lepack" wrote: NZ only works with Nulls, not 0's and -1's. NZ looks at a value and if it's not null uses it, otherwise it uses the other value. Since your value is never null it always uses that one. Try this instead of your nz statement: T1 is the alias I used for [tbl tracking table1] T is the alias I used for [tbl tracking table] IIf([T1].[Finish][T].[Finish],[T1].[Finish],[T].[Finish]) Note: I assumed that you always required the "checked" value over the "non- checked". If that's not the case then change the '' to ''. Cheers, Jason On Feb 21, 8:12 pm, TotallyConfused wrote: I have an update query that I use to update a table with data from another table. However, my "Finish" table is a checkbox (0 and -1). When I run the query it does not update. How can I get the Finish field to update? Thank you in advance for any help will be greatly appreciated. field: Finish Table: Tracking Table Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish]) field: Finish Table: Tracking Table1 Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])
|
|
#5
February 22nd, 2007, 05:55 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Update Query Question Need Help
Sure it will. Backup your database (which you should always do before
you try something new) and test it out to see if it works. Place that in your "update to:" and it will do your update. Cheers, Jason Lepack On Feb 22, 12:41 pm, TotallyConfused wrote: Could I use this same statement to update existing data? Or would I need something else to update existing data. thank you. "Jason Lepack" wrote: NZ only works with Nulls, not 0's and -1's. NZ looks at a value and if it's not null uses it, otherwise it uses the other value. Since your value is never null it always uses that one. Try this instead of your nz statement: T1 is the alias I used for [tbl tracking table1] T is the alias I used for [tbl tracking table] IIf([T1].[Finish][T].[Finish],[T1].[Finish],[T].[Finish]) Note: I assumed that you always required the "checked" value over the "non- checked". If that's not the case then change the '' to ''. Cheers, Jason On Feb 21, 8:12 pm, TotallyConfused wrote: I have an update query that I use to update a table with data from another table. However, my "Finish" table is a checkbox (0 and -1). When I run the query it does not update. How can I get the Finish field to update? Thank you in advance for any help will be greatly appreciated. field: Finish Table: Tracking Table Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish]) field: Finish Table: Tracking Table1 Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])- Hide quoted text - - Show quoted text -
|
|
#6
February 22nd, 2007, 06:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Update Query Question Need Help
Thank you
"Jason Lepack" wrote: Sure it will. Backup your database (which you should always do before you try something new) and test it out to see if it works. Place that in your "update to:" and it will do your update. Cheers, Jason Lepack On Feb 22, 12:41 pm, TotallyConfused wrote: Could I use this same statement to update existing data? Or would I need something else to update existing data. thank you. "Jason Lepack" wrote: NZ only works with Nulls, not 0's and -1's. NZ looks at a value and if it's not null uses it, otherwise it uses the other value. Since your value is never null it always uses that one. Try this instead of your nz statement: T1 is the alias I used for [tbl tracking table1] T is the alias I used for [tbl tracking table] IIf([T1].[Finish][T].[Finish],[T1].[Finish],[T].[Finish]) Note: I assumed that you always required the "checked" value over the "non- checked". If that's not the case then change the '' to ''. Cheers, Jason On Feb 21, 8:12 pm, TotallyConfused wrote: I have an update query that I use to update a table with data from another table. However, my "Finish" table is a checkbox (0 and -1). When I run the query it does not update. How can I get the Finish field to update? Thank you in advance for any help will be greatly appreciated. field: Finish Table: Tracking Table Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish]) field: Finish Table: Tracking Table1 Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])- Hide quoted text - - Show quoted text -
|
|
#7
February 22nd, 2007, 08:04 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Update Query Question Need Help
You're very welcome.
On Feb 22, 1:08 pm, TotallyConfused wrote: Thank you "Jason Lepack" wrote: Sure it will. Backup your database (which you should always do before you try something new) and test it out to see if it works. Place that in your "update to:" and it will do your update. Cheers, Jason Lepack On Feb 22, 12:41 pm, TotallyConfused wrote: Could I use this same statement to update existing data? Or would I need something else to update existing data. thank you. "Jason Lepack" wrote: NZ only works with Nulls, not 0's and -1's. NZ looks at a value and if it's not null uses it, otherwise it uses the other value. Since your value is never null it always uses that one. Try this instead of your nz statement: T1 is the alias I used for [tbl tracking table1] T is the alias I used for [tbl tracking table] IIf([T1].[Finish][T].[Finish],[T1].[Finish],[T].[Finish]) Note: I assumed that you always required the "checked" value over the "non- checked". If that's not the case then change the '' to ''. Cheers, Jason On Feb 21, 8:12 pm, TotallyConfused wrote: I have an update query that I use to update a table with data from another table. However, my "Finish" table is a checkbox (0 and -1). When I run the query it does not update. How can I get the Finish field to update? Thank you in advance for any help will be greatly appreciated. field: Finish Table: Tracking Table Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish]) field: Finish Table: Tracking Table1 Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
