Need help formatting an Access report from a query and SQL help

December 20, 2010 6:58 AM

I'm having trouble formatting a report in Access that ONLY shows checkboxes that are not checked. I probably need some help on the SQL front.

My database is based around a list of tasks. When the person finishes a task, they check it off. I want a report of ONLY the tasks which are incomplete, so I have a query along the lines of this:

SELECT Projects.ProjectID, Projects.ProjectName, Projects.Check01, Projects.Check02, Projects.Check03
FROM Projects
WHERE (((Projects.Check01)=No)) OR (((Projects.Check01)=No)) OR (((Projects.Check01)=No));

This gives me a list of projects for at least one task is not complete. For example, for project "Grocery Shopping," I clipped the coupons but did not make a list or go to the store.

All I want to show on the report is that I still need to make a list and go to the store. So in my report code, I have

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Check01 = False Then
Check01.Visible = True
Else
Check01.Visible = False
End If
If Check02 = False Then
Check02.Visible = True
Else
Check02.Visible = False
End If
If Check03 = False Then
Check03.Visible = True
Else
Check03.Visible = False
End If
End Sub

This gives me a blank line where "clipping coupons" would be, because I've finished that task. Is there a way to eliminate the blank lines? I have 25 tasks per project (this is stable, and all of the tasks are the same for every project) so if half of them are finished, the page looks horrible. I would be fine with almost any kind of list (text file, whatever) as long as it ONLY shows the incomplete tasks in some sort of coherent form. I'm using Access 2002 if it matters, but I'll probably be getting 2007 or 2010 soon.

posted by desjardins to Computers & Internet (4 answers total)

I don't know if you're going to find this helpful, but you might find the whole thing easier if you restructured your data so that it was more normalised. What you could do instead of having all the tasks in separate fields would be to have a table a bit like this

_PROJECT________|__TASK________|_CompletedCheck________
-----------------------------------------------------------
Grocery Shopping__|_Clip Coupons__|_-1
Grocery Shopping__|_Make List_____|_0
Grocery Shopping__|_Go To Store___|_0

When you start a project, you can use a simple append query to add all the tasks.

Then your query can be
SELECT Projects.ProjectID, Projects.ProjectName, Projects.Task
FROM Projects
WHERE (Projects.CompletedCheck = False).



In the report, add the fields then Group By ProjectID, ProjectName.

The reports are much prettier in later versions of Access.
posted by Acheman at 7:25 AM on December 20, 2010

A couple of further tweaks I would suggest would be:

1. Replace the checkbox with a date field. You can then have fun analysing the ways your projects have progressed. The report can still show a checkbox - just add an unbound one since it's only necessary for incomplete tasks.

2. Don't prefill the tasks. Instead, have a table (ProjectID, ProjectName, Task, DateCompleted) that you only make an entry in when the table's completed. If you don't want to record a date, just whether it's been done, it only needs the first three fields. Then have another table Tasks with one field Task in which you have a list of all 25 tasks. Make a query called 'TasksToDo' like this:
SELECT ProjectID, ProjectName, Task
FROM Projects CROSS JOIN Tasks

Then your report query is

SELECT TasksToDo.ProjectID, TasksToDo.ProjectName, TasksToDo.Task
FROM TasksToDo LEFT JOIN Projects ON TasksToDo.ProjectID = Projects.ProjectID
WHERE (Projects.ProjectID Is Null)

From there on in, it's all the same.

3. It might be helpful to have two tables, Projects and CompletedTasks. Projects would be something like (ProjectID, ProjectName, DateStarted, OtherInfo) and CompletedTasks would be (ProjectID, Task, DateCompleted). This would save the endless reduplication of ProjectName, and you could add other information about the project.
posted by Acheman at 7:58 AM on December 20, 2010

Argh, you are so right, and now I have to redo these tables :(

I modified your suggestion and made three tables (all prefaced with TEST for now):

TEST_PROJECTS (ProjectID, ProjectName)
TEST_TASKS (TaskID, Task)
TEST_TASKSCOMPLETE (PersonID, TaskID, Completed)

Here's the SQL for my query, on which the report is based (the report is grouped by project):
SELECT Test_PROJECTS.ProjectName, TEST_TASKS.task
FROM TEST_TASKS INNER JOIN (Test_Projects INNER JOIN TEST_TASKSCOMPLETE ON Test_PROJECTS.ProjectID=Test_TASKSCOMPLETE.PersonID) ON TEST_TASKS.taskID=Test_TASKSCOMPLETE.TaskID
WHERE (((Test_TASKCOMPLETE.Completed)=No));

So the report looks like:

TASKS TO DO
Grocery Shopping

make a list
go to the store

Laundry

buy soap
sort clothes

Which is exactly what I need, so thank you.
posted by desjardins at 8:50 AM on December 20, 2010

"PersonID" in the TEST_TASKSCOMPLETE table and in the second line of the SQL should read "ProjectID"
posted by desjardins at 8:52 AM on December 20, 2010