Qry to copy data and edit data from one field to another

[Maclain]

Good morning all.

I have an issue with a large(ish) database.

We use a primary key in our main table tblJobRegister called job_no which is a numeric field limited to 7 digits.

There is a pre set format of xx/xxxx we use the first two numbers as the year and the last 4 digits range from 0001 to 9999 throughout the year.

My issue is that I a running out of number very quickly and need to increase this from /xxxx to /xxxxx.

Sounds simple enough, until I realised that relationships have been created on this field.

So I assume the best way of doing this is to create a new field in the same table, ie Job_No1 which is set to 8 digits and then copy the data across.

I'm not an avid access user, but I can find my way around code (usually)!

I been advised the best way of doing this is an update qry using something like [FONT=&quot]

Left([job_no],2) & "/0" & Right([Job_No1],4)

But I cant figure out any more.

If anyone has any clues, or could offer some guiding advice, (or a complete solution it would be greatly appreciated.


[/FONT]

 

[pl456]

You pretty much have it with that expression. Create a select query based on your main table and add expr1: with you code into the grid.
Create a second query based on the first query, change it to an update query. The field that you want to update put [expr1] as the criteria.

Should work fine, but test it on a copy of the database.

 

[Maclain]

Morning PL456

Thanks for the quick response!

I've set up a copy of the database to work on, this is a split database (front end and back end)

I'm after a little more elaboration and what exactly I need to do.

I'm not a fantastic access user, and seem to have adopted caring for this database.

Anyhow.

I'm fine with creating the query and selecting the original Job Number filed, but I'm unsure as to what you mean by

expr1: with you code into the grid.

.

I should also point out I've never actually used an update query...


Any chance of you delving a little deeper with this one for me?

ta!

 

[pl456]

Open the query in design mode so you can see the design grid, go to the first empty column and type expr1: followed by your code. save and run the query to see what it does. You should basically see the results of what you want in a new field called expr1.

Create the second query just as you did with the first but base this query on your first query rather than the table. You will not need to add the expr1 as the first query has aleardy done this.
Right click in the grey area where the tables are shown and you will have an option for query type, change this to update.

You will now see below each field in the design grid an "Update to" row, you should put [expr1] in the "update to" box below the field you wish to update.

This will basiaclly take the values from the new field expr1 and put them into which ever field you have put [expr1] under.

Hope this makes sense.

Something to note, you said you have a relationship with another table, if this is the case the second table should have the same info in it so this will need repeating for that table.

 

[neileg]

Just a point to note. Your situation arises only because you have given your PK a meaning to the user. If this field had not been the PK, adding an extra digit would be no big deal. That's why I always use meaningless PKs, i.e. an autonumber. A reference feild that is used by the user can still be provided, just not as a PK.

 

[gemma-the-husky]

i agree with neil

you may be able to get what you want by

a) adding an autonumber field/key to your table job/register
b) then you have more flexibility with the existing field jobno

however, you would have to review your subordinate tables/queries/forms etc to add the new autonumber field to those tables, and to use that as the link between tables, which is probably not a trivial exercise.

now you say your jobno is limited to 7 digits, which implies its text, rather than numeric.
So surely you could just resolve your current problem by changing your jobno in your main table/subordinate tables to a longer field length

 

[Maclain]

Neil,

I know exactly what you mean. I have created numerous databases using auto number PK's, this db was originally built by someone else... typical eh?

gemma:

The field is set to 7 and is text based field, however I believe you can't change the length of a field used as a relationship.... which this one is.

 

[Maclain]

PL456 I've set up both qry's and have tried running it twice.

basically access is running out of memory during the qry - hugely resource intensive (1800mb ram usage maxing both cores).

the error is that the max database size has been reached 2GB or there is not enough temp storage space on the disk drive.

Should i mention there are in excess of 33,000 records in this specific table?

I've just done a compact and repair and have started the qry again..

Edit:

It appears I can't run this qry across the full table, as there is too much data.

As we have entries for 04/* 05/* etc I've altered the select qry expr field criteria to select Job_No field like 04* and am planning to do the update year by year.

Any advice?