zero to many relation?

[broj1]

hello,

how can i donne zero to many relation among two tables in access?
is it even possible?

 

[dhookom]

Can you explain why you think you might need this?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[broj1]

hi
thanks for taking interest.

It is for building up containers -- i have these tables:

tblProject
ProjectID
somedata1
somedata2
...

tblConteiner
ConteinerID
somedata1
somedata2
...

tblRoof
tblBottom
tblWatter
tblElectric
.....all these last 4 tables are part of every container or they can be made as spare parts saparatly

How should work:
*- there can be NO,1 or many containers per project
- each container is made from all parts: tblRoof, tblBottom, tblWatter, tblElectric....(all 1x)
or
*- there is project with no containers: just spare parts - tblRoof, tblBottom, tblWatter, tblElectric....(at least one at a time or all) --

And here is the problem --- how to put this together to work as:
project<->conteiner (one or many)<->spare parts OR just
project <-> spare parts
THIS IS WHAY I THINK THE ZERO-TO-MANY IS NEED IT!??

In a form i am planing to link it something like this:
main form: project -- all others (container spare parts) in subform -
it should link then somehow with Project and somehow with container ?

thanks for any of your help you can give

 

[dhookom]

You should be able to create the relationship but not enforce referential integrity. This should allow containers without being part of a project.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[broj1]

how should i deffine the links between the tables?
tbl
project<->containers<->spare_parts
or
project<->spare_parts
project<->containers
containers<->spare_parts

thx

 

[dhookom]

We don't know much about your parts tables and their fields. I would probably try to avoid separate tables for each type of part.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[broj1]

ok.
Let us say then, i have just 3 tables

tblProject, tblContainer, tblSpare_parts

how to link this as described above?

couse: tblSpare_parts is dependent of tblContainer and tblContainer is dependent of tblProject,
also,
tblSpare_parts is dependent of tblProject if there is no new container just spare parts!
that is the way i see it, just don't know how to put this together

thanks for your help

 

[dhookom]

I would consider placing both a ProjectID and ContainerID field in the spare parts table. tblContainer would have a ProjectID. These "foreign" key fields could be blank.

The other possible solution would be to create junction tables that would contain the relationships.

tblProjectContainers
======================
ProjectID
ContainerID

tblContainerSpartParts
======================
ContainerID
SparePartID

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[broj1]

I would consider placing both a ProjectID and ContainerID field in the spare parts table. tblContainer would have a ProjectID. These "foreign" key fields could be blank.

no it' can't be blank.

have main form. On the main form i have
1. Listbox (Com_list) with all containers for that project-linked with projectID
2. tabs - in each tab i have subform for spare parts (in one for floor,, electrics,, roof... and in one for container )
all subforms are linked as masterlink: projectID, Con_list(containerID)
slavelink: projectID, Con_list
but this is not posible, couse i don't need containerID when i have just a project with spare parts.
(to mention, i can't change masterlink and slavelink from one parameter to two or vice versa in vba)

so, when i enter new record just for spare parts it says it is linked to ContainerID

where is the problem?
thx

 

[dhookom]

Default the ContainerID in the spare parts table to 0. Make a union query of the Com_List listbox so that you can add a 0 value.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[Darrylles]

Hi broj,

There is no such thing as a 'zero' to many relationship.

There is also no such thing as a '1 to many' either. '1 to many' means:

0,1 or more records related to 0 or 1 record.

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.