Expression with sort problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have multiple Field columns for my query. I inserted a field to add
certain other fields together. My formula is Lg Grower: +[8]+[8 1/2]+[9]+[9
1/2]+[10]+[10 1/2]
Table is blank. Total is Expression. I can run this query as is but when I
try to sort descend I get an error message. What I need this to do is add
the fileds together and descend highest to lowest. If I change the total to
sum and try to add fields together it also gives me an error message. Any
ideas?
Thanks
Zenia
 
Can you post the entire SQL string?

Do you mean that the query is blank (returns no rows) or that the field is
blank(no numbers). My guess is that one or more of these fields is NULL, and
when you add anything to a NULL, you get a NULL. Try using the NZ( )
function, which converts Null values to some other value (in this case zero):

Lg_Grower: NZ([8], 0) + NZ([8 1/2], 0) + NZ([9], 0) + NZ([9 1/2], 0) + ...

Do you actually have fields named [8], [8 1/2], [9]? This tells me that
your database is setup more like a spreadsheet than a normallized database.
These "fields" should actually be values in a single field.

If you need help normalizing your data, post back.

HTH
Dale
 
Try it out after populating the table with at least some sample values. Note
that null value operations will likely trigger error messages unless
specifically addressed.
--
********* http://panjas.org
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?
 
This database is for cherry pressure/sizing/firmness etc. We have a machine
called firm tech that stores information about random samples. We take that
information from a csv file and transfer it to a table in access. This is
where we store all cherry data for a specific grower back to specific bins.
The query I am working on is our run order. The fields 8 8/12 9 91/2 10
101/2 11 are cherry sizes. The firm tech seperates cherry size information
according to the sample taken and brings a decimal percent into access. I
want to add the fields 8 8/12 9 91/2 10 101/2 rows together on specific
variety, pools, growers. Whatever grower has the largest decimal percent I
want to be first in my running order. Thank you for helping me =)

SELECT qryMergedData.RunOrder, qryMergedData.RecvDate, qryMergedData.LotNum,
tblLotList.LName, qryMergedData.PoolNum, qryMergedData.Variety,
Sum(qryMergedData.Bins) AS Bins, Avg(qryMergedData.MaxFirm) AS MaxFirm,
Avg(qryMergedData.MinFirm) AS MinFirm, Avg(qryMergedData.AvgFirm) AS AvgFirm,
Avg(qryMergedData.StdFirm) AS StdFirm, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10 1/2]
AS high, Avg(qryMergedData.[8]) AS 8, Avg(qryMergedData.[8 1/2]) AS [8 1/2],
Avg(qryMergedData.[9]) AS 9, Avg(qryMergedData.[9 1/2]) AS [9 1/2],
Avg(qryMergedData.[10]) AS 10, Avg(qryMergedData.[10 1/2]) AS [10 1/2],
Avg(qryMergedData.[11]) AS 11, Avg(qryMergedData.[11 1/2]) AS [11 1/2],
Avg(qryMergedData.[12]) AS 12, Avg(qryMergedData.[13]) AS 13,
Avg(qryMergedData.CullP) AS CullP, Avg(qryMergedData.BrineP) AS BrineP,
qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData ON tblLotList.LotNum =
qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum, tblLotList.LName, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.Packed
HAVING (((qryMergedData.Variety)=[enter variety]) AND
((qryMergedData.Packed)=False))
ORDER BY qryMergedData.RecvDate, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10 1/2] DESC
, Avg(qryMergedData.[10 1/2]) DESC , Avg(qryMergedData.[11]) DESC ,
Avg(qryMergedData.[11 1/2]) DESC , Avg(qryMergedData.[12]) DESC ,
Avg(qryMergedData.[13]) DESC , Avg(qryMergedData.CullP) DESC ,
Avg(qryMergedData.BrineP) DESC;


Dale Fye said:
Can you post the entire SQL string?

Do you mean that the query is blank (returns no rows) or that the field is
blank(no numbers). My guess is that one or more of these fields is NULL, and
when you add anything to a NULL, you get a NULL. Try using the NZ( )
function, which converts Null values to some other value (in this case zero):

Lg_Grower: NZ([8], 0) + NZ([8 1/2], 0) + NZ([9], 0) + NZ([9 1/2], 0) + ...

Do you actually have fields named [8], [8 1/2], [9]? This tells me that
your database is setup more like a spreadsheet than a normallized database.
These "fields" should actually be values in a single field.

If you need help normalizing your data, post back.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


esparzaone said:
I have multiple Field columns for my query. I inserted a field to add
certain other fields together. My formula is Lg Grower: +[8]+[8 1/2]+[9]+[9
1/2]+[10]+[10 1/2]
Table is blank. Total is Expression. I can run this query as is but when I
try to sort descend I get an error message. What I need this to do is add
the fileds together and descend highest to lowest. If I change the total to
sum and try to add fields together it also gives me an error message. Any
ideas?
Thanks
Zenia
Click to expand...
Click to expand...
 
Avg(qryMergedData.[9]) AS 9
This is a problem as you are using the same field name for an alias. Gotta
be different name. Also you would need brackets around the alias like [9].
--
KARL DEWEY
Build a little - Test a little


esparzaone said:
This database is for cherry pressure/sizing/firmness etc. We have a machine
called firm tech that stores information about random samples. We take that
information from a csv file and transfer it to a table in access. This is
where we store all cherry data for a specific grower back to specific bins.
The query I am working on is our run order. The fields 8 8/12 9 91/2 10
101/2 11 are cherry sizes. The firm tech seperates cherry size information
according to the sample taken and brings a decimal percent into access. I
want to add the fields 8 8/12 9 91/2 10 101/2 rows together on specific
variety, pools, growers. Whatever grower has the largest decimal percent I
want to be first in my running order. Thank you for helping me =)

SELECT qryMergedData.RunOrder, qryMergedData.RecvDate, qryMergedData.LotNum,
tblLotList.LName, qryMergedData.PoolNum, qryMergedData.Variety,
Sum(qryMergedData.Bins) AS Bins, Avg(qryMergedData.MaxFirm) AS MaxFirm,
Avg(qryMergedData.MinFirm) AS MinFirm, Avg(qryMergedData.AvgFirm) AS AvgFirm,
Avg(qryMergedData.StdFirm) AS StdFirm, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10 1/2]
AS high, Avg(qryMergedData.[8]) AS 8, Avg(qryMergedData.[8 1/2]) AS [8 1/2],
Avg(qryMergedData.[9]) AS 9, Avg(qryMergedData.[9 1/2]) AS [9 1/2],
Avg(qryMergedData.[10]) AS 10, Avg(qryMergedData.[10 1/2]) AS [10 1/2],
Avg(qryMergedData.[11]) AS 11, Avg(qryMergedData.[11 1/2]) AS [11 1/2],
Avg(qryMergedData.[12]) AS 12, Avg(qryMergedData.[13]) AS 13,
Avg(qryMergedData.CullP) AS CullP, Avg(qryMergedData.BrineP) AS BrineP,
qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData ON tblLotList.LotNum =
qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum, tblLotList.LName, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.Packed
HAVING (((qryMergedData.Variety)=[enter variety]) AND
((qryMergedData.Packed)=False))
ORDER BY qryMergedData.RecvDate, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10 1/2] DESC
, Avg(qryMergedData.[10 1/2]) DESC , Avg(qryMergedData.[11]) DESC ,
Avg(qryMergedData.[11 1/2]) DESC , Avg(qryMergedData.[12]) DESC ,
Avg(qryMergedData.[13]) DESC , Avg(qryMergedData.CullP) DESC ,
Avg(qryMergedData.BrineP) DESC;


