Results 1 to 11 of 11

Thread: A wild sort

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A wild sort

    Dear Jazman,
    It really depends on your data.
    If your group 1 is based on info that can be in either of the fields 1 through 3, how will you know which field has the criteria

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

    Re: A wild sort

    It sounds to me like you've taken the approach of using non-specific fields to hold your addresses, that is you have Address1, Address2, Address3, etc., but you don't know for certain what, if anything, is going to be placed in any of those fields. Am I correct?

    Frankly, it could be done in code, but it would take far too long to process and be completely susceptible to data entry errors even if it completed. You would be better advised to constain certain fields to hold things like State/Province/District, etc. Alternatively, you search backward through the fields until you find a populated one and start applying your logic there.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A wild sort

    I have a table which contains a mailing list that has 6 fields.

    I need to group in a report by 2 separate levels.

    Because it is a mailing list and succeptable to data entry error, I must search more than 1 field for the criteria of each level.

    Group 1 would need to be on the criteria of "District" and would either be in Fields 1,2 or 3

    Group2 would be based on a string that would contain "TX" and that data could be anywhere in fields 3-6

    Try to picture a 6 field paper form. On that form people are filling in data by hand without any regard to disciplined entry. Here are some examples

    1-Name-Address-citystatezip
    2-Name-Co Name-Co address-Po Box-CityStatezip
    3-Name-Title-Co Name-Address-Citystatezip
    4-Name-District-District address-City statezip

    ...ad nauseum
    Most of the data has been cleaned up to conform and respond to sorts and queries...
    How in code could I group them for a report?

    The Access help section on expressions in grouping for reports is abysmal...

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A wild sort

    Hey Rupert
    I have expanded my verbal illustration of what I'm dealing with on the original post. Check it out and see if that helps explain things better. I have used Excel to clean up the data quite a bit (i.e. remove nulls and shift data in) but this is a job for Access and expressions are definitely required.
    Thanks

  5. #5
    New Lounger
    Join Date
    Dec 2000
    Location
    Indiana
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A wild sort

    Jaz,
    Will this query idea work?

    Do a Select * of the table into a new query. Then add two new fields: District and TX, which are determined using the following function:
    *------------------ Put in a public module ---------------------------*
    Function Cust_Instr(String_To_Find, F1, F2, F3, Optional F4) As Boolean
    'Returns true if the text String_To_Find is found
    'among the arguments F1 thru F4.
    Dim f_All, i
    Cust_Instr = False

    If IsMissing(F4) Then F4 = Null

    f_All = F1 & F2 & F3 & F4

    i = InStr(f_All, String_To_Find)

    If Not (i = 0) Then Cust_Instr = True

    End Function
    *-------------------------------------*

    The expression for the district field is District:= Cust_Instr("District", YourTable!Field1, YourTable!Field2, YourTable!Field3)
    Likewise for TX:= Cust_Instr("TX", YourTable!Field4, ....)


    Base the report off the query instead of the table. You should be able to group on District (true or false) and TX (true or false).

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A wild sort

    WOW...impressive code!

    I am having difficulties calling the function, however.

    As I understand it, A new query was created but is the function called from the query? If so, how is that done?
    As an expression, right?

    using this code in the query created an error.
    District:= Cust_Instr("District", Table1!Ad1, Table1!Ad2,
    Table1!Ad3)

    If my fields are all called (Ad 1-5), would the corresponding code in the function be the same?
    Thanks for the promp reply.

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A wild group

    I would prefer that fields be restricted, naturally. Unfortunately, I inherited this as a Word list that was
    entered from a hand written list, and the data entry people knew nothing about fields or which data should go where.

    So now I am forced to search inside the strings for the appropriate value and group from the newly created fields built in the query.

    What I really need are examples of expressions used in reports for customized grouping.
    The Access help section isn't very helpful on this one.
    I imagine that it's an INSTR call, and I believe that your call on searching backwards thru the fields would be the correct one.
    Thanks for the assistance.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A wild sort

    Dear Jazman,

    With regard to your updated question.

    I understand your problem! You have inherited a lot of data that

  9. #9
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A wild sort

    Fortunately, The U.S. does have some type of disciplined zip code system. It's either a 5 digit or a nine with the last four separated by a dash. i.e. 12345-6789

    Just start at the end of the string and go back to the first space. Delimit. That would be the easiest way probably. As long as its a non-numeral or a space.

    Fortunately, here we don't use hyphens in our city names. The postal service hates punctuation in the last line...it throws off the scanners.

    I love that there is more than one way to skin a file.
    Thanks Rupert

  10. #10
    New Lounger
    Join Date
    Dec 2000
    Location
    Indiana
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A wild sort

    Here's the SQL for a query like I described. I use Table1 as the table and Ad1 thru Ad6 as the fields. Readability of this syntax is less than favorable, but it is quite unambiguous. (That's good)
    <pre>SELECT Table1.*,
    Cust_Instr("District",[Table1]![Ad1],[Table1]![Ad2],[Table1]![Ad3]) AS District,
    Cust_Instr("TX",[Table1]![Ad3],[Table1]![Ad4],[Table1]![Ad5],[Table1]![Ad6]) AS TX
    FROM Table1;</pre>


    Put that verbage into a query by using the SQL VIEW option under the VIEW menu when you have the query design screen open. Just paste the verbage over whatever is in there. Then replace table and field names as needed.

  11. #11
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A wild sort

    Yeah!!
    That worked like a charm.
    I really have to learn more SQL, it's so easy to read.
    I usually use it to alter the source but rarely code straight to it. Excellent!
    A little sorting in the report section and the results were spectacular.
    Thanks for all your help KB

Posting Permissions

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