Query doesn't work the way I want it !?!

[Schaap]

I made a query for a bargraph (blockdiagram). But it doesn't work the way I want it!
Little explanation how it has to be :
The graph has a time axis (years).
For every selected year (by fill in boxes, value between .... and ....) that's in the dbase, I wanna see the sum of column 1 (of dbase)with where-condition : Like "yes" and I wanna see the sum of column 2 (of dbase) with where-condition "**-**-****".

But what I get as result is an combination of the both where-conditions !!! That's not what I want.

For every year that will be displayed I wanna see a bar of the sum of column 1 filtered by the where-condition and I wanna see also a bar of the sum of column 2 filtered by the where-condition.

Is this possible with 1 query, or do I have to use 2 queries ?
And if I have to use 2 queries how do I get that into 1 bargraph (blockdiagram0 ?

I hope that I explained my problem well, so that someone understand what I'm writing

 

[kjv1611]

If this is done in code, what does your code look like? Sounds like you might possibly be entering the where clauses as strings somehow..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[Schaap]

It's not done in code !!! Maybe I have to ? (but first I have to solve these problems otherwise I get the same problems in code).

Sorry I wrote something wrong the where-condition is not a sum but a count of specific records.

But this is the SQL code of it :

PARAMETERS [Fill in start year in:] Text ( 255 ), [Fill in stop year in:] Text ( 255 );
SELECT ApplicationForm.BudgetYear, Count(ApplicationForm.ProductReceived) AS AantalVanProductReceived1, Count(ApplicationForm.Agreed) AS AantalVanAgreed
FROM ApplicationForm
WHERE (((ApplicationForm.BudgetYear)>=[Fill in start year in:] And (ApplicationForm.BudgetYear)<=[Fill in stop year in:]) AND ((ApplicationForm.ProductReceived) Like "yes") AND ((ApplicationForm.Agreed) Like "**-**-****"))
GROUP BY ApplicationForm.BudgetYear
ORDER BY ApplicationForm.BudgetYear;

There is nothing wrong with the where condition so far I can see. It's the combination of using 2 where conditions, the query "AND" these conditions and that's what I don't want to happen !But I don't know if that's possible !!!

 

[kjv1611]

Have you tried removing the quotes around the problem parts?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[Schaap]

That's not the problem, cause it works, but it don't work like I want it to be ! And access puts back the quotes immediatly.

There must be another way !!! Maybe by writing code or change the querie or use 2 queries for 1 graph (as I told before), but I don't know !

 

[kjv1611]

ok.. what exactly do you mean by this line?

But what I get as result is an combination of the both where-conditions !!! That's not what I want.

Do you mean that it is showing the strings of the Where conditions, or that it is combining them into one column, or what?


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[Schaap]

I'll explain with an example,

I get 2 columns :

Year: Counts1: Counts2: Result(for count1):
2005 17 4 4
2006 28 8 8
2007 25 7 7
2008 9 1 1

As result for count1 I get the result as above descripted in stead of Counts1. This is caused by the where condition1 that is effected by where condition2 because access puts automatically an AND between both where conditions (see also SQLcode).
More clear as this is not possible.

 

[kjv1611]

Hmm, have you tried using 2 queries yet, as your first post suggested? Maybe you could create 2 separate queries, and then use another query to combine the two... or, maybe what you need to do is change the AND to OR?? I would try the AND to OR part first..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV