Selecting records from part of a field

[rlowden]

I have a table of organizations (tblPartners), each with a unique ID (PCode).

IN another table I have a list of projects (tblProjects) that these organizations are involved with (in various combinations), and a field in that table which lists the various PCodes of the participating organizations. These codes are kept in a text string, each separated by a semicolon. The field is named (ProjectPartners)

I want to place a button on a form based on tblPartners that will search tblProjects and return the subset of Project records that have the appropriate PCode embedded somewhere in that text string.

I can conceive of a big ugly programming statement that might accomplish this, but I am hoping that Access offers some easier solution. I note that in queries one can ask for a value embedded in a string, for example.

All advice much appreciated.

Bob

 

[Brianwarnock]

Have you looked at the Instr function
Returns a Variant (Long) specifying the position of the first occurrence of one string within another.

Brian

 

[datAdrenaline]

You are suffering from poor table design! ... you *should* have a junction table that is stuctured like this ...

tblProjectToPartners
--------------------
ProjectPartnerID (Autonumber, PK)
PCode (Numeric/Long, FK to tblPartners.PCode)
ProjectID (Numeric/Long, FK to tblProjects.ProjectID)

Then you can RELATE both tables in a one-to-many relationship to the junction table, then, you can query on the records that are related to each other ...

.....

Although I do not wish to provide you with a mechanism to further propogate a non-normalized table design, I understand that sometimes we are bound to table designs that are poor ... so ... in that case, you would want to search for a string pattern within a longer string, and delimit the string being searched and the string your searching for ...

WHERE ";" & tblProjects.ProjectPartners & ";" Like "*;YourSearchString;*"

Note ... for this to work reliably, you need to be aware of how spaces may appear between values in ProjectPartners ... I recommend the you don't use ANY spaces between the values (as my suggestion is for that scenario).

 

[georgedwilkinson]

The good solution that Access has is called normalization. Brent gives you the foundation.

Instr can also be used in your query criteria but beware if your table is large. And problems will abound as the table gets larger.

 

[rlowden]

To George and Brent,

Thanks for your reply and the good instruction about normalization.

So rather than trying to use the Instr function to overcome my problem, I have followed Brent's recommendation and set up a junction table. But please excuse my naivete, what's next?

Now that I have the junction table, how do I go about accomplishing my task, which is to place a button in one table (tblPartners) to bring up a subset of projects (tblProjects) with which that partner has involvement?

My thinking is that I should now create a subform based on the junction table, place it under the Projects form (frmProjects), and then use that to associate each project with the various partners involved in it? Is that a valid assumption?

And then I should create a button on tblPartners to open a query to search...what?...how?

Sorry for my inexperience here, but that's why I've wanted to connect with you who do this sort of thing. Thanks for your assistance.
Bob

 

[georgedwilkinson]

Yes, base a subform on the junction table. To do a search on the main using a criteria in the sub, you'll need to write some code to modify the main's record source.

Allen Browne gives you almost everything you need to know here: http://allenbrowne.com/ser-28.html

Yell back if you have questions about how to implement it.

 

[rlowden]

To George, Brent and all others helping with this.

OK, I think I have the general idea about using a junction table, and I think I have a general idea on how to write the selecting SQL statement, but the syntax keeps tripping me up. So, I will restate my goal and show you what I've written, in hopes that one of you can show me the error of my ways.

I want to click a button on a form (frmPartners) so that it opens another form (frmProjects) showing the projects the particular partner is involved with.

These forms are based on tables tblPartners and tblProjects, respectively.

I have set up a junction table between these two tables (tblPartnerToProject), whose PK is ProjectPartnerID and containing the two PKs from the other two tables (PCode for tblPartners, ProjectNumber for tblProjects).

This is the statement so far (which returns a syntax error at this point):

Dim strSQL As String
Dim stDocName As String

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
stDocName = "frmProjects"

strSQL = "SELECT DISTINCTROW ProjectNumber * FROM tblProjects " & _
"INNER JOIN tblPartnerToProject ON " & _
"tblProjects.ProjectNumber = tblPartnertoProject.ProjectNumber " & _
"WHERE tblPartnerToProject.PCode = Me.PCode"

DoCmd.OpenForm stDocName, , , strSQL

 

[georgedwilkinson]

Could be anything wrong with your sql that we wouldn't necessarily be able to tell but one things jumps out at me:

SELECT DISTINCTROW ProjectNumber *

Wouldn't your intention be to put a "." delimiter between ProjectNumber and "*"? Something like this:

SELECT DISTINCTROW ProjectNumber.*

You'll be able to tell a little better by using:

Debug.Print strSQL

before the OpenForm command and pressing <ctrl> g to view the SQL in the immediate window. Then copy the immediate window into a query and see if it runs.

The next badness you're going to run in to is the use of the word "DISTINCTROW". My guess is that the need to use that is because your rows aren't unique. How will Access know which of the many rows of a non-unique set of rows is the one you'll be editing?

 

[rlowden]

To George et al,

Well, I think I am getting closer. I no longer get a syntax error with the following:

strSQL = "SELECT DISTINCTROW ProjectNumber. " * " FROM tblProjects " & _
"INNER JOIN tblPartnerToProject ON " & _
"tblProjects.ProjectNumber = tblPartnerToProject.ProjectNumber " & _
"WHERE tblPartnerToProject.PCode = Me.PCode"

But I do get a Type Mismatch error. I have checked the various key fields to ensure that they are defined properly and are the same type, size, etc, so I'm not sure what that error means.

As for unique rows: my defining keys are generating unique values, so that should not be a problem.

But the real problem here is that I am dabbling with VB and SQL and have no training in these. So your patience and help for a newbie are very appreciated.

Bob