Redunant?

[Becky]

I have tables tblEmployee, tblWorkweek, tblProject and
tblTimesheet.

Table Timesheet has fields: autoID, EmpID,
WorkweekID,T1Start,T1End, Project1(realy ProjectID).

Because the timesheet goes for 14 days I really have 14
ProjectIDs. I can make it work in Access but ASP gets
confused with having a multiple foreign key.

How should I structure this properly?

Thanks,
Becky

 

[Sandra Daigle]

Are you saying that in tblTimesheet you have columns Project1 - Project14?

That is very redundant! This can be solved by normalizing the table. Instead
of having 14 Project Columns, you should have 14 Rows.

For Example - you have:

EmployeeID WorkWeekID Project1 Project2 . . . ProjectN
1223 29 1292 9992
9232
1224 29 2923 9992
9232

Instead you would have

EmployeeID WorkWeekID Projectid
1223 29 1292
1223 29 9992
1223 29 9232
1224 29 2923
1224 29 9992
1224 29 9232

Right now you probably have a non-duplicate index on the columns EmployeeID
+ WorkweekID. Just add ProjectID to the non-duplicate Index so that for any
employee in any workweek, you can only have one record per project. Then
when you need to see it in spreadsheet-like view, you create a crosstab
query.