Comparison Query - How to write it!
in [Queries]
|
From: Sarella on 9 Mar 2010 10:16 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
From: Stefan Hoffmann on 9 Mar 2010 10:25 On 09.03.2010 16:16, Sarella wrote: > 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 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 <--
From: Bob Barrows on 9 Mar 2010 10:26 Sarella wrote: > 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! > 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. -- HTH, Bob Barrows
From: KARL DEWEY on 9 Mar 2010 13:22 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? -- Build a little, test a little. "Sarella" wrote: > 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 >
From: Sarella. on 10 Mar 2010 07:21 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> --- frmsrcurl: http://msgroups.net/microsoft.public.access.queries/Comparison-Query-How-to-write-it
|
Next
|
Last
Pages: 1 2 Prev: recursively retrieve records from 2nd table based on records from Next: Look up by Date issue |