sqlQuery for daily plans and tasks

[Mario Krsnic]

Hello everybody,
I have a following table structure:
Task (Text), 1(first day, integer), 2(second day,
integer) ...etc. 31 (31.day, integer), StartTime (Data/Time),
Monat(integer),
Jahr(integer)
All tasks, that have the same Start time belong to the same plan.
I should list all plans with their tasks

It should be the result:
Plan for 2th day: . StartTime: 07:00:00
Task 1
Task 2
Task 3
Plan for 2th day: . StartTime: 18:00:00
Task 7
Task 9
Task 11
Plan for 3th day: . StartTime: 07:00:00
Task 1
Task 2
Task 3
and so on...

I tried to solve the problem in this way with DAO and VB6. But maybe there
is some better sql-query for it?

Set rs = db.OpenRecordset("select * from TblTasks where Month= 4 und
ActYear= 2008")
dim Y%
For Y = 2 To 32
rs.MoveFirst
While Not rs.EOF
If rs(Y).Value > 0 Then
Text1 = Text1 & "Plan for " & Y - 2 & " . StartTime: " & rs!StartTime
& vbCrLf
Text1 = Text1 & "Task: " & rs!Task & vbCrLf
End If
rs.MoveNext
Wend
Next Y

I get the following result:

Plan for 2th day: . StartTime: 07:00:00
Task 1
Plan for 2th day: . StartTime: 07:00:00
Task 2
Plan for 2th day: . StartTime: 07:00:00
Task 3

How to list the result without repeting the text "Plan for 2th day: .
StartTime: 07:00:00
"
Is there any better sql-query or other solution?
Thanks in advance.
Mario

 

[strive4peace]

Hi Mario,

I have rewritten your code to include what you asked for

'~~~~~~~~~~~~~~~
dim rs as DAO.Database _
, DAO.RecordSet

dim Y as Integer _
, strSQL as string _
, datLastStartTime as Date

set db = CurrentDb

strSQL = "SELECT * " _
& " FROM TblTasks " _
& " WHERE Month= 4 " _
& " AND ActYear= 2008;"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapShot)

For Y = 2 To 32
rs.MoveFirst
datLastStartTime = -1

Do While Not rs.EOF

If rs(Y).Value > 0 Then

if rs!StartTime <> datLastStartTime then
Text1 = Text1 & "Plan for " _
& Y - 2 & " Day: . StartTime: " _
& rs!StartTime _
& vbCrLf
datLastStartTime = rs!StartTime
end if

Text1 = Text1 & "Task: " & rs!Task & vbCrLf

End If

rs.MoveNext

Loop
Next Y

'close (if applicable) and release object variables
rs.close
set rs = nothing
set db = nothing

'~~~~~~~~~~~~~~~

BUT!

you should NOT begin fieldnames with a number

you should not make a field for each day, instead you should have a
different record for each day and record a date, not a day number

read this:

Access Basics on Access MVP site
http://www.accessmvp.com/Strive4Peace/Index.htm
8-part free tutorial that covers essentials in Access

~~~~~~~~~~~~~
"Is there any better sql-query or other solution?"

there are better solutions -- but the best thing you can do is
restructure your data to something like this:

Tasks
- TaskID, autonumber
- Task, text

Work
- WorkID, autonumber
- TaskID, long integer, FK to Tasks
- WorkDate, date
- Hours, integer

Then, you can make a report to show the data as you wish and use sorting
and Grouping

FK is Foreign Key

Warm Regards,
Crystal

*
have an awesome day
*