Help! I am trying to split 1 field into 2 using an update query

[Hargreavesk]

Hi,

Just wondered if anyone could help me?

I have a table which is imported weekly into my database. Unfortunately, it has 1 field called "Match" which holds football match information eg. Blackburn Rovers v Liverpool.

I would like to split this field into 2 fields called "HomeTeam" and "AwayTeam". The Home team always being put in first and the away team second in the "Match" field.

I would like to do this via an update query but I'm not entirely sure how to get rid of the "v" first.

Any help would be much appreciated.

Many thanks

Kate
kate.hargreaves@cis.co.uk

 

[BNPMike]

Kate

You need to locate the first space for each record. Say this is position 12, then your first update is substring (1..11). Your second update is substring (12+3...field length).

Use InStr to locate the blank. Then use Left and Right functions to pick out the fields you need.

If necessary do this is several steps by creating a set of new columns to hold intermediate calculations. mike.stephens@bnpparibas.com

 

[ziwacky]

Is this what you're looking for to do?

Update TeamTable
Set
HomeTeam = Mid(Match, 1, InStr(Match, " v &quot - 1),
AwayTeam = Mid(Match, InStr(Match, " v &quot + 3, Len(Match))

Cheers!

Z

 

[Hargreavesk]

Mike, Ziwacky,

Thankyou for your suggestions. Unfortunately, I'm not a Access developer and am just trying out my new found skills (1 Access course).

Please could you explain everything in Laymans terms for me as I'm new to this?

I need to know where to start creating the Update query!!!

Sorry.

Kate

 

[ziwacky]

Kate,

Don't be sorry. We've all been there. We've all started from scratch.

In your database window go to the Query tab.

Create a new query and select the table you want to update.
In your query window right click and mouse over Query Type and select "Update Query". Assuming that your "HomeTeam" and "AwayTeam" are existing fields in your table, double click or drag and drop each of these fields into the query grid.

In the "Update To" expression for the HomeTeam field enter the following: Mid([Match],1,InStr([Match]," v &quot-1).

For the AwayTeam field: Mid([Match],InStr([Match]," v &quot+3,Len([Match])).

Run your query to make sure that you don't have any syntax errors. Save your query and you will have it available to use it different ways.

This query, though, is only going to work if the separator from one team and the other team is "v" (ex. Yankees v Rangers). I didn't want to introduce other concepts that may confuse you at this time. You have to make sure that this will always be the case and not "v." or "vs". Otherwise, you'll have to implement other logic to verify it. I always do myself cuz I'm very picky at that.

 

[Hargreavesk]

Ziwacky,

Thankyou soooo much. That has worked brilliantly.

Regards

Kate