Assigning values automatically based on another value in a table

[Rik_StHelens]

I have a database which helps us to manage part of our tyre fitting network, based in access 2007.

When we attend to a vehicle, the job is given a job number e.g. FJ12345 (it always begins with FJ)

We then help to manage the retreading or recycling of tyre casings.

In the tyre casings database you may find a job which resulted in 2 casings needing to be managed. Each casing must be given a new value, based on the job number.

So for example,

FJ12345 needs 2 new tyres, so we take away 2 casings.

On the new system these would be recorded as 2 seperate records, with a new number

CAN12345-1
CAN12345-2

The number tells us that although the casings are in 2 seperate records, they came from the same original job number, so we can trace them back if necessary

How can i achieve this?

I hope i have made this clear, although im never so sure =]

thanks a lot for your help.

 

[DCrake]

In your table that holds the casings data as unique codes you also need a foreign key of the FJ number back to the parent table.

David

 

[namliam]

FJ12345 and CAN12345 are basicaly the same 12345 right?
In which case you "need" to split the FJ from 12345 adn CAN 12345, that way you have a "real" key of 12345.
Then in a related table you can add parts that are removed and number them 1,2,3,4,etc as you like.

 

[Rik_StHelens]

FJ12345 and CAN12345 are basicaly the same 12345 right?
In which case you "need" to split the FJ from 12345 adn CAN 12345, that way you have a "real" key of 12345.
Then in a related table you can add parts that are removed and number them 1,2,3,4,etc as you like.

Yeah thats right they are both the same number, eg 12345, but they have the different CAN and FJ prefix, and a CAN number has the -1 -2 etc at the end

how would be the best way to go about splitting them?

 

[Brianwarnock]

If you are saying that you cannot split the fields but want to compare the number portion then you will need to use parsing, if the layout shown is fixed then Right(fjfield,5) =mid(canfield,3,5) but if the sizes are variable then Len and Instr may come into play

Brian

 

[namliam]

Different prefix my foot! they have a fixed number period...
You append the prefix in a query or something while having an auto number key on the first table
Select "FJ" & Autonumberfield from yourtable
and use that to display or report.

Then use ONLY your auto number field to relate tables and append the CAN on a query again on the parts table. While storing the 3rd part 1,2,3,4 in a seperate column that you append to ... when needed in a report or form or whatever.

Now your only challenge left is to use a DMax function to calculate the 1,2,3,4 on the fly and stick it into the column you need to have.

KISS Baby KISS

 

[Rik_StHelens]

If you are saying that you cannot split the fields but want to compare the number portion then you will need to use parsing, if the layout shown is fixed then Right(fjfield,5) =mid(canfield,3,5) but if the sizes are variable then Len and Instr may come into play

Brian

No the FJ and CAN prefixes are not variable, although the number portion will, i would assume, vary in length based on the sheer number of jobs we handle

 

[Rik_StHelens]

Ok

based on Brians advice i have decided to implement an update query which will add a CAN Number. I cannot use Mailmans primary key idea as this database draws all its information from a DOS based system that we do not want to touch, and the CAN Number is going to be the primary key.

At present we import data daily (removing duplicates) from the dos system to the access system. The update query should add the CAN Number to each record imported.

Right("CAN",Mid([OLDADVNUM],3,8) & "-" LEFT("A Count Loop Which Counts The Numer Of Identical FJ Numbers"))

I am hoping that this will take the characters in position 3 to 8, then add a dash, then add a number dependant on the count loop (which i have not figured out yet, i want to get the rest working first).

e.g. CAN123456-1
CAN123456-2
CAN123499-1

and so on...

at present i get a type conversion failure for all records when i run the query, but as the query is only based on one table i dont really see why

 

[namliam]

If you are importing data from the dos system this is THE best moment to "normalize" your data and do as I suggested (IMHO)

Moreso this function
Right("CAN",Mid([OLDADVNUM],3,8) & "-" LEFT("A Count Loop Which Counts The Numer Of Identical FJ Numbers"))

Is just bad... As currently bracketed, at the very least it should be something like
Right("CAN",Mid([OLDADVNUM],3,8) & "-" LEFT("A Count Loop Which Counts The Numer Of Identical FJ Numbers"), SomeNumberHere)

or leave out the right all together...

For normalization and query ease in the future I strongly suggest you split of your "real" key (the number) from your 'actual' key. If not make a surrogate key to run of an auto number or something...

The count is best added after the import when the data is in a table. Then opening the table in code (in a specific order) and writing the "appended" line numbers to the record.
Unless you can enforce the order upon import then a public function MIGHT work...

Making the "-1" (appended line numbers) is going to be cumbersome though...

 

[Brianwarnock]

Let me start by saying that I have always agreed with Namlian's advice to simplify the actual keys. It is always simpler to combine fields than to split them.

My "advice" was only to show what would be needed if it were impossible to split the codes.

Brian

 

[Rik_StHelens]

Thanks for all your help

I ended up combining the FJ number with a tyre positioning number to create a unique (and instantly more relevant) number which is unique, and identifies which tyre position on the lorry was worked on.

Thanks again