I am a novice at this and any help will be gratefully received.

I have imported data which can only be exported by the donor programme as Text. The data is imported into two separate tables. Included with the data in both tables is a number which relates to a specific dwelling, the difference being that the data relating to Rent starts with a 5, and the data relating to Service Charge starts with a 4. The numbers cannot be converted to "number" data type as they include a slash to separate the property indetification number from the flat identification number. E.g. Falling Down Mansions property No: 4010. Flat No 3 Falling Down Mansions would therefore be 4010/003.

I wish to use the Property Number as the Primary Key so that by running a Make table query I can establish whether money is owed as Rent, Service Charge or both. Before I can do this I need to "update" the first digits in the Property Number in all of the records in one of the tables so that the two Property Numbers start with the same digit.

I have tried all sorts including an IIf Statement, but I am getting nowhere fast. I have found how to achieve this in VB (I think), but I have no idea whether I can "break into" the coding section of Access to use it.

Help!

Use an AutoNumber as the Primary Key and don't try to use a meaningful number or text for this key. You can use the Left(), Mid() and Instr() functions to split the Property numbers

Left(PropID,Instr(PropID,"/")-1)
Mid(PropID,Instr(PropID,"/")+1)

Thanks for the response. Unfortunately I have to use the Property Number to make the new "merged " table as not all properties have both a 5000 series number and a 4000 series number. The records would therefore not tie in. Thanks for the thought though.

Where would I use the Left() etc?

In that case you would need to create 2 records. One for the 5000 and one for the 4000. To do otherwise would result in an unnormalized structure.