How to write a query which will allow me to capture multiple events

[scstrachan]

I would like to be able to group consecutive SortID's by SortType's T1,

N1 and P1. For instance, I need to be able to group SortID's 12123,
12122 & 12121. The SortID type is Number and the SortType (type) is
text. Any help would be much appreciated.

Thanks,
Scott


SortID SortType
12123 P1
12122 N1
12121 T1
12120 P1
12119 N1
12118 T1
12117 P1
12116 N1
12115 T1
12114 P1
12113 N1
12112 T1
12111 P1
12110 N1
12109 T1
12108 P1
12107 N1
12106 T1


Reply »

2 From: (e-mail address removed) - view profile
Date: Fri, Jan 12 2007 4:33 pm
Email: (e-mail address removed)
Groups: comp.databases.ms-access
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


select sortId, sortType from tblX order by sortId Desc, sortType


- Hide quoted text -
- Show quoted text -

I would like to be able to group consecutive SortID's by SortType's T1,
N1 and P1. For instance, I need to be able to group SortID's 12123,
12122 & 12121. The SortID type is Number and the SortType (type) is
text. Any help would be much appreciated.


SortID SortType
12123 P1
12122 N1
12121 T1
12120 P1
12119 N1
12118 T1
12117 P1
12116 N1
12115 T1
12114 P1
12113 N1
12112 T1
12111 P1
12110 N1
12109 T1
12108 P1
12107 N1
12106 T1

Reply » Rate this post: Text for clearing space

3 From: (e-mail address removed) - view profile
Date: Fri, Jan 12 2007 7:48 pm
Email: (e-mail address removed)
Groups: comp.databases.ms-access
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author


Same result.

What I am trying to do is capture a series of ID's. For instance" I
need to be able to group 12123 P1, 12122 N1 & 12121 T1 together. This

equals all the activity for 1 day. Each grouping of P1, N1 & T1 need
to be captured together, along with the corresponding sortid. Hope is
a better explanation.


Scott

 

[John Nurick]

Hi Scott,

In your sample data, if you group records with the same SortType the
SortIDs aren't consecutive:

SortID SortType
12107 N1
12110 N1
12113 N1
12116 N1
12119 N1
12122 N1
12108 P1
12111 P1
12114 P1
12117 P1
12120 P1
12123 P1
12106 T1
12109 T1
12112 T1
12115 T1
12118 T1
12121 T1

and if you order the records by SortID (so that consecutive SortIDs are
adjacent) you can't group them by SortType.

Can you show us what you want the output of the query to be?

 

[P Mertens]

Hi,

