Membership Directory

[CMB70]

I have a membership directory and I am trying to create a report that includes the following information;

Company Name
Member Name (based on membership type - BA1)
Member Name (based on membership type - BA2)
Address
City, State ZIP
Phone Fax
Website

I cannot figure out how to get the BA1 member and BA2 member to show up under one Company Name. They are separate in the database, with all the same information with the exception of name and membership type.

Any help building my query to make this work would be fantastic.

 

[anthonys]

self join

I'm not sure if i completely understand your problem but i think you might need to do a self join (use company table twice joining itself) on the table using company name as the joining field.

Then extract all the common information from 1 table and the second member name from the joined table.

If i'm on the wrong track, can you provide some more info, maybe some fake results?

Ant

 

[CMB70]

All of the information I have is in one database. Here are the fields I am using for the query.

fname
lname
company
addr 1
addr 2
city
state
zip
country
Mbr Type

This would be a sample of the records in my database;

Record 1
fname - Jane
lname - Smith
company - ABC Company
addr 1 - 123 my streeet
addr 2 - po box 120
city - Anytown
state - NY
zip - 12345
country - USA
Mbr Type - BA1

Record 2
fname - Dave
lname - Jones
company - ABC Company
addr 1 - 123 my streeet
addr 2 - po box 120
city - Anytown
state - NY
zip - 12345
country - USA
Mbr Type - BA2

What I want to do is create a report (it is actually mailing labels) that looks like this;

ABC Company
Jane Smith
Dave Jones
123 my street po box 120
Anytown NY 12345
USA

Not every company has both a BA1 and BA2 member. My report is set up, but it duplicates the company with the BA2 member and what I really want to do it include them both under one. I don't even know if this is possible.

I hope I have explained it better this time.

 

[anthonys]

Attached solution

Hi,

I have created a quick sample solution and have attached it as a zip file.

It contains 1 table and 3 queries and hopefully will make more sense than me trying to explain it in words to you.

Best of luck and let me know how you get on.

Regard

Ant