Table complex filtering (1 Viewer)

fallito · Oct 9, 2008

Hi everyone,

I new at Access and I'm dealing with a table that has 3 columns. The first column is a machine number (1-13), the second one is a list of alarm numbers, and the third one has the date & time in which the alarm number was triggered and recorded.

The alarms trigger whenever there's a failure. In many cases a single alarm displays a set of alarms. If this happens, the alrms triggered and recorded have the same date & time. Therefore, I need to know the number of times a group of alarms has been triggered. I can't do this in excel because I have more than 500,000 records.

Is there a way to this without doing it manually?

Thanks for your help.

georgedwilkinson · Oct 9, 2008

You said it was in a table, right? That implies it is some kind of RDBMS, like Access (though that's a pretty high record count for Access, but doable).

You would write a select query selecting your 3 fields and a fourth aggregate column based on count(*). Then group the query by the 3 fields. Poof, you've got a result set with only unique rows and the number of times that unique row is repeated in the table.

MSAccessRookie · Oct 9, 2008

fallito said:
Hi everyone,

I new at Access and I'm dealing with a table that has 3 columns. The first column is a machine number (1-13), the second one is a list of alarm numbers, and the third one has the date & time in which the alarm number was triggered and recorded.

The alarms trigger whenever there's a failure. In many cases a single alarm displays a set of alarms. If this happens, the alrms triggered and recorded have the same date & time. Therefore, I need to know the number of times a group of alarms has been triggered. I can't do this in excel because I have more than 500,000 records.

Is there a way to this without doing it manually?

Thanks for your help.

If each alarm time is unique to the event (meaning that not more than one event can occur at the same time), then code similar to the following (you change for your table and columns) might be of use:

Code:

Select A.MachineNumber, A.AlarmNumber, A.AlarmTime From 
[Select AlarmTime From {YourTable}
Group By AlarmTime Having Count(*) > 1] As A

fallito · Oct 9, 2008

Thanks for the reply. But, precisely that's the main issue. Alarms can occur at the same time. That is, if alarm 100 occurs, it generates 3 alarms (100, 101 and 102). If this happened, then the date and time for all the alarms will be the same (E.g. 12/8/2006 11:09:58 PM).

The thing is that I need to count how many times this set of alarms, or whatever set of alarms, appear at the same time in any given machine.

georgedwilkinson · Oct 9, 2008

Then remove AlarmNumber from the query and group by clause.

Table complex filtering (1 Viewer)

fallito

New member

georgedwilkinson

AWF VIP

MSAccessRookie

AWF VIP

fallito

New member

georgedwilkinson

AWF VIP

Similar threads

Users who are viewing this thread