one to many with a '/' delimiter

[Alex Pavluck]

I have a table and a reference table that looks like the following:

Main table
ORGS
PROJECT CONTACT
XSA/DDF/WWS long term impact study Jay
Peterson
OSI short term outcome
Tom Jones
DDF something
else Mary Smith


Reference table
ORG FULLORG
OSI Organization1
DDF Organization2
WWS Organization3
XSA Organization4


I want to link them but I want records from the main table ORGS to
link separately with each match of ORG from the reference table. So,
in this example the result would have the "long term impact study"
listed 3 times because there are 3 organizations. The one thing that
I have going for me is that the '/' is always the delimiter between
organizations.

 

[Alex Pavluck]

Main table
ORGS PROJECT CONTACT
XSA/DDF/WWS long term impact study Jay Peterson
OSI short term outcome Tom Jones
DDF something else Mary Smith

 

[Ken Sheridan]

You need to decompose the Main Table. At present it is not in First Normal
Form as it includes multiple values at a column position in a row. First
Normal Form (1NF) is defined as follows:

'First Normal Form: A relvar is in 1NF if and only if, in every legal value
of that relvar, every tuple contains exactly one value for each attribute.'

This is in the formal language of the relational model. A relvar (relation
variable) equates roughly to a table, a tuple to a row, and an attribute to a
column.

What you need is a table Contacts with ContactID, FirstName. LastName
columns etc, i.e. columns which represent each attribute type of the contacts
entity type. This is related in a many-to-many relationship to the table,
Organisations say, by means of a table, OrgProjContacts say, with columns
ContactID, Org, Project.

You also need a Projects table, with column Project. This is referenced by
the Project column in OrgProjContacts

The primary key of OrgProjContacts might be all three columns, which allows
for an individual being a contact for one or more organisations and for one
or more projects, whether in relation to the same organization or separate
organisations. It also allows for an organisation having more than one
contact and/or the same or different contacts in relation to different
projects. If a contact can only relate to one project (as you sample data
suggests, but does not necessarily confirm) then ContactID and Project also
constitute a candidate key.

As you see, what you have here is a 3-way (ternary) relationship which is
resolved into three one-to-many relationships by the OrgProjContacts table.
This is invariably how many-to-many relationship types are represented in a
relational database.

Joining the tables is now straightforward.

Ken Sheridan
Stafford, England

 

[Albert D. Kallal]

The most simple solution this case is simply to send that data out to a
normalized table.

create a table called tblOrgList

ID (autonumber, pk...not really needed)
main_id long number (this is the link to main table)
Org text, org abbreviation..

You then be able with ease to build a report that displays the data from the
reference table
(and it means the report will also display the contact information.

I'm going to assume that the main table also has an auto number primary key
ID in it.

So we simply need a little process that sends out that project data to
another related table. Once this is done, then it's simple a matter to build
a relational join and send the results of to a report, or export...or
whatever..

The code to do this will look like:

Dim rstMain As DAO.Recordset
Dim rstChild As DAO.Recordset
Dim strSql As String
Dim vOrgs As Variant
Dim vOrg As Variant

strSql = "select * from tblMain where Org is not null"
Set rstMain = CurrentDb.OpenRecordset(strSql)
Set rstChild = CurrentDb.OpenRecordset("tbOrgList")

Do While rstMain.EOF = False
vOrgs = Split(rstMain!Org, "/")
For Each vOrg In vOrgs
rstChild.AddNew
rstChild!Main_id = rstMain!ID
rstChild!Org = vOrg
rstChild.Update
Next
rstMain.MoveNext
Loop
rstMain.Close
rstChild.Close
MsgBox "done"


Once the code is run, then you just fire up the query builder and drop in
the main table, then drop in the above new child table, and then drop in the
org table...draw the joins lines...and you done...

remember, the join lines have to be left joins....

And, since the above is code that can modify data...then you need to make a
copy of your data BEFORE you run such types of code else you risk damage to
your data....