Interleaving tables in query

Question

Greetings,

One last one (for now): I have two tables, Table A and Table B.  There is a relationship where just about every record in Table B has a parent record in Table A.  (The few "orphans" I have are not a problem right now.)  That is, any record in A has 1 (0) ... N associated records in B, linked by indexed ids.

What I would like to do is "interleave" the data from Table A and Table B in such a way that the first record in A is the first record in the resulting query or table, then that record's children (related records in B), then the next record in A and its children from B, etc. all the way until I'm through all records in both tables.

What kind of query am I looking to do?  The answer seems like it should be simple, but it's not coming to me.  I'm assuming there'll be some kind of "Group By" clause in the query because I will ultimately want to display each parent with its children beneath it, then the next parent and its children, and so on.

Thanks for reading,

Mike

Answer 1

Could you explain the nature of the data? It sounds like you have the same "kind" of data in the two tables, with almost but not quite matching table structures. What will you be doing with this interleaved result once you get it? I suspect there may be a better table structure, or some other solution to the real-world problem you're trying to solve - but at the moment I'm not understanding the goal or the tables!

 

Ah, okay.  So here's the deal: I've been working on setting up a basic requirements management database.  My "schema", if you will, is that there are high-level expressions of desired system requirements (source requirements) and lower-level decomposed (derived) requirements which stem from the source requirements.  The two sets are stored in two different tables, with the exception that the derived requirements table has an extra numeric field which links back to the ids in the source requirements table, in effect, giving 1...N derived requirements a parent.

Previously, we have been doing all of our requirements-management work in Excel spreadsheets, and as you can guess, this has been a nightmare: multiple copies of the files, no way to control them, changing ids with scattershot naming conventions, no enforced relationships of any kind, etc.  So what people generally do is, after all the source requirements are written, one to a row in the spreadsheet, rows are inserted underneath each source for as many (again, one to a row) derived requirements as have been decomposed from the source.

The desire is ultimately to produce an Excel spreadsheet or even an Access report which looks the same way, but since it's in Access and I've split up the requirements, I need to pull from both the tables and "put them in order" just as they would be in the spreadsheet.  Right now, I've got all of the fields of interest, except the parent field in the case of the deriveds, but that is a known "issue", in the sense that I obviously can't union different sets of fields from tables of differing structure as you mentioned.

But I know and have used GROUP BY to do a "rollup" in essence of things beneath a topic.  For example, if I had a query or report which listed the systems in an aircraft, I might have line items like "propulsion system", "fuel system", and "hydraulic system", then under each, I would have things like the items particular to each system (nesting).

Hopefully this clears things up a bit.

Mike

Answer 2

Could you explain the nature of the data? It sounds like you have the same "kind" of data in the two tables, with almost but not quite matching table structures. What will you be doing with this interleaved result once you get it? I suspect there may be a better table structure, or some other solution to the real-world problem you're trying to solve - but at the moment I'm not understanding the goal or the tables!

Answer 3

A Table or a Query must be "homogenous" - all records must have the same number of fields of matching datatypes. You can't interleave disparate types of records in the way you describe.

 

If, however, your purpose is to display the data on screen or on paper, you can use the capabilities of the Access Report to do this. Just create a query joining the two tables, and use the report's Sorting and Grouping dialog to create a report in which the Table A data is displayed on the group header, and the Table B data in the report's Detail section.

I should have clarified that both tables do indeed have the same number of fields, in the same order, with the same types.

Mike

Actually, I think I might not be correct.  I just realized that one field extra in Table B is the pointer (id) back to the "parent" record in Table A.  So that probably won't work.

However, I did just manage to use a simple UNION between the two SQL blocks which give me all the common fields between two.  Now how it chose to order/group them...well, that's a bit of a mystery.  I think it just grabbed the ids and ran them in numerical order, so the first column in the results has duplicates ids, which I expected.

So, I got all of my records back, but I just need to know how to order/group them.  More later...

Mike

Answer 4

A Table or a Query must be "homogenous" - all records must have the same number of fields of matching datatypes. You can't interleave disparate types of records in the way you describe.

 

If, however, your purpose is to display the data on screen or on paper, you can use the capabilities of the Access Report to do this. Just create a query joining the two tables, and use the report's Sorting and Grouping dialog to create a report in which the Table A data is displayed on the group header, and the Table B data in the report's Detail section.

I should have clarified that both tables do indeed have the same number of fields, in the same order, with the same types.

Mike

Answer 5

A Table or a Query must be "homogenous" - all records must have the same number of fields of matching datatypes. You can't interleave disparate types of records in the way you describe.

If, however, your purpose is to display the data on screen or on paper, you can use the capabilities of the Access Report to do this. Just create a query joining the two tables, and use the report's Sorting and Grouping dialog to create a report in which the Table A data is displayed on the group header, and the Table B data in the report's Detail section.