updatable recordset with normalized tables

[jancheta]

Hello. I'm trying to create an updatable recordset (DAO or ADO). Here are my tables:

tblWork tblActivity
------- ----------
WorkID(PK) ClientID Date ActivityID(PK) Description
11 100 4/10/06 1 Running
12 100 4/11/06 2 Swimming
13 100 4/13/06 3 Walking

tblHours
--------
WorkID ActivityID Hours
11 1 3
11 2 4
12 1 2
13 1 5
13 3 5

Criteria: capture days 4/10 to 4/13 for a given client
------------------------------
4/10/06 4/11/06 4/12/06 4/13/06
Running 3 2 null 5
Swimming 4 null null null
Walking null null null 5

I've tried crosstabs, but they're not updatable
The only solution I have is to create about 3 queries and build a table based on the queries:
query 1. link and retrieve tblHours-tblWork a client (to check if there was time spent on those days)
query 2. retrieve the tblActivity.Description (Select DISTINCT) for the days and populate a temp table
query 3. populate the temp table with query 1

I've tried to combine these queries into 1 so that it'll be a lot cleaner and easier to maintain but couldn't.

Any ideas for an efficient solution are much appreciated.

 

[Remou]

I cannot quite see why you have tblWork and tblHours. Can these be combined?

tblWorkHours
WorkHoursID - Primary key
ClientID - Foreign key
ActivityID - Foreign key
WorkDate
WorkHours (or From and To)

 

[Remou]

You may like to look at:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

 

[jancheta]

Hi Remou. Thanks for your reply. The reason why those two are separate is that WorkDate is a redundant field. The tables can be combined (denormalized) but that defeats my objective. I feel that a complex SQL statement should do the trick, however, I just can't put it together to acheive the desired result set.

 

[cmmrfrds]

You could create a date table and use that to select your date range. This table would be on the left side of a join to your work table. Then you could take some action (update records etc.) when there is a missing date in your work table.

 

[Golom]

You can do this

SELECT W.WorkID, W.ClientID, W.ActivityDate As [ADate],
       H.Hours, H.ActivityID AS ActID, A.Description

FROM tblActivity AS A INNER JOIN 
     (tblWork AS W INNER JOIN tblHours AS H 
      ON W.WorkID = H.WorkID) 
     ON A.ActivityID = H.ActivityID;

and it is updatable but it produces
[tt]
WorkID ClientID ADate Hours ActID Description
11 100 04/10/2006 3 1 Running
11 100 04/10/2006 4 2 Swimming
12 100 04/11/2006 2 1 Running
13 100 04/13/2006 5 1 Running
13 100 04/13/2006 5 3 Walking
[/tt]
Your desired solution (basically a crosstab) uses field values (specifically the dates) as column names so there's probably no updatable solution of that form.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[jancheta]

Hi Remou. Thanks for providing the link. I'll check it out. The main thing obstacles that I have:
(1) retrieving the info (I have a solution as listed above but I don't think it's efficient)
(2) updating the database

Hi cmmrfrds. Thanks for your post. I understand your suggestion and will give thought to it.

Hi Golom. Thanks for you post and the code. The only thing is presentation to the user (where I would have another or more sqls). Yes, unfortunately crosstabs are not updatable.

---

Maybe if a temp table is created on the server (MySQL) via stored procedure, bind the form to the temp table, then when the user clicks on save, another stored procedure will take it apart (?) or I can take it apart and update the database.
(I don't have experiences with stored procedures... all I know is that it can be ado executed)

---
As I mentioned, I've been toying with my sql, like:
SELECT
tblHours.ActivityID,
Max(IIf([Workid]=11,[hours],Null)) AS Monday,
Max(IIf([workid]=12,[hours],Null)) AS Tuesday
FROM tblHours
GROUP BY tblHours.ActivityID;

SELECT
tblActivity.Description,
Max(IIf([tblHours].[workid]=11,[hours],Null)) AS Mond,
Max(IIf([tblHours].[workid]=12,[hours],Null)) AS Tues
FROM tblActivity INNER JOIN tblHours ON tblActivity. ActivityID = tblHours.ActivityID
GROUP BY tblActivity.Description, tblHours.ActivityID;

WorkID is directly entered and somehow I need to through in the tblWork so I can add the ClientID and WorkDate.