count results of combination of two fields

[robina]

Hi,
I have a query that returns a all records (there are 91) that meet a criteria. From this query I have built another query that groups these results by two fields [Server_Type] and [Item] and then I add the [Item] field again and add Count in the total row. Lastly, I have an OLE object that needs to show up for each record that is returned. The OLE object comes from a separate table and is giving me a hard time because I get the error that you can't group on an OLE object.
My next query based on the first query needs to return a count of the combinations of [Server_Type] and [Item] that are returned and the OLE object. The grouping of this works, but the count does not. I need a total count of each combination of the two fields. Instead it returns all the combinations nicely but the count returns a number I can't even figure out.

The query returns 12 combinations of [Server_Type] and [Item]. Each combination should have the number of times it occurs inthe prior query. As an example, one returns 27 when it should be 23. another returns 270 when it should be 69.

If this is unclear at all, please let me know. I have wrestled with this for 2 days before asking for help.
thank you

 

[plog]

You lost me in all the details. Here's what I got--you have 2 issues, 1-some count is not working and 2-you are unable to use an OLE image in an Aggragate query (one that uses GROUP BY). Correct?

Let's tackle the count issue first. Post the structure of your underlying table(s) along with some sample data. Then post the results you want based on that sample data.

 

[robina]

You are correct. Those are the 2 issues.
data_tbl contains all records. [Server_Type], [Item], [Device_Count], [Billing_Date]
I have another table "baseline_tbl" That contains the same fields with a few differences. The [Device_Count] in the baseline tbl contains the target number in it. the data_tbl contains the actual number of servers. These tables are joined with Date fields.

Next, from those 2 tables I have the "Calcs" query. From the "data_tbl" the fields are [Server_Type](with the criteria [baseline_tble]![Server_Type], [Item], [Device_Count], and [Billing_Date]

From the baseline_tbl: [Device_Count], [Baseline_date]

then 3 calculated fields, one which calculates the difference between target count and actual count, [Change from baseline]

Based off of that query, the next query is Level_2_green"
It contains all fields from the "Calcs" query with 2 added fields; Increment: IIf([Server_Type] Is Null,"na","green") and Change from baseline (which is WHERE)
This returns a count for each records that meets the criteria and adds the "Increment" field that returns the word "green" in each field.

I am doing this to return the OLE object which is a green traffic light.

The last query is "green_count". It uses these fields from the "level_2_green" query:
[Server_Type], [Item], a new field [how many: Item] (Count), and [Traffic_Light] which comes from a table named "Green_Traffic_Lights" and the Total row has "first"

This started out all simple but its not. My entire goal is to add up a count of records where the combination of the server_Type and also return an OLE object of a green traffic light if the [Change from baseline] value is between -0.05 and 0.05, the redlight OLE object if the value is >= 0.05, and the yellow OLE object if the vaule is <=-0.05.

I know it sounds complicated but I was building queries for green light, then yellow, then red. It works nicely except for the grouping. I can get a light to show up on a form but then the count is wrong.

Thank you for responding. You've given me hope.

 

[plog]

My eyes started glazing over again.

Post the structure of your underlying table(s) along with some sample data. Then post the results you want based on that sample data

Use this format

TableNameHere
fieldname1, fieldname2, fieldname3
1, 1/13/2013, 17
2, 2/1/2013, 14

AnotherTableName
afield1, afield2
1/13/2013, 12
2/1/2013, 22

Then post the results you want in a similar manner based on the sample data.

 

[robina]

okay:

Use this format

data_tbl
Server_Type, Item, Device_Count, Billing_Date
End User, EPIC Standard Thin Client Desktops, 1755, Dec-12
Intel Based, Medium 22, Nov-12

baseline_tbl
Server_Type, Item, Device_Count, Baseline_Date, Dead_Band
End User, EPIC Standard Thin Client Desktops, 1755, Dec-12 5%
Intel Based, Medium 8, Nov-12 5%

Traffic_Lights_tbl
Server_Type, Item, Increment, Traffic_Light
End User, EPIC Standard Thin Client Desktops, Red, Bitmap Image
End User, EPIC Standard Thin Client Desktops, yellow, Bitmap Image
End User, EPIC Standard Thin Client Desktops, green, Bitmap Image
Intel Based, Medium, Red, Bitmap Image
Intel Based, Medium, yellow, Bitmap Image
Intel Based, Medium, green, Bitmap Image


Results in a form:
Server_Type, Item, total records, Traffic_Light, Traffic_Light, Traffic_Light
End User, EPIC Standard Thin Client Desktops, 1, green, yellow, red

The traffic lights will have total records under each one. There would be a red, yellow and green light for each combination of Server_Type and Item based on the "Calcs" query field named [Change from baseline]

so, results are:
server_type, Item, red light (with record count underneath), yellow light (with record count underneath), red light (with record count underneath)

It may be easier to do it one light at a time like i've started but at this point who knows.

 

[robina]

I wanted to add quickly that this is my criteria for the 3 diff traffic light graphics:

redlight bmp [Calcs]![Change from basline] >=0.05
yellowlight bmp [Calcs]![Change from basline] <=-0.05
greenlight bmp [Calcs]![Change from basline] between -0.05 and 0.05

"Calcs" is the query based on the baseline_tbl and data_tbl

 

[plog]

I apologize for hoping you would get what I've repeatedly tried to politely imply. So let me explicitly state it: Quit giving me things I don't ask for. I don't want any more explanations or calculations or stories or anything else I haven't asked for. If you want me to help you, give me what I ask for--no more no less.

Your initial post talked about a query you needed. Now with these last two posts you are talking about a form. Initially I thought I had a vague grasp of it, but thanks to your further clarifications I have no idea what you need. So, with the spirit of that first paragraph I typed in mind, what do you need? A query or a report or a form? Try to tell me in 20 words or less and in general terms as possible--no database jargon, no refering to field or table names.

 

[robina]

I need a query that returns the total number of records that meet a certain criteria and shows a graphic based on that criteria.

 

[robina]

Hi Plog,
I did get a query to work to group everything correctly. the only thing left is to add the graphic, I"m still getting the "cannot group OLE object". This is my query SQL:

SELECT DISTINCTROW Level_2_green.Server_Type, Level_2_green.Item, Count(*) AS [Count Of Level_2_green]
FROM Level_2_green
GROUP BY Level_2_green.Server_Type, Level_2_green.Item;

Thank you again for your help.