Multiple instances of same records / normalized data & many-to-many relationship (1 Viewer)

[Cosmos75]

I have a database with 10 tables with the following relationships.

tbl_projects has a one-to-many relationship with tbl_facility

tbl_facility has a one-to-many relationship with tbl_facility task

tbl_facility task has a one-to-many relationship with tbl_Facility

tbl_facility task info has a many-to-one relationship with tbl_people

tbl_facility task info has a one-to-many relationship with tbl_task notes

tbl_facility task info has a one-to-many relationship with tbl_hour/cost

tbl_hours/cost task info has a many-to-one relationship with tbl_week ending

[tbl_facility task and tbl_people have a many-to-many relationship through junction table tbl_facility task info

1) tbl_project
- Contains Project Name

2) tbl_facility
- Facility ID
- Facility Name

3) tbl_facility task
- Lookup to tbl_task type
- Start date
- Target date
- Finish date

4) tbl_task type
- Contains various task types to choose from

5) tbl_facility task info
- Sub Task Description
- Start Date
- Lookup to Status Table
- Status Comment

6) tbl_status
- Contains various status to choose from

7) tbl_people
- First Name
- Last Name

8) tbl_task notes
- Task note
- Date of Note

9) tbl_hour/cost
- Hours Spent
- Cost

10) tbl_week ending
- Week Ending

I have not listed out all the fields in the table, just what I think is enough for you to get an idea of what I am trying to accomplish.

The thing is, when I run a query using People’s name, Facility Task Info (Basically sub-tasks), Facility Task, Facility Name and Project Name I get MANY copies of the same records?!

I just want to show what sub-tasks someone is working on and their status in the query. Anyone know what is going on?

THANKS

p.s. I've seen people post relationships here visually, how do I do that??

[This message has been edited by Cosmos75 (edited 05-13-2002).]

 

[llkhoutx]

Use a left join.

 

[The_Doc_Man]

If you can write a query that tells you everything you want but it gives you too many copies of the same exact record, convert it to an aggregate query, then have it compute something you don't care about and throw away that result. Here's how you do that...

Open your query. The iconic toolbar should change to the query toolbar. Somewhere in the middle of that bar is the button that looks like the Greek letter Sigma (or, if you aren't up on Greek letters, a sideways M). Click that button.

The query grid gains a row. For every field that you want to be unique, click the option in that row to be "Group By." Add one of the field names a second time and change the option to "Count." Then don't check the Show box. Or, heck, leave it visible but just ignore it. Your call.

That should make the resultant datasheet display show only unique records. If it doesn't, one of your fields isn't a GroupBy but should be.

 

[Cosmos75]

Doc,

Got it! It worked. One thing though. I have a field that is memo and though I now only have unique records, the memo field now only has a square in it?! Why is that? Everything else works.

THANKS!

 

[Cosmos75]

llkhoutx - What is a left join? That doesn't mean having to change any of my current relationships, does it?

Looked at the help file and found this syntax:

FROM table1 [ LEFT | RIGHT ] JOIN table2
ON table1.field1 compopr table2.field2

table1, table2: The names of the tables from which records are combined.

field1, field2: The names of the fields that are joined. The fields must be of the same data type and contain the same kind of data, but they do not need to have the same name.

compopr: Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>."

Use a LEFT JOIN operation to create a left outer join. Left outer joins include all of the records from the first (left) of two tables, even if there are no matching values for records in the second (right) table.

The thing is, the people table is "joined" to the Project/Facility/Facilty Task tables by the Facility Task Info table.

Is a left join still possible? If so, I am afraid that it's over my head how to do it...