Dale Fye said:
Can you post the entire SQL string?

Do you mean that the query is blank (returns no rows) or that the field is
blank(no numbers). My guess is that one or more of these fields is NULL, and
when you add anything to a NULL, you get a NULL. Try using the NZ( )
function, which converts Null values to some other value (in this case zero):

Lg_Grower: NZ([8], 0) + NZ([8 1/2], 0) + NZ([9], 0) + NZ([9 1/2], 0) + ...

Do you actually have fields named [8], [8 1/2], [9]? This tells me that
your database is setup more like a spreadsheet than a normallized database.
These "fields" should actually be values in a single field.

If you need help normalizing your data, post back.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


esparzaone said:
I have multiple Field columns for my query. I inserted a field to add
certain other fields together. My formula is Lg Grower: +[8]+[8 1/2]+[9]+[9
1/2]+[10]+[10 1/2]
Table is blank. Total is Expression. I can run this query as is but when I
try to sort descend I get an error message. What I need this to do is add
the fileds together and descend highest to lowest. If I change the total to
sum and try to add fields together it also gives me an error message. Any
ideas?
Thanks
Zenia
Click to expand...
Click to expand...
Click to expand...
 
Why would the alias need to be different?

KARL DEWEY said:
Avg(qryMergedData.[9]) AS 9
Click to expand...
Click to expand...
This is a problem as you are using the same field name for an alias. Gotta
be different name. Also you would need brackets around the alias like [9].
--
KARL DEWEY
Build a little - Test a little


esparzaone said:
This database is for cherry pressure/sizing/firmness etc. We have a machine
called firm tech that stores information about random samples. We take that
information from a csv file and transfer it to a table in access. This is
where we store all cherry data for a specific grower back to specific bins.
The query I am working on is our run order. The fields 8 8/12 9 91/2 10
101/2 11 are cherry sizes. The firm tech seperates cherry size information
according to the sample taken and brings a decimal percent into access. I
want to add the fields 8 8/12 9 91/2 10 101/2 rows together on specific
variety, pools, growers. Whatever grower has the largest decimal percent I
want to be first in my running order. Thank you for helping me =)

SELECT qryMergedData.RunOrder, qryMergedData.RecvDate, qryMergedData.LotNum,
tblLotList.LName, qryMergedData.PoolNum, qryMergedData.Variety,
Sum(qryMergedData.Bins) AS Bins, Avg(qryMergedData.MaxFirm) AS MaxFirm,
Avg(qryMergedData.MinFirm) AS MinFirm, Avg(qryMergedData.AvgFirm) AS AvgFirm,
Avg(qryMergedData.StdFirm) AS StdFirm, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10 1/2]
AS high, Avg(qryMergedData.[8]) AS 8, Avg(qryMergedData.[8 1/2]) AS [8 1/2],
Avg(qryMergedData.[9]) AS 9, Avg(qryMergedData.[9 1/2]) AS [9 1/2],
Avg(qryMergedData.[10]) AS 10, Avg(qryMergedData.[10 1/2]) AS [10 1/2],
Avg(qryMergedData.[11]) AS 11, Avg(qryMergedData.[11 1/2]) AS [11 1/2],
Avg(qryMergedData.[12]) AS 12, Avg(qryMergedData.[13]) AS 13,
Avg(qryMergedData.CullP) AS CullP, Avg(qryMergedData.BrineP) AS BrineP,
qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData ON tblLotList.LotNum =
qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum, tblLotList.LName, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.Packed
HAVING (((qryMergedData.Variety)=[enter variety]) AND
((qryMergedData.Packed)=False))
ORDER BY qryMergedData.RecvDate, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10 1/2] DESC
, Avg(qryMergedData.[10 1/2]) DESC , Avg(qryMergedData.[11]) DESC ,
Avg(qryMergedData.[11 1/2]) DESC , Avg(qryMergedData.[12]) DESC ,
Avg(qryMergedData.[13]) DESC , Avg(qryMergedData.CullP) DESC ,
Avg(qryMergedData.BrineP) DESC;


Dale Fye said:
Can you post the entire SQL string?

Do you mean that the query is blank (returns no rows) or that the field is
blank(no numbers). My guess is that one or more of these fields is NULL, and
when you add anything to a NULL, you get a NULL. Try using the NZ( )
function, which converts Null values to some other value (in this case zero):

Lg_Grower: NZ([8], 0) + NZ([8 1/2], 0) + NZ([9], 0) + NZ([9 1/2], 0) + ...

Do you actually have fields named [8], [8 1/2], [9]? This tells me that
your database is setup more like a spreadsheet than a normallized database.
These "fields" should actually be values in a single field.

If you need help normalizing your data, post back.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I have multiple Field columns for my query. I inserted a field to add
certain other fields together. My formula is Lg Grower: +[8]+[8 1/2]+[9]+[9
1/2]+[10]+[10 1/2]
Table is blank. Total is Expression. I can run this query as is but when I
try to sort descend I get an error message. What I need this to do is add
the fileds together and descend highest to lowest. If I change the total to
sum and try to add fields together it also gives me an error message. Any
ideas?
Thanks
Zenia
Click to expand...
Click to expand...
Click to expand...
 
Because Access insists on it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


esparzaone said:
Why would the alias need to be different?

KARL DEWEY said:
Avg(qryMergedData.[9]) AS 9
Click to expand...
This is a problem as you are using the same field name for an alias.
Gotta
be different name. Also you would need brackets around the alias like
[9].
--
KARL DEWEY
Build a little - Test a little


esparzaone said:
This database is for cherry pressure/sizing/firmness etc. We have a
machine
called firm tech that stores information about random samples. We take
that
information from a csv file and transfer it to a table in access. This
is
where we store all cherry data for a specific grower back to specific
bins.
The query I am working on is our run order. The fields 8 8/12 9 91/2
10
101/2 11 are cherry sizes. The firm tech seperates cherry size
information
according to the sample taken and brings a decimal percent into access.
I
want to add the fields 8 8/12 9 91/2 10 101/2 rows together on specific
variety, pools, growers. Whatever grower has the largest decimal
percent I
want to be first in my running order. Thank you for helping me =)

SELECT qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum,
tblLotList.LName, qryMergedData.PoolNum, qryMergedData.Variety,
Sum(qryMergedData.Bins) AS Bins, Avg(qryMergedData.MaxFirm) AS MaxFirm,
Avg(qryMergedData.MinFirm) AS MinFirm, Avg(qryMergedData.AvgFirm) AS
AvgFirm,
Avg(qryMergedData.StdFirm) AS StdFirm, +[8]+[8 1/2]+[9]+[9
1/2]+[10]+[10 1/2]
AS high, Avg(qryMergedData.[8]) AS 8, Avg(qryMergedData.[8 1/2]) AS [8
1/2],
Avg(qryMergedData.[9]) AS 9, Avg(qryMergedData.[9 1/2]) AS [9 1/2],
Avg(qryMergedData.[10]) AS 10, Avg(qryMergedData.[10 1/2]) AS [10 1/2],
Avg(qryMergedData.[11]) AS 11, Avg(qryMergedData.[11 1/2]) AS [11 1/2],
Avg(qryMergedData.[12]) AS 12, Avg(qryMergedData.[13]) AS 13,
Avg(qryMergedData.CullP) AS CullP, Avg(qryMergedData.BrineP) AS BrineP,
qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData ON tblLotList.LotNum =
qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum, tblLotList.LName, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.Packed
HAVING (((qryMergedData.Variety)=[enter variety]) AND
((qryMergedData.Packed)=False))
ORDER BY qryMergedData.RecvDate, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10 1/2]
DESC
, Avg(qryMergedData.[10 1/2]) DESC , Avg(qryMergedData.[11]) DESC ,
Avg(qryMergedData.[11 1/2]) DESC , Avg(qryMergedData.[12]) DESC ,
Avg(qryMergedData.[13]) DESC , Avg(qryMergedData.CullP) DESC ,
Avg(qryMergedData.BrineP) DESC;


:

Can you post the entire SQL string?

Do you mean that the query is blank (returns no rows) or that the
field is
blank(no numbers). My guess is that one or more of these fields is
NULL, and
when you add anything to a NULL, you get a NULL. Try using the NZ( )
function, which converts Null values to some other value (in this
case zero):

Lg_Grower: NZ([8], 0) + NZ([8 1/2], 0) + NZ([9], 0) + NZ([9 1/2], 0)
+ ...

Do you actually have fields named [8], [8 1/2], [9]? This tells me
that
your database is setup more like a spreadsheet than a normallized
database.
These "fields" should actually be values in a single field.

If you need help normalizing your data, post back.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I have multiple Field columns for my query. I inserted a field to
add
certain other fields together. My formula is Lg Grower: +[8]+[8
1/2]+[9]+[9
1/2]+[10]+[10 1/2]
Table is blank. Total is Expression. I can run this query as is
but when I
try to sort descend I get an error message. What I need this to do
is add
the fileds together and descend highest to lowest. If I change the
total to
sum and try to add fields together it also gives me an error
message. Any
ideas?
Thanks
Zenia
Click to expand...
Click to expand...
Click to expand...
 
If I am understanding correctly I need to change it like this
80: 8 (from merge query) 85: 8 1/2 (from merged query) 90: 9 etc then use my
empty field Lg Fruit: + [80]+[85]+[90] etc call it an expression and descend?

Douglas J. Steele said:
Because Access insists on it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


esparzaone said:
Why would the alias need to be different?

KARL DEWEY said:
Avg(qryMergedData.[9]) AS 9
This is a problem as you are using the same field name for an alias.
Gotta
be different name. Also you would need brackets around the alias like
[9].
--
KARL DEWEY
Build a little - Test a little


:

This database is for cherry pressure/sizing/firmness etc. We have a
machine
called firm tech that stores information about random samples. We take
that
information from a csv file and transfer it to a table in access. This
is
where we store all cherry data for a specific grower back to specific
bins.
The query I am working on is our run order. The fields 8 8/12 9 91/2
10
101/2 11 are cherry sizes. The firm tech seperates cherry size
information
according to the sample taken and brings a decimal percent into access.
I
want to add the fields 8 8/12 9 91/2 10 101/2 rows together on specific
variety, pools, growers. Whatever grower has the largest decimal
percent I
want to be first in my running order. Thank you for helping me =)

SELECT qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum,
tblLotList.LName, qryMergedData.PoolNum, qryMergedData.Variety,
Sum(qryMergedData.Bins) AS Bins, Avg(qryMergedData.MaxFirm) AS MaxFirm,
Avg(qryMergedData.MinFirm) AS MinFirm, Avg(qryMergedData.AvgFirm) AS
AvgFirm,
Avg(qryMergedData.StdFirm) AS StdFirm, +[8]+[8 1/2]+[9]+[9
1/2]+[10]+[10 1/2]
AS high, Avg(qryMergedData.[8]) AS 8, Avg(qryMergedData.[8 1/2]) AS [8
1/2],
Avg(qryMergedData.[9]) AS 9, Avg(qryMergedData.[9 1/2]) AS [9 1/2],
Avg(qryMergedData.[10]) AS 10, Avg(qryMergedData.[10 1/2]) AS [10 1/2],
Avg(qryMergedData.[11]) AS 11, Avg(qryMergedData.[11 1/2]) AS [11 1/2],
Avg(qryMergedData.[12]) AS 12, Avg(qryMergedData.[13]) AS 13,
Avg(qryMergedData.CullP) AS CullP, Avg(qryMergedData.BrineP) AS BrineP,
qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData ON tblLotList.LotNum =
qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum, tblLotList.LName, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.Packed
HAVING (((qryMergedData.Variety)=[enter variety]) AND
((qryMergedData.Packed)=False))
ORDER BY qryMergedData.RecvDate, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10 1/2]
DESC
, Avg(qryMergedData.[10 1/2]) DESC , Avg(qryMergedData.[11]) DESC ,
Avg(qryMergedData.[11 1/2]) DESC , Avg(qryMergedData.[12]) DESC ,
Avg(qryMergedData.[13]) DESC , Avg(qryMergedData.CullP) DESC ,
Avg(qryMergedData.BrineP) DESC;


:

Can you post the entire SQL string?

Do you mean that the query is blank (returns no rows) or that the
field is
blank(no numbers). My guess is that one or more of these fields is
NULL, and
when you add anything to a NULL, you get a NULL. Try using the NZ( )
function, which converts Null values to some other value (in this
case zero):

Lg_Grower: NZ([8], 0) + NZ([8 1/2], 0) + NZ([9], 0) + NZ([9 1/2], 0)
+ ...

Do you actually have fields named [8], [8 1/2], [9]? This tells me
that
your database is setup more like a spreadsheet than a normallized
database.
These "fields" should actually be values in a single field.

If you need help normalizing your data, post back.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I have multiple Field columns for my query. I inserted a field to
add
certain other fields together. My formula is Lg Grower: +[8]+[8
1/2]+[9]+[9
1/2]+[10]+[10 1/2]
Table is blank. Total is Expression. I can run this query as is
but when I
try to sort descend I get an error message. What I need this to do
is add
the fileds together and descend highest to lowest. If I change the
total to
sum and try to add fields together it also gives me an error
message. Any
ideas?
Thanks
Zenia
Click to expand...
Click to expand...
Click to expand...
 
It seems I was mistaken. Try this --
SELECT qryMergedData.RunOrder, qryMergedData.RecvDate, qryMergedData.LotNum,
tblLotList.LName, qryMergedData.PoolNum, qryMergedData.Variety,
Sum(qryMergedData.Bins) AS Bins, Avg(qryMergedData.MaxFirm) AS MaxFirm,
Avg(qryMergedData.MinFirm) AS MinFirm, Avg(qryMergedData.AvgFirm) AS AvgFirm,
Avg(qryMergedData.StdFirm) AS StdFirm, (NZ([8],0)+NZ([8
1/2],0)+NZ([9],0)+NZ([9 1/2],0)+NZ([10],0)+NZ([10 1/2],0)) AS High,
Avg(qryMergedData.[8]) AS 8, Avg(qryMergedData.[8 1/2]) AS [8 1/2],
Avg(qryMergedData.[9]) AS 9, Avg(qryMergedData.[9 1/2]) AS [9 1/2],
Avg(qryMergedData.[10]) AS 10, Avg(qryMergedData.[10 1/2]) AS [10 1/2],
Avg(qryMergedData.[11]) AS 11, Avg(qryMergedData.[11 1/2]) AS [11 1/2],
Avg(qryMergedData.[12]) AS 12, Avg(qryMergedData.[13]) AS 13,
Avg(qryMergedData.CullP) AS CullP, Avg(qryMergedData.BrineP) AS BrineP,
qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData ON tblLotList.LotNum =
qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum, tblLotList.LName, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.Packed
HAVING (((qryMergedData.Variety)=[enter variety]) AND
((qryMergedData.Packed)=False))
ORDER BY qryMergedData.RecvDate, Avg(qryMergedData.[10 1/2]) DESC ,
Avg(qryMergedData.[11]) DESC , Avg(qryMergedData.[11 1/2]) DESC ,
Avg(qryMergedData.[12]) DESC , Avg(qryMergedData.[13]) DESC ,
Avg(qryMergedData.CullP) DESC , Avg(qryMergedData.BrineP) DESC;

--
KARL DEWEY
Build a little - Test a little


esparzaone said:
Why would the alias need to be different?

KARL DEWEY said:
Avg(qryMergedData.[9]) AS 9
Click to expand...
This is a problem as you are using the same field name for an alias. Gotta
be different name. Also you would need brackets around the alias like [9].
--
KARL DEWEY
Build a little - Test a little


esparzaone said:
This database is for cherry pressure/sizing/firmness etc. We have a machine
called firm tech that stores information about random samples. We take that
information from a csv file and transfer it to a table in access. This is
where we store all cherry data for a specific grower back to specific bins.
The query I am working on is our run order. The fields 8 8/12 9 91/2 10
101/2 11 are cherry sizes. The firm tech seperates cherry size information
according to the sample taken and brings a decimal percent into access. I
want to add the fields 8 8/12 9 91/2 10 101/2 rows together on specific
variety, pools, growers. Whatever grower has the largest decimal percent I
want to be first in my running order. Thank you for helping me =)

SELECT qryMergedData.RunOrder, qryMergedData.RecvDate, qryMergedData.LotNum,
tblLotList.LName, qryMergedData.PoolNum, qryMergedData.Variety,
Sum(qryMergedData.Bins) AS Bins, Avg(qryMergedData.MaxFirm) AS MaxFirm,
Avg(qryMergedData.MinFirm) AS MinFirm, Avg(qryMergedData.AvgFirm) AS AvgFirm,
Avg(qryMergedData.StdFirm) AS StdFirm, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10 1/2]
AS high, Avg(qryMergedData.[8]) AS 8, Avg(qryMergedData.[8 1/2]) AS [8 1/2],
Avg(qryMergedData.[9]) AS 9, Avg(qryMergedData.[9 1/2]) AS [9 1/2],
Avg(qryMergedData.[10]) AS 10, Avg(qryMergedData.[10 1/2]) AS [10 1/2],
Avg(qryMergedData.[11]) AS 11, Avg(qryMergedData.[11 1/2]) AS [11 1/2],
Avg(qryMergedData.[12]) AS 12, Avg(qryMergedData.[13]) AS 13,
Avg(qryMergedData.CullP) AS CullP, Avg(qryMergedData.BrineP) AS BrineP,
qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData ON tblLotList.LotNum =
qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum, tblLotList.LName, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.Packed
HAVING (((qryMergedData.Variety)=[enter variety]) AND
((qryMergedData.Packed)=False))
ORDER BY qryMergedData.RecvDate, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10 1/2] DESC
, Avg(qryMergedData.[10 1/2]) DESC , Avg(qryMergedData.[11]) DESC ,
Avg(qryMergedData.[11 1/2]) DESC , Avg(qryMergedData.[12]) DESC ,
Avg(qryMergedData.[13]) DESC , Avg(qryMergedData.CullP) DESC ,
Avg(qryMergedData.BrineP) DESC;


:

Can you post the entire SQL string?

Do you mean that the query is blank (returns no rows) or that the field is
blank(no numbers). My guess is that one or more of these fields is NULL, and
when you add anything to a NULL, you get a NULL. Try using the NZ( )
function, which converts Null values to some other value (in this case zero):

Lg_Grower: NZ([8], 0) + NZ([8 1/2], 0) + NZ([9], 0) + NZ([9 1/2], 0) + ...

Do you actually have fields named [8], [8 1/2], [9]? This tells me that
your database is setup more like a spreadsheet than a normallized database.
These "fields" should actually be values in a single field.

If you need help normalizing your data, post back.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I have multiple Field columns for my query. I inserted a field to add
certain other fields together. My formula is Lg Grower: +[8]+[8 1/2]+[9]+[9
1/2]+[10]+[10 1/2]
Table is blank. Total is Expression. I can run this query as is but when I
try to sort descend I get an error message. What I need this to do is add
the fileds together and descend highest to lowest. If I change the total to
sum and try to add fields together it also gives me an error message. Any
ideas?
Thanks
Zenia
Click to expand...
Click to expand...
Click to expand...
 
Karl,

