Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    Unhappy Unanswered: i'm lost, VB problems

    greetings,

    Let me give you an overview of what's going on in the query and the end/goal that I'm trying to meet. I have a volunteer database built with access 2000. My goal in this query is to take the volunteers that have the same address (i.e. husbands and wives and brothers sisters, etc.) and send one letter to that household, instead of multiple. Hoping that this will cut some of my office supplies expense.

    The code below gets me the volunteers i need and their addresses, however I am unable to produce the correct code to look at the addresses that are the same and combine the names and mailing address, for example:

    John Doe
    123 Helpme Lane
    Cleveland, Oh 44124

    Jane Doe
    123 Helpme Lane
    Cleveland, Oh 44124

    So.... it would be

    John Doe
    Jane Doe
    123 Helpme Lane
    Cleveland, Oh 44124


    Obviously, I am lost when it comes to Visual Basic.... But for some reason I think that the code in VB would be use something like Case 1, Case 2..... I'm probably incorrect. I understand that the code in VB should run a query and sort by address, then check and see is line 2 = line 1, etc. But, I am unsure in how to structure this. Also, not sure if the code would be a module or what.... I would really appreciate some feedback.

    Thanks.

    Code:
    SELECT Volunteers.VolunteerID
    , Volunteers.FirstName
    , Volunteers.VolunteerTypeID
    , Volunteers.StatusNo
    , Volunteers.DeliveryTypeID
    , [Address] & ", " & [Address2] AS FullAddress
    , [City] & ", " & [State] & " " & [PostalCode] AS CityState
    FROM VolunteerType 
    INNER JOIN Volunteers 
    ON VolunteerType.VolunteerTypeID = Volunteers.VolunteerTypeID
    WHERE (((Volunteers.VolunteerTypeID)=1 
    Or (Volunteers.VolunteerTypeID)=2) 
    AND ((Volunteers.StatusNo)=3 
    Or (Volunteers.StatusNo)=5) 
    AND ((Volunteers.DeliveryTypeID)=3))
    ORDER BY Volunteers.FirstName;
    Thanks in advanced!

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb VBA?

    I suspect this can just be done with queries without the use of VBA...

    Can you upload your .zipped database so that we can look at the table relationships and how you store the data please?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    Re: VBA?

    Originally posted by garethdart
    I suspect this can just be done with queries without the use of VBA...

    Can you upload your .zipped database so that we can look at the table relationships and how you store the data please?
    Feel free... i tried doing it with sql but i cannot figure out how to test the relationship if the address matches or not and if it does how to pair the two volunteers. I have uploaded the database. See what ya think. The query is called "qryConnectionsMailingList....Thanks a bunch!

    BAF
    Last edited by ironchef; 01-28-04 at 16:50.

  4. #4
    Join Date
    Oct 2003
    Posts
    706
    The general way to handle duplicates is to make sure that the incoming data is sorted the same way: address, city, state, zip, etc. This causes all identical records to be adjacent in the output.

    Now you then might be able to do a query, based on the first, which uses some funky operator like FIRST to retrieve only the first record ("grouped by" the sort fields from the original query). Or you may find, as I suspect while writing this, that GROUP BY alone might do the trick and only one query would be required.

    The catch is: it only works for absolutely identical values, and addresses rarely are. You should run the query to create a table, then examine the table visually before printing.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  5. #5
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75
    Originally posted by sundialsvcs
    The catch is: it only works for absolutely identical values, and addresses rarely are. You should run the query to create a table, then examine the table visually before printing.
    I completely understand that just matching addresses is very unstable and inaccurate. OK, I can visualize this....help me out a little, see what think and maybe any ideas on how to do it. I am new at this, i have a great imagination but turning logical thinking into code is sometimes difficult.

    The user wants to run the report so he/she clicks the button to generate the report and the query runs and pops-up a form displaying the results (Groupby and sorted acsending) so the user can verify or unverify that the address that are matching really do match (kinda confusing) then he clicks the "continue" button and the reports gathers the mailing address and shoots this out:

    Jim Franklin
    Jenny Franklin
    1234 Ya Ya Road
    Pittsburgh, PA 16584

    Any ideas of how to set this up?

    Regards,

    BAF

  6. #6
    Join Date
    Jan 2004
    Posts
    1
    I have done similar tasks never with joining records

    but my suggestion would be to setup a recordset for the address

    and take all names associated with the address recordset and apply it to a variable

    and then on your letters print the variable and then address

    check out this website for recordset creation

    www.vba-programmer.com
    Last edited by Maddog2323; 01-15-04 at 19:29.

  7. #7
    Join Date
    Dec 2003
    Location
    Bristol, England
    Posts
    29
    Hi,

    This is a systems analyst problem more than a coding.

    Provided that you can ensure every Zip code is entered, that is the first comparison to make. Then you only need to compare the street number (in England we would also have to make provision for comparing a house name if no number). As there should be no possibility of confusion between Zips and between numbers, this should avoid the problem mentioned by sundialsvcs.

    You will, of course, have to have separate database colums for the house numbers and the zips.

    I can't help you with actual VBA code, but once you have created a query sorted on ZIp then House Number, you can easily achieve your objective with something like:

    do while EOF=false
    store ZIP to strZip
    Here store all the address detailsin a string variable and the person's name in strName(0)
    movenext
    if eof=true then
    exit sub
    end if
    do while ZIP=strZip
    store HouseNumber to iNum
    movenext
    x=0
    do while HouseNumber=iNum
    x=x+1
    store Name to strName(x)
    movenext
    end do
    if x=0 then
    exit loop
    end if
    Here enter the code to print the names from strName(x)
    end if
    exit loop
    end do
    if x=0 then
    Here enter the code to print the name from strName(0)
    end if
    Here enter the code to print the address
    end do

    I have not tried to debug the above in any language and if there arn't any bugs, it will be a lifetime first!!

    I'm sorry, but I did type the above code with standard indentations, but the forum text editor flattened it all out!

    Hope this helps,
    Last edited by taxes; 01-15-04 at 19:54.
    taxes

  8. #8
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    Unhappy still can't get it

    I've been struggling with this for a few days now! I can't figure out how to exactly doing this. Please someone must of run into the same problem or scenerio.....

    Some help with the code is what i need.

    Thanks,

    BAF

  9. #9
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    Re: still can't get it

    Attached is a sample database....

    thanks in advanced.
    Attached Files Attached Files

  10. #10
    Join Date
    Dec 2003
    Location
    Bristol, England
    Posts
    29
    Hi Ironchef,

    "I've been struggling with this for a few days now! I can't figure out how to exactly doing this. Please someone must of run into the same problem or scenerio..... "


    I suspect that most people with the same problem are using VB.NET rather than VBA. Sorry I can't help in VBA. (VB.NET is now able to fully directly access all aspects of ACCESS & EXEL)

    Best of luck,
    taxes

Posting Permissions

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