Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: Pulling data from table to query in one line....

    I'm hoping this is simple but I've been working on it so long that I can't see straight any longer. I'm guessing this is something all of you have experienced as well.

    Here is my table:

    teCode.....teClient...........Mindate...........Ma xdate
    1...........ABC...........08-Nov-02...........08-Nov-02
    2...........ABC...........18-Jul-02...........28-Feb-03
    3...........ABC...........06-Aug-02...........07-Feb-03
    4...........ABC...........04-Oct-02...........04-Feb-03
    5...........ABC...........23-Aug-02...........14-Jan-03
    6...........ABC...........24-Dec-02...........31-Jan-03
    7...........ABC...........22-Jul-02...........21-Mar-03
    1...........IBM...........08-Nov-02...........08-Nov-02
    2...........IBM...........18-Jul-02...........28-Feb-03
    3...........IBM...........06-Aug-02...........07-Feb-03
    4...........IBM...........04-Oct-02...........04-Feb-03
    5...........IBM...........23-Aug-02...........14-Jan-03
    6...........IBM...........24-Dec-02...........31-Jan-03
    7...........IBM...........22-Jul-02...........21-Mar-03

    My goal:

    A query that can show the following on a single line for each company:

    teClient...........1Mindate...........1Maxdate.... .2MinDate....2MaxDate...
    ABC................08-Nov-02.........08-Nov-02...18-Jul-02....28-Feb-03
    IBM.................08-Nov-02.........08-Nov-02...18-Jul-02....28-Feb-03

    (I will include all teCodes across the query results but only showed 2 teCodes here) I cannot use a crosstab query.

    Help!

    Thanks,

    Norm

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Pulling data from table to query in one line....

    Originally posted by norm801
    I'm hoping this is simple but I've been working on it so long that I can't see straight any longer. I'm guessing this is something all of you have experienced as well.

    Here is my table:

    teCode.....teClient...........Mindate...........Ma xdate
    1...........ABC...........08-Nov-02...........08-Nov-02
    2...........ABC...........18-Jul-02...........28-Feb-03
    3...........ABC...........06-Aug-02...........07-Feb-03
    4...........ABC...........04-Oct-02...........04-Feb-03
    5...........ABC...........23-Aug-02...........14-Jan-03
    6...........ABC...........24-Dec-02...........31-Jan-03
    7...........ABC...........22-Jul-02...........21-Mar-03
    1...........IBM...........08-Nov-02...........08-Nov-02
    2...........IBM...........18-Jul-02...........28-Feb-03
    3...........IBM...........06-Aug-02...........07-Feb-03
    4...........IBM...........04-Oct-02...........04-Feb-03
    5...........IBM...........23-Aug-02...........14-Jan-03
    6...........IBM...........24-Dec-02...........31-Jan-03
    7...........IBM...........22-Jul-02...........21-Mar-03

    My goal:

    A query that can show the following on a single line for each company:

    teClient...........1Mindate...........1Maxdate.... .2MinDate....2MaxDate...
    ABC................08-Nov-02.........08-Nov-02...18-Jul-02....28-Feb-03
    IBM.................08-Nov-02.........08-Nov-02...18-Jul-02....28-Feb-03

    (I will include all teCodes across the query results but only showed 2 teCodes here) I cannot use a crosstab query.

    Help!

    Thanks,

    Norm
    How are you gonna handle a gap? A missing date? Is there a max # of dates? Access only allows a max of 255 columns ...

  3. #3
    Join Date
    Jan 2004
    Posts
    100

    Re: Pulling data from table to query in one line....

    Originally posted by M Owen
    How are you gonna handle a gap? A missing date? Is there a max # of dates? Access only allows a max of 255 columns ...
    If there is no date then a zero or blank will suffice. The number of date columns will only be 14 (1 max and 1 min for 7 different teCodes).

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Pulling data from table to query in one line....

    Originally posted by norm801
    If there is no date then a zero or blank will suffice. The number of date columns will only be 14 (1 max and 1 min for 7 different teCodes).
    Well then here is what I do (AND it involves coding):

    Create a "template" table with the columns defined.
    Write a query pulling the records.
    Iterate thru them populating each row ...

    Want an example?

  5. #5
    Join Date
    Jan 2004
    Posts
    100

    Re: Pulling data from table to query in one line....

    Originally posted by M Owen
    Well then here is what I do (AND it involves coding):

    Create a "template" table with the columns defined.
    Write a query pulling the records.
    Iterate thru them populating each row ...

    Want an example?
    I would love one......! (The table I supplied was actually a template table created from a different table)

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Pulling data from table to query in one line....

    Originally posted by norm801
    I would love one......! (The table I supplied was actually a template table created from a different table)
    Ok ... This is a doozy:

    Code:
    Function RetrieveBillingDetail() As Integer
        On Error GoTo Err_RBD
        
        Dim BillMonth As Integer, lcv As Integer
        Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
        Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
        FormsConnect.CursorLocation = adUseClient
        FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        ' Clear any previous report values
        SQLString = "DELETE FROM " & RptTbl & ";"
        FormsConnect.Execute SQLString, , adCmdText
        
        ' Retrieve the Dacor Billings
        SQLString = "SELECT [Work Orders II].ContactID, CustomerContacts.[First name] & iif(CustomerContacts.MI & '' <> '', ' ' & CustomerContacts.MI & '. ',' ') & CustomerContacts.[Last name] AS CustomerName, [WO Billing Detail].LaborCharged, [WO Billing Detail].PurchasesCharged, [WO Billing Detail].[Billed Cost], [WO Billing Detail].[Billing Date]"
        SQLString = SQLString & " FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN [WO Billing Detail] ON [Work Orders II].WorkOrderNumber = [WO Billing Detail].WorkOrderNumber"
        SQLString = SQLString & " WHERE (CInt(DatePart('yyyy',[WO Billing Detail].[Billing Date]))=" & RptYear & ");"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, FormsConnect
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' Contact ID
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Customer Name
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value
                ' Zero out the record ...
                For lcv = 2 To 49
                    TrgRecSet.Fields(lcv).Value = 0
                Next
                
                BillMonth = DatePart("m", MyRecSet.Fields(5).Value)
                ' Labor Amount
                If MyRecSet.Fields(2).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 1).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(14).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(15).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(16).Value = MyRecSet.Fields(2).Value
                    TrgRecSet.Fields(17).Value = MyRecSet.Fields(2).Value
                End If
                
                ' Purchases Amount
                If MyRecSet.Fields(3).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 17).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(30).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(31).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(32).Value = MyRecSet.Fields(3).Value
                    TrgRecSet.Fields(33).Value = MyRecSet.Fields(3).Value
                End If
                
                ' Total Billed
                If MyRecSet.Fields(4).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 33).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(46).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(47).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(48).Value = MyRecSet.Fields(4).Value
                    TrgRecSet.Fields(49).Value = MyRecSet.Fields(4).Value
                End If
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        
        ' Retrieve the Cash Payments
    'SELECT [Work Orders II].ContactID, CustomerContacts.[First name], CustomerContacts.MI, CustomerContacts.[Last name], Payments.LaborPortion, Payments.PurchasesPortion, Payments.PaymentAmount, Payments.ReceivedDate
    'FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN Payments ON [Work Orders II].WorkOrderNumber = Payments.WorkOrderNumber;
        SQLString = "SELECT [Work Orders II].ContactID, CustomerContacts.[First name] & iif(CustomerContacts.MI & '' <> '', ' ' & CustomerContacts.MI & '. ',' ') & CustomerContacts.[Last name] AS CustomerName, Payments.LaborPortion, Payments.PurchasesPortion, Payments.PaymentAmount, Payments.ReceivedDate"
        SQLString = SQLString & " FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN Payments ON [Work Orders II].WorkOrderNumber = Payments.WorkOrderNumber"
        SQLString = SQLString & " WHERE (CInt(DatePart('yyyy',Payments.ReceivedDate))=" & RptYear & ");"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, FormsConnect
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' Contact ID
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Customer Name
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value
                ' Zero out the record ...
                For lcv = 2 To 49
                    TrgRecSet.Fields(lcv).Value = 0
                Next
                
                BillMonth = DatePart("m", MyRecSet.Fields(5).Value)
                ' Labor Amount
                If MyRecSet.Fields(2).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 1).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(14).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(15).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(16).Value = MyRecSet.Fields(2).Value
                    TrgRecSet.Fields(17).Value = MyRecSet.Fields(2).Value
                End If
                
                ' Purchases Amount
                If MyRecSet.Fields(3).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 17).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(30).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(31).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(32).Value = MyRecSet.Fields(3).Value
                    TrgRecSet.Fields(33).Value = MyRecSet.Fields(3).Value
                End If
                
                ' Total Billed
                If MyRecSet.Fields(4).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 33).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(46).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(47).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(48).Value = MyRecSet.Fields(4).Value
                    TrgRecSet.Fields(49).Value = MyRecSet.Fields(4).Value
                End If
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        FormsConnect.Close
        Set FormsConnect = Nothing
        Set TrgRecSet = Nothing
        LoadingDelay
        
        RetrieveBillingDetail = 1
    Exit_RBD:
        Exit Function
        
    Err_RBD:
        
        MsgBox Err.Number & ": " & Err.Description
        RetrieveBillingDetail = 0
        Resume Exit_RBD
    End Function

  7. #7
    Join Date
    Jan 2004
    Posts
    100
    Apparently our definitions of 'doozy' are quite similar. I hope this new guy can muddle through it. I'll let you know by November!

    Thanks,

    Norm

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by norm801
    Apparently our definitions of 'doozy' are quite similar. I hope this new guy can muddle through it. I'll let you know by November!

    Thanks,

    Norm
    I knew that by your previous postings ... But anyways, how you can do this (the easy way) is to query for your records and write out a record where you pigeon-hole the date to the proper column then in the report you don't do a visible detail section just a footer that collapses all the related records into 1 row.

    Or you could do it the harder way adding a record upon change of client and then all the related records you iterate thru plugging the dates into the appropriate slots. Then you have a visible detail section and your desired result.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •