match 2 value sin one field to a list of values

[Rob DSO Llandrillo]

I originally posted this in the Reports section, but think its more
appropriate here.

any help would be greatly appreciated.

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?

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 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?

 

[Danny Lesandrini]

Rob:

You have a normalization problem. Any solution would be a hack.
About the only thing you can really do for the report is to normalize it and
then run the report. If it has to be this way, try this approach:

1) Put this code in the Open Event of the report.
a. Run function to split Field1 into the temp table
b. Continue opening the report with a query that links to the temp
table of locations.

2) Build the function to split Field1 into the temp table
a. Assure that the data in Field1 is regular, comma seperated values.
b. Delete from tblTempCoLoc where ID = ID of current report
c. Use the Split() function to load the values into an array
d. Process the array, element by element, adding rows to the temp table

This is clumsy because the tables weren't normalized to begin with, but it
could work.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

I originally posted this in the Reports section, but think its more
appropriate here.

any help would be greatly appreciated.

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?

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 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
--
Rob
Data Systems Officer
Coleg Llandrillo
North Wales