What's wrong with this query?!

P

PayeDoc

Hello All

I have:
SELECT practices.[prac name], DSum("[x confirmed]![ni ees and ers 1e]","[x
confirmed]","[x confirmed]![dname]=[dname] And [x confirmed]![month
number]>=140") AS total_ni,
DLookUp("[months]![number]","[months]","[months]![month name]=[staff bank
name]") AS mthnum, staffs.[staff bank name]
FROM practices INNER JOIN staffs ON practices.[prac name] = staffs.practice
WHERE (((practices.[prac name])=[Forms]![frm x main]![prac name]));

.... but for some unfathomable reason this errors with a message that access
can't find [staff bank name]. But [staff bank name] is definitely in table
[staffs], and in fact if I remove the field 'mthnum' the query runs fine -
and includes the values of [staff bank name] perfectly happily! I have tried
replacing [staff bank name] with [staffs]![staff bank name] in the field
'mthnum', but this makes no difference.

What I really want to do is use the value of 'mthnum' instead of the
currently hard-coded "140" in the field total_ni.

What's up?
Hope someone can help.

Many thanks
Leslie Isaacs
 
J

Jerry Whittle

Does this come close?

SELECT practices.[prac name],
DSum("[x confirmed]![ni ees and ers 1e]","[xconfirmed]","[x
confirmed]![dname]=[dname]
And [x confirmed]![monthnumber]>=140") AS total_ni,
[months]![number] AS mthnum,
staffs.[staff bank name]
FROM practices, staffs, months
WHERE [practices]![prac name]=[Forms]![frm x main]![prac name]
AND [months]![month name]= [staffs].[staff bank name]
AND [practices]![prac name] = [staffs].[practice];
 
J

John Spencer MVP

Just fixing the syntax, you probably end up with something like the following.

