This is my first post on here - i have a basic understanding of access, but still build most of my queries with the wizard rather than in SQL - so please be gentle with me!! I can usually follow them through in SQL, and have searched the forum on here to help me with problems before, but i couldn't find what i need to sort this one out..... i suspect i do not know the function name i need to get the search results to help me!!
I have built a query that will update values in the master table with values from a recharge table as follows:
INNER JOIN [Recharge] ON [Master].Unique = [Recharge].Unique
SET [Master].[Amount Recharged] = [Recharge]![Total Held], [Master].[Total Held] = 0;
The update works for rows where a value exists in [Recharge] - replacing the [Master].[Amount Recharged] value with the [Recharge].[Total Held] value, and the [Master].[Total Held] with zero. However, where there isn't an entry, it replaces the values in [Master].[Amount Recharged] and [Master].[Total Held] with null fields.
How do i get the query to update items in [Master] ONLY where there is a corresponding value in [Recharge] leaving all other values in [Master] as they were originally - i.e. if there is no data in the recharge table for an item in the master table, take no action to that item (but updating entries where there are)?
I tried adding in a WHERE EXISTS statement (that i stole from a thread where someone was trying something similar) but get the message
"operation must use an updatable query".
Any help to sort this out would be greatly appreciated!!!