match 2 values in a field to values listed in a table

[Rob]

I am trying to list 2 locations on an ID card (access report) based on the
values in field1, which is linked to values in the locations table.

eg.
Field1 = C01, C02

location table
C01 - Main site
C02 - 2nd Site
C03 - 3rd Site
etc

i am worndering if this is feasibly possible?
can i just list the values in field1 as in the example above and work out a
way to produce a list of the selected locations or will i have to use an
intermediate table which seperates the values in field1 into single values?

 

[John Spencer (MVP)]

Obviously, you have a table design problem. You are storing more than one
value in one field. That situation should never happen. You should have an
additional table to store the locations for each ID.

To some extent you can work around this problem. It depends on the values in
your tables whther the following will work correctly or not.

This sample query should return one record for each combination of ID and
location. Since the join is not based on equality of values you cannot use
the query design view to build the query, but must use the SQL view.

SELECT [IDCardTable].*, [Locations].FieldCO
FROM [IDCardTable] LEFT JOIN [Locations]
ON [IDCardTable].Field1 LIKE "*" & [Locations].FieldCO & "*"

You can start the query in design view and use a standard join between the two
tables. Once you have selected the fields you want to display and set up a
join between field1 and FieldCo, you can switch to SQL view (Menu View: Sql)
And edit the join so it reads like the one above. Obviously you need to use
your real table and field names.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Rob]

Thanks John, i have done what you suggested and the query now works, i now
just need to build this SQL into the process involved and fingers crossed,
problem sorted.

thanks again,
Rob

Obviously, you have a table design problem. You are storing more than one
value in one field. That situation should never happen. You should have an
additional table to store the locations for each ID.

To some extent you can work around this problem. It depends on the values in
your tables whther the following will work correctly or not.

This sample query should return one record for each combination of ID and
location. Since the join is not based on equality of values you cannot use
the query design view to build the query, but must use the SQL view.

SELECT [IDCardTable].*, [Locations].FieldCO
FROM [IDCardTable] LEFT JOIN [Locations]
ON [IDCardTable].Field1 LIKE "*" & [Locations].FieldCO & "*"

You can start the query in design view and use a standard join between the two
tables. Once you have selected the fields you want to display and set up a
join between field1 and FieldCo, you can switch to SQL view (Menu View: Sql)
And edit the join so it reads like the one above. Obviously you need to use
your real table and field names.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am trying to list 2 locations on an ID card (access report) based on the
values in field1, which is linked to values in the locations table.

eg.
Field1 = C01, C02

location table
C01 - Main site
C02 - 2nd Site
C03 - 3rd Site
etc

i am worndering if this is feasibly possible?
can i just list the values in field1 as in the example above and work out a
way to produce a list of the selected locations or will i have to use an
intermediate table which seperates the values in field1 into single values?

 

[Rob DSO Llandrillo]

i have just found a problem with the SQL you provided John, it returns any
values that match in the locations table.

so for instance, if i type in D25, D26 in field1, then the query returns D2,
D25, D26.

what i need it to do is look at the actual value on either side of the comma
and then match those values to the locations table.

can this be done?
--
Rob Corbett
Data Systems Officer
Coleg Llandrillo Cymru

Thanks John, i have done what you suggested and the query now works, i now
just need to build this SQL into the process involved and fingers crossed,
problem sorted.

thanks again,
Rob

Obviously, you have a table design problem. You are storing more than one
value in one field. That situation should never happen. You should have an
additional table to store the locations for each ID.

To some extent you can work around this problem. It depends on the values in
your tables whther the following will work correctly or not.

This sample query should return one record for each combination of ID and
location. Since the join is not based on equality of values you cannot use
the query design view to build the query, but must use the SQL view.

SELECT [IDCardTable].*, [Locations].FieldCO
FROM [IDCardTable] LEFT JOIN [Locations]
ON [IDCardTable].Field1 LIKE "*" & [Locations].FieldCO & "*"

You can start the query in design view and use a standard join between the two
tables. Once you have selected the fields you want to display and set up a
join between field1 and FieldCo, you can switch to SQL view (Menu View: Sql)
And edit the join so it reads like the one above. Obviously you need to use
your real table and field names.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am trying to list 2 locations on an ID card (access report) based on the
values in field1, which is linked to values in the locations table.

eg.
Field1 = C01, C02

location table
C01 - Main site
C02 - 2nd Site
C03 - 3rd Site
etc

i am worndering if this is feasibly possible?
can i just list the values in field1 as in the example above and work out a
way to produce a list of the selected locations or will i have to use an
intermediate table which seperates the values in field1 into single values?

 

[John Spencer (MVP)]

You will need to concatenate in the comma and perhaps remove the spaces. The
following should work.

SELECT [IDCardTable].*, [Locations].FieldCO
FROM [IDCardTable] LEFT JOIN [Locations]
ON "," & Replace([IDCardTable].Field1," ","") & ","
LIKE "*," & [Locations].FieldCO & ",*"

This expression
"," & Replace([IDCardTable].Field1," ","") & ","
should turn C01, C02 into ,C01,C02,

and "*," & [Locations].FieldCO & ",*"
should turn the value in FieldCO (for example CO1) into ,CO1,

The wildcards should then give you an exact match as long as FIELD1 always has
the multiple values separated by commas (and optionally has some spaces in
there). However if someone enters
CO1 CO2 or
CO1; CO2 or
CO1 - CO2
You will not get a match. If you have that kind of data entry problem then
you would probably be better off developing a custom VBA function to decide on
matches or to at least strip extraneous characters out of the string.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County