We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
7 days, 12 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
1-Many Relationship X 5
Posted on 2011-09-07
Thought I had a simple project, but as I've gotten into it ... just cant find a solution. Hopefully it is an easy fix. I would appreciate any help.
We have 6 Excel spreadsheets that I wanted to import into an Access database in order to create queries & reports. Each file has common IDs (project) with which to identify the data from table to table. Sounds simple enough ... While the initial table (list of projects - Table1) has a Primary Key (ID), the other tables all can list that ID any number of times so I dont have Primary Keys for those tables. Simplified ...
Table 1 (Project) - ID, Office, Fund
Table 2 (Proj Data) - ID, Priority, Percentage (of Proj) ... i.e. 100% = 1 record; 50%/50% = 2 records etc)
Table 3 (Ops) - ID, Category, Qty, Cost (each item would be a separate record under each project ... same for tables 4,5&6)
Table 4 (Labor) - ID, Employee(s), Hrs, Salary
Table 5 (Vehicles) - ID, License#(s), Cost
Table 6 (Cell phones) - ID, Cell#(s), Cost
I created all of the relationships (1-many from Table 1 to the others) and then created a query based on those relationships. There were many duplicates and not at all what we wanted.
Next, created individual queries adding one table at a time (i.e. Table 1+2, 1&2+3 etc) and this worked out somewhat better but still not quite right.
While this is correct based on what I asked (and there are NO exact duplicates), In this example (one project), there are 2 priorities (each with its % of project), 0 Ops, 2 Employees, 2 Vehicles and 0 Cell Phones. There are calculations in the queries which calculate vehicle, salary etc costs based on the % of each priority which works great ... but as you can see, each employee is listed twice under each priority as is each vehicle.
As I said, this is a very simple example ... there are some projects which have up to 5 priorities, 20-30 employees, many vehicles etc. But, I am very confident that if we can figure out how to format the queries to fix this particular example ... it will carry over to all.
Really appreciate any assistance that you can provide. Thanks.
We have 6 Excel spreadsheets that I wanted to import into an Access database in order to create queries & reports. Each file has common IDs (project) with which to identify the data from table to table. Sounds simple enough ... While the initial table (list of projects - Table1) has a Primary Key (ID), the other tables all can list that ID any number of times so I dont have Primary Keys for those tables. Simplified ...
Table 1 (Project) - ID, Office, Fund
Table 2 (Proj Data) - ID, Priority, Percentage (of Proj) ... i.e. 100% = 1 record; 50%/50% = 2 records etc)
Table 3 (Ops) - ID, Category, Qty, Cost (each item would be a separate record under each project ... same for tables 4,5&6)
Table 4 (Labor) - ID, Employee(s), Hrs, Salary
Table 5 (Vehicles) - ID, License#(s), Cost
Table 6 (Cell phones) - ID, Cell#(s), Cost
I created all of the relationships (1-many from Table 1 to the others) and then created a query based on those relationships. There were many duplicates and not at all what we wanted.
Next, created individual queries adding one table at a time (i.e. Table 1+2, 1&2+3 etc) and this worked out somewhat better but still not quite right.
While this is correct based on what I asked (and there are NO exact duplicates), In this example (one project), there are 2 priorities (each with its % of project), 0 Ops, 2 Employees, 2 Vehicles and 0 Cell Phones. There are calculations in the queries which calculate vehicle, salary etc costs based on the % of each priority which works great ... but as you can see, each employee is listed twice under each priority as is each vehicle.
As I said, this is a very simple example ... there are some projects which have up to 5 priorities, 20-30 employees, many vehicles etc. But, I am very confident that if we can figure out how to format the queries to fix this particular example ... it will carry over to all.
Really appreciate any assistance that you can provide. Thanks.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
- +2
8 Comments
Active today
It is normal for your case - you have "star" structure of tables. When you combine different "beams" in one query - you will get your result. In your case better will be to make report (or form) with subreports for every "beam" (table).
When you have "chain" structure (project-employee-cell#), your queries can work.
When you have "chain" structure (project-employee-cell#), your queries can work.
Active today
Try to use more descriptive names for your keys.
Naming them all "ID" is confusing
tblCustomers=CustomerID
tblOrders=OrderID
tblWatermellons=Watermello
...make sense...?
I think a sample spreadsheet and DB would have been easier to follow...
Naming them all "ID" is confusing
tblCustomers=CustomerID
tblOrders=OrderID
tblWatermellons=Watermello
...make sense...?
I think a sample spreadsheet and DB would have been easier to follow...
You are having grief with data normalization.
There's a good tutorial here
http://www.phlonx.com/resources/nf3/
Essentially, there should be no duplicates in Table1
Do yourself a favor and rename ID to ProjectID on all the tables
Now, in Tables 2-6 add a column.
Call them ProjectDataID, OpsID, LaborID, VehicleID, and PhonesID
Fill these columns with sequential numbers.
Now, you should be able to relate each table on ProjectID.
If you are not seeing what you want in your queries, you'll have to look at your data.
But, in a nutshell, if the data is good, you should be somewhat normalized
Now, if you were going to fully normalize it, you'd need more tables (vehicles, employees, cellphones ect) but you'll be started
There's a good tutorial here
http://www.phlonx.com/resources/nf3/
Essentially, there should be no duplicates in Table1
Do yourself a favor and rename ID to ProjectID on all the tables
Now, in Tables 2-6 add a column.
Call them ProjectDataID, OpsID, LaborID, VehicleID, and PhonesID
Fill these columns with sequential numbers.
Now, you should be able to relate each table on ProjectID.
If you are not seeing what you want in your queries, you'll have to look at your data.
But, in a nutshell, if the data is good, you should be somewhat normalized
Now, if you were going to fully normalize it, you'd need more tables (vehicles, employees, cellphones ect) but you'll be started
Each table should have its own primary key. It may store a foreign key as many times as needed. In your case, it appears that you have a primary key in table Projects named ID. It should be named ProjID. If in fact that same ID exists in all the other tables, the field name should be changed to ProjID, and your reference to that field in any of the other tables should be as a foreign key - ProjID
Now in the table ProjData it should have a pk - ProjDataID, as well as the foreign key ProjID, and so on down the line.
There is a reasonable amount of help in A2003 - type in - database design - for a start.
Now in the table ProjData it should have a pk - ProjDataID, as well as the foreign key ProjID, and so on down the line.
There is a reasonable amount of help in A2003 - type in - database design - for a start.
Thanks Guys. Lots of good information! Will have to do some studying and maybe re-thinking.
For the record, I did have "ProjID" for that field in each table. I have now added Primary Keys to each table and numbered them sequentially. Re-ran the queries and still ended up with the exact same records.
Instead of the screenprint on the original post ... this is basically what we want the query to show.
Of course it could be aligned differently, but the bottom line is that within each Project/Priority combination, there should be only 1 Bill Smith, 1 Veh# 423-921 etc.
Beginning to think that als315 was exactly correct ... but gonna try to find a way. Any thoughts? Thanks again.
For the record, I did have "ProjID" for that field in each table. I have now added Primary Keys to each table and numbered them sequentially. Re-ran the queries and still ended up with the exact same records.
Instead of the screenprint on the original post ... this is basically what we want the query to show.
Of course it could be aligned differently, but the bottom line is that within each Project/Priority combination, there should be only 1 Bill Smith, 1 Veh# 423-921 etc.
Beginning to think that als315 was exactly correct ... but gonna try to find a way. Any thoughts? Thanks again.
Without seeing the data, there isn't much I can offer.
I have pointed you at data normalization tutorials.
Ultimately, if your queries aren't returning the data the way you want them, it's becuase your data isn't structured right.
If Bill Smith can appear with different priorities, then Bill Smith doesn't have a relationship with ProjectID, he has a Relationship with Project Data, perhaps.
Working your data down until it is fully normalized.
Its worth the effort.
This may be the structure that results
Project <---> ProjData on ProjID
ProjData <------>Labor on ProjDataID
....ect....
The employee has an indirect relationship with the project, through project data.
Work your data down until it is fully normalized. It's a fundamental skill and requirement
I have pointed you at data normalization tutorials.
Ultimately, if your queries aren't returning the data the way you want them, it's becuase your data isn't structured right.
If Bill Smith can appear with different priorities, then Bill Smith doesn't have a relationship with ProjectID, he has a Relationship with Project Data, perhaps.
Working your data down until it is fully normalized.
Its worth the effort.
This may be the structure that results
Project <---> ProjData on ProjID
ProjData <------>Labor on ProjDataID
....ect....
The employee has an indirect relationship with the project, through project data.
Work your data down until it is fully normalized. It's a fundamental skill and requirement
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Microsoft Access - limit entry to numbers and letters | 10 | 60 | |
| TSQL remove duplicates from different columns | 14 | 52 | |
| Create a Calendar table | 29 | 43 | |
| How to create and use encrypted columns in SQL 2005? | 15 | 60 |
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
- Phishing
- Email Servers
- Microsoft Access
- Email Protocols
- Email Software
- The Email Laundry
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database. This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses
Course of the Month7 days, 12 hours left to enroll
739 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.