How can relational data in a parent-child table relationship be combined to fit into one table? The data is meant for analysis in Excel, where the concept of one-to-many data is not well supported.
Whenever you run a select query based on parent and child tables, you'll usually see a higher degree of duplication in the data returned than you'll see when you run a query based on a single table. Of course, different factors are at play here, such as which fields (particularly those from the child table) are used. The use/avoidance of the
DistinctRow predicates (see Returning Distinct Records) also affects the result.
Figure 6-13 shows the Access Relationships window. In this schema, for each client record from tblClients, there can be zero or more pet records in the tblPets table. Likewise, for each pet record from tblPets, there can be zero or more appointment records in the tblAppointments table.
Figure 6-13. Related one-to-many data
Because many of the parent records will have multiple child records, when a select query includes fields from a child table, fields from the parent table are likely to be duplicated. Figure 6-14 shows a query design that returns records based on data from the parent and child tables.
When this query is run, each client will be listed once for each pet she owns: if a client has three pets, the client's name will appear ...