What's the best way to.... (Access Novice)

[Steve]

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)

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

 

[John Vinson]

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)

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!)

A "Non Equi Join" query may be the ticket here. It's not one of the
standard queries that Access offers in the grid, so you'll need to go
to the SQL window to do it. I will assume that Table1 and Table2 have
a date field for the link.

First, create the query you want joining the Staff No. to the Staff
No; the Ref No. to the Ref No.; and the datefields to Date From. This
will return only those data points on that exact day but it will build
the rest of the query for you.

Then, open the query in SQL view (use the leftmost icon dropdown in
the query design window); edit the JOIN clause (or clauses) in the
query from

ON [Staff Table].[Date From] = [Table 1].[datefield]

to

ON [Staff Table].[Date From] <= [Table 1].[datefield] AND [Staff
Table].[Date To] >= [Table 1].[Datefield]

and similarly for Table 2.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Steve]

Thanks John - that's done the trick !