DCount() to rank scores


G

Guest

I have a update query that uses the DCount function to rank the scores for an
athletic event. It parses one table (ENTRIES) for the scores, Event ID, and
AgeGroup which it uses to determine the scope of the ranking process. It has
worked for years but now, due to changes in the scoring rules I need to store
the scores and ranks in a different(new) table (SCORES) than the event ID and
age group. I could save event ID and age group in both tables but that is
counter to a well designed database.

the function that has worked for years is:
IIf([Entries].[old score]>0,1+DCount("*","Entries","[old score]>" & [old
score] & " And [Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

I have ried to change it to :
IIf([Scores].[Score]>0,1+DCount("*","Scores","[Score]>" & [Score] & " And
[Entries].[Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Entries].[Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

Now I get the error "...Access can't find the name 'Entries.Fee ID' you
entered in and expression" I get the error for each record that should be
updated.

HELP I can't figure this out but there must be an alternative to what I
have tried
 
Ad

Advertisements

T

tina

IIf([Scores].[Score]>0,1+DCount("*","Scores","[Score]>" & [Score] & " And
[Entries].[Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Entries].[Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

you're trying to work with two different tables in the domain function - but
the domain is Scores, not Entries. what value are you trying to pull from
Entries that will restrict the records returned from the Scores domain?

hth
 
G

Guest

The values in 'Event ID' and 'Age Group' will limit which scores are reaked.
are you saying that a domain function is limited to only one table?
--
Steve S


tina said:
IIf([Scores].[Score]>0,1+DCount("*","Scores","[Score]>" & [Score] & " And
[Entries].[Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Entries].[Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

you're trying to work with two different tables in the domain function - but
the domain is Scores, not Entries. what value are you trying to pull from
Entries that will restrict the records returned from the Scores domain?

hth


Steve S said:
I have a update query that uses the DCount function to rank the scores for an
athletic event. It parses one table (ENTRIES) for the scores, Event ID, and
AgeGroup which it uses to determine the scope of the ranking process. It has
worked for years but now, due to changes in the scoring rules I need to store
the scores and ranks in a different(new) table (SCORES) than the event ID and
age group. I could save event ID and age group in both tables but that is
counter to a well designed database.

the function that has worked for years is:
IIf([Entries].[old score]>0,1+DCount("*","Entries","[old score]>" & [old
score] & " And [Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

I have ried to change it to :
IIf([Scores].[Score]>0,1+DCount("*","Scores","[Score]>" & [Score] & " And
[Entries].[Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Entries].[Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

Now I get the error "...Access can't find the name 'Entries.Fee ID' you
entered in and expression" I get the error for each record that should be
updated.

HELP I can't figure this out but there must be an alternative to what I
have tried
 
T

tina

are you saying that a domain function is limited to only one table?

the criteria in a domain function can only refer to fields in the named
domain (the second argument in the function). if the named domain is a
table, then only that table's fields may be referenced. you can also name a
query as the domain in a domain function, whether the query be single- or
multi-table.
The values in 'Event ID' and 'Age Group' will limit which scores are
reaked.

well, only if those values can be linked to fields in the Scores table. if a
relationship exists between the Entries and Scores tables, suggest you write
a query linking the two tables. (you may even be able to write the criteria
into the query, as well). then run the domain function on the query, rather
than the Scores table.

hth


Steve S said:
The values in 'Event ID' and 'Age Group' will limit which scores are reaked.
are you saying that a domain function is limited to only one table?
--
Steve S


tina said:
IIf([Scores].[Score]>0,1+DCount("*","Scores","[Score]>" & [Score] & " And
[Entries].[Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Entries].[Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

you're trying to work with two different tables in the domain function - but
the domain is Scores, not Entries. what value are you trying to pull from
Entries that will restrict the records returned from the Scores domain?

hth


Steve S said:
I have a update query that uses the DCount function to rank the scores
for
an
athletic event. It parses one table (ENTRIES) for the scores, Event
ID,
and
AgeGroup which it uses to determine the scope of the ranking process.
It
has
worked for years but now, due to changes in the scoring rules I need
to
store
the scores and ranks in a different(new) table (SCORES) than the event
ID
and
age group. I could save event ID and age group in both tables but that is
counter to a well designed database.

the function that has worked for years is:
IIf([Entries].[old score]>0,1+DCount("*","Entries","[old score]>" & [old
score] & " And [Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

I have ried to change it to :
IIf([Scores].[Score]>0,1+DCount("*","Scores","[Score]>" & [Score] & " And
[Entries].[Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Entries].[Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

Now I get the error "...Access can't find the name 'Entries.Fee ID' you
entered in and expression" I get the error for each record that should be
updated.

HELP I can't figure this out but there must be an alternative to what I
have tried
 
G

Guest

thanks much. I suspected this was the problem but hoped there was a way to
rank the scored in one query. that is not the case so I will begin work on
the initial linking query.

thanks again
--
Steve S


tina said:
are you saying that a domain function is limited to only one table?

the criteria in a domain function can only refer to fields in the named
domain (the second argument in the function). if the named domain is a
table, then only that table's fields may be referenced. you can also name a
query as the domain in a domain function, whether the query be single- or
multi-table.
The values in 'Event ID' and 'Age Group' will limit which scores are
reaked.

well, only if those values can be linked to fields in the Scores table. if a
relationship exists between the Entries and Scores tables, suggest you write
a query linking the two tables. (you may even be able to write the criteria
into the query, as well). then run the domain function on the query, rather
than the Scores table.

hth


Steve S said:
The values in 'Event ID' and 'Age Group' will limit which scores are reaked.
are you saying that a domain function is limited to only one table?
--
Steve S


tina said:
IIf([Scores].[Score]>0,1+DCount("*","Scores","[Score]>" & [Score] & " And
[Entries].[Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Entries].[Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

you're trying to work with two different tables in the domain function - but
the domain is Scores, not Entries. what value are you trying to pull from
Entries that will restrict the records returned from the Scores domain?

hth


I have a update query that uses the DCount function to rank the scores for
an
athletic event. It parses one table (ENTRIES) for the scores, Event ID,
and
AgeGroup which it uses to determine the scope of the ranking process. It
has
worked for years but now, due to changes in the scoring rules I need to
store
the scores and ranks in a different(new) table (SCORES) than the event ID
and
age group. I could save event ID and age group in both tables but that is
counter to a well designed database.

the function that has worked for years is:
IIf([Entries].[old score]>0,1+DCount("*","Entries","[old score]>" & [old
score] & " And [Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

I have ried to change it to :
IIf([Scores].[Score]>0,1+DCount("*","Scores","[Score]>" & [Score] & " And
[Entries].[Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Entries].[Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

Now I get the error "...Access can't find the name 'Entries.Fee ID' you
entered in and expression" I get the error for each record that should be
updated.

HELP I can't figure this out but there must be an alternative to what I
have tried
 
Ad

Advertisements

T

tina

you're welcome :)


Steve S said:
thanks much. I suspected this was the problem but hoped there was a way to
rank the scored in one query. that is not the case so I will begin work on
the initial linking query.

thanks again
--
Steve S


tina said:
are you saying that a domain function is limited to only one table?

the criteria in a domain function can only refer to fields in the named
domain (the second argument in the function). if the named domain is a
table, then only that table's fields may be referenced. you can also name a
query as the domain in a domain function, whether the query be single- or
multi-table.
The values in 'Event ID' and 'Age Group' will limit which scores are
reaked.

well, only if those values can be linked to fields in the Scores table. if a
relationship exists between the Entries and Scores tables, suggest you write
a query linking the two tables. (you may even be able to write the criteria
into the query, as well). then run the domain function on the query, rather
than the Scores table.

hth


Steve S said:
The values in 'Event ID' and 'Age Group' will limit which scores are reaked.
are you saying that a domain function is limited to only one table?
--
Steve S


:

IIf([Scores].[Score]>0,1+DCount("*","Scores","[Score]>" & [Score] &
"
And
[Entries].[Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Entries].[Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

you're trying to work with two different tables in the domain
function -
but
the domain is Scores, not Entries. what value are you trying to pull from
Entries that will restrict the records returned from the Scores domain?

hth


I have a update query that uses the DCount function to rank the
scores
for
an
athletic event. It parses one table (ENTRIES) for the scores,
Event
ID,
and
AgeGroup which it uses to determine the scope of the ranking
process.
It
has
worked for years but now, due to changes in the scoring rules I
need
to
store
the scores and ranks in a different(new) table (SCORES) than the
event
ID
and
age group. I could save event ID and age group in both tables but that is
counter to a well designed database.

the function that has worked for years is:
IIf([Entries].[old score]>0,1+DCount("*","Entries","[old score]>"
&
[old
score] & " And [Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

I have ried to change it to :
IIf([Scores].[Score]>0,1+DCount("*","Scores","[Score]>" & [Score]
& "
And
[Entries].[Fee ID]=[Forms]![Record Scores]![Fee ID] AND [Entries].[Age
Group]=[Forms]![Record Scores]![Age Group]"),Null)

Now I get the error "...Access can't find the name 'Entries.Fee
ID'
you
entered in and expression" I get the error for each record that should be
updated.

HELP I can't figure this out but there must be an alternative to
what
I
have tried
 
Ad

Advertisements


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

Top