Count Query for QBF

[david9640]

Hi there, I'm pretty new to this forum. I have used this site for help quite a lot in the past though.

I have recently been working on a database that is meant to be quite user friendly. It contains two tables, one that lists 'trainees' and another that lists 'job applications' in a 'one to many' type relationship. It is designed to monitor Job applications being made by people doing courses here.

So far, I have created a very practical form for data entry. I've created a QBF to allow the user to filter the Job Applications quite substantially.

However, I have reached a stumbling block. I'm rather inexperienced with Access, I've been learning as I go I guess. I'm trying to build a form that allows the same criteria to be entered, but that counts the number of Job Applications, and then creates a pie chart that subdivides the applications by 'source of application' (i.e. Indeed.com, Family, Newspaper'), or Industry of the potential employer.

For example, I would like to be able to select a programme or course that someone is on, their starting date, a date range for the application and some other details- i.e. Select an Industry then view a pie charts for the source of those applications and vice versa.

I've created a form for this, with a button for 'Source' and one for 'Industry' the two different pie chart types I would like to be able to select. I have created two separate query by forms for each, with the buttons linking to the respective Macro. The problem I'm having is that the query seems to ignore the WHEN criteria completely.

I have pasted one of the queries here below. Please tell me if I'm just being silly thinking that this is possible without Visual Basic; I wouldn't mind

-----
SELECT COUNT([Job Applications].[Application ID]), [Job Applications].source
FROM [Job Applications] INNER JOIN Trainees ON [Job Applications].[Trainee ID] = Trainees.[Trainee ID]
WHERE (

[Job Applications].[Application Date] > forms!CustomChart!DateAfter OR

forms!CustomChart!DateAfter IS NULL) AND

([Trainees].course = forms!CustomChart!course OR

forms!CustomChart!course IS NULL) AND

([Job Applications].[Application Date] < forms!CustomChart!DateBefore OR

forms!CustomChart!DateBefore IS NULL) AND

([Trainees].[Start Date] = forms!CustomChart!StartDate OR

forms!CustomChart!StartDate IS NULL) AND

([Trainees].[Leaving Date] > forms!CustomChart!LeftAfter OR

forms!CustomChart!LeftAfter IS NULL) AND

([Trainees].[Leaving Date] < forms!CustomChart!LeftBefore OR

forms!CustomChart!LeftBefore IS NULL))

GROUP BY [Job Applications].source;
-----

 

[Trevor G]

Hi David welcome to the Forum, as you have created the query then perhaps the results can be exported into Excel to supply a suitable Graph and use the Query as the Data Source.

Alternatively you can setup a Chart form in Access and use the Query as the source and model how you want to see it.

 

[david9640]

Hi Trevor, thanks for the welcome and the reply.

That's what I've been trying to do, so it seems like I'm not being completely silly haha! The problem seems to be the query, and I am genuinely stumped. It returns the same result, no matter what information is entered into the form.

 

[Trevor G]

Can you look to upload an extract of the database, you can upload up to 2mg which can be zipped or place a copy in dropbox or something like that so someone can take a look at either advice or help on creating a solution.