Limiting fields in report - but unique situation

[russanalyst]

Hi.

I have a query which looks in a table having 13 fileds. One field is a client ID, the others contain text.

I need to have the user enter the ID and then the value that they seek in a given field. And, have only the ID and that one field with that specific value show.

I made a query with parameters for the ID and field value. And put each in the criteria section for each field. BUt what happens is that I get the correct record, just all of the values for that ID's record. (And only one field per record can have that value.)

Ideas?

Russ

 

[dhookom]

It seems your table structure might be un-normalized. I could be wrong. One solution would be to normalize this with a union query:

SELECT ID, Field2 As TheText , 2 As FieldNum
FROM tblWithNoName
UNION ALL
SELECT ID, Field3, 3
FROM tblWithNoName
WHERE Field3 Is Not Null
UNION ALL
SELECT ID, Field4, 4
FROM tblWithNoName
WHERE Field4 Is Not Null
UNION ALL
-- etc --
SELECT ID, Field12, 12
FROM tblWithNoName
WHERE Field12 Is Not Null;

Then search for your value from the union query.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[russanalyst]

Thank you for your response. - And these tables were 'inherited'.

Anyway, i ha dnot mentioned taht I have approx. 20 tables to do this for. I had hoped to us ea maina nd then sub-reports.

Is there another way, than what you suggested, to avoid having to do the manual coding for 20x13=260 fields?

Russ

 

[dhookom]

You could write DAO code to build your union query:

Function CreateUnion(strTableName As String, _
        strPKField As String) As String
    Dim db As dao.Database
    Dim td As dao.TableDef
    Dim fd As dao.Field
    Dim strSQL As String
    Dim intFieldNum As Integer
    Set db = CurrentDb
    Set td = db.TableDefs(strTableName)
    For Each fd In td.Fields
        If fd.Name <> strPKField Then
            intFieldNum = intFieldNum + 1
            If Len(strSQL) = 0 Then
                
                strSQL = strSQL & "SELECT [" & strPKField & "], [" & _
                fd.Name & "] as Fld, " & intFieldNum & " As RecNum " & vbCrLf & _
                "FROM [" & strTableName & "] " & vbCrLf & _
                "WHERE [" & fd.Name & "] Is Not Null " & vbCrLf
             Else
                strSQL = strSQL & "UNION ALL " & vbCrLf
                strSQL = strSQL & "SELECT [" & strPKField & "], [" & _
                    fd.Name & "], " & intFieldNum & vbCrLf & _
                    "FROM [" & strTableName & "] " & vbCrLf & _
                    "WHERE [" & fd.Name & "] Is Not Null " & vbCrLf
           End If
        End If
    Next
    CreateUnion = strSQL & ";"
    Set fd = Nothing
    Set td = Nothing
    Set db = Nothing
    
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]