Sequence Grouping

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

Below is my table:

Raw_Data
@S1
A02F
A12F
A22F
@TPO
@TPC
@S2
A02F
A12F
A22F
@TPO
@TPC
etc……


The logice of the data is that "@" is the header record, and the "A" is the
detail of "@".

I want to wite a quert that results the following result in the "TAG" column:

Raw_Data Tag
@S1 1
A02F 1
A12F 1
A22F 1
@TPO 2
@TPC 3
@S2 4
A02F 4
A12F 4
A22F 4
@TPO 5
@TPC 6

Basically, the "TAG" column is grouping the header records with the detail.
Also, the data is "always" in sequentail order (which is a good thing).

Thanks,

Beagle
 
Beagle said:
Hello,

Below is my table:

Raw_Data
@S1
A02F
A12F
A22F
@TPO
@TPC
@S2
A02F
A12F
A22F
@TPO
@TPC
etc……


The logice of the data is that "@" is the header record, and the "A" is the
detail of "@".

I want to wite a quert that results the following result in the "TAG" column:

Raw_Data Tag
@S1 1
A02F 1
A12F 1
A22F 1
@TPO 2
@TPC 3
@S2 4
A02F 4
A12F 4
A22F 4
@TPO 5
@TPC 6

Basically, the "TAG" column is grouping the header records with the detail.
Also, the data is "always" in sequentail order (which is a good thing).

Thanks,

Beagle
Click to expand...

Your problem seems to be one of the few cases where the order of the
records is important.

Have a subquery count the number of records before or equal to the
present one that start with @.

First add an autonumber primary key to your table:

tblRawData
RDID AutoNumber
Raw_Data Text

Then check to make sure your data is still in the same order.

If it is, try:

SELECT Raw_Data, (SELECT Count(A.Raw_Data) FROM tblRawData AS A WHERE
A.RDID <= tblRawData.RDID AND Left([Raw_Data], 1) = "@") AS Tag FROM
tblRawData;

I tried it on a table with the first six records you show. It seems to
do what you want.

James A. Fortune
(e-mail address removed)
 
Thanks James,

It works perfect. Is there a way you could breakdown the statement so I
could understand it a little better?

Beagle

James A. Fortune said:
Beagle said:
Hello,

Below is my table:

Raw_Data
@S1
A02F
A12F
A22F
@TPO
@TPC
@S2
A02F
A12F
A22F
@TPO
@TPC
etc……


The logice of the data is that "@" is the header record, and the "A" is the
detail of "@".

I want to wite a quert that results the following result in the "TAG" column:

Raw_Data Tag
@S1 1
A02F 1
A12F 1
A22F 1
@TPO 2
@TPC 3
@S2 4
A02F 4
A12F 4
A22F 4
@TPO 5
@TPC 6

Basically, the "TAG" column is grouping the header records with the detail.
Also, the data is "always" in sequentail order (which is a good thing).

Thanks,

Beagle
Click to expand...

Your problem seems to be one of the few cases where the order of the
records is important.

Have a subquery count the number of records before or equal to the
present one that start with @.

First add an autonumber primary key to your table:

tblRawData
RDID AutoNumber
Raw_Data Text

Then check to make sure your data is still in the same order.

If it is, try:

SELECT Raw_Data, (SELECT Count(A.Raw_Data) FROM tblRawData AS A WHERE
A.RDID <= tblRawData.RDID AND Left([Raw_Data], 1) = "@") AS Tag FROM
tblRawData;

I tried it on a table with the first six records you show. It seems to
do what you want.

James A. Fortune
(e-mail address removed)
Click to expand...
 
Beagle said:
Thanks James,

It works perfect. Is there a way you could breakdown the statement so I
could understand it a little better?

Beagle
Click to expand...

I'll try.

tblRawData
RDID Raw_Data
1 @S1
2 A02F
3 A12F
4 A22F
5 @TPO
6 @TPC

SELECT Raw_Data, (SELECT Count(A.Raw_Data) FROM tblRawData AS A WHERE
A.RDID <= tblRawData.RDID AND Left([Raw_Data], 1) = "@") AS Tag FROM
tblRawData;

When the query is at tblRawData.RDID = 1 you get:

@S1, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 1 AND
Left([Raw_Data], 1) = "@") AS Tag
= @S1, 1

When tblRawData.RDID = 2 you get:

A02F, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 2 AND
Left([Raw_Data], 1) = "@") As Tag
= A02F, 1 + 0

The 0 is because the leftmost character of A02F is not '@'

When tblRawData.RDID = 3 you get:

A12F, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 3 AND
Left([Raw_Data], 1) = "@") As Tag
= A12F, 1 + 0 + 0

When tblRawData.RDID = 4 you get:

A22F, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 4 AND
Left([Raw_Data], 1) = "@") As Tag
= A22F, 1 + 0 + 0 + 0

When tblRawData.RDID = 5 you get:

@TPO, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 5 AND
Left([Raw_Data], 1) = "@") As Tag
= @TPO, 1 + 0 + 0 + 0 + 1

When tblRawData.RDID = 6 you get:

