Exporting/Visualizing Multiple One-to-Many Relationships

Question

I've searched all over and can't find a good answer to this, so here goes. By the way, I am working with Access 2010.

My data structure is this: I have a table, Screeners, with an autonumber ID column. I have three other tables, PsychMedications, GeneralMedications, and BrainStims, each with a required foreign key field "ScreenerID" to connect them to a Screener. Each of these relationships are one-to-many relationships, such that each Screener may have 0 or more associated PsychMedications, GeneralMedications, and/or BrainStims records. This is all working fairly comfortably, but I hit a snag: How can multiple one-to-many relationships be exported in a neat and tidy way?

Initially, I was planning to export this like I was exporting some other information in my database, to Excel. However, because of the multiple relationships, it's very hard to find an effective way to do this. Having a query that combines all of them results in numerous near-duplicate records, such that each screener can easily end up taking up 10-15 lines in excel or more. 

Another idea I had was to make the fields in the child table fields in the query, but somehow duplicate all of the fields dynamically based on the largest number of records associated with a screener. For example, a PsychMedName1 field would contain the first PsychMedName for each screener, and PsychMedName2 would contain the 2nd PsychMedName for each screener, and so on, until there was enough duplicated fields to contain all of the data, and if a screener didn't have as many records as the screener with the most records, its extra fields would just be blank. I tried to achieve this with crosstab queries, but could not find a way to do it. I tried crosstab queries, but was unable to figure out a sort of row aggregation.

My final idea was to do a report. The dream is that it would be grouped by screening. Within each screening would be all of the information found in the Screeners table, then, still within the grouping of screener, groupings for each child table, which themselves are grouped by ID. However, I've hardly worked at all with Reports, and am having a hard time with this. Any time I try to add grouping by child table in the report, it groups everything, not just things in its associated child table.

Anyone run into this problem before that could give me some pointers on good ways to solve this problem, or ways to successfully do any of the methods I've identified?

Answer 1

If you are merely looking for a cohesive output format, rather than the ability to use the analytical capabilities of Excel a report is likely to be the best solution.  Rather than grouping a single report,  you are more likely to achieve the results you are aiming for by creating a parent report based on screeners, and embedding three subreports within that, each based on one of the referencing tables.  The subreports would be linked to the parent report on ID/ScreenerID (I'd strongly recommend that you change the name of the primary key of the Screeners table to ScreenerID also, however).

Using subreports like this gives you complete independence over the layout of each subreport, so each can be designed in such a way as to reflect the particular attributes of the entity type modelled by the referencing table in question.

If the attributes of each referencing table are the same you could use a single grouped report, but it would be based on a UNION ALL query which returns the rows from the result tables of three separate JOINs of the Screeners table to each referencing table respectively as a single result table.  You would, in each part of the UNION ALL query, return a literal string expression as a constant, e.g. "Psychiatric Medications", "General Medications" and "Brain Stimulants".  You would then group the report first on screener and then on the column returning these constants.  This would constrain you to the same layout for each group, however, unlike subreports.

Answer 2

Well the issue here is that Excel is a spreadsheet and Access is a relational database. The two are different and it is not easy for Excel to display data relationally.

So my first question to you is WHY export? Since you are dealing with relational data and you have the relations working to your satisfaction, what benefit or advantage do you perceive in exporting?

The only real way to export is to create a query where the Screener data is going to repeat for each record in the child tables. it would then be up to you to format the spreadsheet to accommodate your needs. But do you need to export?

Answer 3

Thanks for the responses!

Steve: To be honest, when I posted, I wasn't entirely sure of the purpose of exporting either. I came in to this database when it was only partially completed, and back then, the way the data was organized made it appropriate for export. However, it was also very poorly normalized; but in normalizing it, I created this particular problem for myself. After consultation with my supervisor, it's not totally clear whether this will be a necessary feature, but I'd still like to have the option available if needed.

Ken: Thank you for the tips! I don't think your second suggestion will work as each child has different attributes and so a UNION ALL won't be suitable. However, subreports may be exactly what I need. I wasn't even aware you could have subreports! I'll have to play around with this a bit more and see how they work, but otherwise, I'm pretty sure this will be my solution (if it's even needed).

Cheers!