Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2007
    Posts
    18

    Question Unanswered: Getting Multiple Rows in a single line

    I am trying to create a report that will show me the following from one table:

    Route
    Stop
    Facility Name
    Main Zip
    Zips Serived
    Scheme Code

    A Facility may service any where from 1 to 30 zips. I am trying to create a report that will show them in a side by side order like the following:

    53151, 53218, 53211, 53024
    56666, 12345, and so on

    Does any one have any idea of how to do this?

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    If all of this is in one table, that would mean that Route, Stop, Facility Name, and Main Zip are repeated in all the records that show a Zip Served. But your field name is Zips Serived, which makes me think you might have more than one zip in that field.

    Please explain some more about how the table is structured, what field or fields would be associated with this list of zips you want. Or is it just Facility? Where do Route, Stop, Main Zip and Scheme Code come in the hierarchy of your data?

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    I am guessing there is multiple records with the same route value...
    Ryan
    My Blog

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assuming that you are happy with how to extract your data.. ie by whatever report grouping you want to do......

    what you could do is con the access report generator
    create a report which gets the zip code for each route
    don't print anything on the detail, but set a group footer for each route.. or whatever interval/ grouping you want to report on.

    create an unbound textbox control, called say tbZipCodes, in the group footer, add whatever other controls you want (say route ID.....)

    then we need to do a bit of trickery in code
    create a global variable at the top of the report called say strZipCodes

    dim strZipCodes as string


    in the group header event
    strzipcodes=""

    in the detail format event
    if strlen (strzipcodes) >5 then ' put a semicolon in between multiple ZipCodes
    strzipcodes = strzipcodes & "; "
    endif
    strzipcodes = strzipcodes & [<myzipcodecolumn>]
    'you may also need to store the current data for reporting inthe group footer (eg the route, master zip code etc...)


    then in the group footer format event
    tbZipCodes.text=strzipcodes
    'plus any other inforamtion you want reporting in the footer

    you will probably need to allow the tbzipcodes column to allow grow.

    ..that should get you near to where you want to be
    HTH
    Last edited by healdem; 07-27-07 at 04:05.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Assuming I had a primary unique key in the table, I've done it where I design one report as a mailing labels report via the wizard (with the primary key in the query) and then manipulate it to the way I want it (ie. to show all the zipcodes), and then add that as a sub report to another report where there's a link on the primary key field. But that's not going to get you the 53151, 53218, 53211, 53024 type format and I think healdem's way is more of what you want.
    Last edited by pkstormy; 07-26-07 at 21:16.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jun 2007
    Posts
    18
    Sorry for not getting back to you last night.

    The Route, Stop, Main Zip and Facility Name can be on multiple rows. The Zip Code Serviced is list only one time in the table. It is also the PK.

    When you are saying create a global variable at the top of the report are you saying going into the code window and create the variable?

    My goal with this is to create a page per stop so that our people on the floor can print out and glue to the outbound shipment. Is there some way I can send someone this to look at?

  7. #7
    Join Date
    Jun 2007
    Posts
    18
    Maybe this view would help. I am pulling this info off of a query.

    Facility Name Route Stop Main_Zip Services Scheme_Info
    SCF HONOLULU HI LaLH930 01 96820 96708 DDU 96708 Would need separate flag
    SCF HONOLULU HI LaLH930 01 96820 96741 DDU 96741 Would need separate flag
    SCF HONOLULU HI LaLH930 01 96820 96743 DDU 96743 Would need separate flag
    DDU KUNIA HI LaLH930 02 96759 96759 DDU 96759 Would need separate flag
    DDU AINA HAINA HI LaLH930 03 96821 96824 DDU 96824 Would need separate flag

    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96801 P5DS 96801 All of these can be combined on one pallet. Would only need one flag
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96802 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96803 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96804 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96805 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96806 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96807 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96808 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96809 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96810 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96811 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96812 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96813 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96814 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96840 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96841 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96842 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96843 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96845 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96846 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96847 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96850 P5DS 96801

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to create a VBA function that you call from your query passing in the value of the column you wish to remain distinct. This then queries the table, gets all your codes in a recordset & iterates through it appending them to a string. This is then returned to your query.

    I can't find an example on the web - I thought Dev Ashish had one but apparently not.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2007
    Posts
    18
    I found this before and I can't seem to make this one work. I tried for about 2 days to get this to work and I have failed miserably.

    Maybe I just don't understand.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ajames420
    I found this before and I can't seem to make this one work. I tried for about 2 days to get this to work and I have failed miserably.
    Post what you came up with and explain what went wrong (errors, wrong data, no data etc).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jun 2007
    Posts
    18
    So I can get Healdam's to run. But It comes back with an error: The Setting for this property is too long.

    Option Compare Database

    Dim strZipCodes As String


    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If Len(strZipCodes) >= 5 Then ' put a semicolon in between multiple ZipCodes
    strZipCodes = strZipCodes & "; "
    End If
    strZipCodes = strZipCodes & Me![tbZipCodes]



    End Sub

    Private Sub GroupHeader_Format(Cancel As Integer, FormatCount As Integer)

    strZipCodes = ""

    End Sub

    Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)

    tbZipCodes.Text = strZipCodes It is putting the error right here

    When I look at this it looks like it is running all the zips together and not breaking them out by Route and Stop.

    End Sub


    I think it is trying to run it across in a straight line. Is there a way to have it add a carriage return after say 5 zipcodes?
    Last edited by ajames420; 07-27-07 at 10:55.

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm not sure about the Route and Stop issue but if you do get too many zip codes that the text box or string can handle, you can ry dimensioning strZipCodes as a variant verses a string. Sometimes this would work for me. Also, maybe try dragging a memo field type to the report and changing it's name to tbZipCodes in replace of the current text field. If that doesn't work, you can possibly use an activeX control which holds more data (I can't remember which one it was that I used.) Or try splitting it into 2 variables and 2 text boxes on the report (which will require changing the coding), setting the CanGrow and CanShrink property to Yes.
    Last edited by pkstormy; 07-27-07 at 20:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by ajames420
    So I can get Healdam's to run. But It comes back with an error: The Setting for this property is too long.......
    how many characters are you trying to stuff into a single control?

    i forget what the limit is , but its certainly over 256 characters... so how muc are you trying to stuff into one control.... could you get away with 'just reporting that as part of a detail?

    are you tryign to stuff
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96801 P5DS 96801 All of these can be combined on one pallet. Would only need one flag
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96802 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96803 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96804 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96805 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96806 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96807 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96808 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96809 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96810 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96811 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96812 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96813 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96814 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96840 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96841 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96842 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96843 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96845 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96846 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96847 P5DS 96801
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 96850 P5DS 96801

    or
    DDU DOWNTOWN HONOLULU HI LaLH930 04 96813 P5DS 96801
    96801 96802 96803 96804 96805 96806....968xx
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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