I created a View that makes a table showing a document's file name, revision variable, document ID, project ID, cleaning variable, revision number, and part number variable.  However, there seems to be a row for each value of these variables.

Here's my View:

SELECT     dbo.Documents.Filename, VariableValue_1.ValueText AS [DRW Rev], dbo.Documents.DocumentID, dbo.DocumentsInProjects.ProjectID,
                      VariableValue_2.ValueText AS [Cleaning Required], VariableValue_1.RevisionNo, dbo.VariableValue.ValueText AS [Part Number]
FROM         dbo.Documents INNER JOIN
                      dbo.VariableValue ON dbo.Documents.DocumentID = dbo.VariableValue.DocumentID INNER JOIN
                      dbo.Variable ON dbo.VariableValue.VariableID = dbo.Variable.VariableID INNER JOIN
                      dbo.VariableValue AS VariableValue_1 ON dbo.Documents.DocumentID = VariableValue_1.DocumentID INNER JOIN
                      dbo.Variable AS Variable_1 ON VariableValue_1.VariableID = Variable_1.VariableID INNER JOIN
                      dbo.VariableValue AS VariableValue_2 ON dbo.Documents.DocumentID = VariableValue_2.DocumentID INNER JOIN
                      dbo.Variable AS Variable_2 ON VariableValue_2.VariableID = Variable_2.VariableID INNER JOIN
                      dbo.DocumentsInProjects ON dbo.Documents.DocumentID = dbo.DocumentsInProjects.DocumentID
WHERE     (dbo.Documents.Filename LIKE '%.slddrw') AND (dbo.Variable.VariableName LIKE 'Part Number') AND (dbo.VariableValue.ValueText LIKE 'RBT-%') AND
                      (Variable_1.VariableName LIKE 'Revision') AND (Variable_2.VariableName LIKE 'Cleaning Required') AND (dbo.Documents.Deleted = 0) AND
                      (dbo.Documents.Shared <> 0) AND (dbo.Documents.DocTypeID = 1) AND (NOT (VariableValue_1.ValueText LIKE '')) AND (NOT (VariableValue_2.ValueText LIKE '')) AND
                      (NOT (dbo.VariableValue.ValueText LIKE ''))

Here is the query that uses it:

select * from dbo.drawingbypartno
where [Part Number] like '%-00B876%'
order by RevisionNo desc

Here are my results:

What I want to have is something that shows one line for each RevisionNo and it should be tied to the corresponding values for the file that is at that revision. In the image above, the correct line for RevionNo 12 is line 5. Looking into SQL commands, I think I need to use the UNION command, but I can't quite figure out how to format it.

Any help would be appreciated.

Thank you,

Tara