Solved

Create relationship between multivalued fields/tables

Posted on 2009-07-01
33
1,251 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
  • 13
  • 10
  • 2
  • +2
33 Comments
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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
Comment Utility

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
Comment Utility
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
 

Author Comment

by:ImmergenceEE1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
No info needs to be shown except the contract number, I think.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:ImmergenceEE1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yes I would appreciate that.  :~)
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Sure you may do that.
0
 

Author Closing Comment

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

Expert Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now