What do you mean you were mistaken? In my 2003 version of Access, I am
unable to create an alias that looks like the field in the aggregation. Has
something changed in 2007?

Actually, I'll be surprised if this SQL runs, since the computed field
(High) does not have and aggregate function and is not included in the
GroupBy clause. Should that line possibly be summed, or Averaged as well.
Based on his comment about the sorting of these results, I think is OrderBy
clause might need to be:

ORDER BY Sum(NZ([8],0)+NZ([8 1/2],0)+NZ([9],0)+NZ([9
1/2],0)+NZ([10],0)+NZ([10 1/2],0)) DESC

Dale

SELECT qryMergedData.RunOrder
, qryMergedData.RecvDate
, qryMergedData.LotNum
, tblLotList.LName
, qryMergedData.PoolNum
, qryMergedData.Variety
, Sum(qryMergedData.Bins) AS Bins
, Avg(qryMergedData.MaxFirm) AS MaxFirm
, Avg(qryMergedData.MinFirm) AS MinFirm
, Avg(qryMergedData.AvgFirm) AS AvgFirm
, Avg(qryMergedData.StdFirm) AS StdFirm
, (NZ([8],0)+NZ([8 1/2],0)+NZ([9],0)+NZ([9
1/2],0)+NZ([10],0)+NZ([10 1/2],0)) AS High
, Avg(qryMergedData.[8]) AS 8
, Avg(qryMergedData.[8 1/2]) AS [8 1/2]
, Avg(qryMergedData.[9]) AS 9
, Avg(qryMergedData.[9 1/2]) AS [9 1/2]
, Avg(qryMergedData.[10]) AS 10
, Avg(qryMergedData.[10 1/2]) AS [10 1/2]
, Avg(qryMergedData.[11]) AS 11
, Avg(qryMergedData.[11 1/2]) AS [11 1/2]
, Avg(qryMergedData.[12]) AS 12
, Avg(qryMergedData.[13]) AS 13
, Avg(qryMergedData.CullP) AS CullP
, Avg(qryMergedData.BrineP) AS BrineP
, qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData
ON tblLotList.LotNum = qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder
, qryMergedData.RecvDate
, qryMergedData.LotNum
, tblLotList.LName
, qryMergedData.PoolNum
, qryMergedData.Variety
, qryMergedData.Packed
HAVING qryMergedData.Variety=[enter variety]
AND qryMergedData.Packed=False
ORDER BY qryMergedData.RecvDate
, Avg(qryMergedData.[10 1/2]) DESC
, Avg(qryMergedData.[11]) DESC
, Avg(qryMergedData.[11 1/2]) DESC
, Avg(qryMergedData.[12]) DESC
, Avg(qryMergedData.[13]) DESC
, Avg(qryMergedData.CullP) DESC
, Avg(qryMergedData.BrineP) DESC;

KARL DEWEY said:
It seems I was mistaken. Try this --
SELECT qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum,
tblLotList.LName, qryMergedData.PoolNum, qryMergedData.Variety,
Sum(qryMergedData.Bins) AS Bins, Avg(qryMergedData.MaxFirm) AS MaxFirm,
Avg(qryMergedData.MinFirm) AS MinFirm, Avg(qryMergedData.AvgFirm) AS
AvgFirm,
Avg(qryMergedData.StdFirm) AS StdFirm, (NZ([8],0)+NZ([8
1/2],0)+NZ([9],0)+NZ([9 1/2],0)+NZ([10],0)+NZ([10 1/2],0)) AS High,
Avg(qryMergedData.[8]) AS 8, Avg(qryMergedData.[8 1/2]) AS [8 1/2],
Avg(qryMergedData.[9]) AS 9, Avg(qryMergedData.[9 1/2]) AS [9 1/2],
Avg(qryMergedData.[10]) AS 10, Avg(qryMergedData.[10 1/2]) AS [10 1/2],
Avg(qryMergedData.[11]) AS 11, Avg(qryMergedData.[11 1/2]) AS [11 1/2],
Avg(qryMergedData.[12]) AS 12, Avg(qryMergedData.[13]) AS 13,
Avg(qryMergedData.CullP) AS CullP, Avg(qryMergedData.BrineP) AS BrineP,
qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData ON tblLotList.LotNum =
qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum, tblLotList.LName, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.Packed
HAVING (((qryMergedData.Variety)=[enter variety]) AND
((qryMergedData.Packed)=False))
ORDER BY qryMergedData.RecvDate, Avg(qryMergedData.[10 1/2]) DESC ,
Avg(qryMergedData.[11]) DESC , Avg(qryMergedData.[11 1/2]) DESC ,
Avg(qryMergedData.[12]) DESC , Avg(qryMergedData.[13]) DESC ,
Avg(qryMergedData.CullP) DESC , Avg(qryMergedData.BrineP) DESC;

--
KARL DEWEY
Build a little - Test a little


esparzaone said:
Why would the alias need to be different?

KARL DEWEY said:
Avg(qryMergedData.[9]) AS 9
This is a problem as you are using the same field name for an alias.
Gotta
be different name. Also you would need brackets around the alias like
[9].
--
KARL DEWEY
Build a little - Test a little


:

This database is for cherry pressure/sizing/firmness etc. We have a
machine
called firm tech that stores information about random samples. We
take that
information from a csv file and transfer it to a table in access.
This is
where we store all cherry data for a specific grower back to specific
bins.
The query I am working on is our run order. The fields 8 8/12 9 91/2
10
101/2 11 are cherry sizes. The firm tech seperates cherry size
information
according to the sample taken and brings a decimal percent into
access. I
want to add the fields 8 8/12 9 91/2 10 101/2 rows together on
specific
variety, pools, growers. Whatever grower has the largest decimal
percent I
want to be first in my running order. Thank you for helping me =)

SELECT qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum,
tblLotList.LName, qryMergedData.PoolNum, qryMergedData.Variety,
Sum(qryMergedData.Bins) AS Bins, Avg(qryMergedData.MaxFirm) AS
MaxFirm,
Avg(qryMergedData.MinFirm) AS MinFirm, Avg(qryMergedData.AvgFirm) AS
AvgFirm,
Avg(qryMergedData.StdFirm) AS StdFirm, +[8]+[8 1/2]+[9]+[9
1/2]+[10]+[10 1/2]
AS high, Avg(qryMergedData.[8]) AS 8, Avg(qryMergedData.[8 1/2]) AS
[8 1/2],
Avg(qryMergedData.[9]) AS 9, Avg(qryMergedData.[9 1/2]) AS [9 1/2],
Avg(qryMergedData.[10]) AS 10, Avg(qryMergedData.[10 1/2]) AS [10
1/2],
Avg(qryMergedData.[11]) AS 11, Avg(qryMergedData.[11 1/2]) AS [11
1/2],
Avg(qryMergedData.[12]) AS 12, Avg(qryMergedData.[13]) AS 13,
Avg(qryMergedData.CullP) AS CullP, Avg(qryMergedData.BrineP) AS
BrineP,
qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData ON tblLotList.LotNum =
qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum, tblLotList.LName, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.Packed
HAVING (((qryMergedData.Variety)=[enter variety]) AND
((qryMergedData.Packed)=False))
ORDER BY qryMergedData.RecvDate, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10
1/2] DESC
, Avg(qryMergedData.[10 1/2]) DESC , Avg(qryMergedData.[11]) DESC ,
Avg(qryMergedData.[11 1/2]) DESC , Avg(qryMergedData.[12]) DESC ,
Avg(qryMergedData.[13]) DESC , Avg(qryMergedData.CullP) DESC ,
Avg(qryMergedData.BrineP) DESC;


