Filter Report by Group Header?

[Gareth Jones]

I have a report based on the below query/table:

Parent Record - Child Record - Ref
Alarm A1 - Alarm A1 - 1
Alarm A1 - Alarm B2 - 2
Alarm A1 - Alarm A2 - 1
Alarm D1 - Alarm D1 - 3
Alarm D1 - Alarm D2 - 3
Alarm D1 - Alarm C4 - 4

The child records are alarms that happen with 5 minutes of the parent alarm, but not all are related. Basically I group by the Parent Record in a group header, and then show the related records below it.

What I am aiming for is to only show records with the same reference as the parent record.

So for the parent record Alarm A1, it would only show Alarm A1 and Alarm A2.

The tables are very big, and it takes a few queries to get to the stage above.


Any ideas anyone?

Thanks in advance
Gareth

[jimatqsi]

Gareth,
Your situation is not very clear. I think you are saying all this data is in one table. It's not clear at all what your table of data means.

Can you not simply join the two alarm events on reference number? What prevents that from giving you the related alarms you want?

Jim

[Gareth Jones]

The above table is the result of 4 previous queries and finally a union to get to this stage. The data tables have millions of records.

Effectively all I am trying to do is only show child records that have the same reference as the parent record. The problem is the data above has thousands of records alone and they are grouped in date order currently. If I group by reference as well then it messes up the ordering and groups alarms at different times together.

Hope this makes sense :)

Gareth

[zmbd]

You really haven't given us enough detail to answer the question.

You have however, given enough information so that I'm in agreement with Jim here... sounds like your database is very
poorly designed to handle the data.

Please read thru:[*]> Database Normalization and Table Structures.

The sooner you normalize your database the better!

[Gareth Jones]

:) Apologies if I havent explained the issue or the design very well. The databases are in a corporate SQL environment with hundreds of users, a high number of related tables, and millions of records. The databases run from dual servers with a high number of complicated jobs and reports running every day. This query I am working on is very complicated to even get this far, and I've nearly got the solution to the last piece of this puzzle.

I appreciate you both taking the time to reply, however I'm not sure its a good thing to assume a database is poorly designed based on the brief outline of the issue above.
I did mention the tables were very big which would indicate there were multiple tables and likely to be joined etc.

I appreciate it would be difficult to answer this question however with the limited info I can give, so I'll work it out :)

Thanks again
Gareth

[jimatqsi]

Gareth, the number of records is, for the moment, unimportant. The number of tables may be important, but how many tables can 3 data items be in? If you have implemented several queries prior to this report then that's important because you have the chance to shape the data appropriately for the report.

Maybe you should start at the beginning and let someone here help you with those queries. I suspect you may be making your problem harder than it is. There's an awful lot about data extraction that you have to learn the hard way, through experience. We can help with that.

Jim

[Gareth Jones]

The above data items are really the only ones that have any relevance to the issue. There are many others but again I probably didnt explain very well :)
I have fixed the issue a few steps before I get to this stage. Basically I linked the Parent record to the Reference at the start of the process as well as to the child records at the end so that I effectively had a Parent ref as well like below

Parent Record - Child Record - Child Ref - Parent ref
Alarm A1 - Alarm A1 - 1 - 1
Alarm A1 - Alarm B2 - 2 - 1
Alarm A1 - Alarm A2 - 1 - 1
Alarm D1 - Alarm D1 - 3 - 3
Alarm D1 - Alarm D2 - 3 - 3
Alarm D1 - Alarm C4 - 4 - 3

All I had to do then was say show me any records where the child ref = the parent ref which removed the records not related. I over simplified the whole process to try and explain the issue which made it almost impossible to answer really :)

Thanks all again
Gareth

[jimatqsi]

Great, that's the best solution of all, when you're able to drill down to your own fix. Way to go.

Jim

[Gareth Jones]

ZMBD: Just to reply to your PM this morning, its all sorted when I replied yesterday afternoon. I fixed the issue by linking the parent record with the reference before I even got to this stage. I over simplified the whole process to try and explain the issue which made it almost impossible for you to answer really. Its all working perfectly now.

Thanks again

[zmbd]

I've reset the best answer because if the detail of information provided in Post #7 had been provided to begin with Jim, One of the other experts, or myself would have been able to help you to the solution.

If anything, it would appear that Post #6 prompted you to review your work; thus, leading to your solution.

For that reason I have selected post #6

[Gareth Jones]

I agree I had to review my work as I still didn't have a solution :) but I have no problem you picking whatever answer you want if it helps.

Its all sorted now, on to the next one :)

Thanks