BOM structure question..Please help a newbie

[ecoli]

Hi All,

I hope this isn't a common question that you are sick of answering. I have a big table on an AS400 I am querying through Access using ODBC. The table is basically a parent-child structure, see below for an example:

PARENT CHILD CHILD-DESCRIPTION
U.S. South Carolina State
U.S. New York State
U.S. New Jersey State
South Carolina Columbia City
New York New York City City
South Carolina Charlestown City
Confederacy South Carolina State
Confederacy Alabama State

I need a query to produce a bill of materials for a given Parent that will go down N number of levels, until it hits the bottom. A child can have multiple parents, and parents can have multiple children.

A BOM for the U.S. from this table would look something like this:

Level Item Description
1 U.S.
2 South Carolina State
3 Columbia City
3 Charlestown City
2 New York State
3 New York City City
2 New Jersey State

So, what should I do? Try to hurt my head with recursion? Do a nested loop? I don't have a way to know the max "depth" of the BOM. Could be 5 or 6 levels, or more.

I am fairly new to Access and SQL, so if any of you gurus out there could help me out with the SQL, I sure would appreciate it.

I've been a lurker for some time, but this is my first time posting, so please be gentle.

Thanks,
Eric

 

[tlbroadbent]

Take a look at Joe Celko's solution at the MVPS Access site. You can download his sample Access DB.

http://www.mvps.org/access/queries/qry0023.htm

Note: using Celko's approach will probably reqire table schema changes. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[ecoli]

Hi Tlbroadbent,

Thanks for replying. I have seen some of Celko's nested solutions before, and one of the requirements for them was that each child can only have one parent. In my case, a child can have multiple parents. That is why I haven't tried to implement that particular solution already. Am I missing something, or will this work for a child with multiple parents?

Also, changing the table in the AS400 is out of the question. I want to link to it, not download it, because it is "live" data. I'm pretty much stuck with it. Given that, what can I do?

Not trying to be a pain, just trying to understand.

Thanks for your patience with me.

Eric

 

[B827]

If your data is set up as shown in a single table the you could try a query on a query with sorting and grouping in a report.

I have called your table data source.

First query "CountryTOSate"

SELECT DISTINCT Datasource.Level1, Datasource.Level2
FROM Datasource AS Datasource_1 RIGHT JOIN Datasource ON Datasource_1.Level2 = Datasource.Level1
WHERE (((Datasource.Description)="State&quot);

Second query "BOM":

SELECT CountryToState.Level1 AS Country, CountryToState.Level2 AS State, Datasource.Level2 AS City, Datasource.Description
FROM CountryToState INNER JOIN Datasource ON CountryToState.Level2 = Datasource.Level1;

Then use BOM as the source of a report and group on Country with a group header and put the country in the header and have City and description in the detail. This may not be the perfect solution but may be something to work on.
Sandy

 

[B827]

Re-reading your question you'll need a group header on City as well. Sandy