in the last few days I have started using a program called Dependency Checker, which you can read about here, and download from here. the program is an ACCESS add-in that does what it says on the tin, it checks the dependencies in your database and provides reports on those dependencies allowing users to analyse their database, improve and tidy up their code, remove anything that is no longer relevant to their needs and improve their understanding of what the database actually does.

however, I am struggling to understand how, exactly, to "analyse" the output from the program, how to understand what the program is telling me and how to achieve the benefits I have just described. so I thought the community of active ACCESS users would include folk who have a lot more experience with this and can provide some very useful insight into what the programme is saying and how to use it.

for this thread I want to focus on understanding Missing Objects, as identified by Dependency Checker, so whether you are able to answer any of my specific questions or not if you have any insight into what a Missing Object is, how to identify and correct them, or how to analyse the output from Dependency Checker to advantage then please feel free to chip in.

the program documentation defines a Missing Object as follows: "Missing objects result from broken dependencies. An example is a query that has been built on a table that has been deleted in the mean time. Then the table is listed as a missing object.
This option is most useful with the "Bottom-Up" selection. Then the missing objects will show the objects that require them as their children."

note: in Dependency Checker, Bottom Up and Top Down are different ways of looking at parent/child relationships. Top Down starts with a Report or Form displaying output and works its way down through the queries to any underlying tables; Bottom Up does the same thing the other way around.

I analysed Reports, then displayed the results of Missing Objects/Bottom Up. this displayed the output shown in the attachment. I have clicked on the node for the bottom query in the list to reveal the parent/child relationships associated with this particular query. so far, I think the red question mark is just a symbol that means, "this object is a query."

this output says that the Dependency Checker has identified the query qry_recordlist_most2 as a Missing Object. from the programme's own definition of Missing Object that would seem to imply that it doesn't have either a parent or child, or, that there is somewhere in its hierarchy a broken dependency. the problem is, identifying it.

for this particular query the chain starts with


1. qry_recordlist_records
Code:
SELECT tbl_race.event, tbl_race.oi, Nz([rd],0) AS ht, tbl_race.venue, tbl_race.rundat, tbl_race.athlete, tbl_race.perf, tbl_race.cha, tbl_race.meet, tbl_race.rec, tbl_race.code
FROM tbl_race
WHERE (((InStr(1,[rec],"01",1))>"0"))
ORDER BY tbl_race.rundat;
this query pulls data from tbl_race, which definitely exists. without this table there wouldn't be a database. the query identifies records that are World Records.

2. qry_recordlist_most0
Code:
SELECT qry_recordlist_records.athlete, Count(qry_recordlist_records.athlete) AS records
FROM qry_recordlist_records
GROUP BY qry_recordlist_records.athlete
ORDER BY Count(qry_recordlist_records.athlete) DESC;
this aggregates data from the previous query, Groups it by athlete, and counts how many world records each athlete set.

3. qry_recordlist_most1
Code:
SELECT Max(qry_recordlist_most0.records) AS most
FROM qry_recordlist_most0;
this aggregates data from the previous query, finding the athlete(s) who set the most World Records.

4. qry_recordlist_most2
Code:
SELECT tbl_race.event, IIf([tbl_race.oi]="i",Switch(IsNull([ea]),racetime([perf])+"i",Not IsNull([ea]),racetime([perf])+"i "+[ea]),Switch(IsNull([ea]),racetime([perf]),Not IsNull([ea]),racetime([perf])+" "+[ea])) AS mark, 
tbl_race.pos, [fname] & " " & [sname] & " (" & [c0de] & ")" AS name, tbl_race.rundat, tbl_race.venue, tbl_race.meet
FROM (qry_recordlist_most1 LEFT JOIN qry_recordlist_most0 ON qry_recordlist_most1.most = qry_recordlist_most0.records) LEFT JOIN tbl_race ON qry_recordlist_most0.athlete = tbl_race.athlete
WHERE (((InStr(1,[rec],"01",1))>"0"))
ORDER BY tbl_race.rundat;
this is the supposedly Missing Object. it lists the records from the first query, associated with the athlete identified as having set the most World Records. it sorts them into date order. this is the output that I want to appear in the report.

5. Record Source property of the report: sub_records_most
Code:
SELECT [qry_recordlist_most2].[event], [qry_recordlist_most2].[mark], [qry_recordlist_most2].[pos], [qry_recordlist_most2].[name], [qry_recordlist_most2].[rundat], 
[qry_recordlist_most2].[venue], [qry_recordlist_most2].[meet]
 FROM qry_recordlist_most2;
this just displays the query output in a report format, rather than in tablesheet view.

that report is just one of three sub-reports that appear on the main report, rpt_worldrecordsmost, which is shown as an attachment, so it clearly exists, and it works.

so, is anyone able to offer any insight into how or why the query qry_recordlist_most2 is identified as a Missing Object, what that means in practical terms and what if anything I can or could do about it, or about other aspects of Dependency Checker such as how to use its output to achieve any of the objectives identified at the beginning of this post?





Click image for larger version. 

Name:	dcmissingobject.jpg 
Views:	11 
Size:	29.0 KB 
ID:	41548Click image for larger version. 

Name:	reportworldrecord.jpg 
Views:	11 
Size:	103.6 KB 
ID:	41549


many thanks,


Cottonshirt