You could go for a grouping based on the sortid ... ( if the sequence is
guarenteed

SELECT
IIf([sorttype]="N1",[Sortid]-1,IIf([sorttype]="P1",[sortid]-2,[sortid])) AS
Expr1, Test.Sorttype
FROM Test;

Test Sortid Sorttype
12118 T1
12119 N1
12120 P1
12121 T1
12122 N1
12123 P1


This would result into

qTest Expr1 Sorttype
12118 T1
12118 N1
12118 P1
12121 T1
12121 N1
12121 P1


And now a cross-tab becomes available or grouping functions like sum, min,
max

Regards,
Patrick




I would like to be able to group consecutive SortID's by SortType's T1,

N1 and P1. For instance, I need to be able to group SortID's 12123,
12122 & 12121. The SortID type is Number and the SortType (type) is
text. Any help would be much appreciated.

Thanks,
Scott


SortID SortType
12123 P1
12122 N1
12121 T1
12120 P1
12119 N1
12118 T1
12117 P1
12116 N1
12115 T1
12114 P1
12113 N1
12112 T1
12111 P1
12110 N1
12109 T1
12108 P1
12107 N1
12106 T1


Reply »

2 From: (e-mail address removed) - view profile
Date: Fri, Jan 12 2007 4:33 pm
Email: (e-mail address removed)
Groups: comp.databases.ms-access
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


select sortId, sortType from tblX order by sortId Desc, sortType


- Hide quoted text -
- Show quoted text -

I would like to be able to group consecutive SortID's by SortType's T1,
N1 and P1. For instance, I need to be able to group SortID's 12123,
12122 & 12121. The SortID type is Number and the SortType (type) is
text. Any help would be much appreciated.


SortID SortType
12123 P1
12122 N1
12121 T1
12120 P1
12119 N1
12118 T1
12117 P1
12116 N1
12115 T1
12114 P1
12113 N1
12112 T1
12111 P1
12110 N1
12109 T1
12108 P1
12107 N1
12106 T1

Reply » Rate this post: Text for clearing space

3 From: (e-mail address removed) - view profile
Date: Fri, Jan 12 2007 7:48 pm
Email: (e-mail address removed)
Groups: comp.databases.ms-access
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author


Same result.

What I am trying to do is capture a series of ID's. For instance" I
need to be able to group 12123 P1, 12122 N1 & 12121 T1 together. This

equals all the activity for 1 day. Each grouping of P1, N1 & T1 need
to be captured together, along with the corresponding sortid. Hope is
a better explanation.


Scott

 

[scstrachan]

This is the output. This grouping represents a day's worth.

id SortName
12129 P1
12130 T1
12131 N1

Hi Scott,

In your sample data, if you group records with the same SortType the
SortIDs aren't consecutive:

SortID SortType
12107 N1
12110 N1
12113 N1
12116 N1
12119 N1
12122 N1
12108 P1
12111 P1
12114 P1
12117 P1
12120 P1
12123 P1
12106 T1
12109 T1
12112 T1
12115 T1
12118 T1
12121 T1

and if you order the records by SortID (so that consecutive SortIDs are
adjacent) you can't group them by SortType.

Can you show us what you want the output of the query to be?

I would like to be able to group consecutive SortID's by SortType's T1,

N1 and P1. For instance, I need to be able to group SortID's 12123,
12122 & 12121. The SortID type is Number and the SortType (type) is
text. Any help would be much appreciated.

SortID SortType
12123 P1
12122 N1
12121 T1
12120 P1
12119 N1
12118 T1
12117 P1
12116 N1
12115 T1
12114 P1
12113 N1
12112 T1
12111 P1
12110 N1
12109 T1
12108 P1
12107 N1
12106 T1

Reply »

2 From: (e-mail address removed) - view profile
Date: Fri, Jan 12 2007 4:33 pm
Email: (e-mail address removed)
Groups: comp.databases.ms-access
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author

select sortId, sortType from tblX order by sortId Desc, sortType

- Hide quoted text -
- Show quoted text -

Reply » Rate this post: Text for clearing space

3 From: (e-mail address removed) - view profile
Date: Fri, Jan 12 2007 7:48 pm
Email: (e-mail address removed)
Groups: comp.databases.ms-access
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author

Same result.

What I am trying to do is capture a series of ID's. For instance" I
need to be able to group 12123 P1, 12122 N1 & 12121 T1 together. This

equals all the activity for 1 day. Each grouping of P1, N1 & T1 need
to be captured together, along with the corresponding sortid. Hope is
a better explanation.

Scott--

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.- Hide quoted text -- Show quoted text -

 

[John Nurick]

Scott,

The sample data you posted previously includes SortID values from 12106
to 12123 inclusive. You now say you want the output from this data to
show SortID values 12129, 12130 and 12131. Either you've made a mistake
or there's something important you're not telling us.

Once again: if the data is as follows (which is what you originally
posted), what should the output of the query be?

SortID SortType
12123 P1
12122 N1
12121 T1
12120 P1
12119 N1
12118 T1
12117 P1
12116 N1
12115 T1
12114 P1
12113 N1
12112 T1
12111 P1
12110 N1
12109 T1
12108 P1
12107 N1
12106 T1

This is the output. This grouping represents a day's worth.

id SortName
12129 P1
12130 T1
12131 N1

Hi Scott,

In your sample data, if you group records with the same SortType the
SortIDs aren't consecutive:

SortID SortType
12107 N1
12110 N1
12113 N1
12116 N1
12119 N1
12122 N1
12108 P1
12111 P1
12114 P1
12117 P1
12120 P1
12123 P1
12106 T1
12109 T1
12112 T1
12115 T1
12118 T1
12121 T1

and if you order the records by SortID (so that consecutive SortIDs are
adjacent) you can't group them by SortType.

Can you show us what you want the output of the query to be?

I would like to be able to group consecutive SortID's by SortType's T1,

N1 and P1. For instance, I need to be able to group SortID's 12123,
12122 & 12121. The SortID type is Number and the SortType (type) is
text. Any help would be much appreciated.

SortID SortType
12123 P1
12122 N1
12121 T1
12120 P1
12119 N1
12118 T1
12117 P1
12116 N1
12115 T1
12114 P1
12113 N1
12112 T1
12111 P1
12110 N1
12109 T1
12108 P1
12107 N1
12106 T1

Reply »

2 From: (e-mail address removed) - view profile
Date: Fri, Jan 12 2007 4:33 pm
Email: (e-mail address removed)
Groups: comp.databases.ms-access
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author

select sortId, sortType from tblX order by sortId Desc, sortType

- Hide quoted text -
- Show quoted text -

(e-mail address removed) wrote:
I would like to be able to group consecutive SortID's by SortType's T1,
N1 and P1. For instance, I need to be able to group SortID's 12123,
12122 & 12121. The SortID type is Number and the SortType (type) is
text. Any help would be much appreciated.

SortID SortType
12123 P1
12122 N1
12121 T1
12120 P1
12119 N1
12118 T1
12117 P1
12116 N1
12115 T1
12114 P1
12113 N1
12112 T1
12111 P1
12110 N1
12109 T1
12108 P1
12107 N1
12106 T1

Reply » Rate this post: Text for clearing space

3 From: (e-mail address removed) - view profile
Date: Fri, Jan 12 2007 7:48 pm
Email: (e-mail address removed)
Groups: comp.databases.ms-access
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author

Same result.

What I am trying to do is capture a series of ID's. For instance" I
need to be able to group 12123 P1, 12122 N1 & 12121 T1 together. This

equals all the activity for 1 day. Each grouping of P1, N1 & T1 need
to be captured together, along with the corresponding sortid. Hope is
a better explanation.

Scott--

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.- Hide quoted text -- Show quoted text -