sorting a Dcount field

G

Guest

Hi,

I have a table with about 1,800 records where each record is asigned a
unique ID number. I created a query and calculated a new field using Dcount :

sequence: DCount("ID","tbl_data","ID <=" & [tbl_data]![ID])

This works fine and numbers all the records from 1 through to 1817, which
is what I wanted. My problem is that even though I specify that the records
should be sorted in ascending order using the Name field in the data table,
the new calculated field 'Sequence' isn't numbered in ascending order. It is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
 
D

Duane Hookom

Wouldn't sorting on the Sequence be the same as sorting on the ID? What does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other object since every
object has a name property.
 
G

Guest

No sorting on the ID wouldn’t be the same as sorting on the Sequence as the
ID is not listed in numerical order when I sort the Name alphabetically. I
created the Sequence field to number the records, which have been sorted
alphabetically using the Name field, from 1 onwards but the Sequence field is
not sorted in ascending order.

I used 'Name' in my question but it is actually School_Name.


Duane Hookom said:
Wouldn't sorting on the Sequence be the same as sorting on the ID? What does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other object since every
object has a name property.

--
Duane Hookom
MS Access MVP
--

Gina said:
Hi,

I have a table with about 1,800 records where each record is asigned a
unique ID number. I created a query and calculated a new field using
Dcount :

sequence: DCount("ID","tbl_data","ID <=" & [tbl_data]![ID])

This works fine and numbers all the records from 1 through to 1817, which
is what I wanted. My problem is that even though I specify that the
records
should be sorted in ascending order using the Name field in the data
table,
the new calculated field 'Sequence' isn't numbered in ascending order. It
is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
Click to expand...
Click to expand...
 
D

Duane Hookom

Then don't use ID in the DCount. Use the field you want to sort on:

