Database Discussion - got any ideas

Print Page | Close Window

got any ideas

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=9337
Printed Date: 28 March 2026 at 10:56am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: got any ideas

Posted By: jimcarey79
Subject: got any ideas
Date Posted: 28 January 2004 at 4:40pm

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.

Replies:

Posted By: Semikolon
Date Posted: 28 January 2004 at 5:15pm

are all the details placed in seperate fields in the database?

Posted By: jimcarey79
Date Posted: 28 January 2004 at 5:24pm

u mean....like FirstName, LastName, Address, Address2, etc....

yes they are in their own field.

hope this is what you are asking.

Posted By: michael
Date Posted: 28 January 2004 at 6:53pm

Well if you don't know how many people are in a household it will be difficult thus also depending on the db you are using. So assuming you just use Microsoft Access and you only want to consider a max of 2 people in a household you could do something like


SELECT 
        First(FirstName) AS FirstName1, 
         Last(FirstNa me) AS FirstName2, 
        First(LastName), 
         Address1,&nb sp;
        Address2
FROM 
        addresses
GROUP BY 
         addresses.Address1, 
         addresses.Address2;

Then in your vbcode you can do a simple
If rs("FirstName1") = rs("FirstName2') then varFirstName2 = ""

If you are using MSSQL or so you can do it better using a sproc, let me know.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker