MeadInKent
| Feedback | Like and wildcards in Criteria | Using Queries |

Linking and updating Access tables with wildcards

Tables (or queries) within a query are normally linked by a common field. In design mode, the cursor can simply be dragged from a fieldname in one source table to another and a line will appear to represent the join and type of relationship. If no line is drawn, the displayed result will normally be a long list of every combination of records from each of the tables.

This page is a peculiar topic. A much more common use of wildcards in queries is for setting the criteria.

Occasionally however it is useful to have a wildcarded value as the link. These may incorporate the '?' and '*' symbols to represent any characters. The '?' can represent any single character and the '*' may represent a string of any number of characters. For example 'A?C*' will match with ABC123 or AFCZZ but not with ABBC123 or ACZZ. Wildcards cannot be embedded within the values of normal linked fields because the special characters will be taken literally.

UpdateDetails: Table ox
  WildcardGroupName ClubType
  *ATHLETICS* Sport
  *SCHOOL* Education
   

Linking tables using wildcards provides the ability to update fields in a range of records in another table. This might be particularly useful in an accounting program where there is a need to update a field for a wildcarded selection of account codes. The following example uses a table of wildcard values to change the details in another table using an Access Update query. The intention is to apply the ClubType field values to any record containing either 'School' or 'Athletics'.

The following query design does not contain a normal link between the two tables. The WildCardGroupName field is added as a criteria against the Group_Name field in the other table and the LIKE() keyword ensures that any wildcard characters are recognised as such. The Group_Type field in the UserGroups table will be updated with new values taken from the ClubType field.


UpdateGroupTypes: Update Query (Design) ox
 
Field:  Group_Name Leader Group_Type  
Table: UserGroups UserGroups UserGroups  
Update To: [ClubType]  
Criteria:  Like [WildcardGroupName]      
Or:        
 

Access 2010 All in One for Dummies

 
UK more ...   USA more ...

When the Update query is run, the 2 wildcard fields match with 5 UserGroups records and the Group_Type fields are updated for only those records.

UserGroups: Tableox
Group_Name Leader Group_Type
CHELSFIELD GIRL GUIDES Miss Black
GODDINGTON SCHOOL Dr Green EDUCATION
HIGH FLYERS ATHLETICS Miss Fit SPORT
HIGHWAYMAN SCHOOL Mr Turpin EDUCATION
MEADINKENT ATHLETICS CLUB Mr C Hustle SPORT
MR AND MRS JONES Mr Jones
PEMBURY SCHOOL SWIMMING CLUB Mr T Kent EDUCATION
SUBSTANTIAL THEATRE GROUP Miss J Ayckborn

This unusual method of linking tables is not confined to Update queries and can also be used with Select, Append or Delete queries.


file: accwildcardlinks.htm Page last updated Mar14 MeadInKent.co.uk 2014