how to link this tables so it will work?

[pinstripe]

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 (- but there can be spare parts), 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
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 ?
(maybe i need somekind of junction table, but just don't know how, where, how to link it)

I have llost many hours with this (internet search, bruteforce ...) but just can't find the solution for this problem to work.

thanks for any of your help you can give

best to all,

 

[pinstripe]

anyone? please...maybe just a suggestion?
thx

 

[supercool]

maybe u could explain bit more into detail

 

[pinstripe]

maybe u could explain bit more into detail

i have problem with makeing the relations between tables!
how to link if if the result should be:
1)
a tblproject:
a tblcontainer:
a spare parts tables tblRoof, tblelectrics, tblFloor (these are components from which the container is made of)
2)
a tblproject:
a spare parts tables tblRoof, tblelectrics, tblFloor (these are components from which the container is made of)

1) is for project where we build new container or many of it (and for each new container we have to include all parts from spare parts )

2) is for project when the costumer ask us, just to make a new roof, or new floor...so, we have to make just some spare parts for them

so, project-if new container then link project-container-spare parts
project-if no container then link project-spare parts

how to gain this?

 

[Jurjen]

relations between tables

i have problem with makeing the relations between tables!
how to link if if the result should be:
1)
a tblproject:
a tblcontainer:
a spare parts tables tblRoof, tblelectrics, tblFloor (these are components from which the container is made of)
2)
a tblproject:
a spare parts tables tblRoof, tblelectrics, tblFloor (these are components from which the container is made of)

1) is for project where we build new container or many of it (and for each new container we have to include all parts from spare parts )

2) is for project when the costumer ask us, just to make a new roof, or new floor...so, we have to make just some spare parts for them

so, project-if new container then link project-container-spare parts
project-if no container then link project-spare parts

how to gain this?

From what i understand of your functional description there are 5 concepts:
Project, Container, Roof, Electrics, Floor

The question is whether Roof, Electrics and Floor are really separate entities. They all refer to spare parts and if the structure of their description is the same (e.g. product number, price) then we are dealing with just one entity Spare Parts. In that case another entity Spare Part Type would be required to distinguish them. Lets assume this is the case.

Now to establish our entity relationship model (ERM) we have to consider the following:

1 - a project can have 0 or more containers
2 - a container is part of 1 project
3 - a project can have 1 or more spare parts
4 - a spare part is part of 1 project
5 - a container can have 1 or more spare parts
6 - a spare part can be part of 0 or 1 container
7 - a spare part has a spare part type
8 - if a spare part is part of a container then its project should be the same as that of the container

To realize point 1 and 2 we need a mandatory reference from Container to Project (N:1 relationship).
To realize point 3 and 4 we need a mandatory reference from Spare Part to Project (N:1 relationship).
To realize point 5 and 6 we need an optional reference from Spare Part to Container (N:0 relationship)
To realize point 7 we need a mandatory reference from Spare Part to Spare Part Type (N:1 relationship)

Translated into tables:
PROJ (Project)
proj_id (long) = primary key
proj_name (text)

CONT (Container)
cont_id (long) = primary key
cont_code (text)
proj_id (long) = mandatory foreign key to PROJ

PART (Spare Part)
part_id (long) = primary key
part_name (text)
part_price (currency)
proj_id (long) = mandatory foreign key to PROJ
cont_id (long) = optional foreign key to CONT
spty_id (long) = mandatory foreign key to SPTY

SPTY (Spare Part Type)
spty_id (long) = primary key
spty_name (text)

OK, we are left with point 8. In a professional database system (like Progress or Oracle) this can be established with so called database triggers: program code that is executed whenever there is a mutation on a record.
In Ms Access you depend on application triggers (events) or validation expressions. I will leave that to you.
I hope that this was a usefull explanation. Success.

 

[pinstripe]

Hi Jurgen,
a have been off for few days ... for business reasons..and now as always i am in a hurry with this project.
i am very thankful for your generous reply and explanation. You did good.
I have tried your method but there is a problem. Still haven't figure it out what or where.

I made connections like you advised but i still get error when trying to make new project just for spare parts...is says it can't do this beacuse there is relation to containeID --so this means that new container ID should be generated. But in that case i DON'T NEED IT. ??
Maybe there is problem with my forms:

i 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 what to do to make this work or where do you think the problem is?
than you for your help