:

Can you post the entire SQL string?

Do you mean that the query is blank (returns no rows) or that the
field is
blank(no numbers). My guess is that one or more of these fields is
NULL, and
when you add anything to a NULL, you get a NULL. Try using the
NZ( )
function, which converts Null values to some other value (in this
case zero):

Lg_Grower: NZ([8], 0) + NZ([8 1/2], 0) + NZ([9], 0) + NZ([9 1/2],
0) + ...

Do you actually have fields named [8], [8 1/2], [9]? This tells me
that
your database is setup more like a spreadsheet than a normallized
database.
These "fields" should actually be values in a single field.

If you need help normalizing your data, post back.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I have multiple Field columns for my query. I inserted a field
to add
certain other fields together. My formula is Lg Grower: +[8]+[8
1/2]+[9]+[9
1/2]+[10]+[10 1/2]
Table is blank. Total is Expression. I can run this query as is
but when I
try to sort descend I get an error message. What I need this to
do is add
the fileds together and descend highest to lowest. If I change
the total to
sum and try to add fields together it also gives me an error
message. Any
ideas?
Thanks
Zenia
Click to expand...
Click to expand...
Click to expand...
 
Not Totally true. As long as you use the full name (TableName.FieldName) in
an expression you can give the expression the same name as the field.

This works:
SELECT FAQ.[fID]*1 AS fID
, FAQ.fPriority
, FAQ.fSubject
, FAQ.fText
FROM FAQ
ORDER BY Faq.[fID]*1;

This fails with a circular reference error:
SELECT [fID]*1 AS fID
, FAQ.fPriority
, FAQ.fSubject
, FAQ.fText
FROM FAQ
ORDER BY Faq.[fID]*1;



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Douglas J. Steele said:
Because Access insists on it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


esparzaone said:
Why would the alias need to be different?

KARL DEWEY said:
Avg(qryMergedData.[9]) AS 9
This is a problem as you are using the same field name for an alias.
Gotta
be different name. Also you would need brackets around the alias like
[9].
--
KARL DEWEY
Build a little - Test a little


:

This database is for cherry pressure/sizing/firmness etc. We have a
machine
called firm tech that stores information about random samples. We
take that
information from a csv file and transfer it to a table in access.
This is
where we store all cherry data for a specific grower back to specific
bins.
The query I am working on is our run order. The fields 8 8/12 9 91/2
10
101/2 11 are cherry sizes. The firm tech seperates cherry size
information
according to the sample taken and brings a decimal percent into
access. I
want to add the fields 8 8/12 9 91/2 10 101/2 rows together on
specific
variety, pools, growers. Whatever grower has the largest decimal
percent I
want to be first in my running order. Thank you for helping me =)

SELECT qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum,
tblLotList.LName, qryMergedData.PoolNum, qryMergedData.Variety,
Sum(qryMergedData.Bins) AS Bins, Avg(qryMergedData.MaxFirm) AS
MaxFirm,
Avg(qryMergedData.MinFirm) AS MinFirm, Avg(qryMergedData.AvgFirm) AS
AvgFirm,
Avg(qryMergedData.StdFirm) AS StdFirm, +[8]+[8 1/2]+[9]+[9
1/2]+[10]+[10 1/2]
AS high, Avg(qryMergedData.[8]) AS 8, Avg(qryMergedData.[8 1/2]) AS [8
1/2],
Avg(qryMergedData.[9]) AS 9, Avg(qryMergedData.[9 1/2]) AS [9 1/2],
Avg(qryMergedData.[10]) AS 10, Avg(qryMergedData.[10 1/2]) AS [10
1/2],
Avg(qryMergedData.[11]) AS 11, Avg(qryMergedData.[11 1/2]) AS [11
1/2],
Avg(qryMergedData.[12]) AS 12, Avg(qryMergedData.[13]) AS 13,
Avg(qryMergedData.CullP) AS CullP, Avg(qryMergedData.BrineP) AS
BrineP,
qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData ON tblLotList.LotNum =
qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum, tblLotList.LName, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.Packed
HAVING (((qryMergedData.Variety)=[enter variety]) AND
((qryMergedData.Packed)=False))
ORDER BY qryMergedData.RecvDate, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10
1/2] DESC
, Avg(qryMergedData.[10 1/2]) DESC , Avg(qryMergedData.[11]) DESC ,
Avg(qryMergedData.[11 1/2]) DESC , Avg(qryMergedData.[12]) DESC ,
Avg(qryMergedData.[13]) DESC , Avg(qryMergedData.CullP) DESC ,
Avg(qryMergedData.BrineP) DESC;


:

Can you post the entire SQL string?

Do you mean that the query is blank (returns no rows) or that the
field is
blank(no numbers). My guess is that one or more of these fields is
NULL, and
when you add anything to a NULL, you get a NULL. Try using the
NZ( )
function, which converts Null values to some other value (in this
case zero):

Lg_Grower: NZ([8], 0) + NZ([8 1/2], 0) + NZ([9], 0) + NZ([9 1/2], 0)
+ ...

Do you actually have fields named [8], [8 1/2], [9]? This tells me
that
your database is setup more like a spreadsheet than a normallized
database.
These "fields" should actually be values in a single field.

If you need help normalizing your data, post back.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I have multiple Field columns for my query. I inserted a field to
add
certain other fields together. My formula is Lg Grower: +[8]+[8
1/2]+[9]+[9
1/2]+[10]+[10 1/2]
Table is blank. Total is Expression. I can run this query as is
but when I
try to sort descend I get an error message. What I need this to
do is add
the fileds together and descend highest to lowest. If I change
the total to
sum and try to add fields together it also gives me an error
message. Any
ideas?
Thanks
Zenia
Click to expand...
Click to expand...
Click to expand...
 
My 2002 SP3 allows it. I had thought it could not.
--
KARL DEWEY
Build a little - Test a little


Dale Fye said:
Karl,

What do you mean you were mistaken? In my 2003 version of Access, I am
unable to create an alias that looks like the field in the aggregation. Has
something changed in 2007?

