Create relationship between multivalued fields/tables

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.
ImmergenceEE1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ralmadaCommented:
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
Mark WillsTopic AdvisorCommented:

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
ImmergenceEE1Author Commented:
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

ImmergenceEE1Author Commented:
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
Mark WillsTopic AdvisorCommented:
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
Mark WillsTopic AdvisorCommented:
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
ImmergenceEE1Author Commented:
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
Mark WillsTopic AdvisorCommented:
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
Mark WillsTopic AdvisorCommented:
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
ImmergenceEE1Author Commented:
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
ralmadaCommented:
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
Mark WillsTopic AdvisorCommented:
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
Mark WillsTopic AdvisorCommented:
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
ImmergenceEE1Author Commented:
No info needs to be shown except the contract number, I think.
0
ImmergenceEE1Author Commented:
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
Mark WillsTopic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ImmergenceEE1Author Commented:
The values I'm trying to return would be displayed in the Records Portal Form under the Contract Number(s) control.
0
Mark WillsTopic AdvisorCommented:
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
ImmergenceEE1Author Commented:
Yes I would appreciate that.  :~)
0
mbizupCommented:
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
ImmergenceEE1Author Commented:
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
mbizupCommented:
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
ImmergenceEE1Author Commented:
I wouldn't disagree and I would do that if I knew how to set up the relationship :~)
0
Leigh PurvisDatabase DeveloperCommented:
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
Leigh PurvisDatabase DeveloperCommented:
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
ImmergenceEE1Author Commented:
Exactly.  It was the requery VB code that actually did the magic.  Without it, only the first record would be returned.  :~D
0
ImmergenceEE1Author Commented:
Sure you may do that.
0
ImmergenceEE1Author Commented:
It's amazing it was as simple as that.  Thank you so much for your help.
0
Mark WillsTopic AdvisorCommented:
Thank you all very much, a little unexpected, and is greatly appreciated :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.