A Wildcard on a Join??

  • Thread starter Thread starter mark909
  • Start date Start date
M

mark909

Im trying to work my way around some data that was setup previously to me
starting at the company.

Theres a Project ID column. This either individual Project IDs or multiple
IDs stored within it i.e.

1111AB
0123CD
0000BA 1111AB

I was wondering how I could get a join to work between 1111AB and 0000BA
1111AB?
 
Let's assume you're trying to join Table1 to Table2, Table1 always has
individual ProjectIDs in it, and Table2 sometimes has multiple IDs in the
field.

Create a query that joins the tables as though ProjectID is the same in each
then go into the SQL view.

Your SQL should contain something like the following in it:

FROM Table1 INNER JOIN Table2 ON Table1.ProjectID = Table2.ProjectID

Change that to

FROM Table1 INNER JOIN Table2 ON InStr(Table2.ProjectID, Table1.ProjectID) >
0

Note that you will not be able to return to Design view once you make this
change, so make sure your query is complete before you do it.
 
While that works for the example given, the correctness really depends on
what's in your data. Whatever you would do, it's a workaround for a
significant problem in your table design. A data field should always be an
atomic value, and NEVER a list of values. I strongly suggest you redesign
your data structure to replace the multiple values in a single field with
multiple records in a 1-Many child table. You will only continue to have
more problems with the current design. You can't enforce relationships, so
someone can easily enter invalid projectID's. You can't easily join to the
matching tables, as you've already found. Reporting will be difficult.

Relational db design has rules and standards for a reason. They work better.

In this case, for example, if the many id's in a field is to list the
project assignments for a Person, create a new PersonProjectAssignment table
with primary key of personID, projectID. So a person with many project
assignments would have many records in this table instead of many
projectID's in a single field of the Person table.
 
While I agree with Paul that a table redesign is preferable, if that's
impossible, you can also create your Join in the Where clause of your query:

SELECT <field list>
FROM Table1, Table2
WHERE Table2.ProjectID LIKE "*" & Table1.ProjectID & "*"

In this way, you will not lose your Design View. However, you cannot create
an Outer Join like this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Thanks alot guys!!!

I got it to work in the end.

Paul I totally agree with your comments but unfortunately thats the format i
was given the data in and i cant do anything about it
 
Back
Top