Solved

Create relationship between multivalued fields/tables

Posted on 2009-07-01
33
1,265 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
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

809 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