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.