Solved

Create relationship between multivalued fields/tables

Posted on 2009-07-01
33
1,271 Views
Last Modified: 2013-11-28
I have a Contracts table and a Cemetery Spaces table.

The contracts have a multivalued field that can associate it with multiple records in the Cemetery Spaces table.

Also, the Cemetery Spaces table's records may be associated with more than one contract, for example if someone purchases a lot but they die and then a family member purchases the memorial.

I would like to be able to select the applicable Cemetery Spaces in the multivalued field from the Contract form and have those multiple contract numbers appear in single records when I flip through records in the Cemetery Spaces form.

I'm having difficulty combining the Contract numbers when there is more than one contract associated with a Cemetery Space.

Example:  

please see file: http://immergencehealth.com/downloads/Cemetery.zip

I have two contracts, "Contract 1001"  associated with spaces A, B, C, and D, and the "Contract 1002" associated only with spaces A, B, and C.

When I flip through the individual records A, B, C, and D, I need for it to display that space A is associated with Contracts 1001, 1002--same with B, and C--and space D is associated with Contract 1001 only.

Thanks.
0
Comment
Question by:ImmergenceEE1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 10
  • 2
  • +2
33 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24763975
Don't have access 2007 here. So cannot open your file. However, have a look at the query below. It will display what you're looking for: (Just replace the column and table names with the one you're using).

select 	b.SpaceName,
         a.ContractID,
	a.ContractName
from Contractstable a
inner join CementerySpacetable b on a.ContractID = b.ContractID

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24764685

Not sure what you mean by "multivalued field" - is that the [associated spaces] with the delimited values "1001;1002;1003;1004"

That contract table seems very unstructured...

Normally you would set up the contract master, the spaces master and have a relationship table joining them.

However, without that, you are going to have to either create a function to delimit the column and return the rows matching, or, use something like :

SELECT *
FROM Contract, Spaces
where instr( ';'& [associated spaces]& ';', ';'&[space id]&';') > 0
0
 

Author Comment

by:ImmergenceEE1
ID: 24764914
Does one of these possible solutions return "1001, 1002" for spaces A, B, C, and only "1001" for space D?  That seems to be the heart of what I'm trying to do, is not only build the relationship, but combine the associated contracts per each space record.

When I build queries and relationships with the multivalued fields, for example, it would return space A twice (once for every associated contract), B twice, C twice, and D once, along with all the spaces not under contract, and then that screws up the ability to look at the records at all (when I click the form, it asks me for the space ID instead of bringing up all spaces).
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:ImmergenceEE1
ID: 24764944
Just for clarification, I keep saying spaces A, B, C, D when they are actually spaces 1001, 1002, 1003, 1004--the example contracts have the same values of 1001, 1002, and I didn't want to confuse.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24765097
Yes, very simply add in the extra qualifier into the WHERE clause. In reality, you would substitute the value of [space id] with your selected value on the form itself.

In the meantime, simply try this in a (sql) query window:

SELECT *
FROM Contract, Spaces
where instr( ';'& [associated spaces]& ';', ';'&[space id]&';') > 0
AND [space id] = 'D'  

(substitue your 'D' alias for whatever value e.g. 1001)

what you will most likely end up with is something more like :

me.list.rowsource = "SELECT [Contract ID],[Contractee Name],[Space ID] FROM Contract, Spaces where instr( ';'& [associated spaces]& ';', ';'&Forms!frmSpaces!SpaceID&';') > 0;"
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24765151
BTW that list.rowsource is not accurate, just for example...

Or, create a "query" establishing that relationship (as per the first posting) and then simply select from that query...
0
 

Author Comment

by:ImmergenceEE1
ID: 24765216
Ok just looking at the first part:

SELECT *
FROM Contract, Spaces
WHERE instr(';' & [associated spaces] & ';',';' & [space id] & ';')>0 And [space id]='1001';

returns error:  "The multi-valued field '[associated spaces]' can not be used in a WHERE or HAVING clause.

Then I tried:

SELECT *
FROM Contract, Spaces
WHERE instr(';' & [associated spaces].value & ';',';' & [space id] & ';')>0 And [space id]='1001';

and this created an ambiguous outer join.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24765590
Sorry about that - keep forgetting 2007....

Technically speaking, a multivalued field really is a "managed" many-to-many relationship. To identify an element / individual member of  a multivalued field, use the .value qualifier as you started to do above.


SELECT *
FROM Contract, Spaces
WHERE [associated spaces].value = [space id]  
AND [space id]='1001';

or more simply...

SELECT *
FROM Contract
WHERE [associated spaces].value = '1001';


0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24765635
You can also do a join...

SELECT *
FROM [Contact]
INNER JOIN Spaces ON [Space id] = [Associated Spaces].Value
WHERE [space id] = '1001';


The above should show you in the query designer underneath the [associated spaces] field, the additional .value field.
0
 

Author Comment

by:ImmergenceEE1
ID: 24765831
Maybe I'm just dense but these just seem like they're not quite hitting the mark.

All these seem to return multiple unique values, so if space 1001 had 2 associated contracts, the query would return 2 contracts as their unique records--but then how is the record in the Record Portal form to know to associate space 1001 with contracts 1001 and 1002?  It can't.

I need to open the record portal and have a field or subform or something that returns "1001, 1002" for space "1001," as both contracts 1001 and 1002 are associated with space 1001.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24766139
To combine multiple rows into a single column you might consider creating a function then.
Have a look at this solution. Use the fConcatenateRecords function
http://www.experts-exchange.com/Database/Miscellaneous/Q_24320625.html?sfQueryTermInfo=1+access+function+ralmada+row 
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24766270
A multivalued column in Access 2007 is a very special beast.

In reality it is taking care of the individual values for you.

Your challenge is how to get the [space id] which then equals one or many of the [associated spaces].value

e.g.

contract id = 1001 and has [associated spaces] = 1001,1002,1003,1004
contract id = 1002 and has [associated spaces] = 1003,1004

that effectively relates to and Access "automatically" manages

contract id = 1001 and has [associated spaces].value = 1001
contract id = 1001 and has [associated spaces].value = 1002
contract id = 1001 and has [associated spaces].value = 1003
contract id = 1001 and has [associated spaces].value = 1004
contract id = 1002 and has [associated spaces].value = 1003
contract id = 1002 and has [associated spaces].value = 1004

so, if you were to select

select [contract id]
from contract
where [associated spaces].value = 1003

you would get :

contract id = 1001 and has [associated spaces].value = 1003
contract id = 1002 and has [associated spaces].value = 1003

So, what you need to do (and I do not have 2007 installed just now) is to grab the [spaces id] entry to use in the "where" clause with [associated spaces].value

Have a look at :  http://office.microsoft.com/en-us/access/HA101492971033.aspx
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24769291
ummmm.... Just how do you intend to display the contracts ? Maybe a list box ? What information do you need to show from the contract ?
0
 

Author Comment

by:ImmergenceEE1
ID: 24769450
No info needs to be shown except the contract number, I think.
0
 

Author Comment

by:ImmergenceEE1
ID: 24769465
It's tricky to me, a novice at this, because there is the Contracts.Contract No field and it seems like I'm trying to make something that is like an "X" in that Associated Spaces is a multivalued field corresponding with single value records and I could also have a Contracts multivalued field to correspond with individual contract records.  

I'm just confused and do not know how to do what I'm trying to do, which is, again, display all associated contracts with their respective spaces and possibly have it work the other way around and display associated spaces with the respective contracts.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 400 total points
ID: 24769480
Did you read that link ? Did that help at all ?

You can make a list box on your form and have it populated with a list of contract numbers

me.list.rowsource = "SELECT [Contract ID] FROM Contract, Spaces where [associated spaces].value = Forms!frmSpaces!SpaceID);"

where Forms!frmSpaces!SpaceID is the object that holds the [spaced id] value.

Have you got a screen layout in mind - even if just mocked up ?
0
 

Author Comment

by:ImmergenceEE1
ID: 24769483
The values I'm trying to return would be displayed in the Records Portal Form under the Contract Number(s) control.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24769496
unfortunately I do not have 2007 on this machine, so cannot see your form.

I will see if I can get some more help for you..
0
 

Author Comment

by:ImmergenceEE1
ID: 24774964
Yes I would appreciate that.  :~)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24782297
ImmergenceEE1,

