Query Help: Job Shop Hours Tracking

[kiralytool]

Good afternoon. I am having some issues pulling together a query to reference performance measures in our machine shop. This may be a little long winded but should provide all the information if anyone is willing to help...



We collect "job cards" filled out by each employee stating the job number and amount of hours they spent on it each day. This information (employee, job number, hours) is entered into a table. From this table I have a query created to total the hours by employee per job...very basic. From this, we were able to total the hours per job, as a performance metric, to compare how many hours we quoted for that particular job. We have years of information in the table and query.

Our problem with this system of tracking performance is that a "job" can have multiple parts all with a different amount of hours quoted to produce them. My solution was to create another table. We would take a job number and add a "P#" to the end of it to track a job by part number. An example of this would be Job123P1, Job123P2, Job123P3 - if Job123 had 3 separate parts. This new table would list each job number with the P# on the end and the amount of hours quoted for that particular part. In addition to this table, I created another query to pull together the query in the paragraph above and this table...in hopes that it would give me a spreadsheet showing Job123P1 was quoted with X amount of hours and these employees had Y amount of time on it.

Seems pretty easy...the problem is that not all job information is recorded accurately with our "new P# system" on employee job cards. So we are left with Job123 versus the breakdown of Job123P1, Job123P2, etc. and the hours worked on each one. This leaves the new query only pulling information that has both a time quoted and hours worked, not including entries where a P# has been left off of an entry.

All that being said here is what I am looking help with:
How can I display job numbers that have been entered without a P# in a query/report with a null value for the quoted amount but still total the hours actually worked, while still pulling all the remaining job numbers with the P#'s that were recorded accurately including their respective quoted hours and hours worked? Is there any simple solution versus remaking my quoted hour table and putting P#'s in a separate column instead of on the end of a job? I believe that may be my only solution.

Any help is greatly appreciated!

[ranman256]

no, you want a table with separate fields

[job], [part]
JOB123, P1
JOB123, P2

then you can sum up part# and job#

[nick404]

Would be helpful to post sql of queries

[kiralytool]

@ranman256 That is what I feared. The good thing is if that is the only solution, I only have about 1000 entries in my quoted hour log so far.

@nick404

Here is the SQL from my first query totaling hours per employee by job #:
SELECT [Time Log Table].[Job Number], [Time Log Table].Employee, Sum([Time Log Table].Hours) AS SumOfHours, First([Time Log Table].Date) AS FirstOfDate, Last([Time Log Table].Date) AS LastOfDate
FROM [Time Log Table]
GROUP BY [Time Log Table].[Job Number], [Time Log Table].Employee
ORDER BY [Time Log Table].[Job Number], [Time Log Table].Employee;

Here is the SQL from the second (new) query looking at quoted hours versus hours recorded:
SELECT [Quoted Hour Log].[Job Number], Sum([Time Log Table].Hours) AS SumOfHours, [Quoted Hour Log].[Quoted Hours]
FROM [Time Log Table] INNER JOIN [Quoted Hour Log] ON [Time Log Table].[Job Number]=[Quoted Hour Log].[Job Number]
GROUP BY [Quoted Hour Log].[Job Number], [Quoted Hour Log].[Quoted Hours];