Query involving Strings : How To Return Matching Data From Both Ta

D

darson4eva

Good afternoon,

I have tried my best on this but am unable to figure it out.

---------------------------------------------------------------------
Scenario:

Table A contains only one column titled [District].
Let's assume that there are three rows here:
1) Bay Area Rapid Transit
2) San Diego Zoo
3) San Mateo


Table B contains other data with these fields:
[GeoCode] , [CountOfHourlyEmployees], [CountOfSalaryEmployees].
Let's assume that there are three rows here:
1) West Coast | CA | Bay Area Rapid Transit , 154, 205
2) CA | Southern | Bay Area Rapid Transit , 105, 206
3) Southwest | CA | San Diego Zoo, 358, 625

I am attempting to create a query that will return all values from Table B
that contain the string from Table A. This should be grouped by the value in
Table A

For example, I would expect my output to be:
Bay Area Rapid Transit, West Coast | CA | Bay Area Rapid Transit , 154, 205
Bay Area Rapid Transit, CA | Southern | Bay Area Rapid Transit , 105, 206
San Diego Zoo, Southwest | CA | San Diego Zoo, 358, 625

--------------------------------------------------------------------------------------------

This is the query that I'm using:
SELECT A.District,
B.GeoCode,
B.CountOfHourlyEmployees,
B.CountOfSalaryEmployees
FROM A, B
WHERE InStr(B.GeoCode, A.District)<>0;

In my results, I'm getting some of the data but not everything. I noticed
that if a value appears more than once in Table B, only the first record is
picked up.

Any suggestions?
 
N

NetworkTrade

does the [District] field not appear in table B?

it seems like it is embedded or implied by the GeoCode field i.e.

West Coast | CA | Bay Area Rapid Transit

It is not clear what the divider line is.....
 
D

darson4eva

You are correct.
This field does not appear in Table B.

The GeoCode field contains similar data. That is precisely why I'm trying
to join on those two fields.



NetworkTrade said:
does the [District] field not appear in table B?

it seems like it is embedded or implied by the GeoCode field i.e.

West Coast | CA | Bay Area Rapid Transit

It is not clear what the divider line is.....

--
NTC


darson4eva said:
Good afternoon,

I have tried my best on this but am unable to figure it out.

---------------------------------------------------------------------
Scenario:

Table A contains only one column titled [District].
Let's assume that there are three rows here:
1) Bay Area Rapid Transit
2) San Diego Zoo
3) San Mateo


Table B contains other data with these fields:
[GeoCode] , [CountOfHourlyEmployees], [CountOfSalaryEmployees].
Let's assume that there are three rows here:
1) West Coast | CA | Bay Area Rapid Transit , 154, 205
2) CA | Southern | Bay Area Rapid Transit , 105, 206
3) Southwest | CA | San Diego Zoo, 358, 625

I am attempting to create a query that will return all values from Table B
that contain the string from Table A. This should be grouped by the value in
Table A

For example, I would expect my output to be:
Bay Area Rapid Transit, West Coast | CA | Bay Area Rapid Transit , 154, 205
Bay Area Rapid Transit, CA | Southern | Bay Area Rapid Transit , 105, 206
San Diego Zoo, Southwest | CA | San Diego Zoo, 358, 625

--------------------------------------------------------------------------------------------

This is the query that I'm using:
SELECT A.District,
B.GeoCode,
B.CountOfHourlyEmployees,
B.CountOfSalaryEmployees
FROM A, B
WHERE InStr(B.GeoCode, A.District)<>0;

In my results, I'm getting some of the data but not everything. I noticed
that if a value appears more than once in Table B, only the first record is
picked up.

Any suggestions?
Click to expand...
Click to expand...
 
N

NetworkTrade

well to join two tables there has to be a common cross reference field in
both....

if this is a new database then set it up so that when GeoCode is selected -
the approrpriate District Code is auto entered into the table - so you will
have a cross referencing field...

if this is an existing database then it is more challenging; if that
GeoCode field has a consistent structure then you can create a cross
reference field via a query. You might be able to filter it out of the
GeoCode and add as a new field.... or possibly using an IIF statement ....

although if only one field is in question there isn't a compelling reason to
do a join - just create the new field.......but if Table A has other data
fields then it would make more sense to need a join.......
--
NTC


darson4eva said:
You are correct.
This field does not appear in Table B.

The GeoCode field contains similar data. That is precisely why I'm trying
to join on those two fields.



NetworkTrade said:
does the [District] field not appear in table B?

