Ho do I shift empty cells up in an access report

  • Thread starter Thread starter GBA
  • Start date Start date
G

GBA

In my contact data base, I have an unequal number of data for each contact,
some have 3 phone numbers some have up to 8 etc. I would like to print out a
directory (phone book) in which records do only show fields that are
populated. In other words, one record may only include 3 lines others may
include 9 lines but I do not want to display and print a fixed number of
lines, only the ones that are populated.
 
GBA said:
In my contact data base, I have an unequal number of data for each contact,
some have 3 phone numbers some have up to 8 etc. I would like to print out a
directory (phone book) in which records do only show fields that are
populated. In other words, one record may only include 3 lines others may
include 9 lines but I do not want to display and print a fixed number of
lines, only the ones that are populated.
Click to expand...


Depends on what the report's record source records look
like. If you would post some sample records and how you
want their fields to appear in the report, most likely
someone can help figure out how.
 
Sounds like you have a spreadsheet with up to 8 fields for phone numbers.
Normalize your data using a union query.
SELECT FName, LName, Phone1
FROM Yourtable
UNION ALL SELECT FName, LName, Phone2
FROM Yourtable
WHERE Phone2 Is Not Null
UNION ALL SELECT FName, LName, Phone3
FROM Yourtable
WHERE Phone3 Is Not Null
.........
UNION ALL SELECT FName, LName, Phone8
FROM Yourtable
WHERE Phone8 Is Not Null;

In your report group on FName and LName.
 
Back
Top