Select/distinct question

[Tracey]

I have a mailing db (access 2000 format) with these fields
FName
LName
Title
Agency
Address
City
Zip
PH
Category (which includes: Elected officials, churches,
rotary, business, principals about 10-15 different ones)
There are several people in up to 3 categories

I need to be able to distinguish by name and address but
have the title and agency appear in the query.

This works but does not allow the title and agency to
appear. If I just put in those two fields, it sees them as
all unique fields and returns all records.

SELECT DISTINCT Main.[FIRST NAME], Main.[LAST NAME],
Main.ADDRESS, Main.CITY, Main.ZIP
FROM Main;

Thanks for any help.. I am not a programmer.. I had help
with the above.
Tracey

 

[[MVP] S. Clark]

I don't understand why Title & Agency can't be added to your current query,
but...

The category field needs to be split to a seperate table:

tblPerson_Category
PCID
PersonID
CategoryID

Where PersonID will refer to one person in the Person Table, and CategoryID
will refer to one category from the Category Table. Oh yeah, make a
category table too.

tblCategory
CategoryID
CategoryName

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Dale Fye]

Steve,

I think the implication here is that a particular individual may be in
the database multiple times, with multiple categories.

Tracey,

Which Title/Agency values to you want to list if a person is in the
table more than once under different title/agency headings?

--
HTH

Dale Fye


I don't understand why Title & Agency can't be added to your current
query,
but...

The category field needs to be split to a seperate table:

tblPerson_Category
PCID
PersonID
CategoryID

Where PersonID will refer to one person in the Person Table, and
CategoryID
will refer to one category from the Category Table. Oh yeah, make a
category table too.

tblCategory
CategoryID
CategoryName

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Tracey]

We just need to eliminate duplicate mailings. I've tried to
clean up the data so the fields i want to be distinct it
will pick up. I guess i want the query to ignore but show
the title/agency fields. Just sort out any dups by name
first and then address.

Tracey

-----Original Message-----
Steve,

I think the implication here is that a particular individual may be in
the database multiple times, with multiple categories.

Tracey,

Which Title/Agency values to you want to list if a person is in the
table more than once under different title/agency headings?

--
HTH

Dale Fye


I don't understand why Title & Agency can't be added to your current
query,
but...

The category field needs to be split to a seperate table:

tblPerson_Category
PCID
PersonID
CategoryID

Where PersonID will refer to one person in the Person Table, and
CategoryID
will refer to one category from the Category Table. Oh yeah, make a
category table too.

tblCategory
CategoryID
CategoryName

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

I have a mailing db (access 2000 format) with these fields
FName
LName
Title
Agency
Address
City
Zip
PH
Category (which includes: Elected officials, churches,
rotary, business, principals about 10-15 different ones)
There are several people in up to 3 categories

I need to be able to distinguish by name and address but
have the title and agency appear in the query.

This works but does not allow the title and agency to
appear. If I just put in those two fields, it sees them as
all unique fields and returns all records.

SELECT DISTINCT Main.[FIRST NAME], Main.[LAST NAME],
Main.ADDRESS, Main.CITY, Main.ZIP
FROM Main;

Thanks for any help.. I am not a programmer.. I had help
with the above.
Tracey

.