SELECT practices.[prac name]
, DSum("[ni ees and ers 1e]","[x confirmed]","dname=""" & [dname] & """ And
[month number]>=" & DLookUp("[number]","months","[month name]=""" & [staff
bank name] & """) ) AS total_ni,
DLookUp("[number]","months","[month name]=""" & [staff bank name] & """) AS mthnum
, staffs.[staff bank name]
FROM practices INNER JOIN staffs ON practices.[prac name] = staffs.practice
WHERE (((practices.[prac name])=[Forms]![frm x main]![prac name]));

You can probably make the query more efficient by including the other tables
in it and setting up the appropriate joins. BUT that might make query so it
cannot be updated. For instance, just adding the Months table

SELECT practices.[prac name]
, DSum("[ni ees and ers 1e]","[x confirmed]","dname=""" & [dname] & """ And
[month number]>=" & Months.Number ) AS total_ni,
Months.Number AS mthnum
, staffs.[staff bank name]
FROM (practices INNER JOIN staffs ON practices.[prac name] = staffs.practice)
LEFT JOIN [Months] on staffs.[Staff Bank Name]=[Months].[Month Name]
WHERE (((practices.[prac name])=[Forms]![frm x main]![prac name]));

You could probably turn this into an aggregate query, but I can't tell how
since I don't know the source table for the field named dName.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
M

MGFoster

PayeDoc said:
Hello All

I have:
SELECT practices.[prac name], DSum("[x confirmed]![ni ees and ers 1e]","[x
confirmed]","[x confirmed]![dname]=[dname] And [x confirmed]![month
number]>=140") AS total_ni,
DLookUp("[months]![number]","[months]","[months]![month name]=[staff bank
name]") AS mthnum, staffs.[staff bank name]
FROM practices INNER JOIN staffs ON practices.[prac name] = staffs.practice
WHERE (((practices.[prac name])=[Forms]![frm x main]![prac name]));

... but for some unfathomable reason this errors with a message that access
can't find [staff bank name]. But [staff bank name] is definitely in table
[staffs], and in fact if I remove the field 'mthnum' the query runs fine -
and includes the values of [staff bank name] perfectly happily! I have tried
replacing [staff bank name] with [staffs]![staff bank name] in the field
'mthnum', but this makes no difference.

What I really want to do is use the value of 'mthnum' instead of the
currently hard-coded "140" in the field total_ni.

What's up?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have some redundant stuff in the domain aggregate functions.
Sometimes, correcting syntax can cure other problems: e.g., you don't
need table references on the column names.

In the following you have "dname=dname," that resolves to True. Do you
really want that? Is the right-side dname in another table, one of the
tables in the FROM clause? If so the correct syntax is:

"dname=" & table_name.dname & " And ...

Which makes the DSum() like the following:

DSum("[ni ees and ers 1e]","[x confirmed]","dname=" &
SOME_TABLE_NAME.dname & " And [month number]>=140") AS total_ni,

I believe the other domain aggregate function should be like this:

DLookUp("number","[months]","[month name]=" & staffs.[staff bank name])
AS mthnum,

To use "mthnum" in the DSum() function you have to have the DLookUp()
function declared before the DSum() function. Then use the mthnum in
the DSum() function:

DLookUp("number","[months]","[month name]=" & staffs.[staff bank name])
AS mthnum,
DSum("[ni ees and ers 1e]","[x confirmed]","dname=" &
SOME_TABLE_NAME.dname & " And [month number]>=" & mthnum) AS total_ni,

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSjpYvYechKqOuFEgEQKY+wCggID2Vwc0ptmv9Umwv9pppaD9HpUAn0Z5
2PfhtgSD+pVORwOsWP4aeSLv
=cCh0
-----END PGP SIGNATURE-----
 
P

PayeDoc

Hello Jerry

Thanks for your reply.
Unfortunately I got the same error with your query!
Thanks anyway - John Spencer has got me up and running.

Thanks again
Les


Jerry Whittle said:
Does this come close?

SELECT practices.[prac name],
DSum("[x confirmed]![ni ees and ers 1e]","[xconfirmed]","[x
confirmed]![dname]=[dname]
And [x confirmed]![monthnumber]>=140") AS total_ni,
[months]![number] AS mthnum,
staffs.[staff bank name]
FROM practices, staffs, months
WHERE [practices]![prac name]=[Forms]![frm x main]![prac name]
AND [months]![month name]= [staffs].[staff bank name]
AND [practices]![prac name] = [staffs].[practice];


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


PayeDoc said:
Hello All

I have:
SELECT practices.[prac name], DSum("[x confirmed]![ni ees and ers 1e]","[x
confirmed]","[x confirmed]![dname]=[dname] And [x confirmed]![month
number]>=140") AS total_ni,
DLookUp("[months]![number]","[months]","[months]![month name]=[staff bank
name]") AS mthnum, staffs.[staff bank name]
FROM practices INNER JOIN staffs ON practices.[prac name] = staffs.practice
WHERE (((practices.[prac name])=[Forms]![frm x main]![prac name]));

.... but for some unfathomable reason this errors with a message that access
can't find [staff bank name]. But [staff bank name] is definitely in table
[staffs], and in fact if I remove the field 'mthnum' the query runs fine -
and includes the values of [staff bank name] perfectly happily! I have tried
replacing [staff bank name] with [staffs]![staff bank name] in the field
'mthnum', but this makes no difference.

What I really want to do is use the value of 'mthnum' instead of the
currently hard-coded "140" in the field total_ni.

What's up?
Hope someone can help.

Many thanks
Leslie Isaacs
 
P

PayeDoc

Hello John

That worked great!
I did add the months table as you suggested, and all is well!

Many thanks again
Les



John Spencer MVP said:
Just fixing the syntax, you probably end up with something like the following.

SELECT practices.[prac name]
, DSum("[ni ees and ers 1e]","[x confirmed]","dname=""" & [dname] & """ And
[month number]>=" & DLookUp("[number]","months","[month name]=""" & [staff
bank name] & """) ) AS total_ni,
DLookUp("[number]","months","[month name]=""" & [staff bank name] & """) AS mthnum
, staffs.[staff bank name]
FROM practices INNER JOIN staffs ON practices.[prac name] = staffs.practice
WHERE (((practices.[prac name])=[Forms]![frm x main]![prac name]));

You can probably make the query more efficient by including the other tables
in it and setting up the appropriate joins. BUT that might make query so it
cannot be updated. For instance, just adding the Months table

SELECT practices.[prac name]
, DSum("[ni ees and ers 1e]","[x confirmed]","dname=""" & [dname] & """ And
[month number]>=" & Months.Number ) AS total_ni,
Months.Number AS mthnum
, staffs.[staff bank name]
FROM (practices INNER JOIN staffs ON practices.[prac name] = staffs.practice)
LEFT JOIN [Months] on staffs.[Staff Bank Name]=[Months].[Month Name]
WHERE (((practices.[prac name])=[Forms]![frm x main]![prac name]));

You could probably turn this into an aggregate query, but I can't tell how
since I don't know the source table for the field named dName.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hello All

I have:
SELECT practices.[prac name], DSum("[x confirmed]![ni ees and ers 1e]","[x
confirmed]","[x confirmed]![dname]=[dname] And [x confirmed]![month
number]>=140") AS total_ni,
DLookUp("[months]![number]","[months]","[months]![month name]=[staff bank
name]") AS mthnum, staffs.[staff bank name]
FROM practices INNER JOIN staffs ON practices.[prac name] = staffs.practice
WHERE (((practices.[prac name])=[Forms]![frm x main]![prac name]));

... but for some unfathomable reason this errors with a message that access
can't find [staff bank name]. But [staff bank name] is definitely in table
[staffs], and in fact if I remove the field 'mthnum' the query runs fine -
and includes the values of [staff bank name] perfectly happily! I have tried
replacing [staff bank name] with [staffs]![staff bank name] in the field
'mthnum', but this makes no difference.

What I really want to do is use the value of 'mthnum' instead of the
currently hard-coded "140" in the field total_ni.

What's up?
Hope someone can help.

Many thanks
Leslie Isaacs
 
P

PayeDoc

Hello "MGFoster"

Many thanks for your reply.
I think I follow what you have suggested, but in fact I was lucky to get 3
responses to my post and had tried John Spencer's before I came to yours:
these newsgroups really are great!!

Thanks again
Les




MGFoster said:
PayeDoc said:
Hello All

I have:
SELECT practices.[prac name], DSum("[x confirmed]![ni ees and ers 1e]","[x
confirmed]","[x confirmed]![dname]=[dname] And [x confirmed]![month
number]>=140") AS total_ni,
DLookUp("[months]![number]","[months]","[months]![month name]=[staff bank
name]") AS mthnum, staffs.[staff bank name]
FROM practices INNER JOIN staffs ON practices.[prac name] = staffs.practice
WHERE (((practices.[prac name])=[Forms]![frm x main]![prac name]));

... but for some unfathomable reason this errors with a message that access
can't find [staff bank name]. But [staff bank name] is definitely in table
[staffs], and in fact if I remove the field 'mthnum' the query runs fine -
and includes the values of [staff bank name] perfectly happily! I have tried
replacing [staff bank name] with [staffs]![staff bank name] in the field
'mthnum', but this makes no difference.

What I really want to do is use the value of 'mthnum' instead of the
currently hard-coded "140" in the field total_ni.

What's up?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have some redundant stuff in the domain aggregate functions.
Sometimes, correcting syntax can cure other problems: e.g., you don't
need table references on the column names.

In the following you have "dname=dname," that resolves to True. Do you
really want that? Is the right-side dname in another table, one of the
tables in the FROM clause? If so the correct syntax is:

"dname=" & table_name.dname & " And ...

Which makes the DSum() like the following:

DSum("[ni ees and ers 1e]","[x confirmed]","dname=" &
SOME_TABLE_NAME.dname & " And [month number]>=140") AS total_ni,

I believe the other domain aggregate function should be like this:

DLookUp("number","[months]","[month name]=" & staffs.[staff bank name])
AS mthnum,

To use "mthnum" in the DSum() function you have to have the DLookUp()
function declared before the DSum() function. Then use the mthnum in
the DSum() function:

DLookUp("number","[months]","[month name]=" & staffs.[staff bank name])
AS mthnum,
DSum("[ni ees and ers 1e]","[x confirmed]","dname=" &
SOME_TABLE_NAME.dname & " And [month number]>=" & mthnum) AS total_ni,

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSjpYvYechKqOuFEgEQKY+wCggID2Vwc0ptmv9Umwv9pppaD9HpUAn0Z5
2PfhtgSD+pVORwOsWP4aeSLv
=cCh0
-----END PGP SIGNATURE-----
 

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

Simply query? 9
Slow query 13
Query loses records? 5
Why is my query asking for a paramater? 6
Query to find missing data 4
Slow query 9
Slow query 1
Initiate an action only if query returns data 2

Top