Question Summarising or counting results

[Gypsie Jones]

Hi,

I am posting in General as i don't know what this comes under. I have a results database that collates information that is collected monthly over numerous locations. It's a temperature result. What I want to be able to do is count the excursions. So temperatures greater than 30 for instance is a fail....29 and below is a pass. I have 14 results per location.

Any ideas how I can summarise this?

Many Thanks,

 

[plog]

What does your look like now (provide your table and field names and some sample data) and what would you like it to look like (using that initial sample data, show what the resulting data should be).

Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
David, 1/3/2008, 72
Sally, 3/9/2010, 14
Tim, 3/8/2012, 0

 

[Gypsie Jones]

Hi,

Thanks for your response.

I'm not entirely sure what you mean but here goes.

Temperature Results
Building Name, Building Number, Sentinel Cold, Sentinel Hot, Calorifier Out, Calorifer In, Cold 1, Hot 1, Cold 2, Hot 2,
Main Building, 101, 10, 66, 10, 66, 66, 55, 10, 66, 10, 66
HSE Building, 102, 10, 66, 10, 66, 66, 55, 10, 44, 25, 66
Site Office, 103, 10, 66, 10, 66, 66, 7, 10, 7, 10, 66

Just some made up data but in essence I want the database to pick up that values 44 and 25 are out of spec for HSE Building and both 7's are out of spec for the Site Office.

I want a list or a table of out of spec values.

Thanks in advance.

 

[namliam]

The real question is "why are 44, 25 and 7 out of spec" ??

Is it because they do not match with Sentinel Cold and Sentinel hot?

Your data format is a problem here, perhaps a union query can help if you cant help the way you get this data. Having this in columns as you do having numbered columns, Cold 1, cold 2, cold 3, etc is a bad idea.

 

[Gypsie Jones]

Hi,

The cold value is out of spec if it is >19. Hot value is out of spec if it is <50.

Why is cold 1, cold 2, cold 3 not good?

 

[namliam]

Cold 1, Cold 2, Cold 3, etc... (and all simular numbered columns) is not good because they are itterations of the same column, Cold.
Itterations in databases go into rows not columns. For example if the measurements are taken every 30 minutes you would see
12:00 14 66
12:30 16 64
13:00 22 40

And not see: 14 66 16 64 22 40

In the first format having itterations in rows you can easily do a query

Select * from yourtable where cold >19

To show all your "out of spec" values
Now you have to trick things to show them....
I.e.
Make a query that goes:

Select [building name], [building number], 1 ColumnNumber, [Cold 1] Cold, [Hot 1] Hot from [Yourtable]
Union 
Select [building name], [building number], 2 ColumnNumber, [Cold 2] Cold, [Hot 2] Hot from [Yourtable]
Union
Select [building name], [building number], 3 ColumnNumber, [Cold 3] Cold, [Hot 3] Hot from [Yourtable]
Union
select ... etc....

Save this query as qryUnionDuNamliam

Now make a second query:

Select * from qryUnionDuNamliam where Cold > 19

and a third

Select * from qryUnionDuNamliam where Hot < 50

Also using spaces in names, probably more issues too....
though probably some you cant really help

 

[Gypsie Jones]

This may seem a basic question but where to I put that code?

I think I need to keep the temperatures in different columns as they are being taken from different places. Temps taken at each location at different time points will appear in rows.

 

[namliam]

even different locations can/should be rows

You put that code which is called SQL into the SQL window of a query.
Create a new query, dont add any tables. Change the (design) view to SQL and paste the sql, then save the query