O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Flattening Data

Problem

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.

Solution

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 Distinct and 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.

Related one-to-many data

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required