Lookup values in another table

[Paulstandere]

BACKGROUND

I am desperate to solve this problem but unfortunately I have not been able to figure it out. Below I will outline a design of a database and the desired results.

I know what I want to do but I don’t know how to do it (or whether it is impossible!)

DATABASE DESIGN

The design below is a simplified version of the real thing but it contains the essential information needed to understand my database.

Staff Data
Contains daily data for several members of staff

• Staff ID
  Staff Name
  Date
  Data Field 1

Example records:
600-001, Bob Smith, 01/03/2006, 50
600-001, Bob Smith, 02/03/2006, 50
600-001, Bob Smith, 03/03/2006, 50
600-001, Bob Smith, 04/03/2006, 50
600-001, Bob Smith, 05/03/2006, 50
600-002, Jayne Cole, 01/03/2006, 60
600-002, Jayne Cole, 02/03/2006, 60
600-002, Jayne Cole, 03/03/2006, 60
600-002, Jayne Cole, 04/03/2006, 60
600-002, Jayne Cole, 05/03/2006, 60
600-003, Alex Winter, 01/03/2006, 20
600-003, Alex Winter, 02/03/2006, 20
600-003, Alex Winter, 03/03/2006, 20
600-003, Alex Winter, 04/03/2006, 20
600-003, Alex Winter, 05/03/2006, 20

Team Lookup
Shows what team each staff member belongs to and what date this is effective.

• Staff ID
  Team
  Start Date

Example records:
600-001, Sales, 01/01/06
600-002, Sales, 01/01/06
600-003, Accounts, 01/01/06
600-002, Accounts, 04/03/06

Please note:

The first three records show that at the start of the year Bob (600-001) and Jayne (600-002) worked for Sales and that Alex (600-003) worked for Accounts.

The last record shows that from 04/03/06 Jayne switched teams to Accounts

Query Assign Team
Assigns the correct team to Staff ID for each date

• Staff ID
  Staff Name
  Team
  Date
  Data Field 1

Desired Results:
600-001, Bob Smith, Sales, 01/03/2006, 50
600-001, Bob Smith, Sales, 02/03/2006, 50
600-001, Bob Smith, Sales, 03/03/2006, 50
600-001, Bob Smith, Sales, 04/03/2006, 50
600-001, Bob Smith, Sales, 05/03/2006, 50
600-002, Jayne Cole, Sales, 01/03/2006, 60
600-002, Jayne Cole, Sales, 02/03/2006, 60
600-002, Jayne Cole, Sales, 03/03/2006, 60
600-002, Jayne Cole, Accounts, 04/03/2006, 60
600-002, Jayne Cole, Accounts, 05/03/2006, 60
600-003, Alex Winter, Accounts, 01/03/2006, 20
600-003, Alex Winter, Accounts, 02/03/2006, 20
600-003, Alex Winter, Accounts, 03/03/2006, 20
600-003, Alex Winter, Accounts, 04/03/2006, 20
600-003, Alex Winter, Accounts, 05/03/2006, 20

Query Group By Team
Summarises data by team/date

• Team – Group By
  Date – Group By
  Data Field 1 - Sum

Desired Results:
Sales, 01/03/06, 110
Sales, 02/03/06, 110
Sales, 03/03/06, 110
Sales, 04/03/06, 50
Sales, 05/03/06, 50
Accounts, 01/03/06, 20
Accounts, 02/03/06, 20
Accounts, 03/03/06, 20
Accounts, 04/03/06, 80
Accounts, 05/03/06, 80

PROBLEM: WHAT I AM TRYING TO DO

I don’t know how to get the query “Query Assign Team” to work!!

I would like to lookup up the ‘Staff ID’ and ‘Date’ in “Team Lookup” and return the appropriate value for ‘Team’

If the only two records in Tbl Staff data were:

600-002, Jayne Cole, 03/03/2006, 60
600-002, Jayne Cole, 04/03/2006, 60

I want the query to return:

600-002, Jayne Cole, Sales, 03/03/2006, 60
600-002, Jayne Cole, Accounts, 04/03/2006, 60

Can anybody help me?

Should I be using DLOOKUP? If so, how?
Is VBA the only way around my problem? If so, can you tell me what it is?
Am I attempting the impossible?

 

[Len Boorman]

Think the first problem is with

Staff Data
Contains daily data for several members of staff
Staff ID
Staff Name
Date
Data Field 1
Example records:
600-001, Bob Smith, 01/03/2006, 50
600-001, Bob Smith, 02/03/2006, 50
600-001, Bob Smith, 03/03/2006, 50
600-001, Bob Smith, 04/03/2006, 50
600-001, Bob Smith, 05/03/2006, 50
600-002, Jayne Cole, 01/03/2006, 60
600-002, Jayne Cole, 02/03/2006, 60
600-002, Jayne Cole, 03/03/2006, 60
600-002, Jayne Cole, 04/03/2006, 60
600-002, Jayne Cole, 05/03/2006, 60
600-003, Alex Winter, 01/03/2006, 20
600-003, Alex Winter, 02/03/2006, 20
600-003, Alex Winter, 03/03/2006, 20
600-003, Alex Winter, 04/03/2006, 20
600-003, Alex Winter, 05/03/2006, 20

