| | Feedback | Like and wildcards in Criteria | Using Queries | | ||
|
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: | ||||
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: Table | ox | |
| 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 |
