a count field in a query?

G

Geoff Cox

Hello,

I have a query in which I would like to create a field which
increments by 1 for each record selected, so if there are 10 records
selected by the query, this column would show numbers 1 to 10.

Ideally I would like to have some text in front of each number, say
invoice1, invoice2 etc.

From a previous posting I realise that this is frowned upon but it
would be the simplest solution for my problem!

How can I do this?

Thanks

Geoff
 
J

John W. Vinson

Hello,

I have a query in which I would like to create a field which
increments by 1 for each record selected, so if there are 10 records
selected by the query, this column would show numbers 1 to 10.

Ideally I would like to have some text in front of each number, say
invoice1, invoice2 etc.

From a previous posting I realise that this is frowned upon but it
would be the simplest solution for my problem!
Click to expand...

If you want this for a Report, it's easy: put a textbox on the report with a
control source

=1

and set its Running Sum property to Over Group (or Over All as appropriate).

It's also possible in a query, although less efficient. You do noeed some
field in the query which is strictly in ascending order - an autonumber ID is
one possibility, a datestamp is another. I'll call it Sortfield, you would use
your own fieldname. In a vacant field cell type

=DCount("*", "[yourqueryname]", "[sortfield] <=" & [sortfield])


John W. Vinson [MVP]
 
G

Geoff Cox

If you want this for a Report, it's easy: put a textbox on the report with a
control source

=1

and set its Running Sum property to Over Group (or Over All as appropriate).

It's also possible in a query, although less efficient. You do noeed some
field in the query which is strictly in ascending order - an autonumber ID is
one possibility, a datestamp is another. I'll call it Sortfield, you would use
your own fieldname. In a vacant field cell type




John W. Vinson [MVP]
Click to expand...

Thanks John,

I have used

=DCount("*", "[renewal-end-dec-07]", "[ID] <=" & [ID])

and this does give 1 to 13 but a couple of things.

Could you explain how it works and is it possible to introduce some
text so that I get for example,

renewal_number_1 etc?

Cheers

Geoff
 
J

John W. Vinson

I have used

=DCount("*", "[renewal-end-dec-07]", "[ID] <=" & [ID])

and this does give 1 to 13 but a couple of things.

Could you explain how it works
Click to expand...

The DCount function (like all the Domain functions, DLookUp, DSum, etc.) takes
three arguments. The first is the field to be retrieved. In the special case
of DCount, you can use * to say "count all records, I'm not interested in any
particular field". The second argument is the name of a table or query from
which you wish to retrieve - in this case renewal-end-dec-07.

The third argument is a search criterion which specifies which records to
retrieve. In this case, you have a field named ID in renewal-end-dec-07 that
(I assume) has a different value for each record, and by which you're sorting
the records. The somewhat confusing criterion

"[ID] <=" & [ID]

looks at the ID value in *this current* record, and tells DCount to count up
all the records with an ID less than or equal to that value. For example, if
the current record in the query has ID value 1, it will find just that one
record; if you have ID values 1254 through 1263, and you're looking at the
record with 1263, it will count ten records.
and is it possible to introduce some
text so that I get for example, renewal_number_1 etc?
Click to expand...

DON'T confuse data PRESENTATION with data CALCULATION.

renewal_number_1 *is not a number*. It *is not a count*. It cannot be the
result of a mathematical calculation!

Just display the text, just like we were discussing in the other thread. The
way (well, one way) to do so is to use the Format property of the control in
which you display this count. If you set the Format property to

"renewal_number_#"

Access will treat the text renewal_number_ as just "wallpaper", displaying
that text string on every record; the # is a valid Format character meaning to
display the value as a number.


John W. Vinson [MVP]
 
G

Geoff Cox

the records. The somewhat confusing criterion

"[ID] <=" & [ID]

looks at the ID value in *this current* record, and tells DCount to count up
all the records with an ID less than or equal to that value. For example, if
the current record in the query has ID value 1, it will find just that one
record; if you have ID values 1254 through 1263, and you're looking at the
record with 1263, it will count ten records.
Click to expand...

John,

OK - I understand that now - thanks.
DON'T confuse data PRESENTATION with data CALCULATION.

renewal_number_1 *is not a number*. It *is not a count*. It cannot be the
result of a mathematical calculation!

Just display the text, just like we were discussing in the other thread. The
way (well, one way) to do so is to use the Format property of the control in
which you display this count. If you set the Format property to

"renewal_number_#"
Click to expand...

this I don't get! at the moment I create the invoice_number field in
the query using

invoice_number: DCount("*","[renewal-end-dec-07]","[ID] <=" & [ID])

and then I am using some mailing software which looks at this query
and sends out an email for each record in the query. At the moment the
emails have the numbers 1 to 13, there being 13 records found by the
query.

How do I get them to appear as renewal_number_1 etc? I don't
understand where I use the "renewal_number_#"!

Cheers

Geoff
 
J

John W. Vinson

and then I am using some mailing software which looks at this query
and sends out an email for each record in the query. At the moment the
emails have the numbers 1 to 13, there being 13 records found by the
query.
Click to expand...

Thanks. You didn't say that, and my telepathy wasn't working correctly. I
thought you wanted to display the value in a Form or Report, rather than
having it in a query datasheet, since that's how data is typically displayed.
How do I get them to appear as renewal_number_1 etc? I don't
understand where I use the "renewal_number_#"!
Click to expand...

