Query problems (User error)

D

Dos Equis

I am trying to create a query which autocalcs pay for services. SQL is
shown below. The problem area is the AS line and I've added the returns
for ease of reading.
[tbl_Carrier.Type] refers to a lookup field that may be showing text
rather than a number, if so how do I fix it?


SELECT DISTINCTROW qry_AreaByCarrier.CNameFirst,
Count(qry_AreaByCarrier.Area) AS Areas,
Sum(qry_AreaByCarrier.[#ofHomes]) AS Homes,
Sum(qry_AreaByCarrier.[#ofSubHomes])

AS Subscribers, ([#ofHomes]*[tbl_Carrier.Type])+([#ofSubHomes]*0.2) AS
Pay

FROM (tbl_CarierPay INNER JOIN tbl_Carrier ON tbl_CarierPay.Type =
tbl_Carrier.Type) INNER JOIN qry_AreaByCarrier ON
tbl_Carrier.CNameFirst = qry_AreaByCarrier.CNameFirst
GROUP BY qry_AreaByCarrier.CNameFirst;

Thanks,

Byron
 
K

kingston via AccessMonster.com

Substitute [tbl_Carrier.Type] with:
IIF(IsNumeric([tbl_Carrier.Type]),[tbl_Carrier.Type],1)
Use whatever numeric value is appropriate instead of 1. You may also want to
trap a Null by using the function Nz().


Dos said:
I am trying to create a query which autocalcs pay for services. SQL is
shown below. The problem area is the AS line and I've added the returns
for ease of reading.
[tbl_Carrier.Type] refers to a lookup field that may be showing text
rather than a number, if so how do I fix it?

SELECT DISTINCTROW qry_AreaByCarrier.CNameFirst,
Count(qry_AreaByCarrier.Area) AS Areas,
Sum(qry_AreaByCarrier.[#ofHomes]) AS Homes,
Sum(qry_AreaByCarrier.[#ofSubHomes])

AS Subscribers, ([#ofHomes]*[tbl_Carrier.Type])+([#ofSubHomes]*0.2) AS
Pay

FROM (tbl_CarierPay INNER JOIN tbl_Carrier ON tbl_CarierPay.Type =
tbl_Carrier.Type) INNER JOIN qry_AreaByCarrier ON
tbl_Carrier.CNameFirst = qry_AreaByCarrier.CNameFirst
GROUP BY qry_AreaByCarrier.CNameFirst;

Thanks,

Byron
Click to expand...
 
D

Dos Equis

Thank you for responding. I copied and pasted that in and it didn't
work, SQL now looks like:

SELECT DISTINCTROW qry_AreaByCarrier.CNameFirst,
Count(qry_AreaByCarrier.Area) AS Areas,
Sum(qry_AreaByCarrier.[#ofHomes]) AS Homes,
Sum(qry_AreaByCarrier.[#ofSubHomes]) AS Subscribers,
(([Homes]*IIf(IsNumeric([tbl_Carrier.Type]),[tbl_Carrier.Type],1))+([Subscribers]*0.2))
AS Pay
FROM (tbl_CarierPay INNER JOIN tbl_Carrier ON tbl_CarierPay.Type =
tbl_Carrier.Type) INNER JOIN qry_AreaByCarrier ON
tbl_Carrier.CNameFirst = qry_AreaByCarrier.CNameFirst
GROUP BY qry_AreaByCarrier.CNameFirst,
(([Homes]*IIf(IsNumeric([tbl_Carrier.Type]),[tbl_Carrier.Type],1))+([Subscribers]*0.2));

and I get an error stating that I tried to execute a query that does
not include the specified expression as part of an agregate function.

Any idea what that's all about?

Thanks,

Byron

Substitute [tbl_Carrier.Type] with:
IIF(IsNumeric([tbl_Carrier.Type]),[tbl_Carrier.Type],1)
Use whatever numeric value is appropriate instead of 1. You may also want to
trap a Null by using the function Nz().


Dos said:
I am trying to create a query which autocalcs pay for services. SQL is
shown below. The problem area is the AS line and I've added the returns
for ease of reading.
[tbl_Carrier.Type] refers to a lookup field that may be showing text
rather than a number, if so how do I fix it?

SELECT DISTINCTROW qry_AreaByCarrier.CNameFirst,
Count(qry_AreaByCarrier.Area) AS Areas,
Sum(qry_AreaByCarrier.[#ofHomes]) AS Homes,
Sum(qry_AreaByCarrier.[#ofSubHomes])

AS Subscribers, ([#ofHomes]*[tbl_Carrier.Type])+([#ofSubHomes]*0.2) AS
Pay

FROM (tbl_CarierPay INNER JOIN tbl_Carrier ON tbl_CarierPay.Type =
tbl_Carrier.Type) INNER JOIN qry_AreaByCarrier ON
tbl_Carrier.CNameFirst = qry_AreaByCarrier.CNameFirst
GROUP BY qry_AreaByCarrier.CNameFirst;

Thanks,

Byron
Click to expand...
Click to expand...
 
K

kingston via AccessMonster.com

Does the rest of the query work? I am suspicious about the join on
tbl_Carrier.Type and tbl_CarierPay.Type since it seems that the field should
be numeric but sometimes isn't. Anyway, the error message is because Count
and Sum are aggregate functions that operate on multiple records and the
calculated field is not an aggregate result. Try to do this in two queries -
one based on qry_AreaByCarrier where Count and Sum are applied and one based
Thank you for responding. I copied and pasted that in and it didn't
work, SQL now looks like:

SELECT DISTINCTROW qry_AreaByCarrier.CNameFirst,
Count(qry_AreaByCarrier.Area) AS Areas,
Sum(qry_AreaByCarrier.[#ofHomes]) AS Homes,
Sum(qry_AreaByCarrier.[#ofSubHomes]) AS Subscribers,
(([Homes]*IIf(IsNumeric([tbl_Carrier.Type]),[tbl_Carrier.Type],1))+([Subscribers]*0.2))
AS Pay
FROM (tbl_CarierPay INNER JOIN tbl_Carrier ON tbl_CarierPay.Type =
tbl_Carrier.Type) INNER JOIN qry_AreaByCarrier ON
tbl_Carrier.CNameFirst = qry_AreaByCarrier.CNameFirst
GROUP BY qry_AreaByCarrier.CNameFirst,
(([Homes]*IIf(IsNumeric([tbl_Carrier.Type]),[tbl_Carrier.Type],1))+([Subscribers]*0.2));

and I get an error stating that I tried to execute a query that does
not include the specified expression as part of an agregate function.

Any idea what that's all about?

Thanks,

Byron
Substitute [tbl_Carrier.Type] with:
IIF(IsNumeric([tbl_Carrier.Type]),[tbl_Carrier.Type],1)
Click to expand...
[quoted text clipped - 23 lines]
Click to expand...
 
D

Dos Equis

This is the query that shows who my carriers are, how many areas they
deliver and how many homes are in the areas.

This is the query that works as required:

SELECT DISTINCTROW qry_AreaByCarrier.CNameFirst AS Name,
Count(qry_AreaByCarrier.Area) AS Areas,
Sum(qry_AreaByCarrier.[#ofHomes]) AS Homes
FROM qry_AreaByCarrier
GROUP BY qry_AreaByCarrier.CNameFirst;

This is the query that returns correct subscriber #'s by carrier:

SELECT tbl_Carrier.CNameFirst AS Carrier,
Count(tbl_SubscribersData.SubID) AS Subscribers
FROM (tbl_Carrier INNER JOIN tbl_Area ON (tbl_Carrier.CID =
tbl_Area.CID) AND (tbl_Carrier.CID = tbl_Area.CID)) INNER JOIN
tbl_SubscribersData ON tbl_Area.AreaID = tbl_SubscribersData.AreaID
GROUP BY tbl_Carrier.CNameFirst, tbl_SubscribersData.SubDND,
tbl_SubscribersData.SubType
HAVING ((Not (tbl_Carrier.CNameFirst)="Mail") AND
((tbl_SubscribersData.SubDND) Is Null) AND
((tbl_SubscribersData.SubType) Is Null));

Can I compine these two queries to come up with a correct pay scale?

Thanks

Byron

Does the rest of the query work? I am suspicious about the join on
tbl_Carrier.Type and tbl_CarierPay.Type since it seems that the field should
be numeric but sometimes isn't. Anyway, the error message is because Count
and Sum are aggregate functions that operate on multiple records and the
calculated field is not an aggregate result. Try to do this in two queries -
one based on qry_AreaByCarrier where Count and Sum are applied and one based
Thank you for responding. I copied and pasted that in and it didn't
work, SQL now looks like:

SELECT DISTINCTROW qry_AreaByCarrier.CNameFirst,
Count(qry_AreaByCarrier.Area) AS Areas,
Sum(qry_AreaByCarrier.[#ofHomes]) AS Homes,
Sum(qry_AreaByCarrier.[#ofSubHomes]) AS Subscribers,
(([Homes]*IIf(IsNumeric([tbl_Carrier.Type]),[tbl_Carrier.Type],1))+([Subscribers]*0.2))
AS Pay
FROM (tbl_CarierPay INNER JOIN tbl_Carrier ON tbl_CarierPay.Type =
tbl_Carrier.Type) INNER JOIN qry_AreaByCarrier ON
tbl_Carrier.CNameFirst = qry_AreaByCarrier.CNameFirst
GROUP BY qry_AreaByCarrier.CNameFirst,
(([Homes]*IIf(IsNumeric([tbl_Carrier.Type]),[tbl_Carrier.Type],1))+([Subscribers]*0.2));

and I get an error stating that I tried to execute a query that does
not include the specified expression as part of an agregate function.

Any idea what that's all about?

Thanks,

Byron
Substitute [tbl_Carrier.Type] with:
IIF(IsNumeric([tbl_Carrier.Type]),[tbl_Carrier.Type],1)
Click to expand...
[quoted text clipped - 23 lines]
Byron
Click to expand...
Click to expand...
Click to expand...
 
K

kingston via AccessMonster.com

Are the fields Name and Carrier the common link between the two datasets? If
so, it appears that you have enough data to perform the calculation (
[#ofHomes]*[tbl_Carrier.Type])+([#ofSubHomes]*0.2) if
#ofHomes = query1.Homes
#ofSubHomes = query2. Subscribers
You'd do a join between these two queries by Name/Carrier and to tbl_Carrier.
Type by Name and come up with the third term tbl_Carrier.Type.
Do you have these queries saved as visual queries? It's probably a lot
easier to do this visually. HTH.

Dos said:
This is the query that shows who my carriers are, how many areas they
deliver and how many homes are in the areas.

This is the query that works as required:

SELECT DISTINCTROW qry_AreaByCarrier.CNameFirst AS Name,
Count(qry_AreaByCarrier.Area) AS Areas,
Sum(qry_AreaByCarrier.[#ofHomes]) AS Homes
FROM qry_AreaByCarrier
GROUP BY qry_AreaByCarrier.CNameFirst;

This is the query that returns correct subscriber #'s by carrier:

SELECT tbl_Carrier.CNameFirst AS Carrier,
Count(tbl_SubscribersData.SubID) AS Subscribers
FROM (tbl_Carrier INNER JOIN tbl_Area ON (tbl_Carrier.CID =
tbl_Area.CID) AND (tbl_Carrier.CID = tbl_Area.CID)) INNER JOIN
tbl_SubscribersData ON tbl_Area.AreaID = tbl_SubscribersData.AreaID
GROUP BY tbl_Carrier.CNameFirst, tbl_SubscribersData.SubDND,
tbl_SubscribersData.SubType
HAVING ((Not (tbl_Carrier.CNameFirst)="Mail") AND
((tbl_SubscribersData.SubDND) Is Null) AND
((tbl_SubscribersData.SubType) Is Null));

Can I compine these two queries to come up with a correct pay scale?

Thanks

Byron
Does the rest of the query work? I am suspicious about the join on
tbl_Carrier.Type and tbl_CarierPay.Type since it seems that the field should
Click to expand...
[quoted text clipped - 38 lines]
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

Query Error - Field too small for data... 0
#ERROR and currency issues 1
problems with a summary query 2
Query or User errors 1
Coding for reports 0
Error in Update Query 1
query summing error 2
query before date 1

Top