X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Filtering Multi-table Query Recordsource Using Multiple Combo-boxes, Access 2016    
post Nov 3 2017, 02:58 PM

Posts: 1
Joined: 3-November 17

This is my first post here and am seeking some help. I have spent the last three months creating a database for a victim advocacy group with multiple tables and relationships and all I need to do at this point is finish a Reports section. I've created a fairly comprehensive query to pull the related data, but have tried for almost a month to get it to filter right. My query for the recordsource is below and what I am trying to do is set it to display filtered data based on choices made in 9 combo boxes located on a "Reports" form and executed by a button that pulls up the filtered data in the Report. I need for the choices made in the combo boxes on the form to gradually filter down the data in the report based on 1st, 2nd, 3rd, etc. of combo boxes chosen. Can someone please give me some help? Or if there's a better way to accomplish this?

The combo boxes on the form (frmReports) are named:

The tables are:
as well as two Junction queries that I created for many to many relationships between Offenders and Crimes and Victims (tblData) and ServiceTypes [which is a variety of services offered to victims].

Here is the query that pulls all the data, but which I need to finish to be able to filter by each of the above. I need help on the WHERE criteria:

SELECT tblData.ID, tblData.DateCreated, tblData.VictimRace, tblData.VictimSex, tblData.AgeCategory, Offenders.Race, Offenders.[censored], Offenders.OffenderAgeCategory, Offenders.County, ServiceTypeJunctionqry.ServiceType, CrimeJunctionqry.Crime
FROM ((((tblData LEFT JOIN ServiceTypeJunctionqry ON tblData.ID = ServiceTypeJunctionqry.ID) LEFT JOIN ServiceType ON ServiceTypeJunctionqry.ServiceTypeID = ServiceType.ServiceTypeID) LEFT JOIN Offenders ON tblData.ID = Offenders.ID) LEFT JOIN CrimeJunctionqry ON Offenders.OffenderID = CrimeJunctionqry.OffenderID) LEFT JOIN Crime ON CrimeJunctionqry.CrimeID = Crime.CrimeID;

Ideally, I would also like to be able to date restrict the results as well (based on the Date Created date) to between two dates (txtBEGINDATE and txtENDDATE), but at this point I'm willing to just let them do that on the report by right clicking the cell if I can just get it to filter correctly. Also need to know about whether the filter clears when the report closes or is there something else I need to do.

Thank you all in advance for your assistance.
Go to the top of the page
post Nov 3 2017, 03:22 PM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin



> I need for the choices made in the combo boxes on the form to gradually filter down the data in the report based on 1st, 2nd, 3rd, etc. of combo boxes chosen.

Just to clarify your question... It sounds like you want the report to open in the background, and as you select a value in a combobox you want the report to refresh, showing the filtered data. Is that right? Or do you just want to be able to select values in SOME of the comboboxes, but not necessarily ALL of them before opening the report?

Either way, you need to go through each combobox, and if it's got a selected value, add another bit to a list of conditions. Take a look at my advice in this previous discussion to see the basic concept. You can apply the same idea to filtering a report.

Hope this helps,

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th October 2019 - 03:40 PM