hide empty fields in a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I just started using access two weeks ago and have created a dbase for my
travel agency.
Some bookings have 20 passengers some only have 1. I have 20 fields (for
passengers) in my table, but not all of them are filled for every booking.
How can I run a report that shows only the records that have passenger data
in them?
Really appreciate any advice.
P.S. Allen Browne...You rock! Thanks for add calendar button on your
website...Would have taken me another 12 months to figure that one out!
 
One cruise/bus/whatever can have many passengers booked. That means you need
2 tables:
Booking table (one record for each cruise/bus/whatever):
BookingID AutoNumber
BookingDate Date/Time
...

BookingDetail table (one record for each passenger in a booking):
BookingDetailID AutoNumber
BookingID which booking this relates to
Passenger
...

Ultimately, the interface is a main form for the Booking, with a subform for
the passengers in the booking.

For an example, open the Northwind sample database that installs with
Access, and choose Relationshps on the Tools menu. You will see how one
Order can have many Order Details (the line items associated with the
order.) You need the same kind of approach to handle many passengers in one
booking.

Then open the Orders form in Northwind, to see how the line items work for
the order. You will have a similar interface for the passengers in a
booking.

Once all that is sorted, out, the report will be based on a query that uses
both tables (like Northwind's Invoices report.) The issue of empty fields
just does not exist once the structure is correct.

Feedback appreciated.
 
Your table design is incorrect, you should have one passenger name in each
record instead of 20 names, and the records will repeat the booking with one
name only. What if you'll get more then 20 bookings?
And in that way it wont need to filter the records.

I thing this link can explain it much better then I can, on "Relational
Database Normalization Basics"

http://www.databasedev.co.uk/database_normalization_basics.html
 
Back
Top