How do I get around this one?

[accessjockey]

Hi All,

Here's a new challenge.

I have a table called 'Timesheets'

Each row is a record of one particular staff members time spent on a range of projects. See below.

I have created the table this way to minimise the amount of records one staff member would enter each day to one row even though they may have worked on up to 4 projects. Which project and how many hours they worked can be stored in whatever field they choose to enter the data in.

Here's the challenge!

I am trying to create a query that will total the amount of hours spent on a particular project. The query will prompt the user for the project number (Like "*"& [Enter the Project Number]&"*") and then go find the data.

For example. Totaling the hours spent on Project 001. See below.

All my attempts have failed.

Can anyone meet this challenge?

 

[pbaldy]

Your difficulty is caused by incorrect design. The table is not normalized due to the repeating fields. I'd correct that; my guess is you'd want a second table with fields for the timesheet ID, project and hours. If a person worked on 3 projects, they would have 3 records in that table. Then it's a simple query to get the total hours for a project.

 

[hbtousa]

I would create a temp table and run for append queries. The 1st query will have project1 and project1 hours, 2nd query will have project2 and project 2 hours....I will have a small table with all records from the same project.
Thanks

 

[pbaldy]

A UNION query would be a simpler workaround than that, but I would still fix the design.

 

[accessjockey]

Thanks Guys for the replies,

It looks like the first thing I need to do is simplify the table.

I was hoping to avoid generating potentially 4 separte entries each day by the same staff member rather than one entry per staff member with 4 project fields.

Are you sure there is no SQL that will ask access to look at each 'Project hours' field and sum only those that match the requested 'ProjectNumber' field?

 

[boblarson]

Thanks Guys for the replies,

It looks like the first thing I need to do is simplify the table.

I was hoping to avoid generating potentially 4 separte entries each day by the same staff member rather than one entry per staff member with 4 project fields.

The number of entries is irrelevant if the design is proper.

Are you sure there is no SQL that will ask access to look at each 'Project hours' field and sum only those that match the requested 'ProjectNumber' field?

Yes, you have many years of experience here telling you that. You would require a Union Query with 4 distinct queries in it to pull all of the data that matched. A redesign will simplify your life immensely.

 

[Pat Hartman]

This is not an Access limitation. You would not find this function in any RDBMS because it implies that the underlying structure is not normalized.