Relations not getting right

[HV_L]

Hi,
I'm trying to create a db to write labour hours on certain projects.
Those project belong to a client.
Labour hours are hired men who need to write the hours spend on the project doing specific work
All this is defined in the tables and when I look in the table client, a + sign represent the project they own, which is correct.
Now I thought project would have a + sign too, relating to the Work table, where the hours are stored. But this is not working.
All my hair is almost gone.. it's driving me mad!!!

Who wants to look at the attached mdb and help me out whatever I'm missing..
Thanks appreciate the help.
Any suggestions are welcome of course

 

[RuralGuy]

Somehow you have managed to get the Werk table filled with two records that have *no* ProjectID value even though RI is turned on.

 

[HV_L]

So how can I repair this and get those two tables connected?

 

[RuralGuy]

Open the Werk table and put valid ProjectID values in that field!

 

[HV_L]

So the design is oke?
Have you looked at the Werk Form?
Still have a question there.. When I enter the projectname, the projectid should get filled. How do I accomplish that?

This the reason I think there is no link set.. projectid is not filled

 

[RuralGuy]

When I enter the projectname, the projectid should get filled. How do I accomplish that?

Where would you put the ProjectID on that form? You are not even pulling the ProjectID in the RowSource of that ComboBox.

 

[HV_L]

Projectid field is there, but invisible, no need for a user to see this.
But because there is a relation between projectname and projectid, I thought I could show the projectname, and autofill the projectid field, and get werkid and projectid connected.
But I'm struggling with the autofill part..

 

[RuralGuy]

Why do you have the ProjectID and ProjectName fields in the Werk table? Only the ProjectID is necessary.

 

[HV_L]

Hmm oke, did that for ease of formdesign..
So the user can select the projectname to write hours on.

You say that I don't need it, oke I get that, but how do I let the user choose the projectname on the form then?
Have to go for a few hours, I'll check back later.
Thanks so far RuralGuy! Appreciate it

 

[RuralGuy]

Let the ComboBox wizard create the cbo for you bound to the ProjectID field. Your form needs to have a query as a RecordSource instead of a table.

 

[HV_L]

Got it !
Thanks a lot m8 !!

 

[HV_L]

Darn.. I removed the projectname field in Werk table and again all is messed up..
Can someone bake me an example query or form?

 

[RuralGuy]

Did you get the cbo wizard to make a cbo bound to the ProjectID field? What do you mean by "all messed up"?

 

[HV_L]

Yes I did.
First I created a Query that selected the projectname and projectid from table projecten.
Then told the wizard to use the projectname field from the query
I see all projects on the form, but data is not stored or connected with the project.. when I look direct in the table
AAAAAAAAAAAAARRRRRRRRRRRRRRRRGGGGGGGGGG

 

[RuralGuy]

Why do you want to store the Projectname instead of the ProjectID? Is your cbo bound to the right field?

 

[HV_L]

I dont need to store the projectname again, it's already in the projecten table.
I just want to select the name on the form which is much easier for user to choose then a number.
And I do see the names in the cb on the form, but in the projecten table there is no connection amongst the two items werkid and projectid like there is on opdrachtgever-projecten
I just don't get it what to do anymore or how to proceed,, tried so many things I'm almost at the point of giving up. which I don't do very quickly normally spoken.
Have been looking for a db example for this but thats ísnt getting me anywhere too.

 

[RuralGuy]

Your query should join the Werk table and the Projecten table on the ProjectID field. Then you can see any ProjectName in the query by just putting in a ProjectID in the ProjectID field. You will only see the ProjectID in the Werk table.

 

[HV_L]

Sorry but dont get it..
Made this query like you said:
SELECT Projecten.Projectnaam, Werk.projectid
FROM Projecten INNER JOIN Werk ON Projecten.projectid = Werk.Projectid;

No output, while there are projects..

 

[RuralGuy]

Use a Left Join instead of the Inner Join and see what you get.

 

[HV_L]

SELECT Projecten.Projectnaam, Werk.projectid
FROM Projecten LEFT JOIN Werk ON Projecten.projectid = Werk.Projectid;

Gives projectnames, no projectid

Be back in an hour