Number records in a query

[Erick C]

Hi everyone -
I am hoping someone can help me out with a problem that I am having.
I have a table that contains customer account information, basically
what securities they own. I am trying to create a query that will
number (from 1 to n) the records belonging to each account number,
basically giving me the following:

(New Field)
Account # Security Number
1 1 1
1 2 2
1 3 3
2 1 1
2 2 2
3 3 3

I have looked up a few other discussions regarding numbering records,
and I have tried using some of the recommendations with no luck. I am
not te best at SQL, so trying to replicate some of the examples has
been a bit difficult for me.
In case, my table name is [Collateral_Details], and the headers for
account and security are the same as in my example.
If anyone could help me figure this out I would appreciate it very
much!

Thanks
Erick

 

[Jeff Boyce]

Erick

You've described "how" you are trying to solve some issue, but not "what"
that issue is.

What will having the new field/number allow you to do?

And given your example, how is that field/number any different than the
Security number?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Erick C]

Hi Jeff -
I am sorry, let me clarify my situation a bit more for you.
Currently, I am making a database that will be importing security
information. This imformation will go into a report that I made,
basically a receipt. It will print out 3 "receipts" per page, each
receipt will be mailed to the customer.
Given the small space on the receipt, I can only fit 8 records into my
security information section. Some customers have quite a few
securities, up to 50 or so.
I am planning on using a query to count how many records show up for
each customer account number in the Collateral Details table and
create a new table that includes the number of securities. I can then
use this count of securities to identify what customers need to have
more than one receipt. (Ex. customer has 15 securities under their
account number, he needs 2 receipts. One receipt has 8 records of
security information, and the other has the remaining 7 records of
security information.)
With the record count I can then create a field for the number of
receipts that a customer needs, this way I can get a customer with
multiple records to print out all of their receipts together one after
another, rather than having a separate report for customers with a lot
of records to be printed.
I hope my description clarifies my situation a bit more for you. If
not, please let me know and I will try to provide any further
information.

Erick

You've described "how" you are trying to solve some issue, but not "what"
that issue is.

What will having the new field/number allow you to do?

And given your example, how is that field/number any different than the
Security number?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi everyone -
I am hoping someone can help me out with a problem that I am having.
I have a table that contains customer account information, basically
what securities they own.  I am trying to create a query that will
number (from 1 to n) the records belonging to each account number,
basically giving me the following:

                                     (New Field)
Account #      Security       Number
    1                    1                1
    1                    2                2
    1                    3                3
    2                    1                1
    2                    2                2
    3                    3                3

I have looked up a few other discussions regarding numbering records,
and I have tried using some of the recommendations with no luck.  I am
not te best at SQL, so trying to replicate some of the examples has
been a bit difficult for me.
In case, my table name is [Collateral_Details], and the headers for
account and security are the same as in my example.
If anyone could help me figure this out I would appreciate it very
much!

Thanks
Erick- Hide quoted text -

- Show quoted text -

 

[KARL DEWEY]

Try these queries --

qryRowNum
SELECT [Collateral_Details].[Account #], [Collateral_Details].[Security],
(SELECT Count(*) FROM [Collateral_Details] AS [XX] WHERE [XX].[Account #] &
[XX].[Security]<= [Collateral_Details].[Account #] &
[Collateral_Details].[Security])-1 AS Row
FROM [Collateral_Details]
GROUP BY [Collateral_Details].[Account #] & [Collateral_Details].[Security],
[Collateral_Details].[Security], [Collateral_Details].[Account #];

SELECT qryRowNum.[Account #], qryRowNum.[Security], ([Row] Mod 3)+1 AS[New
Field]
FROM qryRowNum
ORDER BY Row;

--
Build a little, test a little.

Hi Jeff -
I am sorry, let me clarify my situation a bit more for you.
Currently, I am making a database that will be importing security
information. This imformation will go into a report that I made,
basically a receipt. It will print out 3 "receipts" per page, each
receipt will be mailed to the customer.
Given the small space on the receipt, I can only fit 8 records into my
security information section. Some customers have quite a few
securities, up to 50 or so.
I am planning on using a query to count how many records show up for
each customer account number in the Collateral Details table and
create a new table that includes the number of securities. I can then
use this count of securities to identify what customers need to have
more than one receipt. (Ex. customer has 15 securities under their
account number, he needs 2 receipts. One receipt has 8 records of
security information, and the other has the remaining 7 records of
security information.)
With the record count I can then create a field for the number of
receipts that a customer needs, this way I can get a customer with
multiple records to print out all of their receipts together one after
another, rather than having a separate report for customers with a lot
of records to be printed.
I hope my description clarifies my situation a bit more for you. If
not, please let me know and I will try to provide any further
information.

Erick

You've described "how" you are trying to solve some issue, but not "what"
that issue is.

What will having the new field/number allow you to do?

And given your example, how is that field/number any different than the
Security number?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi everyone -
I am hoping someone can help me out with a problem that I am having.
I have a table that contains customer account information, basically
what securities they own. I am trying to create a query that will
number (from 1 to n) the records belonging to each account number,
basically giving me the following:

(New Field)
Account # Security Number
1 1 1
1 2 2
1 3 3
2 1 1
2 2 2
3 3 3

I have looked up a few other discussions regarding numbering records,
and I have tried using some of the recommendations with no luck. I am
not te best at SQL, so trying to replicate some of the examples has
been a bit difficult for me.
In case, my table name is [Collateral_Details], and the headers for
account and security are the same as in my example.
If anyone could help me figure this out I would appreciate it very
much!

Thanks
Erick- Hide quoted text -

- Show quoted text -

.

 

[Erick C]

Hi Karl -
Thank you for the response. Unfortunately, I believe my poor example
may have given you an incorrect impression of what I was trying to
get.
The first query is definitely on the right track. [RowNum] has a
record count for each security. So my [Collateral Details] table has
631 records, and I have 0 to 630 in the [Row] column. I am having
problems with the second query, though.
My bad example gives the impression that I am looking for a sequence
of 3 securities for each account number, which I am not. One account
number may have only 1 security, while the next account number may
have 25 securities. When I view the second query, the [New Field] that
is created shows a recurring sequence going from 1 to 3 and then
repeating, regardless of what the account number is.
How can I modify the second SQL statement so that it is counting
securities by looking at the account number for an indicator of when
to start over at 1?

Thank you again for all of your assistance.

Try these queries --

   qryRowNum
SELECT [Collateral_Details].[Account #], [Collateral_Details].[Security], 
(SELECT Count(*) FROM [Collateral_Details] AS [XX] WHERE  [XX].[Account#] &
[XX].[Security]<= [Collateral_Details].[Account #] &
[Collateral_Details].[Security])-1 AS Row
FROM [Collateral_Details]
GROUP BY [Collateral_Details].[Account #] & [Collateral_Details].[Security],
[Collateral_Details].[Security], [Collateral_Details].[Account #];

SELECT qryRowNum.[Account #], qryRowNum.[Security],  ([Row] Mod 3)+1  AS[New
Field]
FROM qryRowNum
ORDER BY Row;

--
Build a little, test a little.

Hi Jeff -
I am sorry, let me clarify my situation a bit more for you.
Currently, I am making a database that will be importing security
information.  This imformation will go into a report that I made,
basically a receipt.  It will print out 3 "receipts" per page, each
receipt will be mailed to the customer.
Given the small space on the receipt, I can only fit 8 records into my
security information section.  Some customers have quite a few
securities, up to 50 or so.
I am planning on using a query to count how many records show up for
each customer account number in the Collateral Details table and
create a new table that includes the number of securities.  I can then
use this count of securities to identify what customers need to have
more than one receipt.  (Ex. customer has 15 securities under their
account number, he needs 2 receipts.  One receipt has 8 records of
security information, and the other has the remaining 7 records of
security information.)
With the record count I can then create a field for the number of
receipts that a customer needs, this way I can get a customer with
multiple records to print out all of their receipts together one after
another, rather than having a separate report for customers with a lot
of records to be printed.
I hope my description clarifies my situation a bit more for you.  If
not, please let me know and I will try to provide any further
information.

Erick
You've described "how" you are trying to solve some issue, but not "what"
that issue is.
What will having the new field/number allow you to do?
And given your example, how is that field/number any different than the
Security number?
More info, please...
Regards
Jeff Boyce
Microsoft Access MVP
--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.
Any code or pseudocode included in this post is offered "as is", withno
guarantee as to suitability.
You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi everyone -
I am hoping someone can help me out with a problem that I am having..
I have a table that contains customer account information, basically
what securities they own.  I am trying to create a query that will
number (from 1 to n) the records belonging to each account number,
basically giving me the following:
                                     (New Field)
Account #      Security       Number
    1                    1                1
    1                    2                2
    1                    3                3
    2                    1                1
    2                    2                2
    3                    3                3
I have looked up a few other discussions regarding numbering records,
and I have tried using some of the recommendations with no luck.  I am
not te best at SQL, so trying to replicate some of the examples has
been a bit difficult for me.
In case, my table name is [Collateral_Details], and the headers for
account and security are the same as in my example.
If anyone could help me figure this out I would appreciate it very
much!
Thanks
Erick- Hide quoted text -
- Show quoted text -

.- Hide quoted text -

- Show quoted text -

 

[KARL DEWEY]

How can I modify the second SQL statement so that it is counting securities
by looking at the account number for an indicator of when to start over at 1?
You don't. You have to modify both queries.
qryRowNum
SELECT [Collateral_Details].[Account #], [Collateral_Details].[Security],
(SELECT Count(*) FROM [Collateral_Details] AS [XX] WHERE [XX].[Account #]
=[Collateral_Details].[Account #] AND [XX].[Account #] & [XX].[Security]<=
[Collateral_Details].[Account #] & [Collateral_Details].[Security])-1 AS Row
FROM [Collateral_Details]
GROUP BY [Collateral_Details].[Account #] & [Collateral_Details].[Security],
[Collateral_Details].[Security], [Collateral_Details].[Account #];

SELECT qryRowNum.[Account #], qryRowNum.[Security], ([Row] Mod 3)+1 AS[New
Field]
FROM qryRowNum
ORDER BY qryRowNum.[Account #], Row;

--
Build a little, test a little.

Hi Karl -
Thank you for the response. Unfortunately, I believe my poor example
may have given you an incorrect impression of what I was trying to
get.
The first query is definitely on the right track. [RowNum] has a
record count for each security. So my [Collateral Details] table has
631 records, and I have 0 to 630 in the [Row] column. I am having
problems with the second query, though.
My bad example gives the impression that I am looking for a sequence
of 3 securities for each account number, which I am not. One account
number may have only 1 security, while the next account number may
have 25 securities. When I view the second query, the [New Field] that
is created shows a recurring sequence going from 1 to 3 and then
repeating, regardless of what the account number is.
How can I modify the second SQL statement so that it is counting
securities by looking at the account number for an indicator of when
to start over at 1?

Thank you again for all of your assistance.

Try these queries --

qryRowNum
SELECT [Collateral_Details].[Account #], [Collateral_Details].[Security],
(SELECT Count(*) FROM [Collateral_Details] AS [XX] WHERE [XX].[Account #] &
[XX].[Security]<= [Collateral_Details].[Account #] &
[Collateral_Details].[Security])-1 AS Row
FROM [Collateral_Details]
GROUP BY [Collateral_Details].[Account #] & [Collateral_Details].[Security],
[Collateral_Details].[Security], [Collateral_Details].[Account #];

SELECT qryRowNum.[Account #], qryRowNum.[Security], ([Row] Mod 3)+1 AS[New
Field]
FROM qryRowNum
ORDER BY Row;

--
Build a little, test a little.

Hi Jeff -
I am sorry, let me clarify my situation a bit more for you.
Currently, I am making a database that will be importing security
information. This imformation will go into a report that I made,
basically a receipt. It will print out 3 "receipts" per page, each
receipt will be mailed to the customer.
Given the small space on the receipt, I can only fit 8 records into my
security information section. Some customers have quite a few
securities, up to 50 or so.
I am planning on using a query to count how many records show up for
each customer account number in the Collateral Details table and
create a new table that includes the number of securities. I can then
use this count of securities to identify what customers need to have
more than one receipt. (Ex. customer has 15 securities under their
account number, he needs 2 receipts. One receipt has 8 records of
security information, and the other has the remaining 7 records of
security information.)
With the record count I can then create a field for the number of
receipts that a customer needs, this way I can get a customer with
multiple records to print out all of their receipts together one after
another, rather than having a separate report for customers with a lot
of records to be printed.
I hope my description clarifies my situation a bit more for you. If
not, please let me know and I will try to provide any further
information.

Erick

You've described "how" you are trying to solve some issue, but not "what"
that issue is.

What will having the new field/number allow you to do?

And given your example, how is that field/number any different than the
Security number?

More info, please...

Jeff Boyce
Microsoft Access MVP

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi everyone -
I am hoping someone can help me out with a problem that I am having..
I have a table that contains customer account information, basically
what securities they own. I am trying to create a query that will
number (from 1 to n) the records belonging to each account number,
basically giving me the following:

(New Field)
Account # Security Number
1 1 1
1 2 2
1 3 3
2 1 1
2 2 2
3 3 3

I have looked up a few other discussions regarding numbering records,
and I have tried using some of the recommendations with no luck. I am
not te best at SQL, so trying to replicate some of the examples has
been a bit difficult for me.
In case, my table name is [Collateral_Details], and the headers for
account and security are the same as in my example.
If anyone could help me figure this out I would appreciate it very
much!

Thanks
Erick- Hide quoted text -

- Show quoted text -

.- Hide quoted text -

- Show quoted text -

.

 

[Erick C]

Karl -
Thank you so much, it works!
I did remove the Mod 3 from the second query, since it was still just
returning 1 through 3, but once it was out everything is perfect!
Thank you again for your help.

by looking at the account number for an indicator of when to start over at 1?
You don't.  You have to modify both queries.
   qryRowNum
SELECT [Collateral_Details].[Account #], [Collateral_Details].[Security],
(SELECT Count(*) FROM [Collateral_Details] AS [XX] WHERE  [XX].[Account#]
=[Collateral_Details].[Account #] AND [XX].[Account #] & [XX].[Security]<=
[Collateral_Details].[Account #] & [Collateral_Details].[Security])-1 AS Row
FROM [Collateral_Details]
GROUP BY [Collateral_Details].[Account #] & [Collateral_Details].[Security],
[Collateral_Details].[Security], [Collateral_Details].[Account #];

SELECT qryRowNum.[Account #], qryRowNum.[Security],  ([Row] Mod 3)+1  AS[New
Field]
FROM qryRowNum
ORDER BY qryRowNum.[Account #], Row;

--
Build a little, test a little.

Hi Karl -
Thank you for the response.  Unfortunately, I believe my poor example
may have given you an incorrect impression of what I was trying to
get.
The first query is definitely on the right track. [RowNum] has a
record count for each security.  So my [Collateral Details] table has
631 records, and I have 0 to 630 in the [Row] column.  I am having
problems with the second query, though.
My bad example gives the impression that I am looking for a sequence
of 3 securities for each account number, which I am not.  One account
number may have only 1 security, while the next account number may
have 25 securities. When I view the second query, the [New Field] that
is created shows a recurring sequence going from 1 to 3 and then
repeating, regardless of what the account number is.
How can I modify the second SQL statement so that it is counting
securities by looking at the account number for an indicator of when
to start over at 1?

Thank you again for all of your assistance.

Try these queries --
   qryRowNum
SELECT [Collateral_Details].[Account #], [Collateral_Details].[Security],  
(SELECT Count(*) FROM [Collateral_Details] AS [XX] WHERE  [XX].[Account #] &
[XX].[Security]<= [Collateral_Details].[Account #] &
[Collateral_Details].[Security])-1 AS Row
FROM [Collateral_Details]
GROUP BY [Collateral_Details].[Account #] & [Collateral_Details].[Security],
[Collateral_Details].[Security], [Collateral_Details].[Account #];
SELECT qryRowNum.[Account #], qryRowNum.[Security],  ([Row] Mod 3)+1  AS[New
Field]
FROM qryRowNum
ORDER BY Row;
--
Build a little, test a little.
:
Hi Jeff -
I am sorry, let me clarify my situation a bit more for you.
Currently, I am making a database that will be importing security
information.  This imformation will go into a report that I made,
basically a receipt.  It will print out 3 "receipts" per page, each
receipt will be mailed to the customer.
Given the small space on the receipt, I can only fit 8 records intomy
security information section.  Some customers have quite a few
securities, up to 50 or so.
I am planning on using a query to count how many records show up for
each customer account number in the Collateral Details table and
create a new table that includes the number of securities.  I canthen
use this count of securities to identify what customers need to have
more than one receipt.  (Ex. customer has 15 securities under their
account number, he needs 2 receipts.  One receipt has 8 records of
security information, and the other has the remaining 7 records of
security information.)
With the record count I can then create a field for the number of
receipts that a customer needs, this way I can get a customer with
multiple records to print out all of their receipts together one after
another, rather than having a separate report for customers with a lot
of records to be printed.
I hope my description clarifies my situation a bit more for you.  If
not, please let me know and I will try to provide any further
information.
Erick
You've described "how" you are trying to solve some issue, but not "what"
that issue is.
What will having the new field/number allow you to do?
And given your example, how is that field/number any different than the
Security number?
More info, please...
Regards
Jeff Boyce
Microsoft Access MVP
--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.
Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.
You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi everyone -
I am hoping someone can help me out with a problem that I am having..
I have a table that contains customer account information, basically
what securities they own.  I am trying to create a query thatwill
number (from 1 to n) the records belonging to each account number,
basically giving me the following:
                                     (New Field)
Account #      Security       Number
    1                    1                1
    1                    2                2
    1                    3                3
    2                    1                1
    2                    2                2
    3                    3                3
I have looked up a few other discussions regarding numbering records,
and I have tried using some of the recommendations with no luck..  I am
not te best at SQL, so trying to replicate some of the exampleshas
been a bit difficult for me.
In case, my table name is [Collateral_Details], and the headersfor
account and security are the same as in my example.
If anyone could help me figure this out I would appreciate it very
much!
Thanks
Erick- Hide quoted text -
- Show quoted text -
.- Hide quoted text -
- Show quoted text -

.- Hide quoted text -

- Show quoted text -