Carrying a value between 2 tables

[Samswood]

I have two tables.

Equipment has ID, Machine, Hourly rate.
Jobs has ID, Start Time, End Time, Machine, Hourly Rate, Total Job.

I have set up a lookup on Jobs so that I can select the type of machine from
the Equipment table. That works well. But for love nor money can I work
out how to bring across the appropriate Hourly Rate from Equipment so that I
can calculate the End Time - Start Time * Hourly Rate on the Jobs table.

If I set up another lookup I end up selecting from a list again and all I
want is the hourly rate of the machine I have already selected.

 

[Brendan Reynolds]

I have two tables.

Equipment has ID, Machine, Hourly rate.
Jobs has ID, Start Time, End Time, Machine, Hourly Rate, Total Job.

I have set up a lookup on Jobs so that I can select the type of machine
from
the Equipment table. That works well. But for love nor money can I work
out how to bring across the appropriate Hourly Rate from Equipment so that
I
can calculate the End Time - Start Time * Hourly Rate on the Jobs table.

If I set up another lookup I end up selecting from a list again and all I
want is the hourly rate of the machine I have already selected.

That would be something you would do in a form or query, not in a table.

If the hourly rate never changed, you could do it in a query like so ...

SELECT Jobs.ID, Jobs.[Start Time], Jobs.[End Time], Jobs.Machine,
Equipment.[Hourly Rate], DateDiff("h",[Start Time],[End Time]) AS Hours,
[Hours]*[Equipment].[Hourly Rate] AS [Total Job]
FROM Equipment INNER JOIN Jobs ON Equipment.ID = Jobs.Machine;

However, if you did it this way, the total would change whenever you changed
the hourly rate in the Equipment table, and you don't want it to change for
jobs that were done before the rate changed. There are a couple of ways of
handling that situation. One is to use a combo box on a form which would
have a row source something like ....

SELECT ID, Machine, [Hourly Rate] FROM Equipment

You'd also have a text box on the form bound to the Hourly Rate field in the
Jobs table, and in the AfterUpdate event procedure of the combo box, you'd
put the Hourly Rate from the selected record in the Equipment table into the
Jobs table like so ...

Me.txtHourlyRate = Me.cboMachine.Column(2)

The Column() property of a combo box is zero-based, so the first column is
Column(0), the second column is Column(1), and the third column is
Column(2), which in this example is the Hourly Rate column.

There's no need for the Job Total field in the Jobs table, as that can be
calculated whenever required from Start Date, End Date, and Hourly Rate, as
in the example query above.

 

[Samswood]

Thanks Brendan,

You clearly know the product inside and out. I on the other hand thought I
would get clever and write a tiny application for a friend. I am way out of
my depth but have Access 2007 in my Office package and assumed (wrongly I
find) that it would be easy and logical.

I created the tables and then generated the forms using the Wizard. Was
this wrong? Should I scrap them and start again with forms?

Jennifer

I have two tables.

Equipment has ID, Machine, Hourly rate.
Jobs has ID, Start Time, End Time, Machine, Hourly Rate, Total Job.

I have set up a lookup on Jobs so that I can select the type of machine
from
the Equipment table. That works well. But for love nor money can I work
out how to bring across the appropriate Hourly Rate from Equipment so that
I
can calculate the End Time - Start Time * Hourly Rate on the Jobs table.

If I set up another lookup I end up selecting from a list again and all I
want is the hourly rate of the machine I have already selected.

That would be something you would do in a form or query, not in a table.

If the hourly rate never changed, you could do it in a query like so ...

SELECT Jobs.ID, Jobs.[Start Time], Jobs.[End Time], Jobs.Machine,
Equipment.[Hourly Rate], DateDiff("h",[Start Time],[End Time]) AS Hours,
[Hours]*[Equipment].[Hourly Rate] AS [Total Job]
FROM Equipment INNER JOIN Jobs ON Equipment.ID = Jobs.Machine;

However, if you did it this way, the total would change whenever you changed
the hourly rate in the Equipment table, and you don't want it to change for
jobs that were done before the rate changed. There are a couple of ways of
handling that situation. One is to use a combo box on a form which would
have a row source something like ....

SELECT ID, Machine, [Hourly Rate] FROM Equipment

You'd also have a text box on the form bound to the Hourly Rate field in the
Jobs table, and in the AfterUpdate event procedure of the combo box, you'd
put the Hourly Rate from the selected record in the Equipment table into the
Jobs table like so ...

Me.txtHourlyRate = Me.cboMachine.Column(2)

The Column() property of a combo box is zero-based, so the first column is
Column(0), the second column is Column(1), and the third column is
Column(2), which in this example is the Hourly Rate column.

There's no need for the Job Total field in the Jobs table, as that can be
calculated whenever required from Start Date, End Date, and Hourly Rate, as
in the example query above.

 

[Brendan Reynolds]

I created the tables and then generated the forms using the Wizard. Was
this wrong? Should I scrap them and start again with forms?

Whether it would be better for you to start from scratch or to use the
wizards and modify the result depends, in my opinion, on you and how you
like to work. I certainly wouldn't call it "wrong", especially when getting
started. I still use wizards for some things - e.g. mailing labels. From
your description, it doesn't sound as though the tables need a great deal of
modification.