Stumped by Complex Query/Filter

[diwin]

I have backed myself into a corner.

I am creating a database that holds data on 'Bugs'/'Life Stages' collected in particular 'Years'/'Seasons' at specific 'Sites'.

So I have a form that allows the user to choose Year/Season combinations AND/OR Bug/Stage combinations AND/OR specific Sites to view in a subform list, and further, to produce a report or a spreadsheet with the data that has been queried.

My query can select the sites that conain the chosen criteria, but when search is done, all year/season and bug/stage combinations are listed for each site.

I have then added a checkbox to allow the user to limit results to just the specific records that are requested for the year/season and bug/stage data.

I have been going in circles trying to understand how to get the subforms for the year/season and bug/stage data to limit results to just the selected criteria.

This is the best explanation I can give, and I am not sure it is very clear, so I am providing a stripped down version of the database with all the objects required to make the form run.

I would really love to hear how to approach this problem. I present the product to the departmental Biologist tomorrow AM with a promise to get this last feature figured out soon.

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[MajP]

Its a little involved so it is hard to say. But if it was me:

1) return a bug info query that includes
year
season
location
bug
bug type

This should be the detail records for everything that meets your query.
I assume you do this somewhere in order to return the locations

I am not sure how this works. I assume you want linked subforms. I assume I click on a location, I see the years that match that location with bug data meeting the criteria, I click on a year I see the seasons for that year for that location with matching bug data, I click on a season and see the bugs found in that season for that year for that location, If I click on the bug I see the bug details for that location, that year, that season .

2) If I understand how this form works Then the year subform should be
Select year from buginfo query where location is selected in the location subform
3)Season subform is select season from bug info query where location = selected location and year = selected year
4)Bug subform: select bug from bug infor query where location = selected location and year = selected year and season = selected season.
5)Bug detail subform: select bug details from bug info query where location = selected location and year = selected year and season = selected season and bug = selected bug.

 

[MajP]

I am not confident that you are returning the correct sites. I ran some simple searches and sometimes got the wrong choices.

Some of this is overly complicated. There is a much simpler way to get the query.

To return the correct records you need three simple select queries that require no code once you push your selection to the "pick" tables.

1) If the list count of list25 and list37 is greater than zero (they selected both a year/season and a bug/stage)
build a cartesian product

AqryPickYearSeasonBugStage

SELECT tblPickYearSeason.Year, tblPickYearSeason.Season, tblPickBugStage.Bug, tblPickBugStage.Stage, tblPickBugStage.f_BugID, tblPickBugStage.f_StageID, tblPickYearSeason.f_YearID, tblPickYearSeason.f_SeasonID
FROM tblPickBugStage, tblPickYearSeason
ORDER BY tblPickYearSeason.Year, tblPickYearSeason.Season, tblPickBugStage.Bug, tblPickBugStage.Stage;

now join this query to your sitecollectioninfo by year,season,bug, and stage

qryYearSeasonBugType

SELECT qryViewSiteCollectionInfo.Year, qryViewSiteCollectionInfo.Season, qryViewSiteCollectionInfo.BugName, qryViewSiteCollectionInfo.LifeStage, qryViewSiteCollectionInfo.SiteCode, qryViewSiteCollectionInfo.Subwatershed, qryViewSiteCollectionInfo.WatercourseName, qryViewSiteCollectionInfo.FBI, qryViewSiteCollectionInfo.Easting, qryViewSiteCollectionInfo.Northing, qryViewSiteCollectionInfo.Township, qryViewSiteCollectionInfo.Lot, qryViewSiteCollectionInfo.Con, qryViewSiteCollectionInfo.RoadName, qryViewSiteCollectionInfo.SiteNotes, qryViewSiteCollectionInfo.DateStamp, qryViewSiteCollectionInfo.BugInfoID, qryViewSiteCollectionInfo.BugID, qryViewSiteCollectionInfo.BugNotes, qryViewSiteCollectionInfo.StageInfoID, qryViewSiteCollectionInfo.f_BugStageID, qryViewSiteCollectionInfo.Quantity, qryViewSiteCollectionInfo.Hilsenhoff, aqryPickYearSeasonBugStage.f_StageID, aqryPickYearSeasonBugStage.Stage
FROM qryViewSiteCollectionInfo INNER JOIN aqryPickYearSeasonBugStage ON (qryViewSiteCollectionInfo.SeasonID = aqryPickYearSeasonBugStage.f_SeasonID) AND (qryViewSiteCollectionInfo.YearID = aqryPickYearSeasonBugStage.f_YearID) AND (qryViewSiteCollectionInfo.BugID = aqryPickYearSeasonBugStage.f_BugID) AND (qryViewSiteCollectionInfo.LifeStage = aqryPickYearSeasonBugStage.Stage)
ORDER BY qryViewSiteCollectionInfo.Year, qryViewSiteCollectionInfo.Season, qryViewSiteCollectionInfo.BugName, qryViewSiteCollectionInfo.LifeStage;

