Problem dealing with dates (Newbie)

[C4rtm4N]

Hi

I've got a simple database set up in Access 2000 that looks at productivity
for individuals within teams. Currently I'm using 3 tables, two with
different types of data in them and the 3rd with details of all of the staff
& which teams they're assigned to (which links the other 2).

Everything works fine except for when people leave or change teams as the
'staff' table is just a snapshot that's taken each day from another system &
hence doesn't hold any historical info about where people where on certain
dates so it just reports all info against the staff member's latest team. I
can configure the other system to export 'from' & 'to' data but can't seem
to get my queries to work correctly & show each person's details against the
team that they were assigned to on a given day. Ultimately I'd like to be
able to run a query that shows part of a person's data against their
original team then the rest against their new team.

Essentially the setup will be

Table 1
Staff no.
Other data
(zero or one record per person, per day)

Table 2
Ref no.
Other data
(zero to several records per person, per day)

Staff Table
Staff no.
Ref no.
Team details
Date from
Date to
(1 record per person at the moment but this will be multiple for the future as there'll be one per date range)

What I'm trying to do is probably really easy but I'm a novice so please
bear with me (& go easy - Excel is more my forte!)

Thanks

Steve

 

[lupins46]

If you need historical data then you must record historical data. I am guessing that tables 1 and/or 2 are some sort of transactions; so as each transaction is created you need to record the team id that is current for the staff member.

That should then give you the ability to group data into the correct team.

 

[C4rtm4N]

Thanks - I probably didn't word things very well in my first post. That's how we used to do things when we used Excel but I was under the impression that using Access I'd be able to stop having to run a macro to 'attach' team details to every record.

By adding the from & to fields to the staff table to signify the dates that people were members of certain teams, can Access not perform a lookup to see who was where on each date? The system (Oracle db) that we're getting the staff 'snapshot' from currently manages to do this by using this method & all I'm essentially doing is exporting a table from there.

Thanks

Steve

 

[lupins46]

You didn't give any indication that there was a date on the table. Yes, you can use a lookup approach if that's what you prefer. It will be slower than keeping hard data but is more in keeping with formal database design principles.
To get the team number for a given date you could use a DLookup function:

This is an example using the orders table in northwind.
It finds the order number of an order for a given customer (read: Staffid) for a given date which falls between two dates on the record.
Sub getteam()


Dim custid
Dim vdate As Date
Dim x
Dim crit
vdate = "21-sep-1996"
custid = "Vinet"

crit = "[Customerid]= '" & custid & "' and #"
crit = crit & vdate & "#> orders.orderdate and #" & vdate & "# < orders.requireddate"

x = DLookup("Orderid", "Orders", crit)
Debug.Print x
End Sub

SO you would have something like (precise statement depends on datatypes and actual fieldnames:

x = Dlookup("[teamid]", "StaffTable", "[staffid]= '" & somestaffidfield & "' and #" & somedatefield & "# >= Stafftable.fromdate and #" & somedatefield & "# <= stafftable.todate")

It would be worth thinking about writing a function similar to the procedure above so that you were not continually having to write the dlookup statement.

You might also be able to create pseudo joins between the transaction table and staff table using a WHere clause similar to the criteria string for the dlookup.