I'm looking at your recordportal form...

Your ContractNumbers combo box is bound to a field (ContractNumbers) which doesn't exist in the recordsource of the form. That is why you are seeing the #Name? error in the combo box (and the green triangle in the design view of the form).

Is this what you are trying to resolve in this question? Try to break the question down into samller chunks, and be as specific as possible regarding what the Experts should be looking for in your database.

I've asked for additional help, but it may help if you answer the following to help others find the right parts of your database to look at:

- What do you want to see displayed in the combo box (give us an example)?
- What field/ table do these values for the options come from?
- What field/table should selections be written to (this will determine how to set the control source of the combo)?

0
 

Author Comment

by:ImmergenceEE1
ID: 24782310
The ControlSource for ContractNumbers was deleted and is now null.  I know this.

To answer your questions:

If Contracts 1001 and 1002 both have Spaces 1001, 1002, 1003 selected per the Associated Spaces multi-valued field, the Record Portal form should display 1001, 1002 in the Contract Number(s) field.

The only tables with related data are Contracts and Space Details.

If data is written to a field as opposed to just decompiled and recompiled in the Contract Number(s) field for Record Portal, then it would be added to Space Details table as ContractNumbers.

Does this help?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24782370
I think a subform would be better for this than a combo box, but let's see if anyone else responds who is more familiar with Access 2007's multivalued fields than I am.
0
 

Author Comment

by:ImmergenceEE1
ID: 24782379
I wouldn't disagree and I would do that if I knew how to set up the relationship :~)
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 100 total points
ID: 24796670
This is for display only purposes yes?
i.e. you want to display the related contracts from a given Spaces form?
If editing is what you're after then, IMO, you're going to have to look at implementing a proper link table rather than relying on MVFs.
The query is essentially as Mark suggested - it's just a question of filtering and requerying appropriately.
Demonstrated in your example with a listbox stuck in place (for display only) instead of the Customers field.
Oh wow. EE's refusal to accept an ACCDB file within a zip archive is really annoying.
I suppose an upload is in order then. :-s
Download
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24799606
No probs, but as I mentioned, Mark had also all but given you all the required information. The implementation of filtering and requerying isn't even 2007 specific.
0
 

Author Comment

by:ImmergenceEE1
ID: 24799613
Exactly.  It was the requery VB code that actually did the magic.  Without it, only the first record would be returned.  :~D
0
 

Author Comment

by:ImmergenceEE1
ID: 24800041
Sure you may do that.
0
 

Author Closing Comment

by:ImmergenceEE1
ID: 31599036
It's amazing it was as simple as that.  Thank you so much for your help.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24800274
Thank you all very much, a little unexpected, and is greatly appreciated :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question