Change my suggestion, then. In the calculated field type

ExpSeq: "renewal_number_" & DCount("*", <etcetera>)

The & operator concatenates the literal text string "renewal_number_" with the
result of the dcount expression.

John W. Vinson [MVP]
 
G

Geoff Cox

Thanks. You didn't say that, and my telepathy wasn't working correctly. I
thought you wanted to display the value in a Form or Report, rather than
having it in a query datasheet, since that's how data is typically displayed.


Change my suggestion, then. In the calculated field type

ExpSeq: "renewal_number_" & DCount("*", <etcetera>)

The & operator concatenates the literal text string "renewal_number_" with the
result of the dcount expression.

John W. Vinson [MVP]
Click to expand...

John,

Got it!!

I had gone to bed then thought I'd better tell you that I had tried
various format options wihout success!

The moral being always state the question clearly!

Many thanks.

Cheers

Geoff
 
G

Geoff Cox

Change my suggestion, then. In the calculated field type

ExpSeq: "renewal_number_" & DCount("*", <etcetera>)

The & operator concatenates the literal text string "renewal_number_" with the
result of the dcount expression.

John W. Vinson [MVP]
Click to expand...

John,

Is it possible to get the date involved in this?

ExpSeq: "renewal_number_"& date & DCount("*", <etcetera>)

doesn't work.

Ideally I would like to create

renewal_number_dec_07_1, renewal_number_jan_08_1, etc, then the query
would work OK for each month it is used.

Is this a step too far or does it require VBA code?

Cheers

Geoff
 
J

John W. Vinson

ExpSeq: "renewal_number_"& date & DCount("*", <etcetera>)

doesn't work.

Ideally I would like to create

renewal_number_dec_07_1, renewal_number_jan_08_1, etc, then the query
would work OK for each month it is used.

Is this a step too far or does it require VBA code?

Cheers
Click to expand...

"renewal_number_" & Format(Date(), "mmm-yy") & "_" & DCount(...

will put the current date's month and year in the string. Replace Date() with
the name of a date/time field in your table to get that date. Is 08 the year
or the day? You can use yy for the year, dd for the day in the Format()
function call.

John W. Vinson [MVP]
 
G

Geoff Cox

"renewal_number_" & Format(Date(), "mmm-yy") & "_" & DCount(...

will put the current date's month and year in the string. Replace Date() with
the name of a date/time field in your table to get that date. Is 08 the year
or the day? You can use yy for the year, dd for the day in the Format()
function call.

John W. Vinson [MVP]
Click to expand...

John,

Fine! Thanks yet again. Yes 08 was the year.

I just wonder where I might find this level of information re the use
of, for example, DCount, apart from taking up your time?!

I have just bought the Access 2003 Bible, all 1362 pages, but even it
doesn't go beyond a brief bit about DCount.

Any suggestions?

Cheers

Geoff
 
J

John W. Vinson

I just wonder where I might find this level of information re the use
of, for example, DCount, apart from taking up your time?!

I have just bought the Access 2003 Bible, all 1362 pages, but even it
doesn't go beyond a brief bit about DCount.

Any suggestions?
Click to expand...

Open the VBA editor (for example by pressing Ctrl-G).
Press the F1 key or select Help.
Search for the name of the function you want to learn about.
If need be, press the "related issues" link.

John W. Vinson [MVP]
 
J

James A. Fortune

Geoff said:
I just wonder where I might find this level of information re the use
of, for example, DCount, apart from taking up your time?!

I have just bought the Access 2003 Bible, all 1362 pages, but even it
doesn't go beyond a brief bit about DCount.

Any suggestions?

Cheers

Geoff
Click to expand...

Are there any discernable differences between:

DLookup("Count(*)", "tblShippers", "SID >= 1000")
DCount("*", "tblShippers", "SID >= 1000")

DLookup("Sum([InvoiceAmount])", "tblShippers")
DSum("[InvoiceAmount]", "tblShippers")

DLookup("Max([InvoiceAmount])", "tblShippers")
DMax("[InvoiceAmount]", "tblShippers")

DLookup("Min([InvoiceAmount])", "tblShippers")
DMin("[InvoiceAmount]", "tblShippers")

DLookup("Avg([InvoiceAmount])", "tblShippers")
DAvg("[InvoiceAmount]", "tblShippers")

DLookup("First([InvoiceAmount])", "tblShippers")
DFirst("[InvoiceAmount]", "tblShippers")

DLookup("Last([InvoiceAmount])", "tblShippers")
DLast("[InvoiceAmount]", "tblShippers")

DLookup("StDev([InvoiceAmount])", "tblShippers")
DStDev("[InvoiceAmount]", "tblShippers")

DLookup("StDevP([InvoiceAmount])", "tblShippers")
DStDevP("[InvoiceAmount]", "tblShippers")

DLookup("Var([InvoiceAmount])", "tblShippers")
DVar("[InvoiceAmount]", "tblShippers")

DLookup("VarP([InvoiceAmount])", "tblShippers")
DVarP("[InvoiceAmount]", "tblShippers")

?

If not, then the information about DLookup is enough. Unless, of
course, the book doesn't cover DLookup :).

James A. Fortune
[email protected]
 
Top