Comparison Query - How to write it!

  • Thread starter Thread starter Sarella
  • Start date Start date
S

Sarella

I need to write a query which will make one table’s worth of information
(Labour Hours Table) look up its corresponding fields in another table (Rates
of Pay) and report back the resulting “rate†per hour

Table: Rates of pay
Field : ID / Autokey
Field : Labour ID
Field :Customer ID
Field : Job ID
Field :Shift ID
Field :Rate

Table: Labour hours
Field :ID / Auto Key
Field :Job ID
Field : Labour ID
Field :Date Worked
Field : Shift ID

Please advise how this query would look, or point me in the right direction
of how to write it, as it is driving me mad!

Many thanks
 
I need to write a query which will make one table’s worth of information
(Labour Hours Table) look up its corresponding fields in another table (Rates
of Pay) and report back the resulting “rate†per hour

Table: Rates of pay
Field : ID / Autokey
Field : Labour ID
Field :Customer ID
Field : Job ID
Field :Shift ID
Field :Rate

Table: Labour hours
Field :ID / Auto Key
Field :Job ID
Field : Labour ID
Field :Date Worked
Field : Shift ID
Click to expand...
The obvious:

Add both tables to your query. Draw three join lines for [Labour ID],
[Job ID] and [Shift ID].

The mysterious:

What kind of table is [Rates of pay] ?
What kind of a field is [Date Worked] ?

Normally you would use either

[Rates of pay].[Rate] * [Labour hours].[Date Worked]

or

[Rates of pay].[Rate] / [Labour hours].[Date Worked]

as expression to calculate it, but your table and field names are _very_
cryptic on that behalf.

btw, you should avoid spaces and special characters in table and field
names.


mfG
--> stefan <--
 
Sarella said:
I need to write a query which will make one table's worth of
information (Labour Hours Table) look up its corresponding fields in
another table (Rates of Pay) and report back the resulting "rate" per
hour

Table: Rates of pay
Field : ID / Autokey
Field : Labour ID
Field :Customer ID
Field : Job ID
Field :Shift ID
Field :Rate

Table: Labour hours
Field :ID / Auto Key
Field :Job ID
Field : Labour ID
Field :Date Worked
Field : Shift ID

Please advise how this query would look, or point me in the right
direction of how to write it, as it is driving me mad!
Click to expand...
Without a couple rows of sample data and intended results, all we can do
is guess and offer generic advice. So ...

Create a new query in Design view. Select both tables from the Choose
Tables dialog and close the dialog.
Click and drag the fields that are required to form the links between
the tables from one table element to another. It appears you would need
to create links for Labour ID, Job ID and Shift ID, but I'm not sure
about what your business rules are.
Drag the fields you want to see in the results into the column grid.
Test it and and see if it gives you the results you want.
If not, show us a couple rows of sample data followed by the rows of
result data you would expect to see returned by your query.
Then show us the incorrect results followed by the sql of the query you
created to get those incorrect results. You expose the sql by switching
your query to SQL View using the toolbar button, or the View menu, or
the right-click context menu.
 
I think Stefan cover it but I question why your rate table has Customer ID
and Job ID fields. Do you pay your people differet wages based upon the job
and customer?

I can possibly see job as it might be high risk so they get hazardous pay
but why different pay based on who the customer is?
 
Hi

Thanks for the feed back so far. To clarify why there are so many fields, virtually all our labour is outsourced, so a labourer (identified by Labour ID) can be paid a different rate based on where they are working (Customer ID), Which job(Job ID) - potentially have more than one job at same customer, and what shift they are working (Shift ID)

This is why I'm finding it so complicated to write

Each of these "ID's" has another separate table that holds data on the labourer, customer, job requirements and shift patterns etc

What I want to achieve is a report that adds the coresponding "rate" to the relevant record in the Labour Hour table by comparing the labourer, the job and the shift

I hope this helps
 
When I write a query as Bob Barrows has described, I get an error message saying "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be created first, create a separate query that performs the first join and then include that query in your sql statement

I am unsure exactly how to write these queries separately - I'm a bit of a novice to access, and I thought this bit would be easy

Many thanks again
 
Sarella said:
When I write a query as Bob Barrows has described, I get an error
message saying "The SQL statement could not be executed because it
contains ambiguous outer joins. To force one of the joins to be
created first, create a separate query that performs the first join
and then include that query in your sql statement"
Click to expand...
Well, then you did not do it the way that I described, since the
technique I gave you would not result in any outer joins. Again, if you
require more specific help, you need to help us. Again:

If not, show us a couple rows of sample data followed by the rows of
result data you would expect to see returned by your query.
Then show us the incorrect results you received followed by the sql of
the query you
created to get those incorrect results.

You expose the sql by switching
your query to SQL View using the toolbar button, or the View menu, or
the right-click context menu.
 
Back
Top