Results 1 to 13 of 13

Thread: SQL Won

  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Won

    SQL Won

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Won

    strSQL: SELECT s.lngBPermitID INTO tblResultsBP FROM qrytblBPermit s ORDER BY strPermitNo

    strSQL: SELECT s.lngContractorID INTO tblResultsContractor FROM qrytblContractor s ORDER BY strAccountNo

    What contains those fields ? You select Longs and sort on Strings. Is it that what you want ?
    Francois

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Won

    Hi Francois

    Yes that is what I want. This is part of a more complex criteria form that allows the operator to enter criteria and some code builds the following statements.

    I am selecting all the lngContractorID's into a results table from a query that contains all the table fields from tblContractor sorted by strAccountNo

    this one does everything but the sort:
    strSQL: SELECT s.lngContractorID INTO tblResultsContractor FROM qrytblContractor s ORDER BY strAccountNo

    this one works:
    strSQL: SELECT s.lngBPermitID INTO tblResultsBP FROM qrytblBPermit s ORDER BY strPermitNo

    Thanks, John

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Won

    Did it make a difference if you change the order by to :
    ORDER BY s.strPermitNo

    edit
    Sorry, I mean
    ORDER BY s.strAccountNo
    Francois

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Won

    Hi Francis

    I tried your suggestion wih same results

    This is qrytblContractor

    The joins are code and descriptions tables I'm using to (flatten) bring everything together with tblContractor for selection and reporting purposes. This use t work.

    Thanks, for your help

    John

    SELECT tblContractor.*, tbl_BP_OtherLookup.strCode AS CODstrCode, tbl_BP_OtherLookup.strDescription AS CODstrDescription, tbl_BP_OtherLookup.strTypeCode AS CODstrType, tbl_BP_OtherLookup_1.strCode AS CTYstrCode, tbl_BP_OtherLookup_1.strDescription AS CTYstrDescription, tbl_BP_OtherLookup_1.strTypeCode AS CTYstrType, tbl_BP_OtherLookup_2.strCode AS CCLstrCode, tbl_BP_OtherLookup_2.strDescription AS CCLstrDescription, tbl_BP_OtherLookup_2.strTypeCode AS CCLstrType, tbl_BP_OtherLookup_3.strCode AS COSstrCode, tbl_BP_OtherLookup_3.strDescription AS COSstrDescription, tbl_BP_OtherLookup_3.strTypeCode AS COSstrType
    FROM ((((tblContractor LEFT JOIN tbl_BP_OtherLookup ON tblContractor.lngDeptCodeID = tbl_BP_OtherLookup.lngID) LEFT JOIN tbl_BP_OtherLookup AS tbl_BP_OtherLookup_1 ON tblContractor.lngAccountTypeID = tbl_BP_OtherLookup_1.lngID) LEFT JOIN tbl_BP_OtherLookup AS tbl_BP_OtherLookup_2 ON tblContractor.lngContractorClassID = tbl_BP_OtherLookup_2.lngID) LEFT JOIN tbl_BP_OtherLookup AS tbl_BP_OtherLookup_3 ON tblContractor.lngStatusID = tbl_BP_OtherLookup_3.lngID) INNER JOIN tblResultsContractor ON tblContractor.lngContractorID = tblResultsContractor.lngContractorID
    WHERE (((tbl_BP_OtherLookup.strTypeCode)="COD") AND ((tbl_BP_OtherLookup_1.strTypeCode)="CTY") AND ((tbl_BP_OtherLookup_2.strTypeCode)="CCL") AND ((tbl_BP_OtherLookup_3.strTypeCode)="COS"));

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL Won

    Maybe this is a dumb question, but why do you care what order they're inserted in? Table order isn't significant in Access anyhow unless you don't index the table, and in that case everything runs much slower than it should.
    Charlotte

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Won

    Hi Charlotte

    This criteria form is used by the operator to isolate record ID's as a record source for reports, in addition to entering criteria which this example dosen't show (to keep it simple), the operator can select one of multiple sort sequences, the code builds the sql based on these selections. In this example it is strAccountNo, it could have been strBusinessName, etc.

    In any case tblResultsContractor should contain the ID's that meet the criteria and in the user selected sequence, in this case strAccountNo.

    I have used this method before in all my criteria forms, all of a sudden its nt working on this one:

    this one does everything but the sort:
    strSQL: SELECT s.lngContractorID INTO tblResultsContractor FROM qrytblContractor s ORDER BY strAccountNo

    this one works:
    strSQL: SELECT s.lngBPermitID INTO tblResultsBP FROM qrytblBPermit s ORDER BY strPermitNo

    Thanks, John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Won

    John,

    As Charlotte wrote, the order of records in a table is irrelevant. If your table has a primary key, the records will be displayed sorted by this index (unless another sort order is imposed explicitly), regardless of the physical order.

    If you want records displayed in a different order, create a query. For reports, you have to set the sort order in the Sorting and Grouping window anyway, because reports usually ignore the sort order of the record source (table or query). See for instance the thread starting at <post#=166698>post 166698</post#>.

    So trying to insert records into a table in a certain order is inefficient and practically useless. Set the sort order in a query or in the Sorting and Grouping window of a report to return records the way you want.

    Regards,
    Hans

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Won

    Hi Hans

    I'm probably noy explaining myself.

    I put together sample db however it is 165mb, is there a way to get it to you or Charlotte, in a few minutes you will see what I'm trying to do.

    John

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Won

    I hope it is 165kb.
    If Hans or Charlotte don't have the time, I'll be glad to have a look at it.
    Francois

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Won

    HI Francois

    Woops its 165kb, I emailed it to you

    Thanks, John

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Won

    John,

    In a report, never relay on the sort order of the underlying query or table. You have to specify it in the report itself.
    You can do it in the Sort and Grouping window (see attachment) or by code in the On Open Event of the report.
    <pre>Private Sub Report_Open(Cancel As Integer)
    'Call GetReportInfo(Me)
    Me.RecordSource = "qryResultsContractor"
    Me.OrderBy = "strBusinessName"
    Me.OrderByOn = True
    End Sub</pre>

    Attached Images Attached Images
    Francois

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Won

    Hi Francois

    I took your advice, changed as follows so I can reuse reports for various criteria and sorting options, still don't understand how the concept was working for the past 6 months, oh well, access was letting me get a way with sloppy programming then it bit me.

    Thanks, John

    Private Sub Report_Open(Cancel As Integer)

    Call GetReportInfo(Me)

    If IsLoaded("frmCriteriaContractor") Then
    If Nz(Forms![frmSelectReport]![Application], "NA") = "CO" Then
    Me.RecordSource = "qryResultsContractor"
    Me.OrderBy = [Forms]![frmCriteriaContractor]![lblSelectFields].Caption
    Me.OrderByOn = True
    End If
    End If
    End Sub

Posting Permissions

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