can't input into form

[Guest]

I have a form based on a query... I was able to enter info into the form and
then I changed some of this and now I can't. Form set to can edit etc.
Thought I remember reading something about "expression" in the qry but now I
can't find it
The numbers are category numbers ie [260] that contain Currency info. Each
Plan has different prices for categories but the categories need to be added
for a ttl and the ttl of some make up just the bldg. Then that needs to be
devided by the sqft.

Is there some other way to do this?

SELECT tblPlan.PlanID, tblPlan.SQFT, tblPlan.MTGRms, tblPlan.Seating,
tblPlan.LastEdited, tblPlan.[260], tblPlan.[280], tblPlan.[320],
tblPlan.[360], tblPlan.[380], tblPlan.[400], tblPlan.[420], tblPlan.[450],
tblPlan.[500], tblPlan.[520], tblPlan.[540], tblPlan.[560], tblPlan.[580],
tblPlan.[600], tblPlan.[610], tblPlan.[620], tblPlan.[630], tblPlan.[660],
tblPlan.[670], tblPlan.[680], tblPlan.[700], tblPlan.[720], tblPlan.Examples,
tblPlan.Notes,
Sum([260]+[280]+[320]+[360]+[380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630]+[660]+[670]+[680]+[700]+[720])
AS Total,
Sum([380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630]) AS EstBuildCost, CCur([EstBuildCost]/[SQFT]) AS SqFTPrice
FROM tblPlan
GROUP BY tblPlan.PlanID, tblPlan.SQFT, tblPlan.MTGRms, tblPlan.Seating,
tblPlan.LastEdited, tblPlan.[260], tblPlan.[280], tblPlan.[320],
tblPlan.[360], tblPlan.[380], tblPlan.[400], tblPlan.[420], tblPlan.[450],
tblPlan.[500], tblPlan.[520], tblPlan.[540], tblPlan.[560], tblPlan.[580],
tblPlan.[600], tblPlan.[610], tblPlan.[620], tblPlan.[630], tblPlan.[660],
tblPlan.[670], tblPlan.[680], tblPlan.[700], tblPlan.[720], tblPlan.Examples,
tblPlan.Notes;

Thanks

 

[Allen Browne]

In query design view, you pressed the Total button on the toolbar, so the
query grid has a Total row.

You cannot update a query that performs any grouping, since there is no
single record to write the value to.

 

[Guest]

Thanks I removed the grouping changed Sum on the 2 to CCur which when I
looked back at the Total row it had changed to Expression on all rows... it
works now. The only question is WHY? I don't even know what the changes I
made mean I just guessed. (I know that CCur Returns an expression that has
been converted to a Variant of subtype Currency) But I sure wish I knew WHY
it works...
Thanks Again!
--
Remember: There are suppose to be no "dumb" questions!

In query design view, you pressed the Total button on the toolbar, so the
query grid has a Total row.

You cannot update a query that performs any grouping, since there is no
single record to write the value to.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form based on a query... I was able to enter info into the form
and
then I changed some of this and now I can't. Form set to can edit etc.
Thought I remember reading something about "expression" in the qry but now
I
can't find it
The numbers are category numbers ie [260] that contain Currency info. Each
Plan has different prices for categories but the categories need to be
added
for a ttl and the ttl of some make up just the bldg. Then that needs to be
devided by the sqft.

Is there some other way to do this?

SELECT tblPlan.PlanID, tblPlan.SQFT, tblPlan.MTGRms, tblPlan.Seating,
tblPlan.LastEdited, tblPlan.[260], tblPlan.[280], tblPlan.[320],
tblPlan.[360], tblPlan.[380], tblPlan.[400], tblPlan.[420], tblPlan.[450],
tblPlan.[500], tblPlan.[520], tblPlan.[540], tblPlan.[560], tblPlan.[580],
tblPlan.[600], tblPlan.[610], tblPlan.[620], tblPlan.[630], tblPlan.[660],
tblPlan.[670], tblPlan.[680], tblPlan.[700], tblPlan.[720],
tblPlan.Examples,
tblPlan.Notes,
Sum([260]+[280]+[320]+[360]+[380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630]+[660]+[670]+[680]+[700]+[720])
AS Total,
Sum([380]+[400]+[420]+[450]+[500]+[520]+[540]+[560]+[580]+[600]+[610]+[620]+[630])
AS EstBuildCost, CCur([EstBuildCost]/[SQFT]) AS SqFTPrice
FROM tblPlan
GROUP BY tblPlan.PlanID, tblPlan.SQFT, tblPlan.MTGRms, tblPlan.Seating,
tblPlan.LastEdited, tblPlan.[260], tblPlan.[280], tblPlan.[320],
tblPlan.[360], tblPlan.[380], tblPlan.[400], tblPlan.[420], tblPlan.[450],
tblPlan.[500], tblPlan.[520], tblPlan.[540], tblPlan.[560], tblPlan.[580],
tblPlan.[600], tblPlan.[610], tblPlan.[620], tblPlan.[630], tblPlan.[660],
tblPlan.[670], tblPlan.[680], tblPlan.[700], tblPlan.[720],
tblPlan.Examples,
tblPlan.Notes;

 

[Rick Brandt]

Thanks I removed the grouping changed Sum on the 2 to CCur which when
I looked back at the Total row it had changed to Expression on all
rows... it works now. The only question is WHY? [snip]

As Allen stated...you cannot update a query that has "View - Totals" turned on.
That immediately causes all rows in the output to be based on aggregations of
the actual data rows. The fact that using Group By on every field yields output
identical to not having "View - Totals" turned on is irrelevant.

Imagine an Orders table and you create a Totals query like...

SELECT CustomerID, Sum(FreightCharges) as Freight
FROM Orders
GROUP BY CustomerID

In the output you see one row with...

CustomerID Freight
123456 250.00

The figure 250.00 might be based on one order or a thousand orders. It should
be obvious why that value cannot be edited as it does not represent data in any
single row, but rather an aggregation of anywhere from one row to (a whole lot)
of rows.

If you create a Totals query with GroupBy on every field you will see one row
for every row in the source table and even though no aggregation is being done
Access still sees that the query is a Totals query and does not allow editing.

I always have felt that switching to a Totals query should have been done by
changint the query "Type" in the menu instead of by using the confusing "View"
menu. The latter does not adequately convey the fact that you are fundamentally
changing how the query works. Newbies think that all it does is show/hide a row
in the query designer much as "View - Tables" does.