DCount() to rank scores

[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

 

[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

 

[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

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

 

[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

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

 

[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

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

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

 

[tina]

you're welcome

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

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

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