2. Sometimes they may pick only year/season
list25.listcount > 0 and list37.listcount < 0

qryYearSeason

SELECT qryViewSiteCollectionInfo.Year, qryViewSiteCollectionInfo.Season, qryViewSiteCollectionInfo.BugName, qryViewSiteCollectionInfo.LifeStage, qryViewSiteCollectionInfo.SiteCode, qryViewSiteCollectionInfo.Subwatershed, qryViewSiteCollectionInfo.WatercourseName, qryViewSiteCollectionInfo.FBI, qryViewSiteCollectionInfo.Easting, qryViewSiteCollectionInfo.Northing, qryViewSiteCollectionInfo.Township, qryViewSiteCollectionInfo.Lot, qryViewSiteCollectionInfo.Con, qryViewSiteCollectionInfo.RoadName, qryViewSiteCollectionInfo.SiteNotes, qryViewSiteCollectionInfo.DateStamp, qryViewSiteCollectionInfo.BugInfoID, qryViewSiteCollectionInfo.BugID, qryViewSiteCollectionInfo.BugNotes, qryViewSiteCollectionInfo.StageInfoID, qryViewSiteCollectionInfo.f_BugStageID, qryViewSiteCollectionInfo.Quantity, qryViewSiteCollectionInfo.Hilsenhoff
FROM tblPickYearSeason INNER JOIN qryViewSiteCollectionInfo ON (tblPickYearSeason.f_SeasonID=qryViewSiteCollectionInfo.SeasonID) AND (tblPickYearSeason.f_YearID=qryViewSiteCollectionInfo.YearID)
ORDER BY qryViewSiteCollectionInfo.Year, qryViewSiteCollectionInfo.Season, qryViewSiteCollectionInfo.BugName, qryViewSiteCollectionInfo.LifeStage;

3. Same idea if they only pick a bug/stage

qryBugStage

SELECT qryViewSiteCollectionInfo.Year, qryViewSiteCollectionInfo.Season, qryViewSiteCollectionInfo.BugName, qryViewSiteCollectionInfo.LifeStage, qryViewSiteCollectionInfo.SiteCode, qryViewSiteCollectionInfo.Subwatershed, qryViewSiteCollectionInfo.WatercourseName, qryViewSiteCollectionInfo.FBI, qryViewSiteCollectionInfo.Easting, qryViewSiteCollectionInfo.Northing, qryViewSiteCollectionInfo.Township, qryViewSiteCollectionInfo.Lot, qryViewSiteCollectionInfo.Con, qryViewSiteCollectionInfo.RoadName, qryViewSiteCollectionInfo.SiteNotes, qryViewSiteCollectionInfo.DateStamp, qryViewSiteCollectionInfo.BugInfoID, qryViewSiteCollectionInfo.BugID, qryViewSiteCollectionInfo.BugNotes, qryViewSiteCollectionInfo.StageInfoID, qryViewSiteCollectionInfo.f_BugStageID, qryViewSiteCollectionInfo.Quantity, qryViewSiteCollectionInfo.Hilsenhoff
FROM qryViewSiteCollectionInfo INNER JOIN tblPickBugStage ON (qryViewSiteCollectionInfo.LifeStage = tblPickBugStage.Stage) AND (qryViewSiteCollectionInfo.BugID = tblPickBugStage.f_BugID)
ORDER BY qryViewSiteCollectionInfo.Year, qryViewSiteCollectionInfo.Season, qryViewSiteCollectionInfo.BugName, qryViewSiteCollectionInfo.LifeStage;

4. So these queries return the correct information. Depending on if they selected both year/season bug/stage, year/season only, or bug/stage only. You then do a select distinct site query from one of the three linked to your site information.

Now start chaining the subform queries. You do not really need to write code. Except to determine which case.

 

[diwin]

Thanks MajP.

I did solve the problem. Too complex to explain. Brain shrinkage you know.

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)