How do i use subforms and reports with self joins??

[Barry A&P]

I have been working on a Aircraft parts database and i truely appreciate the
great answers and help ive been getting. I have been happy with my progress
and i have been having a great time but i have been having a little trouble
figuring out the best way to deal with self join data. here are some of my
tables.

T_PartNumbers
PNID (pk)
PartNumber
Description
Life
(CategoryID (fk)

T_SerialNumbers
SNID (pk)
SerialNumber
NextHigherAssyID (fk) (self join to SNID)
LocationID (fk)

T_installHistory
InstallID (pk)
SNID (fk)
InstallTime
RemovalTime
Notes

some of my forms are

F_SerialNumbers (based on Q_AllItems)
SNID (pk)
PartNumber
Description
SerialNumber
ParentID (combo)

F_Sub_InstallHistory
SNID
ParentID
InstallTime
RemovalTime
TimeInService (=RemovalTime-InstallTime)
TimeRemaining (=T_Partnumbers.Life-TimeInService)

My main form is based on T-SerialNumbers as S/N individuates each item.
i can use my form to enter Times and installation data for each serialized
item but i find myself searching around alot to update sub-component times.

How would i do something like a subform that would display all of the
Sub-Components associated to the Major component listed on the main form. and
group them by Sub-Assemblies.

Also how would i run a report to show a tree of all related sub-assemblies,
and sub-components.

would anybody be willing to share a database sample that reports self join
data.

would anybody be willing to look at my database to see if i am way off base?
Or If anybody has more insight on what im doing it would be greatly
appreciated. here are some criteria or goals i am trying to meet.

1. A Part Number (P/N) can have many Serial Numbers (S/N)
2. Different P/N's can have same S/N (most Sub-components are serialized to
assembly)
3. A P/N belings to a category, a Category has many P/N's
4. A S/N may either be a parent assembly, A sub-Assembly or a Sub Component
5. A S/N must belong to a current Location (or Parent that has a current
location) history must be kept of previous locations (or Parents).
6. If Time in service is updated on a parent S/N the sub-S/N's must also
reflect a time update.
7. when a Major assembly is added it will be convienient to also add its sub
assemblies from existing recorded items or by adding new records.
8. If a report is requested on a S/N Assembly it will show all
Sub-Components, Sub assemblies, and Sub-assembly Sub-Components. Or just
Sub-Assemblies.
9A. A S/N component may have a Life limit. #of hours in service.
9B. A Sub component may have a shorter life than its parent.
9C. Current Time in service is applied to the highest level parent, Low time
sub components must be easy to report, or alert if a time threshold has been
exceeded.

Am i out of my league or what.

 

[Tom van Stiphout]

On Fri, 14 Nov 2008 16:09:03 -0800, Barry A&P

There is no way in Access to write a recursive query. You can drag the
same table on the query design surface N times and this would allow
you to build a N-level recursive query, but you cannot build a
unlimited-level query. If that is a major requirement, you will need
to either look at SQL Server (there is a free version) or at
pre-processing your query into a temp table.

In general it is best to discover those things BEFORE embarking on a
major project. Hire competent help if needed.

-Tom.
Microsoft Access MVP

 

[Barry A&P]

Thanks Tom
Fortunately i only have to deal with 4 or 5 generations of assemblies. i
have done the n(4) level recursive query as crazily complicated as it is.
but i am having a hard time dealing with the results. I have experimented
with Left and Right joins in the query but i am not saavy enough to get the
results in a single table with all of the nulls and repeats eliminated.

Question1: the results i get are a query with all of the parents on the left
and children to the right (back 4 generations)
IE:
4level Recursive Query
ParentID
ParentName
Gen1ID
Gen1Name
Gen2ID
Gen2Name
Gen3ID
Gen3Name

All of the Parents are populated
Most of gen1 are populated
Some of gen2 are populated
very few Gen3 are populated

is there a way to report these results without having to account for all of
the null gen1,2&3's

Question 2: If i am Retarded Where do i find "Competent Help" and how much
might i have to spend?

 

[Tom van Stiphout]

On Fri, 14 Nov 2008 22:01:01 -0800, Barry A&P

Email me a zipped copy of your db and I will take a look. Probably
needs outer join queries and tests for Null. Omit .no.spam in my email
address.

-Tom.
Microsoft Access MVP