I have on my first table one field called ID with data such as 123456Aberdeen
345765Manchester
765987Aberdeen
Now i want a list so it only shows me Aberdeen or Manchester on a separate column and then i can link that to another table which has 3 fields
ID, Branch
123456, Aberdeen
345765, Manchester
765987, Aberdeen
Finally i would like it group so when i select an IF it gives me a list of all in that branch? I hope i am making sense sorry totally new to this.
Thanks
This may be what you are after: (from ms access help "Record part")
You would use Left([PartID],6)
Extract part of existing text values using a calculated field@import url(/Office.css);Extract part of existing text values using a calculated field
In query Design view, create a query. Add the tables whose records you want to use.
To find values in part of a field, use the Left, Right, or Mid function in an expression in an empty cell in the Field row in the query design grid. The syntax for these functions is:
Left(stringexpr,n)
Right(stringexpr,n)
Mid(stringexpr,start,n)
The stringexpr argument can be either a field name (enclosed in brackets) or a text expression; n is the number of characters you want to extract; start is the position of the first character you want to extract.
The following table shows examples of these functions. If the value in
Part ID is
This expression
Returns BA-7893-R12 Left([PartID],2) BA BA-7893-R12 Right([PartID],3) R12 BA-7893-R12 Mid([PartID],4,4) 7893
Add any other fields you want to include in the query to the query design grid.