Query update ? :confused:

[Christopherusly]

Hello all, complete beginner with access, have been playing for a few months now and have hit a dead end.

I have a table - in which i wish to create a Unique ID called SAreportref. The fields and values of interest are:

Table Fields

IWP_number (autonumber) to create a unique ID for each record
SAreportref (Text)

How would it be possible to create a way of adding the SAreportref prefix of SA to the IWP_number to give for example SA 12 for record number 12 in the SAreportref field.

If that makes sense, any help or guidance would be greatly appreciated.

 

[Mile-O]

Make a query of your table and concatenate the two in that.

i.e.

SELECT SAreportref & IWP_number AS NewField
FROM MyTable;

Change MyTable to the name of your table.

In the query view, you move into one of the unused query field and write:

NewField: SAreportref & IWP_number

Use the query for your forms - queries should always be used for this.

 

[Christopherusly]

ahhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh !! many thanks SJ McAbney. Seems to do the trick nicely.

To build on this further, is is possible to take the output of NewField: SAreportref & IWP_number from the query and write it to mytable as a value which then becomes independant of the query which then runs only the once ( so it cannot be changed )

 

[Mile-O]

is it possible to take the output of NewField: SAreportref & IWP_number from the query and write it to mytable as a value which then becomes independant of the query which then runs only the once ( so it cannot be changed )

Why? It's a very bad practice to store calculated values in a table when the result can be calculated at any time.

 

[Christopherusly]

"my table" contains report records, the query runs to create the report number - based on the SAreportref & IWP_number AS NewField query, this will then display on the report form.

I was looking to write this as a value to the table so when creating reports and running with automation to word ( to output a report ) it keeps things simple and does not require that i take values from queries and tables.