How can I query a table field without knowing which field

[mpjtaylor]

Can anyone help please am stuck ACCESS 2007

I have two tables one of them (the rates table) is a Synced list from SharePoint the owner of the list created in an awkward way for me but simple for them. What I need to be able to do is query the Timesheet table (data is imported to this from excel 2003 worksheets) and for each entry work out the appropriate value (value = rate*hours) if the Company cannot be found then the Default rate for that employee is used.

How can I query the rate table for a specific company and a Specific Empoyee giving me one rate value.

Timesheet data
ID Date Hours Company Employee 1 26/01/2009 0.3 CompanyA Employee1 2 27/01/2009 0.5 CompanyB Employee2 3 28/01/2009 1 CompanyC Employee3 4 29/01/2009 1.3 CompanyA Employee4 5 30/01/2009 1.2 CompanyZ Employee2 6 31/01/2009 1.6 CompanyB Employee3 7 01/02/2009 5 CompanyA Employee4 8 02/02/2009 3 CompanyB Employee2 9 03/02/2009 2 CompanyA Employee3

Rates Table

ID Company Employee1
Employee2 Employee3 Employee4
1 Default 75 60 50 44
2 CompanyB 60 50 44 22
3 CompanyC 44 60 50 44
4 CompanyD 44 44 60 50
5 CompanyE 44 22 44 22
6 CompanyF 60 50 44 22
7 CompanyG 44 60 50 44

Many thanks

Marcus

 

[The_Doc_Man]

The best answer depends on how often you use this data and how often you must refresh it. To my way of thinking, it is often better to do a two-stage import of data so that you can bring in everything normalized and work from a table where simple relationships become possible in way not supported by denormalized tables.

Having said that, your Rates table is denormalized badly and the input from the spreadsheet isn't any better. Asking us to show you how to make a denormalized table work correctly is possible, but we usually work better by telling you the RIGHT way to do something.

I see a table for employees, a table for companies, a table for time-expended charges, a table for rates that is a junction of employee and company, and a table that is a junction between the time-expended table and the rate table using the common keys they share.

tblEmployees
EmpID, Prime Key, whatever you are using for internal employee ID numbers
other employee-centric data

tblCompanies
CompID, Prime Key, whatever you are using for company internal ID and here, autonumber could be viable.
other company-related data.

tblEmpRates
EmpID, foreign key >> tblEmployees
CompID, foreign key >> tblCompanies
Rate, value of this employee working for that company

tblChargedHours
EmpID, foreign key >> tblEmployees
CompID, foreign key >> tblCompanies
Hours, value in hours/fractions for time worked for the particular company
WorkDate, date work occurred

and a query that joins the timesheet to the rate table on COMPANY and EMPLOYEE ID fields (which must match). This would need to be an outer join to accomodate the case where the time charge doesn't match any rate record.

qryMoney
EmpID
CompID
Hours
HourlyRate
Money = Hours * Hourly Rate

You can include an IIF for the hourly rate such that if there IS no matching rate (i.e. rate is empty, null, zero, whatever) then do a DLookup of the default rate associated with that company.

If you try to do this with your current structure, you will grow old before your time and will eventually make Ebeneezer Scrooge (pre-ghost-visit) look like a positive Pollyanna.

 

[mpjtaylor]

The_Doc_Man

Many thanks for an excellent reply Given the fact that its was SharePoint list structure causing the problem I managed to get Finance to rework it saving me the hassle!

Marcus