[AccessD] Presenting data problem

Susan,

Some years back, I wrote had a vaguely similar app; well, similar in the
sense that my SalesDetails table has to store a variety of different
"objects": event tickets, hotel bookings, limo bookings (to and from the
airport), perhaps car rental, and a few others. My first thought was to
have several types of SalesDetails tables, each corresponding to a
different type of object. But the downstream problem of creating an
itinerary for the whole package, based on date/time of the item, proved
insurmountable. So finally I opted for creating a single SalesDetails table
containing all the columns necessary to support all the DetailTypes, and
adding a couple of columns for DetailType and Description. A separate
table, DetailTypes, contained a list of Detail Types and a column
containing the name of the form to load. The record source for each detail
form was a named query (as time went on and the app moved from Access to
SQL Server, the named queries became Views) that presented only the fields
relevant to that particular detail type. Each of these forms contained an
identically named procedure called MakeDescription, that assembled the
values into a readable text string. For example, in the case of a flight,
it would say something like "Business class tickets, Air Canada Flight 123,
departing at 12:30 p.m, arriving at 2:00 p.m". The quantity, price and
amount columns contained those values.

Yes, the model broke some normalization rules, But there were two
significant gains in this approach. First, printing the itinerary was a
cinch, since all the details were in a single table. Second, as the firm
expanded its range of offerings, I had to make almost no code changes: just
design a new detail form, add a row to the DetailTypes table, and write a
new MakeDescription procedure to assemble the text string for Description.

You might consider this approach in your animal app. The named queries hide
all the columns irrelevant to the particular disposition type, while still
allowing you to view/present them in a single subform.

Arthur
​