Duplicates on a Report (maybe a Query problem too) - Access 2000

[Rebecca]

Hi,
I have a db with a table for people, a table for people's phone
numbers and a table for people's cell numbers. I've made a query
bringing together all the info (name, phone numbers, cell numbers). On
that query I've made a report to serve as a printed phone list.
When a person has 2 phone numbers and 2 cell numbers the query and the
subsequent report allocate that person 4 lines even though in order to
present all the info only 2 are needed. The best I was able to do was
to get:
name phone1 cell1
name phone1 cell2
name phone2 cell1
name phone2 cell2
The "Hide Duplicates" property was able to have the name and phone
number appear only once but does not help with the number of lines
used to present the info.
Ideally what I need is:
name phone1 cell1
(name) phone2 cell2
Any ideas?
Thanks,
Rebecca

 

[Jeff Boyce]

Rebecca

Are you saying that you have separate tables for cell phone numbers and
"regular" numbers? If so, why? Both are "types" of phone numbers, so you
could add a single "type" field to a single table and store both types in
one.

When you say you have a query to "bring together" the person, phone, and
cell numbers, what are you using to link those together (i.e., the table
structure you have in place that tells Access the rows are related)?

 

[Rebecca]

Rebecca

Are you saying that you have separate tables for cell phone numbers and
"regular" numbers? If so, why? Both are "types" of phone numbers, so you
could add a single "type" field to a single table and store both types in
one.

When you say you have a query to "bring together" the person, phone, and
cell numbers, what are you using to link those together (i.e., the table
structure you have in place that tells Access the rows are related)?

It's like this:

The phone numbers and the cell numbers are in separate tables because
phone numbers have a 2 digit area code while cell numbers have a 3
digit area code. Both have a 7 digit number.

As for the table structure, each person has an unique ID (automatic
number) used as a foreign in the phone/cell numbers table.

Hope that's enough info,
Rebecca

 

[Jeff Boyce]

Rebecca

So, he said, tongue in cheek, I suppose you also have one table for each
person, since very few names have the same number of characters, much like
the difference between your phone and cell area codes?!

In your example, you indicated a "phone1" and a "cell1", then a "2" version
of each. How do you (or Access decide which phone numbers represent the
"first" and "second"? Do you have any marked in the tables as "primary"?

 

[Rebecca]

Jeff,

Point taken on the logic of having separate tables for phone and cell
numbers.

I tried as you suggested, one table with:
person number
Type (cell or phone)
PhonePrefix (2 digits)
CellPrefix (3 digits)
TheNumber(7 digits).

Since each record in the table is one number (cell or phone, according
to the value of "Type") I still have 4 records for a person who has 4
numbers - 2 cell and 2 phone in this case. These 4 records still take
up 4 rows on my report. I seem to have even taken a step away from my
goal since I have only 1 number per line:
name cell1
name cell2
name phone1
name phone2

The numbers are "sorted" into columns by:
IIF([Type]="cell",[CellPrefix] & "-" & [TheNumber],"")
IIF([Type]="phone",[PhonePrefix] & "-" & [TheNumber],"")

It is not important to me which is "cell1" and which is "phone2".
There is no "primary" number.

Rebecca

 

[Jeff Boyce]

Rebecca

If FirstName holds a (variable length) first name, and Prefix holds a
(variable length) prefix, you can simplify that single phone table one step
further... unless there's something particularly unique about the prefixes,
there's no need to keep each separate. You are already indicating type
(cell or not-cell -- sorry, I couldn't resist, since a cell phone is a
phone). If you have no interest past identifying which phone is a cell
phone, you could even get by with a Yes/No field to record that a row is a
"cell phone" row.

I tried as you suggested, one table with:
person number
Type (cell or phone)
PhonePrefix (2 digits)
CellPrefix (3 digits)
TheNumber(7 digits).

Actually, this is not exactly what I was suggesting -- see above.

Since each record in the table is one number (cell or phone, according
to the value of "Type") I still have 4 records for a person who has 4
numbers - 2 cell and 2 phone in this case. These 4 records still take
up 4 rows on my report. I seem to have even taken a step away from my
goal since I have only 1 number per line:
name cell1
name cell2
name phone1
name phone2

The numbers are "sorted" into columns by:
IIF([Type]="cell",[CellPrefix] & "-" & [TheNumber],"")
IIF([Type]="phone",[PhonePrefix] & "-" & [TheNumber],"")

It is not important to me which is "cell1" and which is "phone2".
There is no "primary" number.

Here's a reply in the Queries newsgroup that might point you in the
direction you are seeking:

There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
solution involves writing one function that can be used for all
concatenation tasks.