Find solution
How to make only ONE Sql query?

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

  • 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.

  • Answer:

    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.

desjardins at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

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.

Acheman

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 Shoppingmake a list go to the storeLaundrybuy soap sort clothesWhich is exactly what I need, so thank you.

desjardins

"PersonID" in the TEST_TASKSCOMPLETE table and in the second line of the SQL should read "ProjectID"

desjardins

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.