Record Duplication 2

[withoutwax]

Any advice would be appreciated. I have a database which contains a list of individuals and their addresses. To enable me to organise them I have grouped them in categories using a subform. For instance, we may have an individual that is a member but also represents an organisation. They may have one category or they may have several. The problem with this is that when I come to print labels for several categories, eg. "members" and "organisations" then those people with both those categories are duplicated. Would anybody be able suggest a different grouping system or some sort of solution to this?

 

[Remou]

If you have a person in the persons table for each category that they belong to, you need a junction table:

[tt]tblPersonCategory
PersonID ) Individually, as foreign keys, combined as
CategoryID ) Primary Key for this table[/tt]

In the meantime, it should be possible to Group:
[tt]SELECT Name, Address
FROM tblPersons
WHERE Category IN (1,7,11)
GROUP BY Name, Address[/tt]

 

[fneily]

You don't show your table structures. You may want to read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

a good place to start to study normalization. All tables must be normalized.

You could have these tables:
tblMember
MemID
fname
lname
address
city
state
zip
phone
etc. other member info

tblOrganizations
OrgID
OrgName
Description
Address
City
State
Zip
Phone
etc. other organization info

Then a junction table:
tblOrgMem
OMID
MemID
OrgID
DateOfMembership
etc. and COMMON fields

Now if you only want the members address, use the tblMember.
If you want their organization address, then, in a query, connect the tables and use the org. address.

 

[Remou]

(Hmm, normalization, fneily now what did I read recently ... )

 

[PHV]

Anyway, you may consider the DISTINCT predicate in your SELECT instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

Remou and PHV - now I know how the turkeys feel today this Thanksgiving. Cut, slice, dice.
Since I've already suggested to PHV to spend more time holiday shopping, may I suggest, another reading - the annotated version of War and Peace.

I being kicked in the genua by the geniuses of their genus.