Actually, I'll be surprised if this SQL runs, since the computed field
(High) does not have and aggregate function and is not included in the
GroupBy clause. Should that line possibly be summed, or Averaged as well.
Based on his comment about the sorting of these results, I think is OrderBy
clause might need to be:

ORDER BY Sum(NZ([8],0)+NZ([8 1/2],0)+NZ([9],0)+NZ([9
1/2],0)+NZ([10],0)+NZ([10 1/2],0)) DESC

Dale

SELECT qryMergedData.RunOrder
, qryMergedData.RecvDate
, qryMergedData.LotNum
, tblLotList.LName
, qryMergedData.PoolNum
, qryMergedData.Variety
, Sum(qryMergedData.Bins) AS Bins
, Avg(qryMergedData.MaxFirm) AS MaxFirm
, Avg(qryMergedData.MinFirm) AS MinFirm
, Avg(qryMergedData.AvgFirm) AS AvgFirm
, Avg(qryMergedData.StdFirm) AS StdFirm
, (NZ([8],0)+NZ([8 1/2],0)+NZ([9],0)+NZ([9
1/2],0)+NZ([10],0)+NZ([10 1/2],0)) AS High
, Avg(qryMergedData.[8]) AS 8
, Avg(qryMergedData.[8 1/2]) AS [8 1/2]
, Avg(qryMergedData.[9]) AS 9
, Avg(qryMergedData.[9 1/2]) AS [9 1/2]
, Avg(qryMergedData.[10]) AS 10
, Avg(qryMergedData.[10 1/2]) AS [10 1/2]
, Avg(qryMergedData.[11]) AS 11
, Avg(qryMergedData.[11 1/2]) AS [11 1/2]
, Avg(qryMergedData.[12]) AS 12
, Avg(qryMergedData.[13]) AS 13
, Avg(qryMergedData.CullP) AS CullP
, Avg(qryMergedData.BrineP) AS BrineP
, qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData
ON tblLotList.LotNum = qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder
, qryMergedData.RecvDate
, qryMergedData.LotNum
, tblLotList.LName
, qryMergedData.PoolNum
, qryMergedData.Variety
, qryMergedData.Packed
HAVING qryMergedData.Variety=[enter variety]
AND qryMergedData.Packed=False
ORDER BY qryMergedData.RecvDate
, Avg(qryMergedData.[10 1/2]) DESC
, Avg(qryMergedData.[11]) DESC
, Avg(qryMergedData.[11 1/2]) DESC
, Avg(qryMergedData.[12]) DESC
, Avg(qryMergedData.[13]) DESC
, Avg(qryMergedData.CullP) DESC
, Avg(qryMergedData.BrineP) DESC;

KARL DEWEY said:
It seems I was mistaken. Try this --
SELECT qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum,
tblLotList.LName, qryMergedData.PoolNum, qryMergedData.Variety,
Sum(qryMergedData.Bins) AS Bins, Avg(qryMergedData.MaxFirm) AS MaxFirm,
Avg(qryMergedData.MinFirm) AS MinFirm, Avg(qryMergedData.AvgFirm) AS
AvgFirm,
Avg(qryMergedData.StdFirm) AS StdFirm, (NZ([8],0)+NZ([8
1/2],0)+NZ([9],0)+NZ([9 1/2],0)+NZ([10],0)+NZ([10 1/2],0)) AS High,
Avg(qryMergedData.[8]) AS 8, Avg(qryMergedData.[8 1/2]) AS [8 1/2],
Avg(qryMergedData.[9]) AS 9, Avg(qryMergedData.[9 1/2]) AS [9 1/2],
Avg(qryMergedData.[10]) AS 10, Avg(qryMergedData.[10 1/2]) AS [10 1/2],
Avg(qryMergedData.[11]) AS 11, Avg(qryMergedData.[11 1/2]) AS [11 1/2],
Avg(qryMergedData.[12]) AS 12, Avg(qryMergedData.[13]) AS 13,
Avg(qryMergedData.CullP) AS CullP, Avg(qryMergedData.BrineP) AS BrineP,
qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData ON tblLotList.LotNum =
qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum, tblLotList.LName, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.Packed
HAVING (((qryMergedData.Variety)=[enter variety]) AND
((qryMergedData.Packed)=False))
ORDER BY qryMergedData.RecvDate, Avg(qryMergedData.[10 1/2]) DESC ,
Avg(qryMergedData.[11]) DESC , Avg(qryMergedData.[11 1/2]) DESC ,
Avg(qryMergedData.[12]) DESC , Avg(qryMergedData.[13]) DESC ,
Avg(qryMergedData.CullP) DESC , Avg(qryMergedData.BrineP) DESC;

--
KARL DEWEY
Build a little - Test a little


esparzaone said:
Why would the alias need to be different?

:

Avg(qryMergedData.[9]) AS 9
This is a problem as you are using the same field name for an alias.
Gotta
be different name. Also you would need brackets around the alias like
[9].
--
KARL DEWEY
Build a little - Test a little


:

This database is for cherry pressure/sizing/firmness etc. We have a
machine
called firm tech that stores information about random samples. We
take that
information from a csv file and transfer it to a table in access.
This is
where we store all cherry data for a specific grower back to specific
bins.
The query I am working on is our run order. The fields 8 8/12 9 91/2
10
101/2 11 are cherry sizes. The firm tech seperates cherry size
information
according to the sample taken and brings a decimal percent into
access. I
want to add the fields 8 8/12 9 91/2 10 101/2 rows together on
specific
variety, pools, growers. Whatever grower has the largest decimal
percent I
want to be first in my running order. Thank you for helping me =)

SELECT qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum,
tblLotList.LName, qryMergedData.PoolNum, qryMergedData.Variety,
Sum(qryMergedData.Bins) AS Bins, Avg(qryMergedData.MaxFirm) AS
MaxFirm,
Avg(qryMergedData.MinFirm) AS MinFirm, Avg(qryMergedData.AvgFirm) AS
AvgFirm,
Avg(qryMergedData.StdFirm) AS StdFirm, +[8]+[8 1/2]+[9]+[9
1/2]+[10]+[10 1/2]
AS high, Avg(qryMergedData.[8]) AS 8, Avg(qryMergedData.[8 1/2]) AS
[8 1/2],
Avg(qryMergedData.[9]) AS 9, Avg(qryMergedData.[9 1/2]) AS [9 1/2],
Avg(qryMergedData.[10]) AS 10, Avg(qryMergedData.[10 1/2]) AS [10
1/2],
Avg(qryMergedData.[11]) AS 11, Avg(qryMergedData.[11 1/2]) AS [11
1/2],
Avg(qryMergedData.[12]) AS 12, Avg(qryMergedData.[13]) AS 13,
Avg(qryMergedData.CullP) AS CullP, Avg(qryMergedData.BrineP) AS
BrineP,
qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData ON tblLotList.LotNum =
qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum, tblLotList.LName, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.Packed
HAVING (((qryMergedData.Variety)=[enter variety]) AND
((qryMergedData.Packed)=False))
ORDER BY qryMergedData.RecvDate, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10
1/2] DESC
, Avg(qryMergedData.[10 1/2]) DESC , Avg(qryMergedData.[11]) DESC ,
Avg(qryMergedData.[11 1/2]) DESC , Avg(qryMergedData.[12]) DESC ,
Avg(qryMergedData.[13]) DESC , Avg(qryMergedData.CullP) DESC ,
Avg(qryMergedData.BrineP) DESC;


