Multiple records

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I understand how the IIF statement is working but there
is still a problem. I have two tables (Employee) and
(Contacts)joined by [EmplNbr]. My query gets information
from table employee and table Contacts. If an employee
has more than one contact number there are two records
for that employee when I want only one. Even using the
IIF statement I will have an employee with a blank for
[Homephone: IIF([Type] = "home", [Number], "")] and a
number in [Cellphone] and then another row for the same
employee with just the opposite - a cell number and a
blank homephone field.

I'm still experimenting with this.

I hope you can steer me in the right direction. I'm
tempted to simply create two fields [cell] and [home] and
be done with it but that is improper database structure
and I'll never get better if I regress.

Thanks!
 
"Steve" wrote
I understand how the IIF statement is working but there
is still a problem. I have two tables (Employee) and
(Contacts)joined by [EmplNbr]. My query gets information
from table employee and table Contacts. If an employee
has more than one contact number there are two records
for that employee when I want only one. Even using the
IIF statement I will have an employee with a blank for
[Homephone: IIF([Type] = "home", [Number], "")] and a
number in [Cellphone] and then another row for the same
employee with just the opposite - a cell number and a
blank homephone field.

I'm still experimenting with this.

I hope you can steer me in the right direction. I'm
tempted to simply create two fields [cell] and [home] and
be done with it but that is improper database structure
and I'll never get better if I regress.
Click to expand...
Hi Steve,

I believe you just need to change your
query to a Group By Query (click on the
sigma button in query designer menu).

Under the columns for Homephone and
Cellphone, change "Group By" to Max.

If there are other fields that will cause multiple
rows for each contact, change "Group By"
to "First"

One disadvantage to this method is that the
query will not be updateble. If you must be
able to edit the query, please respond back.

Good luck,

Gary Walter
 
Back
Top