Слишком сложный запрос. Является ли конструктор запросов доступа испортить SQL?
ArrayIndexOutOfBoundsException означает, что вы пытаетесь получить доступ к индексу массива, который не существует или не связан с границей этого массива. Индексы массива начинаются с 0 и заканчиваются на длину - 1.
В вашем случае
for(int i = 0; i<=name.length; i++) {
System.out.print(name[i] +'\n'); // i goes from 0 to length, Not correct
}
ArrayIndexOutOfBoundsException происходит, когда вы пытаетесь получить доступ к элементу index.length, который делает не существует (индекс массива заканчивается на -1). просто заменяя & lt; = с & lt; решит эту проблему.
for(int i = 0; i < name.length; i++) {
System.out.print(name[i] +'\n'); // i goes from 0 to length - 1, Correct
}
0
задан Parfait 4 March 2019 в 19:27
поделиться
1 ответ
Это не просто ответ, а скорее первый шаг для рассмотрения. Я возьму этого монстра и получу синтаксический анализатор / форматировщик sql, чтобы сделать его читаемым. SQLInForm плагин для Notepad ++ - это то, что я использовал здесь:
SELECT
tblRecord.RecRecordNumber
, tblUVNumber.UVNo
, tblRecord.RecDate
, tblRecord.RefNoInformer
, tblPersons.PerFullName
, tblPersons.PerOfficielObserver
, tblLocation.LocCountry
, tblLocation.LocProvince
, tblLocation.LocMuncipality
, tblLocation.LocNearestTown
, tblLocation.LocAreaName
, tblFieldTrip.FiTPathNumber
, tblValidate.ValSCALP
, tblRecord.RecValidateYes
, tblRecord.RecFieldTripYes
, tblRecord.RecScatYes
, tblRecord.RecPreyYes
, tblRecord.RecOtherDNAYes
, tblRecord.RecSightingYes
, tblRecord.RecTrackYes
, tblRecord.RecHowlYes
, tblRecord.RecUrinBloodYes
, tblRecord.RecCameraYes
, tblRecord.RecDenYes
, tblValidate.ValInsideWolfZone
, tblRecord.RecSampleType
, tblRecord.RecSampleEvidence
, tblRecord.RecDogPresent
, tblRecord.RecGELat
, tblRecord.RecGELong
, tblRecord.RecCoordinates
, tblRecord.RefNoCoorSys
, tblRecord.RecUTMQuadrant
, tblRecord.RecUTMCoorX
, tblRecord.RecUTMCoorY
, tblLocPrecision.LocationPrecision
, tblRecord.RefNoValidate
, tblValidate.ValSenckLabID
, tblValidate.ValSenckType_mtDNA
, tblValidate.ValSenckHaploType
, tblValidate.ValSenckInfo_mtDNA
, tblValidate.ValSenckType_KernDNA
, tblValidate.ValSenckInfo_KernDNA
, tblAnimal_1.AniNumberOfAnimals
, tblAnimal.AniNumberOfAnimals
, tblPersons_1.PerFullName
, tblPersons_2.PerFullName
, tblPersons_3.PerFullName
, tblPersons_4.PerFullName
, tblPersons_5.PerFullName
, tblPersons_6.PerFullName
, tblPersons_7.PerFullName
, tblPersons_8.PerFullName
, tblPersons_9.PerFullName
, tblValidate.RefNoValSpecies
, tblValidateSpecies.ValSpeGenus
, tblValidateSpecies.ValSpeSpecies
, tblValidateSpecies.ValSpeDanishName
, tblPack.PackName
, tblIndividual.Individual
, tblIndividual.IndiSex
, tblIndividual.IndiBornYear
, tblIndividual.IndiBornPlace
, tblIndividual.IndiDead
, tblIndividual.IndiDeadCause
, tblValidate.RefNoProbIndiGuess
, tblValidate.ValIndiAgeAtSampleTime
, tblTerritoryName.TerritoryName
, tblMonitoringYear.MonYear
, tblPhotoDoc.PhDPhotoNotes
, tblPhotoDoc.PhDPermissionToUsePhotoText
, tblPhotoDoc.PhDPhotoFiles
FROM
(tblDen
INNER JOIN
(tblIndividual
INNER JOIN
((tblValidateSpecies
INNER JOIN
(tblPack
INNER JOIN
tblValidate
ON
tblPack.KeyPack = tblValidate.RefNoPack)
ON
tblValidateSpecies.KeyValidateSpecies = tblValidate.RefNoValSpecies)
INNER JOIN
(tblUVNumber
INNER JOIN
((tblPersons AS tblPersons_8
INNER JOIN
tblUrinBlood
ON
tblPersons_8.KeyPersons = tblUrinBlood.RefNoWriterUrinBlood)
INNER JOIN
((tblPersons AS tblPersons_7
INNER JOIN
tblTrack
ON
tblPersons_7.KeyPersons = tblTrack.RefNoWriterTrack)
INNER JOIN
(tblTerritoryName
INNER JOIN
((tblAnimal
INNER JOIN
(tblPersons AS tblPersons_2
INNER JOIN
tblSighting
ON
tblPersons_2.KeyPersons = tblSighting.RefNoWriterSight)
ON
tblAnimal.KeyAnimal = tblSighting.RefNoAnimal)
INNER JOIN
((tblPersons AS tblPersons_6
INNER JOIN
tblScat
ON
tblPersons_6.KeyPersons = tblScat.RefNoWriterScat)
INNER JOIN
((tblPersons AS tblPersons_5
INNER JOIN
tblPrey
ON
tblPersons_5.KeyPersons = tblPrey.RefNoWriterPrey)
INNER JOIN
(tblPhotoDoc
INNER JOIN
(tblPersons
INNER JOIN
((tblPersons AS tblPersons_4
INNER JOIN
tblOtherDNA
ON
tblPersons_4.KeyPersons = tblOtherDNA.RefNoWriterOther)
INNER JOIN
(tblMonitoringYear
INNER JOIN
(tblLocPrecision
INNER JOIN
(tblLocation
INNER JOIN
((tblPersons AS tblPersons_3
INNER JOIN
tblHowl
ON
tblPersons_3.KeyPersons = tblHowl.RefNoWriterHowl)
INNER JOIN
(tblFieldTrip
INNER JOIN
((tblAnimal AS tblAnimal_1
INNER JOIN
(tblPersons AS tblPersons_1
INNER JOIN
tblCamera
ON
tblPersons_1.KeyPersons = tblCamera.RefNoWriterCam)
ON
tblAnimal_1.KeyAnimal = tblCamera.RefNoAnimal)
INNER JOIN
tblRecord
ON
tblCamera.KeyCamera = tblRecord.RefNoCamera)
ON
tblFieldTrip.KeyFieldTrip = tblRecord.RefNoFieldTrip)
ON
tblHowl.KeyHowl = tblRecord.RefNoHowl)
ON
tblLocation.KeyLocation = tblRecord.RefNoLocation)
ON
tblLocPrecision.KeyLocPrecision = tblRecord.RefNoPrecision)
ON
tblMonitoringYear.KeyMonYear = tblRecord.RefNoMonYear)
ON
tblOtherDNA.KeyOtherDNA = tblRecord.RefNoOtherDNA)
ON
tblPersons.KeyPersons = tblRecord.RefNoInformer)
ON
tblPhotoDoc.KeyPhoto = tblRecord.RefNoPhotoDoc)
ON
tblPrey.KeyPrey = tblRecord.RefNoPrey)
ON
tblScat.KeyScat = tblRecord.RefNoScat)
ON
tblSighting.KeySigthing = tblRecord.RefNoSighting)
ON
tblTerritoryName.KeyTerritoryName = tblRecord.RefNoTerritoryName)
ON
tblTrack.KeyTrack = tblRecord.RefNoTrack)
ON
tblUrinBlood.KeyUrinBlood = tblRecord.RefNoUrinBlood)
ON
tblUVNumber.KeyUVNo = tblRecord.RefNoUVNo)
ON
tblValidate.KeyValidate = tblRecord.RefNoValidate)
ON
tblIndividual.KeyIndividual = tblValidate.RefNoIndi)
ON
tblDen.KeyDen = tblRecord.RefNoDen)
INNER JOIN
tblPersons AS tblPersons_9
ON
tblDen.RefNoWriterDen = tblPersons_9.KeyPersons
WHERE
(
(
(
tblRecord.RecRecordNumber
)
Like [Formularer]![frmSearchGeneral].[FindRecord] & "*"
)
AND
(
(
tblUVNumber.UVNo
)
Like [Formularer]![frmSearchGeneral].[FindUV] & "*"
)
AND
(
(
tblRecord.RecDate
)
Between IIf([Forms]![frmSearchGeneral].[FindDateStart] Is Null,#1/1/1000#,[Forms]![frmSearchGeneral].[FindDateStart]) And IIf([Forms]![frmSearchGeneral].[FindDateEnd] Is Null,#12/31/9999#,[Forms]![frmSearchGeneral].[FindDateEnd])
)
AND
(
(
tblPersons.PerFullName
)
Like [Formularer]![frmSearchGeneral].[FindPerson] & "*"
)
AND
(
(
tblPersons.PerOfficielObserver
)
Like [Formularer]![frmSearchGeneral].[FindOfficial] & "*"
)
AND
(
(
tblLocation.LocCountry
)
Like [Formularer]![frmSearchGeneral].[FindCountry] & "*"
)
AND
(
(
tblLocation.LocProvince
)
Like [Formularer]![frmSearchGeneral].[FindProvince] & "*"
)
AND
(
(
tblLocation.LocMuncipality
)
Like [Formularer]![frmSearchGeneral].[FindMuncipality] & "*"
)
AND
(
(
tblLocation.LocNearestTown
)
Like [Formularer]![frmSearchGeneral].[FindNearestTown] & "*"
)
AND
(
(
tblLocation.LocAreaName
)
Like [Formularer]![frmSearchGeneral].[FindAreaName] & "*"
)
AND
(
(
tblFieldTrip.FiTPathNumber
)
Like [Formularer]![frmSearchGeneral].[FindTrackNo] & "*"
)
AND
(
(
tblValidate.ValSCALP
)
Like [Formularer]![frmSearchGeneral].[FindSCALP] & "*"
)
AND
(
(
tblRecord.RecValidateYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindValidate]=Yes,[Formularer]![frmSearchGeneral].[FindValidate],"*")
)
AND
(
(
tblRecord.RecScatYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindScat]=Yes,[Formularer]![frmSearchGeneral].[FindScat],"*")
)
AND
(
(
tblRecord.RecPreyYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindPrey]=Yes,[Formularer]![frmSearchGeneral].[FindPrey],"*")
)
AND
(
(
tblRecord.RecOtherDNAYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindOtherDNA]=Yes,[Formularer]![frmSearchGeneral].[FindOtherDNA],"*")
)
AND
(
(
tblRecord.RecSightingYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindSighting]=Yes,[Formularer]![frmSearchGeneral].[FindSighting],"*")
)
AND
(
(
tblRecord.RecTrackYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindTrack]=Yes,[Formularer]![frmSearchGeneral].[FindTrack],"*")
)
AND
(
(
tblRecord.RecHowlYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindHowl]=Yes,[Formularer]![frmSearchGeneral].[FindHowl],"*")
)
AND
(
(
tblRecord.RecUrinBloodYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindUrinBlood]=Yes,[Formularer]![frmSearchGeneral].[FindUrinBlood],"*")
)
AND
(
(
tblRecord.RecCameraYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindCamera]=Yes,[Formularer]![frmSearchGeneral].[FindCamera],"*")
)
AND
(
(
tblValidate.ValInsideWolfZone
)
Like IIf([Formularer]![frmSearchGeneral].[FindWolfZone]=Yes,[formularer]![frmSearchGeneral].[FindWolfZone],"*")
)
AND
(
(
tblValidate.RefNoValSpecies
)
Like [Formularer]![frmSearchGeneral].[FindValidateSpecies] & "*"
)
AND
(
(
tblPack.PackName
)
Like [Formularer]![frmSearchGeneral].[FindPack] & "*"
)
AND
(
(
tblIndividual.Individual
)
Like [Formularer]![frmSearchGeneral].[FindIndividual] & "*"
)
AND
(
(
tblTerritoryName.TerritoryName
)
Like [formularer]![frmSearchGeneral].[FindTerritory] & "*"
)
AND
(
(
tblRecord.Deactivated
)
=False
)
AND
(
(
Year([RecDate])
)
=[Forms]![frmSearchGeneral].[FindYearSpecific]
)
)
OR
(
(
(
tblRecord.RecRecordNumber
)
Like [Formularer]![frmSearchGeneral].[FindRecord] & "*"
)
AND
(
(
tblUVNumber.UVNo
)
Like [Formularer]![frmSearchGeneral].[FindUV] & "*"
)
AND
(
(
tblRecord.RecDate
)
Between IIf([Forms]![frmSearchGeneral].[FindDateStart] Is Null,#1/1/1000#,[Forms]![frmSearchGeneral].[FindDateStart]) And IIf([Forms]![frmSearchGeneral].[FindDateEnd] Is Null,#12/31/9999#,[Forms]![frmSearchGeneral].[FindDateEnd])
)
AND
(
(
tblPersons.PerFullName
)
Like [Formularer]![frmSearchGeneral].[FindPerson] & "*"
)
AND
(
(
tblPersons.PerOfficielObserver
)
Like [Formularer]![frmSearchGeneral].[FindOfficial] & "*"
)
AND
(
(
tblLocation.LocCountry
)
Like [Formularer]![frmSearchGeneral].[FindCountry] & "*"
)
AND
(
(
tblLocation.LocProvince
)
Like [Formularer]![frmSearchGeneral].[FindProvince] & "*"
)
AND
(
(
tblLocation.LocMuncipality
)
Like [Formularer]![frmSearchGeneral].[FindMuncipality] & "*"
)
AND
(
(
tblLocation.LocNearestTown
)
Like [Formularer]![frmSearchGeneral].[FindNearestTown] & "*"
)
AND
(
(
tblLocation.LocAreaName
)
Like [Formularer]![frmSearchGeneral].[FindAreaName] & "*"
)
AND
(
(
tblFieldTrip.FiTPathNumber
)
Like [Formularer]![frmSearchGeneral].[FindTrackNo] & "*"
)
AND
(
(
tblValidate.ValSCALP
)
Like [Formularer]![frmSearchGeneral].[FindSCALP] & "*"
)
AND
(
(
tblRecord.RecValidateYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindValidate]=Yes,[Formularer]![frmSearchGeneral].[FindValidate],"*")
)
AND
(
(
tblRecord.RecScatYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindScat]=Yes,[Formularer]![frmSearchGeneral].[FindScat],"*")
)
AND
(
(
tblRecord.RecPreyYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindPrey]=Yes,[Formularer]![frmSearchGeneral].[FindPrey],"*")
)
AND
(
(
tblRecord.RecOtherDNAYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindOtherDNA]=Yes,[Formularer]![frmSearchGeneral].[FindOtherDNA],"*")
)
AND
(
(
tblRecord.RecSightingYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindSighting]=Yes,[Formularer]![frmSearchGeneral].[FindSighting],"*")
)
AND
(
(
tblRecord.RecTrackYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindTrack]=Yes,[Formularer]![frmSearchGeneral].[FindTrack],"*")
)
AND
(
(
tblRecord.RecHowlYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindHowl]=Yes,[Formularer]![frmSearchGeneral].[FindHowl],"*")
)
AND
(
(
tblRecord.RecUrinBloodYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindUrinBlood]=Yes,[Formularer]![frmSearchGeneral].[FindUrinBlood],"*")
)
AND
(
(
tblRecord.RecCameraYes
)
Like IIf([Formularer]![frmSearchGeneral].[FindCamera]=Yes,[Formularer]![frmSearchGeneral].[FindCamera],"*")
)
AND
(
(
tblValidate.ValInsideWolfZone
)
Like IIf([Formularer]![frmSearchGeneral].[FindWolfZone]=Yes,[formularer]![frmSearchGeneral].[FindWolfZone],"*")
)
AND
(
(
tblValidate.RefNoValSpecies
)
Like [Formularer]![frmSearchGeneral].[FindValidateSpecies] & "*"
)
AND
(
(
tblPack.PackName
)
Like [Formularer]![frmSearchGeneral].[FindPack] & "*"
)
AND
(
(
tblIndividual.Individual
)
Like [Formularer]![frmSearchGeneral].[FindIndividual] & "*"
)
AND
(
(
tblTerritoryName.TerritoryName
)
Like [formularer]![frmSearchGeneral].[FindTerritory] & "*"
)
AND
(
(
tblRecord.Deactivated
)
=False
)
AND
(
(
Year([RecDate])
)
Between IIf([Forms]![frmSearchGeneral].[FindYearStart] Is Null,#1/1/1000#,[Forms]![frmSearchGeneral].[FindYearStart]) And IIf([Forms]![frmSearchGeneral].[FindYearEnd] Is Null,#12/31/9999#,[Forms]![frmSearchGeneral].[FindYearEnd])
)
AND
(
(
[Forms]![frmSearchGeneral].[FindYearSpecific]
)
Is Null
)
)
;
Теперь это 606 строчек.
Access любит некоторые скобки, и большую часть времени они на 100% не нужны. Фактически, за исключением вашего единственного условия OR в предложении WHERE, остальные скобки являются излишними. Кроме того, доступ - это глупость, когда он склеивает эти вложенные ВНУТРЕННИЕ СОЕДИНЕНИЯ, как это. Они просто не нужны, и предложения ON просто разбросаны по случайности. Это технически правильно, но это беспорядок для отладки.
Переписано:
SELECT
tblRecord.RecRecordNumber
, tblUVNumber.UVNo
, tblRecord.RecDate
, tblRecord.RefNoInformer
, tblPersons.PerFullName
, tblPersons.PerOfficielObserver
, tblLocation.LocCountry
, tblLocation.LocProvince
, tblLocation.LocMuncipality
, tblLocation.LocNearestTown
, tblLocation.LocAreaName
, tblFieldTrip.FiTPathNumber
, tblValidate.ValSCALP
, tblRecord.RecValidateYes
, tblRecord.RecFieldTripYes
, tblRecord.RecScatYes
, tblRecord.RecPreyYes
, tblRecord.RecOtherDNAYes
, tblRecord.RecSightingYes
, tblRecord.RecTrackYes
, tblRecord.RecHowlYes
, tblRecord.RecUrinBloodYes
, tblRecord.RecCameraYes
, tblRecord.RecDenYes
, tblValidate.ValInsideWolfZone
, tblRecord.RecSampleType
, tblRecord.RecSampleEvidence
, tblRecord.RecDogPresent
, tblRecord.RecGELat
, tblRecord.RecGELong
, tblRecord.RecCoordinates
, tblRecord.RefNoCoorSys
, tblRecord.RecUTMQuadrant
, tblRecord.RecUTMCoorX
, tblRecord.RecUTMCoorY
, tblLocPrecision.LocationPrecision
, tblRecord.RefNoValidate
, tblValidate.ValSenckLabID
, tblValidate.ValSenckType_mtDNA
, tblValidate.ValSenckHaploType
, tblValidate.ValSenckInfo_mtDNA
, tblValidate.ValSenckType_KernDNA
, tblValidate.ValSenckInfo_KernDNA
, tblAnimal_1.AniNumberOfAnimals
, tblAnimal.AniNumberOfAnimals
, tblPersons_1.PerFullName
, tblPersons_2.PerFullName
, tblPersons_3.PerFullName
, tblPersons_4.PerFullName
, tblPersons_5.PerFullName
, tblPersons_6.PerFullName
, tblPersons_7.PerFullName
, tblPersons_8.PerFullName
, tblPersons_9.PerFullName
, tblValidate.RefNoValSpecies
, tblValidateSpecies.ValSpeGenus
, tblValidateSpecies.ValSpeSpecies
, tblValidateSpecies.ValSpeDanishName
, tblPack.PackName
, tblIndividual.Individual
, tblIndividual.IndiSex
, tblIndividual.IndiBornYear
, tblIndividual.IndiBornPlace
, tblIndividual.IndiDead
, tblIndividual.IndiDeadCause
, tblValidate.RefNoProbIndiGuess
, tblValidate.ValIndiAgeAtSampleTime
, tblTerritoryName.TerritoryName
, tblMonitoringYear.MonYear
, tblPhotoDoc.PhDPhotoNotes
, tblPhotoDoc.PhDPermissionToUsePhotoText
, tblPhotoDoc.PhDPhotoFiles
FROM
/*TblRecord is the driver here. Everything else joins to it snowflake style*/
tblRecord
/*bring in camera data*/
INNER JOIN
tblCamera
ON
tblCamera.KeyCamera = tblRecord.RefNoCamera
INNER JOIN
tblAnimal AS tblAnimal_1
ON
tblAnimal_1.KeyAnimal = tblCamera.RefNoAnimal
INNER JOIN
tblPersons AS tblPersons_1
ON
tblPersons_1.KeyPersons = tblCamera.RefNoWriterCam
/*Howl Data*/
INNER JOIN
tblHowl
ON
tblHowl.KeyHowl = tblRecord.RefNoHowl
INNER JOIN
tblPersons AS tblPersons_3
ON
tblPersons_3.KeyPersons = tblHowl.RefNoWriterHowl
/*Field Trip Data*/
INNER JOIN
tblFieldTrip
ON
tblFieldTrip.KeyFieldTrip = tblRecord.RefNoFieldTrip
/*Location Data*/
INNER JOIN
tblLocation
ON
tblLocation.KeyLocation = tblRecord.RefNoLocation
/*Precision Data*/
INNER JOIN
tblLocPrecision
ON
tblLocPrecision.KeyLocPrecision = tblRecord.RefNoPrecision
/*Monitoring Year Data*/
INNER JOIN
tblMonitoringYear
ON
tblMonitoringYear.KeyMonYear = tblRecord.RefNoMonYear
/*Other DNA data*/
INNER JOIN
tblOtherDNA
ON
tblOtherDNA.KeyOtherDNA = tblRecord.RefNoOtherDNA
INNER JOIN
tblPersons AS tblPersons_4
ON
tblPersons_4.KeyPersons = tblOtherDNA.RefNoWriterOther
/*Primary Persons data for the record*/
INNER JOIN
tblPersons
ON
tblPersons.KeyPersons = tblRecord.RefNoInformer
/*Photo Doc Data*/
INNER JOIN
tblPhotoDoc
ON
tblPhotoDoc.KeyPhoto = tblRecord.RefNoPhotoDoc
/*Prey Data*/
INNER JOIN
tblPrey
ON
tblPrey.KeyPrey = tblRecord.RefNoPrey
INNER JOIN
tblPersons AS tblPersons_5
ON
tblPersons_5.KeyPersons = tblPrey.RefNoWriterPrey
/*Scat Data*/
INNER JOIN
tblScat
ON
tblScat.KeyScat = tblRecord.RefNoScat
INNER JOIN
tblPersons AS tblPersons_6
ON
tblPersons_6.KeyPersons = tblScat.RefNoWriterScat
/*Sighting Data*/
INNER JOIN
tblSighting
ON
tblSighting.KeySigthing = tblRecord.RefNoSighting
INNER JOIN
tblAnimal
ON
tblAnimal.KeyAnimal = tblSighting.RefNoAnimal
INNER JOIN
tblPersons AS tblPersons_2
ON
tblPersons_2.KeyPersons = tblSighting.RefNoWriterSight
/*Territory Name Data*/
INNER JOIN
tblTerritoryName
ON
tblTerritoryName.KeyTerritoryName = tblRecord.RefNoTerritoryName
/*Track Data*/
INNER JOIN
tblTrack
ON
tblTrack.KeyTrack = tblRecord.RefNoTrack
INNER JOIN
tblPersons AS tblPersons_7
ON
tblPersons_7.KeyPersons = tblTrack.RefNoWriterTrack
/*Urin Blood Data*/
INNER JOIN
tblUrinBlood
ON
tblUrinBlood.KeyUrinBlood = tblRecord.RefNoUrinBlood
INNER JOIN
tblPersons AS tblPersons_8
ON
tblPersons_8.KeyPersons = tblUrinBlood.RefNoWriterUrinBlood
/*UV Number Data*/
INNER JOIN
tblUVNumber
ON
tblUVNumber.KeyUVNo = tblRecord.RefNoUVNo
/*Validation Data*/
INNER JOIN
tblValidate
ON
tblValidate.KeyValidate = tblRecord.RefNoValidate
INNER JOIN
tblValidateSpecies
ON
tblValidateSpecies.KeyValidateSpecies = tblValidate.RefNoValSpecies
/*Pack Data*/
INNER JOIN
tblPack
ON
tblPack.KeyPack = tblValidate.RefNoPack
INNER JOIN
tblIndividual
ON
tblIndividual.KeyIndividual = tblValidate.RefNoIndi
/*Den Data*/
INNER JOIN
tblDen
ON
tblDen.KeyDen = tblRecord.RefNoDen
INNER JOIN
tblPersons AS tblPersons_9
ON
tblDen.RefNoWriterDen = tblPersons_9.KeyPersons
WHERE
(
tblRecord.RecRecordNumber like [Formularer]![frmSearchGeneral].[FindRecord] & "*"
AND tblUVNumber.UVNo Like [Formularer]![frmSearchGeneral].[FindUV] & "*"
AND tblRecord.RecDate Between
IIf([Forms]![frmSearchGeneral].[FindDateStart] Is Null ,#1/1/1000# ,[Forms]![frmSearchGeneral].[FindDateStart]
And IIf([Forms]![frmSearchGeneral].[FindDateEnd] Is Null,#12/31/9999#,[Forms]![frmSearchGeneral].[FindDateEnd]
AND tblPersons.PerFullName Like [Formularer]![frmSearchGeneral].[FindPerson] & "*"
AND tblPersons.PerOfficielObserver Like [Formularer]![frmSearchGeneral].[FindOfficial] & "*"
AND tblLocation.LocCountry Like [Formularer]![frmSearchGeneral].[FindCountry] & "*"
AND tblLocation.LocProvince Like [Formularer]![frmSearchGeneral].[FindProvince] & "*"
AND tblLocation.LocMuncipality Like [Formularer]![frmSearchGeneral].[FindMuncipality] & "*"
AND tblLocation.LocNearestTown Like [Formularer]![frmSearchGeneral].[FindNearestTown] & "*"
AND tblLocation.LocAreaName Like [Formularer]![frmSearchGeneral].[FindAreaName] & "*"
AND tblFieldTrip.FiTPathNumber Like [Formularer]![frmSearchGeneral].[FindTrackNo] & "*"
AND tblValidate.ValSCALP Like [Formularer]![frmSearchGeneral].[FindSCALP] & "*"
AND tblRecord.RecValidateYes Like (IIf([Formularer]![frmSearchGeneral].[FindValidate]=Yes ,[Formularer]![frmSearchGeneral].[FindValidate] ,"*")
AND tblRecord.RecScatYes Like IIf([Formularer]![frmSearchGeneral].[FindScat]=Yes ,[Formularer]![frmSearchGeneral].[FindScat] ,"*")
AND tblRecord.RecPreyYes Like IIf([Formularer]![frmSearchGeneral].[FindPrey]=Yes,[Formularer]![frmSearchGeneral].[FindPrey],"*")
AND tblRecord.RecOtherDNAYes Like IIf([Formularer]![frmSearchGeneral].[FindOtherDNA]=Yes,[Formularer]![frmSearchGeneral].[FindOtherDNA] ,"*")
AND tblRecord.RecSightingYes Like IIf([Formularer]![frmSearchGeneral].[FindSighting]=Yes ,[Formularer]![frmSearchGeneral].[FindSighting],"*")
AND tblRecord.RecTrackYes Like IIf([Formularer]![frmSearchGeneral].[FindTrack]=Yes,[Formularer]![frmSearchGeneral].[FindTrack] ,"*")
AND tblRecord.RecHowlYes Like IIf([Formularer]![frmSearchGeneral].[FindHowl]=Yes,[Formularer]![frmSearchGeneral].[FindHowl] ,"*")
AND tblRecord.RecUrinBloodYes Like IIf([Formularer]![frmSearchGeneral].[FindUrinBlood]=Yes,[Formularer]![frmSearchGeneral].[FindUrinBlood] ,"*")
AND tblRecord.RecCameraYes Like IIf([Formularer]![frmSearchGeneral].[FindCamera]=Yes,[Formularer]![frmSearchGeneral].[FindCamera] ,"*")
AND tblValidate.ValInsideWolfZone Like IIf([Formularer]![frmSearchGeneral].[FindWolfZone]=Yes,[formularer]![frmSearchGeneral].[FindWolfZone] ,"*")
AND tblValidate.RefNoValSpecies Like [Formularer]![frmSearchGeneral].[FindValidateSpecies] & "*"
AND tblPack.PackName Like [Formularer]![frmSearchGeneral].[FindPack] & "*"
AND tblIndividual.Individual Like [Formularer]![frmSearchGeneral].[FindIndividual] & "*"
AND tblTerritoryName.TerritoryName Like [formularer]![frmSearchGeneral].[FindTerritory] & "*"
AND tblRecord.Deactivated =False
AND Year[RecDate] =[Forms]![frmSearchGeneral].[FindYearSpecific]
)
OR
(
tblRecord.RecRecordNumber Like [Formularer]![frmSearchGeneral].[FindRecord] & "*"
AND tblUVNumber.UVNo Like [Formularer]![frmSearchGeneral].[FindUV] & "*"
AND tblRecord.RecDate Between
IIf([Forms]![frmSearchGeneral].[FindDateStart] Is Null,#1/1/1000# ,[Forms]![frmSearchGeneral].[FindDateStart])
And IIf([Forms]![frmSearchGeneral].[FindDateEnd] Is Null,#12/31/9999# ,[Forms]![frmSearchGeneral].[FindDateEnd])
AND tblPersons.PerFullName Like [Formularer]![frmSearchGeneral].[FindPerson] & "*"
AND tblPersons.PerOfficielObserver Like [Formularer]![frmSearchGeneral].[FindOfficial] & "*"
AND tblLocation.LocCountry Like [Formularer]![frmSearchGeneral].[FindCountry] & "*"
AND tblLocation.LocProvince Like [Formularer]![frmSearchGeneral].[FindProvince] & "*"
AND tblLocation.LocMuncipality Like [Formularer]![frmSearchGeneral].[FindMuncipality] & "*"
AND tblLocation.LocNearestTown Like [Formularer]![frmSearchGeneral].[FindNearestTown] & "*"
AND tblLocation.LocAreaName Like [Formularer]![frmSearchGeneral].[FindAreaName] & "*"
AND tblFieldTrip.FiTPathNumber Like [Formularer]![frmSearchGeneral].[FindTrackNo] & "*"
AND tblValidate.ValSCALP Like [Formularer]![frmSearchGeneral].[FindSCALP] & "*"
AND tblRecord.RecValidateYes Like IIf([Formularer]![frmSearchGeneral].[FindValidate]=Yes ,[Formularer]![frmSearchGeneral].[FindValidate],"*")
AND tblRecord.RecScatYes Like IIf([Formularer]![frmSearchGeneral].[FindScat]=Yes ,[Formularer]![frmSearchGeneral].[FindScat] ,"*")
AND tblRecord.RecPreyYes Like IIf([Formularer]![frmSearchGeneral].[FindPrey]=Yes ,[Formularer]![frmSearchGeneral].[FindPrey] ,"*")
AND tblRecord.RecOtherDNAYes Like IIf([Formularer]![frmSearchGeneral].[FindOtherDNA]=Yes ,[Formularer]![frmSearchGeneral].[FindOtherDNA] ,"*")
AND tblRecord.RecSightingYes Like IIf([Formularer]![frmSearchGeneral].[FindSighting]=Yes ,[Formularer]![frmSearchGeneral].[FindSighting] ,"*")
AND tblRecord.RecTrackYes Like IIf([Formularer]![frmSearchGeneral].[FindTrack]=Yes ,[Formularer]![frmSearchGeneral].[FindTrack] ,"*")
AND tblRecord.RecHowlYes Like IIf([Formularer]![frmSearchGeneral].[FindHowl]=Yes ,[Formularer]![frmSearchGeneral].[FindHowl] ,"*")
AND tblRecord.RecUrinBloodYes Like IIf([Formularer]![frmSearchGeneral].[FindUrinBlood]=Yes ,[Formularer]![frmSearchGeneral].[FindUrinBlood] ,"*")
AND tblRecord.RecCameraYes Like IIf([Formularer]![frmSearchGeneral].[FindCamera]=Yes ,[Formularer]![frmSearchGeneral].[FindCamera] ,"*")
AND tblValidate.ValInsideWolfZone Like IIf([Formularer]![frmSearchGeneral].[FindWolfZone]=Yes ,[formularer]![frmSearchGeneral].[FindWolfZone] ,"*")
AND tblValidate.RefNoValSpecies Like [Formularer]![frmSearchGeneral].[FindValidateSpecies] & "*"
AND tblPack.PackName Like [Formularer]![frmSearchGeneral].[FindPack] & "*"
AND tblIndividual.Individual Like [Formularer]![frmSearchGeneral].[FindIndividual] & "*"
AND tblTerritoryName.TerritoryName Like [formularer]![frmSearchGeneral].[FindTerritory] & "*"
AND tblRecord.Deactivated =False
AND Year[RecDate] Between
IIf([Forms]![frmSearchGeneral].[FindYearStart] Is Null,#1/1/1000# ,[Forms]![frmSearchGeneral].[FindYearStart])
And IIf([Forms]![frmSearchGeneral].[FindYearEnd] Is Null,#12/31/9999# ,[Forms]![frmSearchGeneral].[FindYearEnd])
AND [Forms]![frmSearchGeneral].[FindYearSpecific] Is Null
);
Это не решает проблему сложности с Access, но решает ее для людей.
То, что я хотел бы сделать, это отметить комментарии, которые я сделал в ваших объединениях, и сделать подзапрос для каждого из этих комментариев, где вы делаете следующее. Объединение каждого из этих подзапросов в один последний запрос.
SELECT fields, you, need
FROM tblRecord
/*Den Data*/
INNER JOIN
tblDen
ON
tblDen.KeyDen = tblRecord.RefNoDen
INNER JOIN
tblPersons AS tblPersons_9
ON
tblDen.RefNoWriterDen = tblPersons_9.KeyPersons
0
ответ дан JNevill 4 March 2019 в 19:27
поделиться
Другие вопросы по тегам: