Look up query (1 Viewer)

[Christopherusly]

I have two tables:

- Project finance, each line is a single transaction for a job, i.e. how many hours someone has spent working on a project, so for each project there are / will be multiple lines.

- Grade rates, a list of staff members, their grade and their charge out rate per hour.

What i would like to be able to do, is run a query on the project finance table whereby it pulls up the time (hours) someone has booked to the job, looks at who has done the work, pulls the persons rate from tbl - grade rate and then multiples it by the hours worked to give me a true project cost.

Is this possible ?

My office has just started using SAP and it is a nightmare / pain in the ass, since i am not allowed to (read important enough) to request custom reports i have to build my own.

Any hinters or pointers in the right direction would be appreciated.

 

[Christopherusly]

okay, so i get it now that Dlook is probably the way to go, but when running Dlookup as an expression i get an unknown error - my expression is as follows:

=Dlookup("Rate", "tbl_graderates", "staff member")

which i would assume is saying look in table graderates, for staff member, but i do not know how i would get this to return multiple value as all the examples i have seen use a fixed reference ?

Any help or suggestions would be most welcome, thanks guys (and girls)

 

[Galaxiom]

Don't use a DLookUp. The tables just need to be joined on the staff member key.

BTW The third argument of a DLookup is a condition. eg "somefield = whatever"

 

[highandwild]

which i would assume is saying look in table graderates, for staff member, but i do not know how i would get this to return multiple value as all the examples i have seen use a fixed reference ?

Whatever you are doing is on a record by record basis so I am not too sure where the issue of multiple values comes in. I assume that each person only has one rate.

Be carefull when rates change as they often do and especially when a reporting period spans the date upon which the new rate is effective. You may need a Grade rates table which has multiple records per employee and the dates recorded between when the rate is effective.

 

[Christopherusly]

Be carefull when rates change as they often do and especially when a reporting period spans the date upon which the new rate is effective. You may need a Grade rates table which has multiple records per employee and the dates recorded between when the rate is effective.

Alas, i do not know enough about access to be able to use multiple values, at the moment i am working on the 10/11 charge out rates that should not change for at least the next year, i figured being such a newbie it would be easier to start a new database the following year for the 11/12 rates - the easy way out i know, but when i do not know how to do anything better

I am also unsure as to how linking the tables as suggested :"The tables just need to be joined on the staff member key" would actually help me, i have done this and still do not have the actual rate appearing ... any suggestions would be welcome (p.s. i am a complete beginner so be gentle with me, thank you for your time and patience)

 

[Galaxiom]

Create a query in design view with both tables included.

Drag the field from one table to the corresponding field in the other table. A line will appear connecting them. Now you can work with the two tables as though the fields in one are part of the other where the joined values match.

So your ChargeOut is simply the Hours times the Rate field. The join on StaffID automatically connects the correct pay rate to the job by the StaffID.

Using multiple values is quite simple. The table has StaffID, Rate and EffectiveDate. The query selects the rate for the StaffID that corresponds to the maximum EffectiveDate that is less then or equal to the date of the record in the Project table.