I suggest that your data is not normalised. The Staff Data should contain surely just one entry for each member of staff. You also would have a primary key

This then would link to your Team allocations

Without knowing what the data field relates to it is dificult to advise but I think basically you are suffering from a lack of Normalisation and defined relationships between tables.

Len

 

[Paulstandere]

Linking By Staff ID

By linking Staff ID in Tbl Staff Data and in Tbl Team Lookup within a single query I will get 20 records instead of the 15 records I require.

This would be caused by having 2 records for Jayne Cole in Tbl Team Lookup

The five records I want to return would be:

600-002, Jayne Cole, Sales, 01/03/2006, 60
600-002, Jayne Cole, Sales, 02/03/2006, 60
600-002, Jayne Cole, Sales, 03/03/2006, 60
600-002, Jayne Cole, Accounts, 04/03/2006, 60
600-002, Jayne Cole, Accounts, 05/03/2006, 60

The five records I do not want to return are:

600-002, Jayne Cole, Accounts, 01/03/2006, 60
600-002, Jayne Cole, Accounts, 02/03/2006, 60
600-002, Jayne Cole, Accounts, 03/03/2006, 60
600-002, Jayne Cole, Sales, 04/03/2006, 60
600-002, Jayne Cole, Sales, 05/03/2006, 60

Example: I want my query to lookup 'Jayne Cole' and '05/03/06' in Tbl Team Lookup and return a record with those values plus the value 'Accounts' for the field Team. I don't want other records created for 'Jayne Cole' and '05/03/06'

 

[Len Boorman]

You will continue to have great problem until the data is normalised.
The table for Staff should contain ONE record only for each person.

The Team Lookup should show

Staff ID Team and Date

The tables are joined by Staff ID and the query basically returns all values of Team Lookup for that person. You use a criteria in the query to return values for certain Date or Dates

Len

 

[Paulstandere]

Len,

Thanks for the reply I will go a read up about 'normalization' to see if that clears up things for me.

The data structure in Tbl Staff Data is correct. The table is supposed to have a record for each day's performance for each member of staff. (This is the file structure that is extracted on a daily basis from an external system)

To be honest the Staff Name is surplus to requirements because this could be stored in another lookup table.

So the table needs to look like this:

Tbl Staff Data

Staff ID - Which staff member is being referred to
Date - What day the performance relates to
Data Field 1 - The actual performance

I have not set a primary key but if there was one it would be a combination of staff ID and Date - as this would be the only way to uniquely identify a record.

Does this make sense?

 

[Len Boorman]

Len,

The data structure in Tbl Staff Data is correct. The table is supposed to have a record for each day's performance for each member of staff.

Paul Sorry but No this is not correct

tbl Staff should contain Staff information. That is information that is directly related to the Entity Staff, Name, Rank, Serial Number, Sex, Date of Birth etc

Their activities is a different entity

Basically the activity is something to which a member of Staff is related

That's the clue Related

You must have Primary and Foreign keys to allow the relationship to be declared created.

Len

 

[Paulstandere]

Clarification

Len,

I think we are talking at cross purposes (probably caused by my naming convention)

You are right...a table specifically required to store all data about staff would contain Name, Rank, Serial Number, Sex, Date of Birth as well as, in my database Staff ID. Let's call this Tbl Staff.

My original table (Tbl Staff Data) isn't designed for storing this information but it is where all the staff daily performance stats are. So it really should be called Tbl Daily Stats or soemthing like that.

A company with 5 staff over the period of 5 working days would have 5 records in Tbl Staff and 25 records in Tbl Daily Stats . If one of the staff was on holiday for two days there would be 23 records in Tbl Daily Stats instead.


Does this make sense?

 

[Len Boorman]

Okay we are talking on the same wave length again.

The two tables are looking good. Nice and clean and Normalised

Keep going

len

 

[CraigDolphin]

I'm an amateur myself: so find a grain of salt to take with this.

I was able to do something like this in the attached db. I added a 'date reassigned field to your Team table in case one worker might be assigned to more than one team at a time.

Basically a did a query to say who was assigned to which team from date x to date y (if no date 'Y' then I used the present date as a cut off).

Then I created the query you wanted using the date worked with the criteria that they be between the assigned date and end date in the first query.

Jayne is listed twice for one date that she worked because she was assigned to more than one Team for a short time. If the rule is one person to one team, then you would only get one listing for each date provided that reassignment dates for a staff member are not equal to or larger than the next 'assignment date'.

Hope it is helpful.

 

[Paulstandere]

Craig,

Thanks a lot for having a go at solving my problem, it sounds very interesting.

I might not get a chance to look at it this morning (UK time) but hopefully this afternoon.

Thanks again, I appreciate it.

Paul