Hi folks, I am new to this forum and new to access and databases in general and I am having a problem which is driving me a bit loopy.

I have three tables:-

mechanics
jobs
timesheets

Jobs is related to mechanics using a mechanic id, and timesheets is realated to mechanics using a mechanic id.

I want a query which will take a date range and a mechanic name as input (from a form) and give me a report showing all the jobs that particular mechanic completed within that date range, and also show the hours that he has claimed for the same date range on his timesheets. (I want to compare the hours that all his jobs have taken compared to the hours he has claimed on his timesheets).

The problem I am having is I am get multiple results for the time sheets.

So for example, if a mechanic has worked two jobs in one day, the query will show me the two jobs, but it will also who me two timesheets for that day, but obviously there is only one timesheet for each day.

How do I show each distinct time sheet for that date range and mechanic?

I have uploaded a db with a sample form, query and report to give a better idea of what I mean.

Any help in trying to solve this would be great! :D

Your time sheets need to be date specific linked to a mechanic. A mechanic can work on any number of jobs in a single day but the time taken relates to that day only.

When you first pick a mechanic and create a timesheet for them the first thing your system should do it first see if a timesheet exists for the selected mechanic for that day. If not create one. These entries will be held in a child table with Mechnic Id, Date,WorkSheet ID, Entry No as Primary keys.

Each time span will be appended using the above but have adding 1 to the entry number to keep the entries sequential.

Then when you bring the details to screen or report you can filter the data accordingly. Ie

Select * From MyData Where Mechanic ID = n And Date = Today ORDER By Entry ID.


CodeMaster::cool:

Thanks for the reply my friend!

Like I say, I am quite new to access and what you describe sounds quite complicated to me :D

Could you give me a smal example of how to go about building the child table and apending to it everytime I make up a time sheet?

Here is a very brief outline of your requirements:

Parent table

Mechanics

ID = Primary Key
Other fields personal to each mechanic

Child table

TimeSheets

Mechanic Id
TimeSheet Id
Job Number
Date
Time Start
Time End
Description
Etc

Create a relationship between the above using the Mechanic Id as a one to many relationship

The first four fields in your timesheet table make up the primary key.

Create a main form that you can select a mechanic and a date from. Have a subform that is linked to the above two fields, this will then filter out records in your timesheet table based on the two items. If no records are found then this must be the first job of the day. (Job number is one)

Then in your sub form you can select a job that the mechanic was working on and when it saves the data it will add the date chosen in the main form and the next sequential number into the Job number field.

David

I'm really sorry but I forgot to mention another specification that I require!

I pretty sure I need a seperate Jobs table, as I want to be able to search individual jobs too. More than one mechanic can work on each job. So mechanic a might work on job number 123456 from 9am to 12pm and then mechanic b works on it from 12pm to 3pm. This can be either on the same day, or on seperate days.

I want a facility where I can simply enter a job number onto a form and it gived me a list of mechanics that worked on this job and when.

If I have a seperate jobs table, I can achieve this, but then this throws up my original problem when searching the jobs and the time sheets at the same time.

As outlined I mentioned that on your subform you had a mechanismn of selecting an ongoing job. This source data would be in a seperate table as you suggested. So if you want to run reports or queries based on specfiic jobs the data item is there to be queired.

David

I'm just a bit confused on how the sub form would work to be honest.

How to I link the controls for choosing a date and a mechanic to the subform?

Is there not a way of doing this with a report rather than a subform?

Reports are data bound. You need to be able to record data in order for the report to report upon it.

When you create a mainform/subform the subform has two properties

Linked Parent Control
Linked Child Control

In your case your need two items:

Parent TxtMechanicId;TxtWorkDate
Child TxtMechanicId;TxtWorkDate


The field names relate to matching fields on both the main form and sub form. Use the names you have not the names given above. Brevity only.

Once you have the data you can then progress to the reporting stage.

David

Thing is, when I try and set the

Linked Parent Control
Linked Child Control

properties to the text boxes, I get an error message saying

"can't build a link between unbound forms"

When I create the main form, do I use the wizard and chose the mechanics table as its data source?