Can't data entry when form is based on a avg. query?

[Guest]

I am linking a table from a cvs file that gives me how the pressure and size
of cherries tested. I built a query that from the lot number of the cvs link
will read the lot number of the Lot table to provide more info such as
orchard name and location. I built a query based on these 2 tables and
called it RunOrder. It gives me all the pertinent information I need to
decide what I am going to run for a specific order that needs to be filled.
I built a form from this query.

My only problem is I want to use the orderrun column to data input numbers
such as 1,2,3. Everytime I insert a number in this field it autofills all
the way down so each record can't have an idividual number. Here is my query

SELECT [Data query].LotNum, [Data query].tblData.TicketNum, Sum([Data
query].Bins) AS SumOfBins, First([Data query].RecvDate) AS FirstOfRecvDate,
Avg([Data query].CullP) AS AvgOfCullP, Avg([Data query].BrineP) AS
AvgOfBrineP, Avg([Data query].P080) AS AvgOfP080, Avg([Data query].P085) AS
AvgOfP085, Avg([Data query].P090) AS AvgOfP090, Avg([Data query].P095) AS
AvgOfP095, Avg([Data query].P100) AS AvgOfP100, Avg([Data query].P105) AS
AvgOfP105, Avg([Data query].P110) AS AvgOfP110, Avg([Data query].P115) AS
AvgOfP115, Avg([Data query].P120) AS AvgOfP120, Avg([Data query].P130) AS
AvgOfP130, [Data query].PoolNum, [Data query].Variety
FROM tblLotList INNER JOIN [Data query] ON tblLotList.LotNum = [Data
query].LotNum
GROUP BY [Data query].LotNum, [Data query].tblData.TicketNum, [Data
query].PoolNum, [Data query].Variety
HAVING ((([Data query].Variety)=[Enter Variety]));

 

[Guest]

Some errors when Typing 1) I built a query named it data query then linked
lot table. And I need runorder column to have a number per row.

 

[John Vinson]

I am linking a table from a cvs file that gives me how the pressure and size
of cherries tested. I built a query that from the lot number of the cvs link
will read the lot number of the Lot table to provide more info such as
orchard name and location. I built a query based on these 2 tables and
called it RunOrder. It gives me all the pertinent information I need to
decide what I am going to run for a specific order that needs to be filled.
I built a form from this query.

No Group By query is *ever* updateable; in this case it cannot be
updateable, because each "row" in this query corresponds to several
rows in the table. Access cannot tell which row or rows you want
updated.

You're getting the "fill down" because you're using an unbound
textbox; there is actually only ONE textbox, displayed repeatedly on
each row. If it's not bound to a table field, setting a value in the
textbox sets the same value in all the instances of the textbox -
because it's really only one textbox.

You'll need to come up with some other technique, such as basing an
Append query on your averages query to populate an actual table.


John W. Vinson[MVP]

 

[Guest]

So are you saying to build a table with this query attached to it and then
build a form from it. And if yes, then I have a table with all the
information except obviously avg and sum . Could I build a form based off
the table and sum and avg in specific boxes(would this be easier) or no or is
this even possible. What do you think?

 

[John Vinson]

So are you saying to build a table with this query attached to it and then
build a form from it. And if yes, then I have a table with all the
information except obviously avg and sum . Could I build a form based off
the table and sum and avg in specific boxes(would this be easier) or no or is
this even possible. What do you think?

You can certainly calculate sums and averages on a Form, a couple of
different ways. Simplest might be to display individual records in the
body of a continuous Form, and put textboxes in the Form Footer with
control sources such as

=Avg([fieldname])

or

=Sum([fieldname])

John W. Vinson[MVP]