Update query with Dlookup function results in conversion error

[James Grant]

Hi Everyone,

I've created a database (MS Access 2003) to help monitor staffing at my work.

I now need to transcribe data from one table (tblPositions) into another (tblTeams) based on a common column (Position ID). Essentially I wish to copy the [Position title] and [Position description] of different positions from tblPositions, into tblTeams according to their already recorded [Position ID] (this is to make the data more readable).

Having looked through this and other forums I've been attempting to automate this process using an UPDATE statement. It is as follows:

UPDATE tblTeams SET tblTeams.[Position description] = (DLookUp("[Description]","tblPositions","(tblPositions.[Position code] = " & tblTeams.[Position code] & ""))
WHERE (((tblTeams.[Position code])=30471524));

Please note the WHERE statement is only here to as a testing clause (I don't want to overwrite any data accidentally).

Every time I've trialled this query however I receive an error box citing a Conversion error as my problem. I've checked and re-checked again and again and as far as I can tell all the fields (description, title, position ID) all have identical formatting.

Is there any other way a Conversion error might be generated?
Is there a better way of doing this?
As a worst case scenario I can manually re-type the data (only about 200-250 records) but I think solving this now will lead to many time saving gains in the future.

Many thanks!

James

[NeoPa]

Hi James. Welcome to Bytes!

Your question indicates a level of inexperience with databases generally (excuse me for mentioning), and a lack of awareness of the fundamental and critically important concept of Normalisation (Database Normalisation and Table structures) particularly. I won't go into all the details of that as the link covers it well already. Suffice to say that updating the related table as you are trying to do is a very bad idea.

If you were to continue with this approach against the express warnings of an experienced database developer, then you would probably want to do it with a JOIN in your SQL instead of using a Domain Aggregate function (DLookup()) as this is a clumsy alternative and not necessary.

Finally, if you're not happy with any of the advice, or even for the sake of simple curiosity, the end of your first line should look like & ")") as the first parenthesis of the two is part of the WHERE string supplied as the last parameter to DLookup().

[James Grant]

Hi NeoPa,

Thank-you very much for your rapid and comprehensive answer.

I intend on re-designing the database in light of the article on normalisation you referred to. I fell into a trap of believing there was such a thing as too many tables, when further normalisation would have probably saved me a lot of trouble initially.

I have tested the syntax correction you posted which worked 'perfectly' in my flawed system. Whilst initially I will have to utilise this fix to keep my managers happy, the re-designing process should be enlightening.

Please note the following section deviates from my original post. Please simply let me know if I should start a whole new thread and I will do so.
One underlying issue to the current state of affairs is modifying data that I have generated using JOIN queries. The key example would be generating a query of my database that combined information from the Staff, Positions and Allocations tables. Using such a query I would be able to see the Staff name, Allocated position number, and Position description (using the common fields in the Allocations table -- StaffID allocated to a PositionID).
However, in my initial testing of such joins, I was unable to edit the data generated by such a query -- the key element in creating the query in the first place.

Is there a way to allow updates to occur across the tables? Does it lie in re-designing the database to only have 1-to-1 relationships?

Many thanks for any more assistance. Again, I will happily re-post to a new topic if that is general policy.

James

[NeoPa]

James,

Clearly your inexperience is limited to database design (and I have a funny feeling that won't last long either). An impressive response, and, Yes, the new question will need to be posted in its own separate thread, but we (I was chatting with the Normalisation article author and site administrator Mary about your response just now) are very much looking forward to seeing it. We both feel that it could prove a seed for a good discussion on related topics (as well as providing you with a number of potential solutions to your issues).

I'm really quite unused to new members understanding that our rules insist on separate threads for separate questions. I usually have to split the new question off from the first one and explain the situation patiently (I'm good at the first step but make no claim to the requisite patience for the second).

The recommended procedure at this point would be to formulate your question in a new thread and then to add a new post in this one with a link to that new thread. That is perfectly acceptable, and gives an opportunity for any members already subscribed to this thread to become aware of the new one without having to catch it from among all the new threads we get every day in the forum.

I'll save my contributions for the new thread as that will keep everything together for anyone searching later ;-)

-NeoPa.

[James Grant]

Hi NeoPa,

I'm glad I could be somewhat of a surprise for you. It all seemed fairly common sense to me keeping each topic about that topic, but I work in healthcare and I appreciate how uncommon sense can be at times.

Thank-you for your reply, I have started a new thread and hope to learn much from you and the Community.

-James