it seems like it is embedded or implied by the GeoCode field i.e.

West Coast | CA | Bay Area Rapid Transit

It is not clear what the divider line is.....

--
NTC


darson4eva said:
Good afternoon,

I have tried my best on this but am unable to figure it out.

---------------------------------------------------------------------
Scenario:

Table A contains only one column titled [District].
Let's assume that there are three rows here:
1) Bay Area Rapid Transit
2) San Diego Zoo
3) San Mateo


Table B contains other data with these fields:
[GeoCode] , [CountOfHourlyEmployees], [CountOfSalaryEmployees].
Let's assume that there are three rows here:
1) West Coast | CA | Bay Area Rapid Transit , 154, 205
2) CA | Southern | Bay Area Rapid Transit , 105, 206
3) Southwest | CA | San Diego Zoo, 358, 625

I am attempting to create a query that will return all values from Table B
that contain the string from Table A. This should be grouped by the value in
Table A

For example, I would expect my output to be:
Bay Area Rapid Transit, West Coast | CA | Bay Area Rapid Transit , 154, 205
Bay Area Rapid Transit, CA | Southern | Bay Area Rapid Transit , 105, 206
San Diego Zoo, Southwest | CA | San Diego Zoo, 358, 625

--------------------------------------------------------------------------------------------

This is the query that I'm using:
SELECT A.District,
B.GeoCode,
B.CountOfHourlyEmployees,
B.CountOfSalaryEmployees
FROM A, B
WHERE InStr(B.GeoCode, A.District)<>0;

In my results, I'm getting some of the data but not everything. I noticed
that if a value appears more than once in Table B, only the first record is
picked up.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
D

darson4eva

Good point.

In Oracle, I would do something like this:
SELECT A.District, B.GeoCode, B.CountOfHourlyEmployees,
B.CountOfSalaryEmployees
FROM A, B
WHERE InStr( B.GeoCode, (SELECT District FROM A) )<>0;

The idea would be to make the InStr function operate on a RANGE of values
instead of just one.
Access does not accept this syntax though.


NetworkTrade said:
well to join two tables there has to be a common cross reference field in
both....

if this is a new database then set it up so that when GeoCode is selected -
the approrpriate District Code is auto entered into the table - so you will
have a cross referencing field...

if this is an existing database then it is more challenging; if that
GeoCode field has a consistent structure then you can create a cross
reference field via a query. You might be able to filter it out of the
GeoCode and add as a new field.... or possibly using an IIF statement ....

although if only one field is in question there isn't a compelling reason to
do a join - just create the new field.......but if Table A has other data
fields then it would make more sense to need a join.......
--
NTC


darson4eva said:
You are correct.
This field does not appear in Table B.

The GeoCode field contains similar data. That is precisely why I'm trying
to join on those two fields.



NetworkTrade said:
does the [District] field not appear in table B?

it seems like it is embedded or implied by the GeoCode field i.e.

West Coast | CA | Bay Area Rapid Transit

It is not clear what the divider line is.....

--
NTC


:

Good afternoon,

I have tried my best on this but am unable to figure it out.

---------------------------------------------------------------------
Scenario:

Table A contains only one column titled [District].
Let's assume that there are three rows here:
1) Bay Area Rapid Transit
2) San Diego Zoo
3) San Mateo


Table B contains other data with these fields:
[GeoCode] , [CountOfHourlyEmployees], [CountOfSalaryEmployees].
Let's assume that there are three rows here:
1) West Coast | CA | Bay Area Rapid Transit , 154, 205
2) CA | Southern | Bay Area Rapid Transit , 105, 206
3) Southwest | CA | San Diego Zoo, 358, 625

I am attempting to create a query that will return all values from Table B
that contain the string from Table A. This should be grouped by the value in
Table A

For example, I would expect my output to be:
Bay Area Rapid Transit, West Coast | CA | Bay Area Rapid Transit , 154, 205
Bay Area Rapid Transit, CA | Southern | Bay Area Rapid Transit , 105, 206
San Diego Zoo, Southwest | CA | San Diego Zoo, 358, 625

--------------------------------------------------------------------------------------------

This is the query that I'm using:
SELECT A.District,
B.GeoCode,
B.CountOfHourlyEmployees,
B.CountOfSalaryEmployees
FROM A, B
WHERE InStr(B.GeoCode, A.District)<>0;

In my results, I'm getting some of the data but not everything. I noticed
that if a value appears more than once in Table B, only the first record is
picked up.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Top