Limit Query Results

[ocampod]

Hello,



I created a small project tracking database for our department. It has the following structure:

1. Master Project Table - lists and contains information about the project
2. Project Log - items associated to the project (one-to-many relationship)
3. Action Items - action items related to any project log (one-to-many relationship)

I can create a query and generate a report just fine. However, my problem is in the presentation of the data.

What I am trying to generate is a report that shows the project ID (found in the Master Project Table), the associated Project Logs and the associated Action Items.

My main problem, however, is that when I do that. I get redundant rows in my report which stems from the query. Obviously, this isn't pretty and I would like to find a way to group it without showing duplicate rows.

Any help?

Attached is an example of my issue.

[ajetrumpet]

My main problem, however, is that when I do that. I get redundant rows in my report which stems from the query. Obviously, this isn't pretty and I would like to find a way to group it without showing duplicate rows.

I don't follow your post completely, but if you're looking to eliminate duplicate rows from a dataset, this function might be useful to you:

Function DeleteDups(tblName As String)

On Error Resume Next

'******************************************************************************
'                                                                             *
'Author: Adam Evanovich                                                       *
'Date: 11/11/2010                                                             *
'Purpose: Deletes duplicate records in a single table.                        *
'                                                                             *
'Arguments:                                                                   *
'tblName > Your table.                                                        *
'                                                                             *
'******************************************************************************

Dim tempTbl As String
Dim tempSql As String
Dim db As DAO.Database
Set db = CurrentDb
tempTbl = db.TableDefs(tblName).Name

   If err.Number = 3265 Then
      MsgBox "Table does not exist!"
         GoTo Exit_Handle
   Else
      tempTbl = tblName & "2"
   End If

On Error GoTo Err_Handle

   tempSql = "SELECT DISTINCT *" & _
            " INTO " & tempTbl & _
            " FROM " & tblName

      db.Execute tempSql, dbFailOnError
      db.TableDefs.Delete (tblName)
      DoCmd.Rename tblName, acTable, tempTbl
      db.TableDefs.Refresh

Exit_Handle:
   db.Close
      Set db = Nothing
         Exit Function

Err_Handle:
   MsgBox err.Description
      Resume Exit_Handle

End Function '//LL

[ssanfu]

Do you know about the keywords "Distinct" and "DistinctRow" when creating a select query? You have to type it in when in SQL view of the query.

Look in help or google "SELECT DISTINCT" and "SELECT DISTINCTROW".

[ocampod]

Hi Adam and Steve,

Thanks for your help. I am attaching a screenshot of my issue.

• The first column has the Project ID from the Master Project Table
• The second column has the Project Phase from the Master Project Table
• The third column has the Project Status from the Master Project Table
• The fourth column has Project Log item from the Project Log Table and is associated to the Master Project Table (one Project has many Project Logs)
• The fifth column has the Project Log Description from the Project Log Table
• The sixth column has the Action Item from the Action Item Table and is associated to the Project Log Table (one Project Log can have multiple Action Items)
• The seventh column has the Action Item Description

My problem, however, is that when I create a report based on this query, I get this exact result when I am looking for more of cascaded format (please see the "Reporting Ideal" image).

I tried tweaking it within the report itself, but I figure the problem is within my query.

[ssanfu]

The query looks fine.

....... I am looking for more of cascaded format (please see the "Reporting Ideal" image).

For reports, you need to use "Sorting and Grouping" under the VIEW menu.

You can select the order of the fields to sort by. For each field in the grid, you can select if you want a group header (set Group Header to "YES"). Then in each group detail, put the fields you want to see in that group detail.

[ocampod]

Thanks for all the support guys! I was able to manipulate the data per your feedback.