1 Reply by brianmiller

  • brianmiller
  • Veteran
  • Offline
  • Registered: 10-05-2006
  • Posts: 113

Re: Incl. Parent Location in SQL report

I am wanting to include the parent name of a location within the SQL report in R2K2: |All records|Location|All records for location(s) in rucksack, as well as including the subsites, i.e. to see what the subsite is under.

I have managed to edit it to show the parent location, but it seems to be only showing the parent and not the subsites as the records go from over 800 to just over 300 records.

I assumed the process of including this would be similar to including the common name of a species, but it appears not to be the case. I have tried a number of permutations but with nothing producing the result I need.

Can anyone help direct me to where I have gone wrong with this please? I have attached the SQL code below of what I have done to the query.

SELECT SAMPLE.LOCATION_NAME AS [Sample Location], LOCATION_NAME_1.ITEM_NAME AS [Parent Name], LOCATION_NAME.ITEM_NAME AS [Location Name], LOCATION.FILE_CODE, IIf([SAMPLE]![SPATIAL_REF] Is Null,Null,Switch([SAMPLE]![SPATIAL_REF_SYSTEM]="OSGB",Left$([SAMPLE]![SPATIAL_REF],3) & Mid$([SAMPLE]![SPATIAL_REF],(Len([SAMPLE]![SPATIAL_REF])-2)/2+3,1),[SAMPLE]![SPATIAL_REF_SYSTEM]="OSNI",Left$([SAMPLE]![SPATIAL_REF],2) & Mid$([SAMPLE]![SPATIAL_REF],(Len([SAMPLE]![SPATIAL_REF])-2)/2+2,1),Left$([SAMPLE]![SPATIAL_REF_SYSTEM],2)<>"OS",Null)) AS 10Km, INDEX_TAXON_NAME.PREFERRED_NAME AS [Scientific name], IIf([INDEX_TAXON_NAME]![PREFERRED_NAME]<>[INDEX_TAXON_NAME]![COMMON_NAME],[INDEX_TAXON_NAME]![COMMON_NAME],Null) AS [Common name], SAMPLE.VAGUE_DATE_START, SAMPLE.VAGUE_DATE_END, SAMPLE.VAGUE_DATE_TYPE, SAMPLE.SPATIAL_REF AS [Grid ref], Trim([INDIVIDUAL]![FORENAME] & " " & [INDIVIDUAL]![SURNAME]) AS Recorder, SAMPLE.SAMPLE_REFERENCE AS [Sample ref], SAMPLE_TYPE.SHORT_NAME AS Method, LOCATION_NAME.LOCATION_KEY
FROM ((INDIVIDUAL INNER JOIN SURVEY_EVENT_RECORDER ON INDIVIDUAL.NAME_KEY = SURVEY_EVENT_RECORDER.NAME_KEY) INNER JOIN (((SAMPLE_TYPE INNER JOIN ((LOCATION AS LOCATION_1 INNER JOIN LOCATION_NAME AS LOCATION_NAME_1 ON LOCATION_1.PARENT_KEY = LOCATION_NAME_1.LOCATION_KEY) INNER JOIN ((SAMPLE INNER JOIN LOCATION_NAME ON SAMPLE.LOCATION_KEY = LOCATION_NAME.LOCATION_KEY) INNER JOIN LOCATION ON (LOCATION.LOCATION_KEY = LOCATION_NAME.LOCATION_KEY) AND (SAMPLE.LOCATION_KEY = LOCATION.LOCATION_KEY)) ON LOCATION_1.LOCATION_KEY = SAMPLE.LOCATION_KEY) ON SAMPLE_TYPE.SAMPLE_TYPE_KEY = SAMPLE.SAMPLE_TYPE_KEY) INNER JOIN SAMPLE_RECORDER ON SAMPLE.SAMPLE_KEY = SAMPLE_RECORDER.SAMPLE_KEY) INNER JOIN TAXON_OCCURRENCE ON SAMPLE.SAMPLE_KEY = TAXON_OCCURRENCE.SAMPLE_KEY) ON SURVEY_EVENT_RECORDER.SE_RECORDER_KEY = SAMPLE_RECORDER.SE_RECORDER_KEY) INNER JOIN (TAXON_DETERMINATION INNER JOIN INDEX_TAXON_NAME ON TAXON_DETERMINATION.TAXON_LIST_ITEM_KEY = INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY) ON TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY = TAXON_DETERMINATION.TAXON_OCCURRENCE_KEY
WHERE (((LOCATION_NAME.LOCATION_KEY) In ([Rucksack/Location])) AND ((TAXON_OCCURRENCE.ZERO_ABUNDANCE)=False) AND ((TAXON_OCCURRENCE.CONFIDENTIAL)=[Conf_True] Or (TAXON_OCCURRENCE.CONFIDENTIAL)=0) AND ((TAXON_OCCURRENCE.VERIFIED)<>1) AND ((TAXON_OCCURRENCE.CHECKED)=True) AND ((TAXON_DETERMINATION.PREFERRED)=True) AND ((LOCATION_NAME.PREFERRED)=True Or (LOCATION_NAME.PREFERRED) Is Null) AND ((LOCATION_NAME_1.PREFERRED)=True Or (LOCATION_NAME_1.PREFERRED) Is Null))
ORDER BY SAMPLE.LOCATION_NAME, LOCATION_NAME.ITEM_NAME, IIf([SAMPLE]![SPATIAL_REF] Is Null,Null,Switch([SAMPLE]![SPATIAL_REF_SYSTEM]="OSGB",Left$([SAMPLE]![SPATIAL_REF],3) & Mid$([SAMPLE]![SPATIAL_REF],(Len([SAMPLE]![SPATIAL_REF])-2)/2+3,1),[SAMPLE]![SPATIAL_REF_SYSTEM]="OSNI",Left$([SAMPLE]![SPATIAL_REF],2) & Mid$([SAMPLE]![SPATIAL_REF],(Len([SAMPLE]![SPATIAL_REF])-2)/2+2,1),Left$([SAMPLE]![SPATIAL_REF_SYSTEM],2)<>"OS",Null)), INDEX_TAXON_NAME.PREFERRED_NAME, SAMPLE.VAGUE_DATE_START DESC , INDEX_TAXON_NAME.PREFERRED_NAME;

Many thanks

Brian Miller

Brian Miller
(Conservation Officer (Buckinghamshire), BBOWT)

brianmiller's Website