:

Can you post the entire SQL string?

Do you mean that the query is blank (returns no rows) or that the
field is
blank(no numbers). My guess is that one or more of these fields is
NULL, and
when you add anything to a NULL, you get a NULL. Try using the
NZ( )
function, which converts Null values to some other value (in this
case zero):

Lg_Grower: NZ([8], 0) + NZ([8 1/2], 0) + NZ([9], 0) + NZ([9 1/2],
0) + ...

Do you actually have fields named [8], [8 1/2], [9]? This tells me
that
your database is setup more like a spreadsheet than a normallized
database.
These "fields" should actually be values in a single field.

If you need help normalizing your data, post back.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I have multiple Field columns for my query. I inserted a field
to add
certain other fields together. My formula is Lg Grower: +[8]+[8
1/2]+[9]+[9
1/2]+[10]+[10 1/2]
Table is blank. Total is Expression. I can run this query as is
but when I
try to sort descend I get an error message. What I need this to
do is add
the fileds together and descend highest to lowest. If I change
the total to
sum and try to add fields together it also gives me an error
message. Any
ideas?
Thanks
Zenia
Click to expand...
Click to expand...
Click to expand...
 
I love it when I learn something new.

Have a wonderful weekend.

John Spencer said:
Not Totally true. As long as you use the full name (TableName.FieldName)
in an expression you can give the expression the same name as the field.

This works:
SELECT FAQ.[fID]*1 AS fID
, FAQ.fPriority
, FAQ.fSubject
, FAQ.fText
FROM FAQ
ORDER BY Faq.[fID]*1;

This fails with a circular reference error:
SELECT [fID]*1 AS fID
, FAQ.fPriority
, FAQ.fSubject
, FAQ.fText
FROM FAQ
ORDER BY Faq.[fID]*1;



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Douglas J. Steele said:
Because Access insists on it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


esparzaone said:
Why would the alias need to be different?

:

Avg(qryMergedData.[9]) AS 9
This is a problem as you are using the same field name for an alias.
Gotta
be different name. Also you would need brackets around the alias like
[9].
--
KARL DEWEY
Build a little - Test a little


:

This database is for cherry pressure/sizing/firmness etc. We have a
machine
called firm tech that stores information about random samples. We
take that
information from a csv file and transfer it to a table in access.
This is
where we store all cherry data for a specific grower back to specific
bins.
The query I am working on is our run order. The fields 8 8/12 9 91/2
10
101/2 11 are cherry sizes. The firm tech seperates cherry size
information
according to the sample taken and brings a decimal percent into
access. I
want to add the fields 8 8/12 9 91/2 10 101/2 rows together on
specific
variety, pools, growers. Whatever grower has the largest decimal
percent I
want to be first in my running order. Thank you for helping me =)

SELECT qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum,
tblLotList.LName, qryMergedData.PoolNum, qryMergedData.Variety,
Sum(qryMergedData.Bins) AS Bins, Avg(qryMergedData.MaxFirm) AS
MaxFirm,
Avg(qryMergedData.MinFirm) AS MinFirm, Avg(qryMergedData.AvgFirm) AS
AvgFirm,
Avg(qryMergedData.StdFirm) AS StdFirm, +[8]+[8 1/2]+[9]+[9
1/2]+[10]+[10 1/2]
AS high, Avg(qryMergedData.[8]) AS 8, Avg(qryMergedData.[8 1/2]) AS
[8 1/2],
Avg(qryMergedData.[9]) AS 9, Avg(qryMergedData.[9 1/2]) AS [9 1/2],
Avg(qryMergedData.[10]) AS 10, Avg(qryMergedData.[10 1/2]) AS [10
1/2],
Avg(qryMergedData.[11]) AS 11, Avg(qryMergedData.[11 1/2]) AS [11
1/2],
Avg(qryMergedData.[12]) AS 12, Avg(qryMergedData.[13]) AS 13,
Avg(qryMergedData.CullP) AS CullP, Avg(qryMergedData.BrineP) AS
BrineP,
qryMergedData.Packed
FROM tblLotList RIGHT JOIN qryMergedData ON tblLotList.LotNum =
qryMergedData.LotNum
GROUP BY qryMergedData.RunOrder, qryMergedData.RecvDate,
qryMergedData.LotNum, tblLotList.LName, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.Packed
HAVING (((qryMergedData.Variety)=[enter variety]) AND
((qryMergedData.Packed)=False))
ORDER BY qryMergedData.RecvDate, +[8]+[8 1/2]+[9]+[9 1/2]+[10]+[10
1/2] DESC
, Avg(qryMergedData.[10 1/2]) DESC , Avg(qryMergedData.[11]) DESC ,
Avg(qryMergedData.[11 1/2]) DESC , Avg(qryMergedData.[12]) DESC ,
Avg(qryMergedData.[13]) DESC , Avg(qryMergedData.CullP) DESC ,
Avg(qryMergedData.BrineP) DESC;


:

Can you post the entire SQL string?

Do you mean that the query is blank (returns no rows) or that the
field is
blank(no numbers). My guess is that one or more of these fields is
NULL, and
when you add anything to a NULL, you get a NULL. Try using the
NZ( )
function, which converts Null values to some other value (in this
case zero):

Lg_Grower: NZ([8], 0) + NZ([8 1/2], 0) + NZ([9], 0) + NZ([9 1/2],
0) + ...

Do you actually have fields named [8], [8 1/2], [9]? This tells me
that
your database is setup more like a spreadsheet than a normallized
database.
These "fields" should actually be values in a single field.

If you need help normalizing your data, post back.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I have multiple Field columns for my query. I inserted a field
to add
certain other fields together. My formula is Lg Grower: +[8]+[8
1/2]+[9]+[9
1/2]+[10]+[10 1/2]
Table is blank. Total is Expression. I can run this query as is
but when I
try to sort descend I get an error message. What I need this to
do is add
the fileds together and descend highest to lowest. If I change
the total to
sum and try to add fields together it also gives me an error
message. Any
ideas?
Thanks
Zenia
Click to expand...
Click to expand...
Click to expand...
 
Back
Top