concatenate in update query

[scubadiver007]

I want to create a new field in a table based on two other columns called ID.

This is the SQL I currently have:

UPDATE dbo_ES_factbase SET dbo_ES_factbase.ID = [gppracticecode] & " - " & [quartercode];

It doesn't throw up an error but there is nothing in the rows.So what am I doing wrong?

 

[vbaInet]

No need to concatenate the fields, just concatenate it in a SELECT query when you want to use it:

Expr1: [gppracticecode] & " - " & [quartercode]

where Expr1 is the default alias.

 

[scubadiver007]

But what if I want to use it to join tables?

 

[vbaInet]

Not a wise thing to use such a field as a join between tables/queries. It will certainly affect performance and of course there will be no indexing.

In any case you can still join on that field. Create another query based on that one and you will be able to join on that field.

 

[scubadiver007]

I tried creating a select query and then using that as the source for another query to join two tables but not much luck.

To give some detail, I have a doctor surgery table. I also have some patient population data on each surgery and 3 monthly updates (ie every quarter).

I don't have a unique ID for this population table to which I can join to a much larger table of records (called "dbo_ES_factbase"). Basically I want to associate each record in this larger table with the appropriate surgery code and quarter.

This larger table is from an ODBC server.

Is there any other way that can be suggested? (screen shot attached)

And if there is anything wrong with my table relations feel free to let me know! (it has been awhile)

 

[scubadiver007]

The number of records in the join query matches the 'factbase' table so it seems to be ok now.

 

[vbaInet]

I can see that there is no direct link between the population table and the factbase table, but there is a chain which indirectly links them. You can go via those tables to pull up the records.

I noticed a table or even a query called tbl_Query... If this is a query and if what you posted is your Relationships diagram, I wouldn't advise including a query to link your tables. You can do so on query level but at the baseline only tables should be used. Unless, there's absolutely no other way of linking your table to the external source (which might be your dilema) would you employ that method.

 

[Brianwarnock]

I know this is nothing to do with the current position but I wonder why the update didn't work. Don't want to appear rude but you did run the query not just click the view icon?

Brian

 

[scubadiver007]

Hello Brian,

I only ran the query because I presumed the data would get inserted. But it also might be because my new column was not in the correct format!

 

[vbaInet]

I know this is nothing to do with the current position but I wonder why the update didn't work. Don't want to appear rude but you did run the query not just click the view icon?

Brian

Most likely because the string being inserted was not wrapped in quotes or trying to insert a string into a number field.

 

[Brianwarnock]

The new column obviously has to be a text string because of the " - " after that no problems
[gppracticecode] & " - " & [quartercode]

will be inserted whether the codes are text or numbers.

Brian

 

[vbaInet]

That's what I mean - the value being inserted is text and if you're trying to insert a text into a number field it will fail or set that value to Null. I suspect the OP has Warnings turned off which will cause it to run without any failure warnings.

 

[Brianwarnock]

I suspect the OP has Warnings turned off which will cause it to run without any failure warnings.

Not very clever when testing if that is the case, but lets not be too judgemental and wait to see if the OP enlightens us.

Brian