Re-organising Database - Confusing Query

[Ian Mac]

All,

This may a little difficult to explain but hopfully the attached DB will help.

I am re-designing my database and I'm at an stop the process to change the data stucture.

I originally had:

Main Employee Table
EmployeeID - PK/Autonumber
GivenName - Text
Surname - Text
StartDate - Date
EndDate - Date

Employee Contract Table
ContractID - PK (Not actually important) Autonumber
EmployeeID - FK/Long
ContractID - FK/Long
StartDate - Date
EndDate - Date

The above was linked to Contract Table

ContactID - PK
ContractType - Text.

OK I have similar for Grade Contract, with the EmployeeID Grade ID and SDate and EDate. GradeID linked to a Lookup Table.

The Hours is a little different:

EmployeeID - FK
StartDate
EndDate
Monday - Int
Tuesay - Int
'
'
'
'
Sunday - Int

That was then.

What I'm doing changing the data to:

Employee Main Table as Before.

A new Table for LabourAccount:

LabourAccountID - PK
HoursPatternID
GradeID
ContractID

The orginal lookup table for Grade and Contract now supply the Account Table
and I have created a new Lookup Table for hours.

That bit is fine.

Now I'm trying to compact the Orginal Hours, Grade and Contract information to:

HomeLabourAccount:

HomeLabourID - Autonumber
EmployeeID - FK
LabourAccount - FK
StartDate
EndDate

---------------------------------------------------

The problem I'm having is the last bit.

What my idea is:
I have created a temporary table

TempDate

which has everydate which covers before anyone started to 30-09-05

(don't worry it's only 670 dates)

I have created the Query:

SELECT TempDate.TempDate, tblEmployeeMain.EmployeeID
FROM TempDate, tblEmployeeMain;

this gives me 338350 records - 1 for each date/employee.
I am now trying to create a query which will return a record each date for each employee giving the snap shot for each one.

i.e. I wish to return:

01-01-03 | 001 | 23 |
02-01-03 | 001 | 58 |
etc.

the 23 and 58 are Account types.

------by the way I may be doing this a really long way round------

Then I thought I could create a query on the whole table to return the unique values, then adding to more Columns for Start Date and End Date:

Something like:

Min(TempDate.TempDate) AS StartDate
And
Max(TempDate.TempDate) AS EndDate

Create and table of that and remove all the 30-09-05 from the EndDate
(I've choosen tomorrow 03-09-05 as nobody has any changes for then)

Does any of that make sense.

Please ask all the questions you need.

Cheers,