Calculated Fields

[Sandy]

I have two queries both created from the same table. The first is to record
customer requirements prior to any job being done and has a calculated field
for JobCost.
The second query is to record details when the customer collects the
finished job and has a reminder of the JobCost.

Because certain of the other fields are not present in the second query for
the calculations it means I have a rather contrived calculation :-

"JobCost: ([Number]*[SellPrice]-[Number]*[SellPrice]*[DiscountCalc]/100)"
which works but I was wondering if there was a way to refer to the field
'JobCost' in the first query directly from the second.

I tried something like 'JobCost:=([qryRepairJobs]![JobCost) to no avail.

Thanks
Sandy

 

[John W. Vinson]

"JobCost: ([Number]*[SellPrice]-[Number]*[SellPrice]*[DiscountCalc]/100)"
which works but I was wondering if there was a way to refer to the field
'JobCost' in the first query directly from the second.

Use DLookUp:

JobCost: DLookUp("[JobCost]", "[FirstQueryName]", "<optional criteria>")

where the criteria specify which record to look up - for instance

"[JobNumber] = " & [JobNumber]

to look up the current record's job number.

John W. Vinson [MVP]

 

[Tom Wickerath]

Hi Sandy,

Have you tried adding your first query, qryRepairJobs, to the QBE grid of
the second query, and joining this virtual table to the existing table using
a common field, such as a primary key or uniquely indexed field? I would
think that the JobCost from qryRepairJobs should then be available to your
second query.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Sandy]

Hi Tom

Thank you once again. I found this solution easy to implement.

Sandy

Hi Sandy,

Have you tried adding your first query, qryRepairJobs, to the QBE grid of
the second query, and joining this virtual table to the existing table
using
a common field, such as a primary key or uniquely indexed field? I would
think that the JobCost from qryRepairJobs should then be available to your
second query.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have two queries both created from the same table. The first is to
record
customer requirements prior to any job being done and has a calculated
field
for JobCost.
The second query is to record details when the customer collects the
finished job and has a reminder of the JobCost.

Because certain of the other fields are not present in the second query
for
the calculations it means I have a rather contrived calculation :-

"JobCost: ([Number]*[SellPrice]-[Number]*[SellPrice]*[DiscountCalc]/100)"
which works but I was wondering if there was a way to refer to the field
'JobCost' in the first query directly from the second.

I tried something like 'JobCost:=([qryRepairJobs]![JobCost) to no avail.

Thanks
Sandy

 

[Tom Wickerath]

You're welcome. I'm glad to read of your success, so thanks for posting back.

Good luck on your project!


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________