If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
hide empty fields in a report
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! |
#2
|
|||
|
|||
hide empty fields in a report
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "TonganShark" wrote in message ... 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! |
#3
|
|||
|
|||
hide empty fields in a report
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/databas...on_basics.html -- Good Luck BS"D "TonganShark" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|