Access Help

[ps2328h]

Hi There,

I am new to this site and Access as well. I have two tables i need to link and and get the data.

Can anyone help me on how to set the query so it shows me results after the first 6 numbers?

Thanks in advance

 

[PNGBill]

can you provide some info on what type of records you have on what tables.

 

[ps2328h]

Hi Yes,

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

 

[PNGBill]

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

1. In query Design view, create a query. Add the tables whose records you want to use.
2. 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
3. Add any other fields you want to include in the query to the query design grid.

 

[PNGBill]

Will give you what you don't want. You want what is left after removing the 6 left chrs. Still checking...

 

[PNGBill]

Use:

Mid(stringexpr,start,n)

Mid([ID],7,50)

See if this will give you up to 50 chrs starting at the 7th.

Help shows Mid([ID],7,4) as returning 4 chrs starting at the 7th but of course you don't know how many chrs from the 7th you want.

 

[boblarson]

Did you create this concatenated ID or does it come from another system?

 

[ps2328h]

Thanks I'll give a go...

The Id's come from another system, I get a csv file and then have to import and sort the data.