Related tables and entries

[EDUGRA]

Hello!



We have an MS Access application to control all projects related to our products and I would like to add new functionality. I’m not an expert, but I’ve been always curious and would like to learn, and was hoping I would be able to solve this with your help.

I would like to track all projects related to each product we have, so I created a new table for the Products and linked a relationship to the Projects table. When I go to the Product table and click on the +, I can see all Projects we’ve had related to that specific Product. Now I’m trying to build a form to add and search our products and add a tab with a subform with all projects related to that specific product, but I can make it populate. The product info is working properly, but I can not see all projects I have related to the product I’m searching for. I have this feature built for Employees, when I go to their Employee profile, I can see all projects each one of them has, and I’m trying to track how to populate it for hours, but no success.

Can anyone help me?



Thanks.

[June7]

What is nature of relationship - many to many?

How did you relate tables - are you using a multivalue field?

How did you accomplish this for employee/projects?

If you want to provide db for analysis, follow instructions at bottom of my post.

[orange]

Can you describe what a Project is in simple English? What is it that relates a Product or Products to a Project? Can you give us an example of a Project involving 1 or any Products?

[mike60smart]

Hi

You said "When I go to the Product table and click on the +" !!!!

You should NOT be using the + (Subdatasheet) to do anything at all in any of your tables.

The subdatasheet should be removed from all tables.

You should be using a Main Form/Subform layout to do Data Input for this process.

[EDUGRA]

I can't upload the db because of my company's policy, but here's what I have.

I had 3 tables: (1) Employees, (2) Projects and (3) Tasks. The Projects table is the main table that host all projects we have, then we have the Employees table to hook up all projects and tasks for an owner, and then the Task table for all tasks related to each project.

I added a new table (Products) table and added a new row to the Projects. Users will select which Products is related to each Project. What I want is one year from now have a history of all projects we've done for each Product. When I go to the Products table, I can see all the Project hooked up to each product, but I can't see them in the form.

What I'm trying is to see all the projects I have to a specific product when I have that product in the form. The 3rd screen show all the projects under that specific employee and that's pretty much what I'm trying to do for the Products.

Thank you!

[mike60smart]

Hi

Can a Project have many Products associated with it?

[EDUGRA]

We might have some projects for multiple products, although it's not that common, but I would like to allow user to associate a project to multiple products.

[mike60smart]

Ok, in that case, your design is wrong.

You need a table for Projects and then a related table where you can select Multiple Products.

[EDUGRA]

Ok, in that case, your design is wrong.

You need a table for Projects and then a related table where you can select Multiple Products.

Thank you. I'm new to this, though. I would I do that?

[June7]

Each project can have multiple products and each product can associate with multiple projects? That is a many-to-many relationship and requires 3 tables. Main form bound to Projects. Subform bound to a ProjectDetails table with a combobox to select product from Products table. Very basic.

If that is not the correct relationship (you say "see all the projects I have to a specific product") - if a project can associate with many products but a product can associate with only one project then save ProjectID into Products table.

[ssanfu]

I would fix the naming issues ......

Naming Conventions
What not to use in names



EVERY table has "ID" as the pk field name?? .......BAD
Spaces in field names - BAD
Special characters in field names (%, -) ....... BAD
"ADD" is a reserved word in Access .......BAD


I would name the tables
--------------------------
tblEmployees
tblTasks
tblInitiatives
tblCommonTasks (Note: no spaces in name)
tblProducts



Better Primary Key field names (All PK fields are Autonumber Type)
--------------------------
EmployeeID_PK
TaskID_PK
InitiativeID_PK
CommonTaskID_PK
ProductID_PK