Trouble with Tables, Relationships & Forms

[MsDYJ]

I'm building a database to manage projects with 5 tables. There is a main project table and the other 4 tables are all parts of the project (stake holder, resources, materials, results.)

My form is set up in a way where there is the main project entry form and it has a tab control that contains subforms that relate to the other tables. The first tab contains the subform "Stakeholders"

The problem is that I have only 2 projects, yet my main form is showing 6. In the first project there are 2 stake holders and in the second there are 4, which totals to 6. And what is happening when I skip to the next project it shows the 1st project twice and the 2nd project four times. For some reason I think that some issue with the tables and relationships is causing the database to count the stakeholders as projects.

I'm completely lost on this and would greatly appreciate any advice.

Many thanks in advance,
MsDYJ

 

[David R]

Is the data source for your main form the project table, or a query with both the project table and some other table(s) linked to it?

At a guess, I'd say you've done the latter on accident. Subforms generally take care of that associated data, without causing Access to duplicate the data (essentially, it's creating a row in the query for each <project table entry> * <stakeholder table entry>).

 

[MsDYJ]

David,

All of my fields on the main form are being pulled from the project table. I assumed that Access wouldn't duplicate the data because like you said, the subforms usually take care of it. I've done a database kind of like this one before which is why I am confused about the issue I am having.

If you have any other suggestions I would greatly appreciate it.

Thanks!!
Diana

 

[Access Hero]

Duplicate thread reported: http://www.access-programmers.co.uk/forums/showthread.php?t=176927

 

[David R]

Fields, yes, but is the subform table listed in the data source at all?

Post these two things, if you can:
Main Form > Properties > Data > Record Source
and then the SQL for that recordsource (..., View > SQL View), if it's not just the name of a single table.

 

[MsDYJ]

This is what was in the Recordsource from the property sheet:
SELECT Project.*, [Key Stakeholders].Stakeholder_ID FROM Project INNER JOIN [Key Stakeholders] ON Project.Proj_ID=[Key Stakeholders].Proj_ID;

Let me know if this isn't what you were looking for.

Many thanks,
Diana

 

[David R]

Yep; your main form is looking at both the table Project and the table Key Stakeholders.

Go to the Data tab again, click on the [...] again, and remove [Key Stakeholders] from the grey area at the top. Save the record source, and it should work as you expect.

If you put both tables in there to achieve some sort of funky sorting or something, this may break that, but that's a separate problem.