Need help with a query

[John M. Vazquez]

Hi,

I am trying to build a genealogy from my parts database. Every part has a
unique id. These ids are linked to the next part that is used by that part.
What I need to do is to research a part and all the part that are link to it
and then I need the part s that are link to those parts linked to the first
part and so forth. So it would be like a genealogy tree grandfather, father,
son, and so forth till the last gen.

If anyone can help please do so.

Thanks,

Yanen

 

[Arvin Meyer [MVP]]

Have a look at Allen Browne's pedigree example:

http://www.allenbrowne.com/ser-06.html
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[John M. Vazquez]

Thanks,

Have one question what if and this is going to happen I have a father with
multiple sons

Thanks,

yanen.

 

[John M. Vazquez]

Ok ifound out what happens. However when I run this query I am not getting
all the table. I lose more and more parts as I to the design view. Now I
query with 8 records and have only 5 parts show?

Can ne1 help me? Please.

Thanks,

John M. Vazquez

 

[Allen Browne]

John, I'm not sure what's happening with your query.

Did you use an outer join between tables, as mentioned in Steps 2 and 5 of:
http://allenbrowne.com/ser-06.html
If so, are the arrowheads pointing the right direction between the tables
(as in the screenshot)?

Or did you add criteria to the fields on the outer side of the join?

For more info about those 2 ideas, see:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

If that does not address your issue, post your SQL statement so we can see
more of what your query is doing.

 

[John M. Vazquez]

Ok thanks this helped a lot the joins were wrong. Now I have them right.
Ok now that that problem is somewhat solved. I want to ask how can I get
this query add another table to the relationship until it find no Item left.
I need this so I don't have to change the query everytime I add and item.

Thanks,
john

 

[Allen Browne]

Hi John

Good. Outer joins are crucial.

The suggested design copes with a fixed number of generations--4 generations
in the example. Less than that will be fine, but it starts to get messy
where you have more and want to trace the entire possible tree.

There is also the added problem of circular data, where a record is its own
ancestor. (In most cases, this represents bad data entry, but there are some
logical structures where this kind of thing might represent valid data.)
There is, therefore, always the possibility of infinite recursion in any
attempt to resolve this kind of data. If possible, therefore, my preferred
approach is to write some VBA code that populates a temp table with a
resolved set of data, and work from that. The code works to a limited depth,
e.g. 5 generations, and pikes out with an error message if it finds
recursive data or records that cannot be resolved to that depth.

If you want to try to resolve this kind of structure with SQL rather than
with VBA code, you should probably read Joe Celko's stuff. It's not simple
to implement though. Some leads:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

 

[John M. Vazquez]

I saw your web site. And found an article this was a message that I was
sending and sent it to my self lol.

Ok I went to your website Allen and I found out it can be done but I am
reading it a forth time and trying to relate it to what I am doing. I know
that look at the sql trees will work for me. But trying to get my code there
and understanding the article will take some time. If you can help me
understand it more please do.

Thanks

john

 

[Allen Browne]

Not sure I can provide further help here.

If you are still struggling with the example at:
http://allenbrowne.com/ser-06.html
I suggest you create a table named "tblHorses", with at least these fields:
ID AutoNumber primary key
SireID Number
DamID Number
Be sure to remove the 0 from the default value for the 2 number fields.
After saving the table, you can create a new query, switch to SQL view (View
menu), paste in the SQL statement from the webpage,and see the results.

If you are struggling with the Celko stuff, I can't help with that.

If you are stuggling with the VBA code, again that's beyond the help we can
give here.