Query an entire row within a Table produced by a CrossTab Query

[KP_SoCal]

I would like to query each entire row in Table1 as opposed to a column for wherever Field2, Field3, or Field4 contain XYZ. The results would be displayed something like Table2. "YES" if row contains "XYZ" in Table1 and "NO" if it does not contain "XYZ" under any of the fields for that particular row. Does this make sense?



Table1
Field 1 ---------Field2----- Field3-----Field4
Product1-------XYZ---------JKL---------EFG
Product2-------JKL----------ABC-------LMN
Product3-------QRS--------DEF--------JKL
Product4-------ABC--------XYZ--------QRS

Table2 (Results)
Field 1 ---------Field2
Product1-------YES
Product2-------YES
Product3-------YES
Product4-------NO

(sorry, I tried posting this as an image before, but it didn't work)

 

[raskew]

Hi -

If your question is an add-on to this post, try adding a calculated field to a query:

Expr1: IIf(InStr([Fld2],"XYZ")>0 Or InStr([Fld3],"XYZ")>0 Or InStr([Fld4],"XYZ")>0,"Yes","No")

HTH - Bob

 

[KP_SoCal]

Hi -

If your question is an add-on to this post, try adding a calculated field to a query:

Expr1: IIf(InStr([Fld2],"XYZ")>0 Or InStr([Fld3],"XYZ")>0 Or InStr([Fld4],"XYZ")>0,"Yes","No")

HTH - Bob

Hi Bob, thanks for answering my post. I just realized my image wasn't displaying so I typed in the illustration of what I was trying to accomplish. That may give some more clarity.

What I'm looking to accomplish is a little bit more complex. I could easily accomplish it with a UNION query like this:

Select Field1 as [B]Product[/B], Field2 as [B]ProdCode[/B]
From Table1
UNION ALL
Select Field1 as [B]Product[/B], Field3 as [B]ProdCode[/B]
From Table1
UNION ALL
Select Field1 as [B]Product[/B], Field4 as [B]ProdCode[/B]
From Table1

Now I could query off of the ProdCode field from the above union query. The reason why I can't use the nest IIF statement is because I have over 100 fields in Table1. That's why Table1 was created by means of a CrossTable Query. I'm trying to avoid having to create over 100 union queries to accomplish this, but I completely understand if this is my only option. Sorry if this is coming across somewhat convaluted.

Thanks again for your help.

 

[raskew]

Hi -

I'm confused.

The reason why I can't use the nest IIF statement is because I have over 100 fields in Table1.

Is that true? Do you perhaps mean over 100 records.

Could you provide an sample of Table1. If so, please save as an .mdb (pre-A2007)

Bob

 

[KP_SoCal]

I know it sounds bizarre, but the actual table I'm working with has over 100 field names. Table1 in the attached database has 100 field names with field1 as the primary key. What I would like to accomplish is: Show all products that have Option Codes in '1234' and '1111' (the number of variables in this example are only two, but in some cases I would use more or less depending how many option codes I'm searching).

I've search all over the web to see if there is anyway to query rows as opposed to columns and I haven't found anything. The only way I know to achieve the end results for the problem above is to create a UnionQuery and query it's results. This sounds confusing, but if you look at my database queries it will make sense. Writing a bunch of union queries isn't the end of the world, I was just curious if their was another way around it that might be faster...even if it means creating a function inside a module.

 

[gemma-the-husky]

have a function that takes as input the various fields you want to check. return a value of true or false based on whatever values you test.

you may need to understand the nz() function to handle any NULL values, for best results

 

[raskew]

Hi -

Try this:

1) Create Table2 with two fields: Product, OptionCode

2) Copy the below code to a standard module, make sure the module and function names are not duplicates.

3) Call the function from the debug/immediate window, i.e.
? TransposeRecordset("Table1", "Table2", "Product")

4) Open Table2 to view results

5) Build your queries based on Table2

Function TransposeRecordset(pstrrecoriginal As String, pstrrecnew As String, pstrkey As String)

    Dim db          As Database
    Dim recorg      As Recordset
    Dim recnew      As Recordset
    Dim intCount    As Integer
    Dim varkeyvalue As Variant
    Dim bolfound    As Boolean
    
    Set db = CurrentDb()
    
    Set recorg = db.OpenRecordset("select * from [" & pstrrecoriginal & "]")
    Set recnew = db.OpenRecordset("select * from [" & pstrrecnew & "]")
    
    'Loop through records in recorginal
    While Not recorg.EOF
                    
        intCount = 0
        bolfound = False
        
        'Loop through fields in recorginal looking for key
        While intCount <= recorg.Fields.Count - 1 And bolfound = False
        
            If recorg(intCount).name = pstrkey Then
            
                varkeyvalue = recorg(intCount)
                bolfound = True
                docmd.Echo True, "Transposing " & varkeyvalue
            End If
            
            intCount = intCount + 1
            
        Wend
        
        For intCount = 0 To recorg.Fields.Count - 1
        
            'skip key field
            If recorg(intCount).name <> pstrkey Then
                recnew.AddNew
                recnew(0) = varkeyvalue
                
                recnew(1) = Nz(recorg(intCount).Value, "")
                recnew.Update
            End If
                
                
        Next
    
        recorg.MoveNext
        
    Wend
    docmd.Echo True, ""
End Function

HTH - Bob

 

[KP_SoCal]

Bob, wow! This gets me sooooo close to the expected result but comes up slightly short. It transposes all the product names in Field1 from Table1 to the OptionCode field in Table2. Field1 product names should be transposed to the Product field in Table2.

I attached the database so you can see what I mean. Also I created a table entitled ExpectedResults to better demonstrate the results I would need the function to return to Table2.

Thanks again for all your help on this!!!

 

[Poppa Smurf]

The attached will give the expected result.
This reads the source data into an array, then each column of the array is written as a record to the output.

column 0 of each row in the array is the product name
a for loop is used to cycle through each column of the array to give you
product name value in column 1 which is field2 in your original table
product name value in column 2 which is field3 in your original table

the output table looks like this
Widget1 1253
Widget1 3843

 

[raskew]

OK. Very strange although I see what you're saying (worked perfectly in my tests) Do the following:

1) In table design for table2, ensured that Indexed is set to No for each of the two fields.

2) Change the function call to:
? TransposeRecordset("Table1", "Table2", "Field1")

Bob

 

[KP_SoCal]

Bob, you nailed it! Thanks so much!!!

Allan, I tested your recommendation and it worked as well! Cheers!!!

 

[KP_SoCal]

Bob, if you are still out there. I have been using the code you developed for the past couple of months and it works great! For everyone's reference, I reposted it below. However, is it possible to only transpose records that meet a certain criteria?

For instance, is there a way to include a criteria that would only transpose values under the "FullVIN" field that have specific "OptionCode" values? If I were to write this in SQL, the where statement would look like,

WHERE Left([OptionCode],3)='816' OR OptionCode='929KA1';

Anyway to incorporate that in the code below so that only the values under "FullVin" that meet this criteria would be transposed? Thanks for any suggestions!

Option Compare Database
Option Explicit

Public Function RunDataInfo()
Call TransposeRecordset("tblBreakDown", "tblFinal", "FullVIN")
MsgBox "Data is transposed", vbInformation + vbOKOnly
End Function

Private Function TransposeRecordset(pstrrecoriginal As String, pstrrecnew As String, pstrkey As String)

    Dim db          As Database
    Dim recorg      As Recordset
    Dim recnew      As Recordset
    Dim intCount    As Integer
    Dim varkeyvalue As Variant
    Dim bolfound    As Boolean
    
    Set db = CurrentDb()
    
    Set recorg = db.OpenRecordset("select * from [" & pstrrecoriginal & "]")
    Set recnew = db.OpenRecordset("select * from [" & pstrrecnew & "]")
    
    'Loop through records in recorginal
    While Not recorg.EOF
                    
        intCount = 0
        bolfound = False
        
        'Loop through fields in recorginal looking for key
        While intCount <= recorg.Fields.Count - 1 And bolfound = False
        
            If recorg(intCount).Name = pstrkey Then
            
                varkeyvalue = recorg(intCount)
                bolfound = True
                DoCmd.Echo True, "Transposing " & varkeyvalue
            End If
            
            intCount = intCount + 1
            
        Wend
        
        For intCount = 0 To recorg.Fields.Count - 1
        
            'skip key field
            If recorg(intCount).Name <> pstrkey Then
                recnew.AddNew
                recnew(0) = varkeyvalue
                
                recnew(1) = Nz(recorg(intCount).Value, "")
                recnew.Update
            End If
                
                
        Next
    
        recorg.MoveNext
        
    Wend
    DoCmd.Echo True, ""
End Function