sequence: DCount("ID","tbl_data","[School_Name] <=""" & [School_Name] &
"""")


--
Duane Hookom
MS Access MVP
--

Gina said:
No sorting on the ID wouldn't be the same as sorting on the Sequence as
the
ID is not listed in numerical order when I sort the Name alphabetically.
I
created the Sequence field to number the records, which have been sorted
alphabetically using the Name field, from 1 onwards but the Sequence field
is
not sorted in ascending order.

I used 'Name' in my question but it is actually School_Name.


Duane Hookom said:
Wouldn't sorting on the Sequence be the same as sorting on the ID? What
does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other object since
every
object has a name property.

--
Duane Hookom
MS Access MVP
--

Gina said:
Hi,

I have a table with about 1,800 records where each record is asigned a
unique ID number. I created a query and calculated a new field using
Dcount :

sequence: DCount("ID","tbl_data","ID <=" & [tbl_data]![ID])

This works fine and numbers all the records from 1 through to 1817,
which
is what I wanted. My problem is that even though I specify that the
records
should be sorted in ascending order using the Name field in the data
table,
the new calculated field 'Sequence' isn't numbered in ascending order.
It
is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I tried what you suggested but the School names aren't unique so the sequence
results are something like:

Sequence
1
2
3
7
7
7
7
8

Where the school name is the same the sequence number is the same.


Duane Hookom said:
Then don't use ID in the DCount. Use the field you want to sort on:

sequence: DCount("ID","tbl_data","[School_Name] <=""" & [School_Name] &
"""")


--
Duane Hookom
MS Access MVP
--

Gina said:
No sorting on the ID wouldn't be the same as sorting on the Sequence as
the
ID is not listed in numerical order when I sort the Name alphabetically.
I
created the Sequence field to number the records, which have been sorted
alphabetically using the Name field, from 1 onwards but the Sequence field
is
not sorted in ascending order.

I used 'Name' in my question but it is actually School_Name.


Duane Hookom said:
Wouldn't sorting on the Sequence be the same as sorting on the ID? What
does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other object since
every
object has a name property.

--
Duane Hookom
MS Access MVP
--

Hi,

I have a table with about 1,800 records where each record is asigned a
unique ID number. I created a query and calculated a new field using
Dcount :

sequence: DCount("ID","tbl_data","ID <=" & [tbl_data]![ID])

This works fine and numbers all the records from 1 through to 1817,
which
is what I wanted. My problem is that even though I specify that the
records
should be sorted in ascending order using the Name field in the data
table,
the new calculated field 'Sequence' isn't numbered in ascending order.
It
is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

That's what I would expect. You would need to add something unique into the
where clause of DCount().

BTW: if this is for a report, you would be better off using a running sum in
a report text box.

--
Duane Hookom
MS Access MVP
--

Gina said:
I tried what you suggested but the School names aren't unique so the
sequence
results are something like:

Sequence
1
2
3
7
7
7
7
8

Where the school name is the same the sequence number is the same.


Duane Hookom said:
Then don't use ID in the DCount. Use the field you want to sort on:

sequence: DCount("ID","tbl_data","[School_Name] <=""" & [School_Name] &
"""")


--
Duane Hookom
MS Access MVP
--

Gina said:
No sorting on the ID wouldn't be the same as sorting on the Sequence as
the
ID is not listed in numerical order when I sort the Name
alphabetically.
I
created the Sequence field to number the records, which have been
sorted
alphabetically using the Name field, from 1 onwards but the Sequence
field
is
not sorted in ascending order.

I used 'Name' in my question but it is actually School_Name.


:

Wouldn't sorting on the Sequence be the same as sorting on the ID?
What
does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other object since
every
object has a name property.

--
Duane Hookom
MS Access MVP
--

Hi,

I have a table with about 1,800 records where each record is asigned
a
unique ID number. I created a query and calculated a new field
using
Dcount :

sequence: DCount("ID","tbl_data","ID <=" & [tbl_data]![ID])

This works fine and numbers all the records from 1 through to 1817,
which
is what I wanted. My problem is that even though I specify that the
records
should be sorted in ascending order using the Name field in the data
table,
the new calculated field 'Sequence' isn't numbered in ascending
order.
It
is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

No, this isn't for a report. The query data will be exported into Excel.

Duane Hookom said:
That's what I would expect. You would need to add something unique into the
where clause of DCount().

BTW: if this is for a report, you would be better off using a running sum in
a report text box.

--
Duane Hookom
MS Access MVP
--

Gina said:
I tried what you suggested but the School names aren't unique so the
sequence
results are something like:

Sequence
1
2
3
7
7
7
7
8

Where the school name is the same the sequence number is the same.


Duane Hookom said:
Then don't use ID in the DCount. Use the field you want to sort on:

sequence: DCount("ID","tbl_data","[School_Name] <=""" & [School_Name] &
"""")


--
Duane Hookom
MS Access MVP
--

No sorting on the ID wouldn't be the same as sorting on the Sequence as
the
ID is not listed in numerical order when I sort the Name
alphabetically.
I
created the Sequence field to number the records, which have been
sorted
alphabetically using the Name field, from 1 onwards but the Sequence
field
is
not sorted in ascending order.

I used 'Name' in my question but it is actually School_Name.


:

Wouldn't sorting on the Sequence be the same as sorting on the ID?
What
does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other object since
every
object has a name property.

--
Duane Hookom
MS Access MVP
--

Hi,

I have a table with about 1,800 records where each record is asigned
a
unique ID number. I created a query and calculated a new field
using
Dcount :

sequence: DCount("ID","tbl_data","ID <=" & [tbl_data]![ID])

This works fine and numbers all the records from 1 through to 1817,
which
is what I wanted. My problem is that even though I specify that the
records
should be sorted in ascending order using the Name field in the data
table,
the new calculated field 'Sequence' isn't numbered in ascending
order.
It
is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Did you figure out how to add some uniqueness to the where clause or are you
still looking for some help? You could combine your original where clause
with the school name where clause to make it unique.

--
Duane Hookom
MS Access MVP
--

Gina said:
No, this isn't for a report. The query data will be exported into Excel.

Duane Hookom said:
That's what I would expect. You would need to add something unique into
the
where clause of DCount().

BTW: if this is for a report, you would be better off using a running sum
in
a report text box.

--
Duane Hookom
MS Access MVP
--

Gina said:
I tried what you suggested but the School names aren't unique so the
sequence
results are something like:

Sequence
1
2
3
7
7
7
7
8

Where the school name is the same the sequence number is the same.


:

Then don't use ID in the DCount. Use the field you want to sort on:

sequence: DCount("ID","tbl_data","[School_Name] <=""" & [School_Name]
&
"""")


--
Duane Hookom
MS Access MVP
--

No sorting on the ID wouldn't be the same as sorting on the Sequence
as
the
ID is not listed in numerical order when I sort the Name
alphabetically.
I
created the Sequence field to number the records, which have been
sorted
alphabetically using the Name field, from 1 onwards but the Sequence
field
is
not sorted in ascending order.

I used 'Name' in my question but it is actually School_Name.


:

Wouldn't sorting on the Sequence be the same as sorting on the ID?
What
does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other object
since
every
object has a name property.

--
Duane Hookom
MS Access MVP
--

Hi,

I have a table with about 1,800 records where each record is
asigned
a
unique ID number. I created a query and calculated a new field
using
Dcount :

sequence: DCount("ID","tbl_data","ID <=" & [tbl_data]![ID])

This works fine and numbers all the records from 1 through to
1817,
which
is what I wanted. My problem is that even though I specify that
the
records
should be sorted in ascending order using the Name field in the
data
table,
the new calculated field 'Sequence' isn't numbered in ascending
order.
It
is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

The ID uniquely identifies each school, I thought that would be enough. I'm
still stuck. When you say combine do you mean concatenate the ID and
school_name field and then Dcount on that or did you mean:

DCount("ID","tbl_data","ID <=" & [tbl_data]![ID] AND "[School_Name] <=""" &
[School_Name] &"""")



Duane Hookom said:
Did you figure out how to add some uniqueness to the where clause or are you
still looking for some help? You could combine your original where clause
with the school name where clause to make it unique.

--
Duane Hookom
MS Access MVP
--

Gina said:
No, this isn't for a report. The query data will be exported into Excel.

Duane Hookom said:
That's what I would expect. You would need to add something unique into
the
where clause of DCount().

BTW: if this is for a report, you would be better off using a running sum
in
a report text box.

--
Duane Hookom
MS Access MVP
--

I tried what you suggested but the School names aren't unique so the
sequence
results are something like:

Sequence
1
2
3
7
7
7
7
8

Where the school name is the same the sequence number is the same.


:

Then don't use ID in the DCount. Use the field you want to sort on:

sequence: DCount("ID","tbl_data","[School_Name] <=""" & [School_Name]
&
"""")


--
Duane Hookom
MS Access MVP
--

No sorting on the ID wouldn't be the same as sorting on the Sequence
as
the
ID is not listed in numerical order when I sort the Name
alphabetically.
I
created the Sequence field to number the records, which have been
sorted
alphabetically using the Name field, from 1 onwards but the Sequence
field
is
not sorted in ascending order.

I used 'Name' in my question but it is actually School_Name.


:

Wouldn't sorting on the Sequence be the same as sorting on the ID?
What
does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other object
since
every
object has a name property.

--
Duane Hookom
MS Access MVP
--

Hi,

I have a table with about 1,800 records where each record is
asigned
a
unique ID number. I created a query and calculated a new field
using
Dcount :

sequence: DCount("ID","tbl_data","ID <=" & [tbl_data]![ID])

This works fine and numbers all the records from 1 through to
1817,
which
is what I wanted. My problem is that even though I specify that
the
records
should be sorted in ascending order using the Name field in the
data
table,
the new calculated field 'Sequence' isn't numbered in ascending
order.
It
is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Close, try this:
DCount("ID","tbl_data","ID <=" & [tbl_data]![ID] & " AND [School_Name] <="""
& [School_Name] &"""")

--
Duane Hookom
MS Access MVP
--

Gina said:
The ID uniquely identifies each school, I thought that would be enough.
I'm
still stuck. When you say combine do you mean concatenate the ID and
school_name field and then Dcount on that or did you mean:

DCount("ID","tbl_data","ID <=" & [tbl_data]![ID] AND "[School_Name] <="""
&
[School_Name] &"""")



Duane Hookom said:
Did you figure out how to add some uniqueness to the where clause or are
you
still looking for some help? You could combine your original where clause
with the school name where clause to make it unique.

--
Duane Hookom
MS Access MVP
--

Gina said:
No, this isn't for a report. The query data will be exported into
Excel.

:

That's what I would expect. You would need to add something unique
into
the
where clause of DCount().

BTW: if this is for a report, you would be better off using a running
sum
in
a report text box.

--
Duane Hookom
MS Access MVP
--

I tried what you suggested but the School names aren't unique so the
sequence
results are something like:

Sequence
1
2
3
7
7
7
7
8

Where the school name is the same the sequence number is the same.


:

Then don't use ID in the DCount. Use the field you want to sort on:

sequence: DCount("ID","tbl_data","[School_Name] <=""" &
[School_Name]
&
"""")


--
Duane Hookom
MS Access MVP
--

No sorting on the ID wouldn't be the same as sorting on the
Sequence
as
the
ID is not listed in numerical order when I sort the Name
alphabetically.
I
created the Sequence field to number the records, which have been
sorted
alphabetically using the Name field, from 1 onwards but the
Sequence
field
is
not sorted in ascending order.

I used 'Name' in my question but it is actually School_Name.


:

Wouldn't sorting on the Sequence be the same as sorting on the
ID?
What
does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other object
since
every
object has a name property.

--
Duane Hookom
MS Access MVP
--

Hi,

I have a table with about 1,800 records where each record is
asigned
a
unique ID number. I created a query and calculated a new
field
using
Dcount :

sequence: DCount("ID","tbl_data","ID <=" & [tbl_data]![ID])

This works fine and numbers all the records from 1 through to
1817,
which
is what I wanted. My problem is that even though I specify
that
the
records
should be sorted in ascending order using the Name field in
the
data
table,
the new calculated field 'Sequence' isn't numbered in
ascending
order.
It
is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That didn't work either. The sequence field came out with duplicate numbers.
I also tried this, but came up with the same results.

DCount("School_Name","tbl_data","ID <=" & [tbl_data]![ID] & " AND
[Schoo_Name] <=""" & [School_Name] & """")


Duane Hookom said:
Close, try this:
DCount("ID","tbl_data","ID <=" & [tbl_data]![ID] & " AND [School_Name] <="""
& [School_Name] &"""")

--
Duane Hookom
MS Access MVP
--

Gina said:
The ID uniquely identifies each school, I thought that would be enough.
I'm
still stuck. When you say combine do you mean concatenate the ID and
school_name field and then Dcount on that or did you mean:

DCount("ID","tbl_data","ID <=" & [tbl_data]![ID] AND "[School_Name] <="""
&
[School_Name] &"""")



Duane Hookom said:
Did you figure out how to add some uniqueness to the where clause or are
you
still looking for some help? You could combine your original where clause
with the school name where clause to make it unique.

--
Duane Hookom
MS Access MVP
--

No, this isn't for a report. The query data will be exported into
Excel.

:

That's what I would expect. You would need to add something unique
into
the
where clause of DCount().

BTW: if this is for a report, you would be better off using a running
sum
in
a report text box.

--
Duane Hookom
MS Access MVP
--

I tried what you suggested but the School names aren't unique so the
sequence
results are something like:

Sequence
1
2
3
7
7
7
7
8

Where the school name is the same the sequence number is the same.


:

Then don't use ID in the DCount. Use the field you want to sort on:

sequence: DCount("ID","tbl_data","[School_Name] <=""" &
[School_Name]
&
"""")


--
Duane Hookom
MS Access MVP
--

No sorting on the ID wouldn't be the same as sorting on the
Sequence
as
the
ID is not listed in numerical order when I sort the Name
alphabetically.
I
created the Sequence field to number the records, which have been
sorted
alphabetically using the Name field, from 1 onwards but the
Sequence
field
is
not sorted in ascending order.

I used 'Name' in my question but it is actually School_Name.


:

Wouldn't sorting on the Sequence be the same as sorting on the
ID?
What
does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other object
since
every
object has a name property.

--
Duane Hookom
MS Access MVP
--

Hi,

I have a table with about 1,800 records where each record is
asigned
a
unique ID number. I created a query and calculated a new
field
using
Dcount :

sequence: DCount("ID","tbl_data","ID <=" & [tbl_data]![ID])

This works fine and numbers all the records from 1 through to
1817,
which
is what I wanted. My problem is that even though I specify
that
the
records
should be sorted in ascending order using the Name field in
the
data
table,
the new calculated field 'Sequence' isn't numbered in
ascending
order.
It
is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Is the ID field unique it tbl_data? If not, what field is the primary key?
I assume your [Schoo_Name] is a typo.

--
Duane Hookom
MS Access MVP
--

Gina said:
That didn't work either. The sequence field came out with duplicate
numbers.
I also tried this, but came up with the same results.

DCount("School_Name","tbl_data","ID <=" & [tbl_data]![ID] & " AND
[Schoo_Name] <=""" & [School_Name] & """")


Duane Hookom said:
Close, try this:
DCount("ID","tbl_data","ID <=" & [tbl_data]![ID] & " AND [School_Name]
<="""
& [School_Name] &"""")

--
Duane Hookom
MS Access MVP
--

Gina said:
The ID uniquely identifies each school, I thought that would be enough.
I'm
still stuck. When you say combine do you mean concatenate the ID and
school_name field and then Dcount on that or did you mean:

DCount("ID","tbl_data","ID <=" & [tbl_data]![ID] AND "[School_Name]
<="""
&
[School_Name] &"""")



:

Did you figure out how to add some uniqueness to the where clause or
are
you
still looking for some help? You could combine your original where
clause
with the school name where clause to make it unique.

--
Duane Hookom
MS Access MVP
--

No, this isn't for a report. The query data will be exported into
Excel.

:

That's what I would expect. You would need to add something unique
into
the
where clause of DCount().

BTW: if this is for a report, you would be better off using a
running
sum
in
a report text box.

--
Duane Hookom
MS Access MVP
--

I tried what you suggested but the School names aren't unique so
the
sequence
results are something like:

Sequence
1
2
3
7
7
7
7
8

Where the school name is the same the sequence number is the
same.


:

Then don't use ID in the DCount. Use the field you want to sort
on:

sequence: DCount("ID","tbl_data","[School_Name] <=""" &
[School_Name]
&
"""")


--
Duane Hookom
MS Access MVP
--

No sorting on the ID wouldn't be the same as sorting on the
Sequence
as
the
ID is not listed in numerical order when I sort the Name
alphabetically.
I
created the Sequence field to number the records, which have
been
sorted
alphabetically using the Name field, from 1 onwards but the
Sequence
field
is
not sorted in ascending order.

I used 'Name' in my question but it is actually School_Name.


:

Wouldn't sorting on the Sequence be the same as sorting on
the
ID?
What
does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other
object
since
every
object has a name property.

--
Duane Hookom
MS Access MVP
--

Hi,

I have a table with about 1,800 records where each record
is
asigned
a
unique ID number. I created a query and calculated a new
field
using
Dcount :

sequence: DCount("ID","tbl_data","ID <=" & [tbl_data]![ID])

This works fine and numbers all the records from 1 through
to
1817,
which
is what I wanted. My problem is that even though I specify
that
the
records
should be sorted in ascending order using the Name field in
the
data
table,
the new calculated field 'Sequence' isn't numbered in
ascending
order.
It
is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yep, it was meant to be [School_Name]. The ID field is the primary key.

Duane Hookom said:
Is the ID field unique it tbl_data? If not, what field is the primary key?
I assume your [Schoo_Name] is a typo.

--
Duane Hookom
MS Access MVP
--

Gina said:
That didn't work either. The sequence field came out with duplicate
numbers.
I also tried this, but came up with the same results.

DCount("School_Name","tbl_data","ID <=" & [tbl_data]![ID] & " AND
[Schoo_Name] <=""" & [School_Name] & """")


Duane Hookom said:
Close, try this:
DCount("ID","tbl_data","ID <=" & [tbl_data]![ID] & " AND [School_Name]
<="""
& [School_Name] &"""")

--
Duane Hookom
MS Access MVP
--

The ID uniquely identifies each school, I thought that would be enough.
I'm
still stuck. When you say combine do you mean concatenate the ID and
school_name field and then Dcount on that or did you mean:

DCount("ID","tbl_data","ID <=" & [tbl_data]![ID] AND "[School_Name]
<="""
&
[School_Name] &"""")



:

Did you figure out how to add some uniqueness to the where clause or
are
you
still looking for some help? You could combine your original where
clause
with the school name where clause to make it unique.

--
Duane Hookom
MS Access MVP
--

No, this isn't for a report. The query data will be exported into
Excel.

:

That's what I would expect. You would need to add something unique
into
the
where clause of DCount().

BTW: if this is for a report, you would be better off using a
running
sum
in
a report text box.

--
Duane Hookom
MS Access MVP
--

I tried what you suggested but the School names aren't unique so
the
sequence
results are something like:

Sequence
1
2
3
7
7
7
7
8

Where the school name is the same the sequence number is the
same.


:

Then don't use ID in the DCount. Use the field you want to sort
on:

sequence: DCount("ID","tbl_data","[School_Name] <=""" &
[School_Name]
&
"""")


--
Duane Hookom
MS Access MVP
--

No sorting on the ID wouldn't be the same as sorting on the
Sequence
as
the
ID is not listed in numerical order when I sort the Name
alphabetically.
I
created the Sequence field to number the records, which have
been
sorted
alphabetically using the Name field, from 1 onwards but the
Sequence
field
is
not sorted in ascending order.

I used 'Name' in my question but it is actually School_Name.


:

Wouldn't sorting on the Sequence be the same as sorting on
the
ID?
What
does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other
object
since
every
object has a name property.

--
Duane Hookom
MS Access MVP
--

Hi,

I have a table with about 1,800 records where each record
is
asigned
a
unique ID number. I created a query and calculated a new
field
using
Dcount :

sequence: DCount("ID","tbl_data","ID <=" & [tbl_data]![ID])

This works fine and numbers all the records from 1 through
to
1817,
which
is what I wanted. My problem is that even though I specify
that
the
records
should be sorted in ascending order using the Name field in
the
data
table,
the new calculated field 'Sequence' isn't numbered in
ascending
order.
It
is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Try this:
Val(DCount("ID","tbl_data","[School_Name] & ID <=""" & [School_Name] & [ID]
& """"))

--
Duane Hookom
MS Access MVP
--

Gina said:
Yep, it was meant to be [School_Name]. The ID field is the primary key.

Duane Hookom said:
Is the ID field unique it tbl_data? If not, what field is the primary
key?
I assume your [Schoo_Name] is a typo.

--
Duane Hookom
MS Access MVP
--

Gina said:
That didn't work either. The sequence field came out with duplicate
numbers.
I also tried this, but came up with the same results.

DCount("School_Name","tbl_data","ID <=" & [tbl_data]![ID] & " AND
[Schoo_Name] <=""" & [School_Name] & """")


:

Close, try this:
DCount("ID","tbl_data","ID <=" & [tbl_data]![ID] & " AND [School_Name]
<="""
& [School_Name] &"""")

--
Duane Hookom
MS Access MVP
--

The ID uniquely identifies each school, I thought that would be
enough.
I'm
still stuck. When you say combine do you mean concatenate the ID
and
school_name field and then Dcount on that or did you mean:

DCount("ID","tbl_data","ID <=" & [tbl_data]![ID] AND "[School_Name]
<="""
&
[School_Name] &"""")



:

Did you figure out how to add some uniqueness to the where clause
or
are
you
still looking for some help? You could combine your original where
clause
with the school name where clause to make it unique.

--
Duane Hookom
MS Access MVP
--

No, this isn't for a report. The query data will be exported
into
Excel.

:

That's what I would expect. You would need to add something
unique
into
the
where clause of DCount().

BTW: if this is for a report, you would be better off using a
running
sum
in
a report text box.

--
Duane Hookom
MS Access MVP
--

I tried what you suggested but the School names aren't unique
so
the
sequence
results are something like:

Sequence
1
2
3
7
7
7
7
8

Where the school name is the same the sequence number is the
same.


:

Then don't use ID in the DCount. Use the field you want to
sort
on:

sequence: DCount("ID","tbl_data","[School_Name] <=""" &
[School_Name]
&
"""")


--
Duane Hookom
MS Access MVP
--

No sorting on the ID wouldn't be the same as sorting on the
Sequence
as
the
ID is not listed in numerical order when I sort the Name
alphabetically.
I
created the Sequence field to number the records, which
have
been
sorted
alphabetically using the Name field, from 1 onwards but the
Sequence
field
is
not sorted in ascending order.

I used 'Name' in my question but it is actually
School_Name.


:

Wouldn't sorting on the Sequence be the same as sorting on
the
ID?
What
does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other
object
since
every
object has a name property.

--
Duane Hookom
MS Access MVP
--

Hi,

I have a table with about 1,800 records where each
record
is
asigned
a
unique ID number. I created a query and calculated a
new
field
using
Dcount :

sequence: DCount("ID","tbl_data","ID <=" &
[tbl_data]![ID])

This works fine and numbers all the records from 1
through
to
1817,
which
is what I wanted. My problem is that even though I
specify
that
the
records
should be sorted in ascending order using the Name field
in
the
data
table,
the new calculated field 'Sequence' isn't numbered in
ascending
order.
It
is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I noticed that the Dcount field was a text field when I changed the query to
make a table and tried using Va( ) but it didn't quite fix my problem,
however what you gave me did!! Thanks so much, it has been a real headache.
I'm glad it is finally working.

Gina



Duane Hookom said:
Try this:
Val(DCount("ID","tbl_data","[School_Name] & ID <=""" & [School_Name] & [ID]
& """"))

--
Duane Hookom
MS Access MVP
--

Gina said:
Yep, it was meant to be [School_Name]. The ID field is the primary key.

Duane Hookom said:
Is the ID field unique it tbl_data? If not, what field is the primary
key?
I assume your [Schoo_Name] is a typo.

--
Duane Hookom
MS Access MVP
--

That didn't work either. The sequence field came out with duplicate
numbers.
I also tried this, but came up with the same results.

DCount("School_Name","tbl_data","ID <=" & [tbl_data]![ID] & " AND
[Schoo_Name] <=""" & [School_Name] & """")


:

Close, try this:
DCount("ID","tbl_data","ID <=" & [tbl_data]![ID] & " AND [School_Name]
<="""
& [School_Name] &"""")

--
Duane Hookom
MS Access MVP
--

The ID uniquely identifies each school, I thought that would be
enough.
I'm
still stuck. When you say combine do you mean concatenate the ID
and
school_name field and then Dcount on that or did you mean:

DCount("ID","tbl_data","ID <=" & [tbl_data]![ID] AND "[School_Name]
<="""
&
[School_Name] &"""")



:

Did you figure out how to add some uniqueness to the where clause
or
are
you
still looking for some help? You could combine your original where
clause
with the school name where clause to make it unique.

--
Duane Hookom
MS Access MVP
--

No, this isn't for a report. The query data will be exported
into
Excel.

:

That's what I would expect. You would need to add something
unique
into
the
where clause of DCount().

BTW: if this is for a report, you would be better off using a
running
sum
in
a report text box.

--
Duane Hookom
MS Access MVP
--

I tried what you suggested but the School names aren't unique
so
the
sequence
results are something like:

Sequence
1
2
3
7
7
7
7
8

Where the school name is the same the sequence number is the
same.


:

Then don't use ID in the DCount. Use the field you want to
sort
on:

sequence: DCount("ID","tbl_data","[School_Name] <=""" &
[School_Name]
&
"""")


--
Duane Hookom
MS Access MVP
--

No sorting on the ID wouldn't be the same as sorting on the
Sequence
as
the
ID is not listed in numerical order when I sort the Name
alphabetically.
I
created the Sequence field to number the records, which
have
been
sorted
alphabetically using the Name field, from 1 onwards but the
Sequence
field
is
not sorted in ascending order.

I used 'Name' in my question but it is actually
School_Name.


:

Wouldn't sorting on the Sequence be the same as sorting on
the
ID?
What
does
the Name field have to do with the Sequence field?

BTW: Name is not a good name to name a field or any other
object
since
every
object has a name property.

--
Duane Hookom
MS Access MVP
--

Hi,

I have a table with about 1,800 records where each
record
is
asigned
a
unique ID number. I created a query and calculated a
new
field
using
Dcount :

sequence: DCount("ID","tbl_data","ID <=" &
[tbl_data]![ID])

This works fine and numbers all the records from 1
through
to
1817,
which
is what I wanted. My problem is that even though I
specify
that
the
records
should be sorted in ascending order using the Name field
in
the
data
table,
the new calculated field 'Sequence' isn't numbered in
ascending
order.
It
is
all jumbled up.

Is there a way to sort the DCount field??

Thanks
Click to expand...
Click to expand...
Click to expand...
 

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

Access Dcount (multiple criteria) 3
Dcount returning no results!!! 0
DCount alwaysreturning 1 0
DCount and Sequence 4
DCount Not Working Properly 7
Access MS Access DCount function problem 0
Dcount Date time field 2
DCount() to rank scores 5

Top