Group and transpose

[micang]

Access 2003

Hi All,

I have a table with the following data:

Color, Make, Doors, EnCC, BType, Model, MakeCode,ModelCode
Red, BMW, 5 , 1998, Sal, 320i , DG ,445
Red, BMW, 3 , 1998, Sal, 320i , DG ,445
Green, Ford,5 , 1598, Hat, Fiesta, F2, ,125
etc etc

What I would like to do is this:

Where the complete row is distinct\unique, leave as is.
Where there are 2 or more make and model codes that are equal, then "group" those rows together: by this I mean the above would be:

Color, Make, Doors, EnCC, BType, Model, MakeCode,ModelCode
Red, BMW, 5 , 1998, Sal, 320i , DG ,445
null, null 3 , null, null, null , null ,null


Then from this I need to create:
Color, Make, Doors, EnCC, BType, Model, MakeCode,ModelCode
Red, BMW, 5/3, 1998, Sal, 320i, DG, 445

So basically, wherever in the data there is a make and model code that is not unique, I need to create the above for them.

The duplicate make and model codes can be anything from 2 to 6 or so (on average).

I don't know where to start...!

As usual, all info is appreciated.

Thanks
Michael

 

[PHV]

Have a look here:
faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[micang]

Thank you PH, appreciated.

Michael

 

[micang]

Hi All again,

I been trying for hours to get the above to work, but with no success. I am not understanding how to apply the function. Could anyone provide an example how it works?

I am trying to apply it to a single table:

Color, Make, Doors, EnCC, BType, Model, MakeCode,ModelCode
Red, BMW, 5 , 1998, Sal, 320i , DG ,445
Red, BMW, 3 , 1998, Sal, 320i , DG ,445
Green, Ford,5 , 1598, Hat, Fiesta, F2, ,125

to get:

Color, Make, Doors, EnCC, BType, Model, MakeCode,ModelCode
Red, BMW, 5/3, 1998, Sal, 320i, DG, 445
Green, Ford,5 , 1598, Hat, Fiesta, F2, ,125

I have not got a primary key in the table, but one can be put in if need be.

Also, the fields that identify if the records need to be concatenated are MakeCode and ModelCode - whereever there is 2 or more, then concatentation needs to happen (for 4 columns in the record).

Any further help really appreciated - I just can't get it to work, but I am sure this will do exactly what I need.

Many thanks.

Michael

 

[dhookom]

How would you know which Doors values match with which Colors and EnCC etc?
To concatenate the Doors assuming MakeCode is text and ModelCode is numeric:

Concatenate("SELECT DISTINCT Doors FROM tblSingle WHERE MakeCode ='" & MakeCode & "' AND ModelCode =" & ModelCode,"/")

Concatenating other attributes would be similar.



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]

 

[micang]

Thank you Duane for the input, this is proving harder than I thought! I am going to give it a try and post back.

Thanks for the help

Michael

 

[micang]

Hi All,

I found some other code that does what I need. I am posting it in case someone else can make use of it.

It work's fine, but again someone could maybe assist: It won't allow nulls, which makes sense, but let's say I want it concatenate even with nulls, I think I need to use NZ, but I am not sure where/ how to use it in the code.

Thanks for all the help.

Public Function FixTable() As Boolean
On Error Resume Next

Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
Dim strColumn1 As String, strColumn2 As String

Set db = CurrentDb()
Call RecreateTables(db)


sSQL = "SELECT Column1, Column2 FROM tblOriginal " _
       & "ORDER BY Column1, Column2 ASC"
Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

If Not rst.BOF And Not rst.EOF Then
  rst.MoveFirst
  strColumn1 = rst!Column1
  strColumn2 = rst!Column2
  
  rst.MoveNext
  Do Until rst.EOF
    If strColumn1 = rst!Column1 Then
      strColumn2 = strColumn2 & ", " & rst!Column2
    Else
      sSQL = "INSERT INTO tblCopy (Column1, Column2)  " _
           & "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
      db.Execute sSQL
      strColumn1 = rst!Column1
      strColumn2 = rst!Column2
    End If
    rst.MoveNext
  Loop
  
  ' Insert Last Record
  sSQL = "INSERT INTO tblCopy (Column1, Column2)  " _
       & "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
  db.Execute sSQL
End If

Set rst = Nothing
Set db = Nothing


End Function

Michael

 

[dhookom]

I'm not sure what your code results are but it doesn't seem to concatenate multiple values into a single string with "/" between the values.

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]

 

[micang]

Hi Duan,

This bit here, is where I set the "/" (the sample has ",")

    If strColumn1 = rst!Column1 Then
      strColumn2 = strColumn2 & ", " & rst!Column2

As for the nulls, on my home pc running Access 2007, it does not allow nulls, but at work on Access 2003, it does. But leaving in nulls/blanks is incorrect as then you will get things like "//5/3" etc.

Thanks

Michael

 

[dhookom]

Looking at the code again, it seems to be similar to my generic concatenate function without the "generic".

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]

 

[micang]

I agree, I am going to run both, get the results, and compare.

Then post back.

Many thanks.

Michael