Statistic Reporting: Merge summary into 1 layout

[Tumoz]

I have a database where i search by specific criteria statistical reports. The last page of the report contains summary of the data.
My problem is that when I want to search for just colors "blue" and "red", Access report makes two summary for each of their own.

How do I search only for specific words, and that the statistics show this information in the same summary?

Example
I want to know how much blue and red i have in database.
In query i type in field Color criteria: Is like "Blue" Or Like "Red"

Summary sheet 1

Information 1	332
Information 2	12
Red	1
Blue	0

<tbody>
</tbody>

Summary sheet 2

Information 1	332
Information 2	12
Red	0
Blue	3

<tbody>
</tbody>

What i want

Information 1	332
Information	12
Red	1
Blue	3

<tbody>
</tbody>

 

[mole999]

have a read here http://www.w3schools.com/sql/sql_and_or.asp its SQL but might need a minor adaptation to get into Access

 

[Tumoz]

All my data is on one (1) table, and this table is imported from excel.

When i'm using Query, in criteria Like "red" Or Like "blue", this gives me 2 rows of data in the "query preview" (when pressing RUN).
One row is for the red values, and the other is for blues.
Can i somehow merge these lines together?

Or i think i could fix this problem, if field contains text, it would choose this, and if this field is blank it would ignore it.
For some reason, when i tried this query was blank?

Sorry for bad english

 

[mole999]

can you copy the query and paste here

 

[Tumoz]

CountOfLanguage	Location	Color	CountOfPhone	CountOfAddress	CountOfPostal Address	CountOfMail	CountOfGroup1	CountOfGroup2	CountOfGroup3	CountOfRed	CountOfBlue	CountOfOrange	CountOfYellow	CountOfGreen	CountOfGrey	CountOfGold	CountOfGroup11	CountOfGroup12	CountOfGroup13	CountOfGroup14	CountOfGroup15	CountOfGroup16	CountOfGroup17	CountOfGroup18	CountOfGroup19
3359	LONDON	Red	1302	2187	1889	1731	2191	2359	1542	2359	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4718	LONDON	Blue	801	1615	1324	1166	1622	1718	1013	0	1718	0	0	0	0	0	0	0	0	0	0	0	0	0	0

<tbody>
</tbody>

Location in query is GROUP BY
Criteria: Like "London"

Color is GROUP BY
Criteria: Like "Red" Or Like "Blue"

When i removed column Color from Query, it shows summary in same box, but it adds the rest of the color values into grand total.

[/URL][/IMG]

 

[mole999]

They Query, not the result, in the data source

 

[Tumoz]

I made example file with less fields.

SELECT Count(ExampleMRExcel.Language) AS CountOfLanguage, ExampleMRExcel.Location, ExampleMRExcel.Color, Count(ExampleMRExcel.Phone) AS CountOfPhone, Count(ExampleMRExcel.Address) AS CountOfAddress, Count(ExampleMRExcel.Red) AS CountOfRed, Count(ExampleMRExcel.Blue) AS CountOfBlue, Count(ExampleMRExcel.Green) AS CountOfGreen, Count(ExampleMRExcel.Yellow) AS CountOfYellow
FROM ExampleMRExcel
GROUP BY ExampleMRExcel.Location, ExampleMRExcel.Color
HAVING (((ExampleMRExcel.Location) Like "London") AND ((ExampleMRExcel.Color) Like "Red" Or (ExampleMRExcel.Color) Like "Blue"));

Excel file below.
Colors in the end is just for grand total (e.g. how many reds there are)

Language	Location	Color	Phone	Address	Red	Blue	Green	Yellow
English	London	Red	45466	koo	Red
English	London	Red	555683	poo	Red
English	London	Blue	245	eee		Blue
English	London	Blue	321	www		Blue
English	London	Blue	42	qqq		Blue
English	London	Red		rrr	Red
China	London	Green	5123	fas			Green
China	London	Blue	424	cas		Blue
China	London	Yellow	2	ewq				Yellow

<colgroup><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[mole999]

so you should be able to do
(Count(ExampleMRExcel.Blue) OR Count(ExampleMRExcel.Red)) AS CountOfBlue/Red

something like that (I think)

 

[Tumoz]

so you should be able to do
(Count(ExampleMRExcel.Blue) OR Count(ExampleMRExcel.Red)) AS CountOfBlue/Red

something like that (I think)

How do i perform this?

I tried fitting your line but I'm getting error "Characters found after end of SQL statement."

 

[mole999]

SELECT 
Count(ExampleMRExcel.Language) AS CountOfLanguage
,  ExampleMRExcel.Location
, ExampleMRExcel.Color
,  Count(ExampleMRExcel.Phone) AS CountOfPhone
,  Count(ExampleMRExcel.Address) AS CountOfAddress
,  Count(ExampleMRExcel.Red) AS CountOfRed
, Count(ExampleMRExcel.Blue) AS  CountOfBlue
, Count(ExampleMRExcel.Green) AS CountOfGreen
,  Count(ExampleMRExcel.Yellow) AS CountOfYellow
FROM ExampleMRExcelGROUP BY ExampleMRExcel.Location
, ExampleMRExcel.Color
HAVING (((ExampleMRExcel.Location) Like "London") 
[B]AND  ((ExampleMRExcel.Color) Like "Red" Or (ExampleMRExcel.Color) Like  "Blue")[/B]);

I would have thought that was close, but I can't test it here, if thats what you have i can't see why yellow or green is being returned.

I don't know if HAVING can be substituted with WHERE