list details horizontally

Ziggy1 · Aug 15, 2004

I trying to create a crosstab query that will list out my location details horizontally. Each Item can have many locations, I only trying the crosstab because that is what I think I need?

My results are listing every possible location accross as column headings and if a particular item has that location the location will be repeated there.

I want this to apear in a report but there are too many columns and I would want the locations to be seperated by comma's for each record

SQL:

TRANSFORM Var(IC10_Product.ID) AS VarOfID
SELECT IC10_Product.[ITEM CODE], IC10_Product.[LOT IDENT], Var(IC10_Product.ID) AS [Total Of ID]
FROM IC10_Product
GROUP BY IC10_Product.[ITEM CODE], IC10_Product.[LOT IDENT]
PIVOT IC10_Product.Location;

Thanks for any help

Uncle Gizmo · Aug 16, 2004

The attached DB contains a list of items that have been returned for repair. As the items are booked in, they are examined and the list of repairs is added to. So each repair required, appears as a separate entry in the repair log table.

However it is difficult to display the results on a form efficiently. If there are ten or twenty repairs identified and listed then that number of rows will be needed in a normal report, therefore the report could run to many pages. To reduce space and make the report look nice and tidy I wrote a function which concentrates the repairs from a table into a string which is used in the report.

raskew · Aug 16, 2004

I'm working strictly with A97 today so if the following duplicates Uncle Gizmo's response, my apoligies. Having said that, try pasting the following into a new module in Northwind. Then follow the instructions for invoking the procedure.

Code:

Public Sub CustomersAndCategories(Optional Sdate As Date = #8/1/94#)
'*******************************************
're:        [url]http://www.access-programmers.co.uk/forums/showthread.php?t=71302[/url]
'Purpose:   Create/populate table with customer names and a comma-delimited
'           list of product categories purchased between #1-Aug-94# and
'           #1-Jan-95# (default)
'Coded by:  raskew
'Inputs:    from debug window:
'           call CustomersAndCategories
'Output:    tblCustAndCats
'*******************************************

Dim db      As DATABASE
Dim rs      As Recordset
Dim rs2     As Recordset
Dim dteHold As Date
Dim i       As Integer
Dim n       As Integer
Dim pTable  As String
Dim strCust As String
Dim strHold As String
Dim strSQL  As String


    Set db = CurrentDb
    pTable = "tblCustAndCats"
    'Step 1) Prepare temp table
    On Error Resume Next
    With db
        .Execute "DROP TABLE " & pTable & ";"
        .Execute "CREATE TABLE " & pTable & " ( Customer TEXT (50)," _
                 & " Categories TEXT (254) );"
    End With
    db.TableDefs.Refresh
    
    Set rs2 = db.OpenRecordset(pTable)
    
    dteHold = IIf(IsMissing(Sdate), #8/1/94#, Sdate)
    strSQL = "SELECT DISTINCT Customers.CompanyName, Categories.CategoryName" _
        & " FROM (Categories RIGHT JOIN ((Orders LEFT JOIN [Order Details]" _
        & " ON Orders.OrderID = [Order Details].OrderID)" _
        & " LEFT JOIN Products ON [Order Details].ProductID = Products.ProductID)" _
        & " ON Categories.CategoryID = Products.CategoryID)" _
        & " LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID" _
        & " WHERE (((Orders.OrderDate) Between #" & dteHold & "# And #" & DateAdd("m", 5, dteHold) & "#))" _
        & " GROUP BY Customers.CompanyName, Categories.CategoryName" _
        & " HAVING ((Not (Categories.CategoryName) Is Null))" _
        & " ORDER BY Customers.CompanyName, Categories.CategoryName;"
    
    Set rs = db.OpenRecordset(strSQL)
    rs.MoveLast
    n = rs.RecordCount
    rs.MoveFirst
    For i = 1 To n
       strCust = rs!CompanyName
       strHold = ""
       Do While rs!CompanyName = strCust
          strHold = strHold & rs!CategoryName & ", "
          rs.MoveNext
          i = i + 1
          If rs.EOF Then Exit Do
       Loop
       strHold = Left(Trim(strHold), Len(Trim(strHold)) - 1)
       With rs2
          .AddNew
          !Customer = strCust
          !Categories = strHold
          .Update
       End With
    Next i
    rs.Close
    rs2.Close
    db.Close
    Set db = Nothing
     
    docmd.OpenTable "tblCustAndCats", acViewNormal, acReadOnly

End Sub

HTH - Bob

Ziggy1 · Aug 16, 2004

That's perfect, I'm going to take some time to fiddle around with both your responses so I understand how it works.

thanks a lot

Ziggy

Ziggy1 · Aug 17, 2004

Hi again,

I'm having difficulty relating to the design, your example uses 3 tables but I am looking at just the one (below). Each item has many locations, so can you show me how the code would look with just one table?

tblLocationDetail

Location
ITEM CODE
LOT IDENT

Thanks

Ziggy

Uncle Gizmo · Oct 15, 2004

Sorry, I didn't realize my example have three tables in it till I just had a look!

Could you post a copy of your DB for me to have a look at, as what I usually do is create the queries in the query builder and then copy the SQL into the module. So the best way to do this would be to have a copy of the actual database.

list details horizontally

Ziggy1

Registered User.

Uncle Gizmo

Nifty Access Guy

Attachments

raskew

AWF VIP

Ziggy1

Registered User.

Ziggy1

Registered User.

Uncle Gizmo

Nifty Access Guy

Similar threads

Users who are viewing this thread