PS the tables are like spreadsheets, one with about 15 fields, others have fewer and the only common thing is the record number so it coulld be 30 or 40 fields of info about each item in total, plus a picture of each.
Is the value to be matched always a number and the "added on" portion always letters?
If so, a pair of calcualtion fields:
GetAsNumber(OriginalField) // Select Number as your result type
could be set up to strip out the non-numeric characters.
But that reference to A, B and C components suggest a one to many relationship may exist and that in turn argues that you should not merge this data into a single table.
To your first question, yes. But I need to retain the letters while matching on the first four numbers.
When you say 'pair of fields,' I assume you mean I need to set up a calculation field in each of the tables being matched?
Don't think it's one to many issue; the original creator just added letters to some of the record numbers to show that the item being cataloged in the db was made out of three components A, B an C--but she didn't do it consistently. 0001A-C in one table is the same item as 0001 in another and needs to link up to the 0001 photo of the item. However the letters are item classifiers and need to be retained.
What I am suggesting is that you define new calcualtion fields that use GetAsNumber to stip off just the numeric portion of the text. This keeps your original fields unchanged so you do not lose the added letters.
Then use these calculation fields in your relationships to match just by number.
OK. Did a bit of research about calc fields. When you said 'strip off' I thought the letters would be removed. Haven't tried it yet, just on the verge, think it will work. Thanks very much.
yes, the letters should not be visible in the calculation field.
If the calc field is going to return a result that is only a number, that isn't what I need. I need to combine the data from eight tables which all have different fields, whose only common field is an Accession number field. Unfortunately the creator used 0001 in one table and 0001A-C in another table but those Accession numbers are for the same item. I need to combine the 8 tables and their fields to one form view layout per record using the Accession number as the match--only it doesn't match. And I need to keep the letters.
Needs to be a text field. This isn't a math computation. Left ( text ; numberOfCharacters ).
If the part of the ID field that uniquely identifies thes records is only numeric--which is what you have confirmed is the case, a number data type should work just fine and without issues.
It doesn't seem to on a preliminary attempt but I ran out of time to play with it and think it through. Serious deadline right now writing an iPad manual for someone who is 86.
I have to confess I'm not sure how this is supposed to work. I create a calc field GetAsNumber [Accession Number field] in each of the 8 tables? This lets me match the Accession number fields by the digits while ignoring the letters but retainng them so I can add data from one table to another? (I have to create a new layout with all the fields from all the tables; the tables have no fields in common except this problematic Acccession number field.) But there will only be one Accession number field, so if one table has 0001 and another has 0001A-C, which one ends up in the field? Has to be tested.
I'm not clear how putting the calc field there accomplishes this and will have to test it another time. A developer told me since the Accession number field is text is should use get as text and indicate the places, but no time to test.
I create a calc field GetAsNumber [Accession Number field] in each of the 8 tables? This lets me match the Accession number fields by the digits while ignoring the letters but retainng them so I can add data from one table to another?
(I have to create a new layout with all the fields from all the tables; the tables have no fields in common except this problematic Acccession number field.)
No/ This is not necessary.
But there will only be one Accession number field, so if one table has 0001 and another has 0001A-C, which one ends up in the field? Has to be tested.
Don't quite follow that. With your relationships now matching strictly by the numbers you can put either field or both on a given layout.
Very kind of you to stick with me.
(I have to create a new layout with all the fields: Later I'll test the new layout issue. If the fields from one table don't exist in another, then you can't match fields to tell the stuff where to go. Maybe if they don't exist they create their own fields on import if you just use the arrow to tell them to import. Will check. Just seems tidier to give them a place to go.