@TPC, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 6 AND
Left([Raw_Data], 1) = "@") As Tag
= @TPC, 1 + 0 + 0 + 0 + 1 + 1

Note: The query assumes that there are no records with Raw_Data = Null.

James A. Fortune
(e-mail address removed)
 
Thanks James, Very Cool.... This will gove me someting to think about.

James A. Fortune said:
Beagle said:
Thanks James,

It works perfect. Is there a way you could breakdown the statement so I
could understand it a little better?

Beagle
Click to expand...

I'll try.

tblRawData
RDID Raw_Data
1 @S1
2 A02F
3 A12F
4 A22F
5 @TPO
6 @TPC

SELECT Raw_Data, (SELECT Count(A.Raw_Data) FROM tblRawData AS A WHERE
A.RDID <= tblRawData.RDID AND Left([Raw_Data], 1) = "@") AS Tag FROM
tblRawData;

When the query is at tblRawData.RDID = 1 you get:

@S1, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 1 AND
Left([Raw_Data], 1) = "@") AS Tag
= @S1, 1

When tblRawData.RDID = 2 you get:

A02F, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 2 AND
Left([Raw_Data], 1) = "@") As Tag
= A02F, 1 + 0

The 0 is because the leftmost character of A02F is not '@'

When tblRawData.RDID = 3 you get:

A12F, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 3 AND
Left([Raw_Data], 1) = "@") As Tag
= A12F, 1 + 0 + 0

When tblRawData.RDID = 4 you get:

A22F, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 4 AND
Left([Raw_Data], 1) = "@") As Tag
= A22F, 1 + 0 + 0 + 0

When tblRawData.RDID = 5 you get:

@TPO, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 5 AND
Left([Raw_Data], 1) = "@") As Tag
= @TPO, 1 + 0 + 0 + 0 + 1

When tblRawData.RDID = 6 you get:

@TPC, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 6 AND
Left([Raw_Data], 1) = "@") As Tag
= @TPC, 1 + 0 + 0 + 0 + 1 + 1

Note: The query assumes that there are no records with Raw_Data = Null.

James A. Fortune
(e-mail address removed)
Click to expand...
 
James,

Having a problem appying the SQL statement.

The main datatable has 7,000 rows of data (not very much). When the SQL
statement is applied, the query will not finish running. If I reduce the
number of records to 58 from 7,000, then the query will finish running.

Any suggestions on how I could keep the 7,000+ rows of data and have the
query finish?

Thanks,

Beagle

James A. Fortune said:
Beagle said:
Thanks James,

It works perfect. Is there a way you could breakdown the statement so I
could understand it a little better?

Beagle
Click to expand...

I'll try.

tblRawData
RDID Raw_Data
1 @S1
2 A02F
3 A12F
4 A22F
5 @TPO
6 @TPC

SELECT Raw_Data, (SELECT Count(A.Raw_Data) FROM tblRawData AS A WHERE
A.RDID <= tblRawData.RDID AND Left([Raw_Data], 1) = "@") AS Tag FROM
tblRawData;

When the query is at tblRawData.RDID = 1 you get:

@S1, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 1 AND
Left([Raw_Data], 1) = "@") AS Tag
= @S1, 1

When tblRawData.RDID = 2 you get:

A02F, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 2 AND
Left([Raw_Data], 1) = "@") As Tag
= A02F, 1 + 0

The 0 is because the leftmost character of A02F is not '@'

When tblRawData.RDID = 3 you get:

A12F, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 3 AND
Left([Raw_Data], 1) = "@") As Tag
= A12F, 1 + 0 + 0

When tblRawData.RDID = 4 you get:

A22F, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 4 AND
Left([Raw_Data], 1) = "@") As Tag
= A22F, 1 + 0 + 0 + 0

When tblRawData.RDID = 5 you get:

@TPO, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 5 AND
Left([Raw_Data], 1) = "@") As Tag
= @TPO, 1 + 0 + 0 + 0 + 1

When tblRawData.RDID = 6 you get:

@TPC, (SELECT Count(Raw_Data) FROM tblRawData WHERE RDID <= 6 AND
Left([Raw_Data], 1) = "@") As Tag
= @TPC, 1 + 0 + 0 + 0 + 1 + 1

Note: The query assumes that there are no records with Raw_Data = Null.

James A. Fortune
(e-mail address removed)
Click to expand...
 
Beagle said:
James,

Having a problem appying the SQL statement.

The main datatable has 7,000 rows of data (not very much). When the SQL
statement is applied, the query will not finish running. If I reduce the
number of records to 58 from 7,000, then the query will finish running.

Any suggestions on how I could keep the 7,000+ rows of data and have the
query finish?

Thanks,

Beagle
Click to expand...

Even 7,000 records should not cause the query to take over a couple of
minutes. Make sure RDID is the primary key and that Raw_Data is
indexed. If it's too slow after doing that, post back and I'll try
writing a VBA routine that makes a single pass through the data.

James A. Fortune
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Gaps in ordered series 1
Change a crostab report to a simple columnar format 4
Multiple Sums 3
Returning values to unique rows 3
Help writing a query 4
Queries Automation 6
Sorting of a pivot table by outer and inner row field combinations 1
matching ids help 2

Back
Top