annoying problem with a query

Here's my problem... I've created a query to pull the data I need but it is not updateable. However, the whole point of the query was to update a table with it... so I have a second query that pulls the data from the first and attempts to update the table.

All I can get it to do is error out and tell me I must have an updateable query. But I'm not trying to update the query, I'm trying to update the updateable table.

first query is:
SELECT
ROUND(Avg((report.onin+report.offin)/IIf((report.son+report.soff)=0,1,(report.son+repor t.soff))),2) AS ptotal,
EIDCrossRef.EID
FROM (report INNER JOIN agent ON report.aid = agent.AGENT_ID) INNER JOIN EIDCrossRef ON agent.SOCIAL_SECURITY_NO = EIDCrossRef.SSNNumber
WHERE report.date Between [start_date] And [end_date]
GROUP BY EIDCrossRef.EID;

update query is:
UPDATE
cmgscoresdvr_cmgresults_local cmgres
SET
cmgres.ADH = (
SELECT ptotal
FROM qryReportAvgsByDate
WHERE cmgres.EID=qryReportAvgsByDate.eid)
WHERE
cmgres.YEARMONTH=[YearMonth];

Any thoughts? what can I do?

I'll assume the first query brings back correct info

Try this
Second query

UPDATE
cmgscoresdvr_cmgresults_local AS cmgres
INNER JOIN qryReportAvgsByDate
ON cmgres.EID = qryReportAvgsByDate.eid
SET cmgres.ADH = qryReportAvgsByDate.ptotal
Where cmgres.YEARMONTH=[YearMonth];


S-

You are correct, the first query does work correctly.

When I saw yours I thought "Why didn't I think of that" but it's not working either. Same problem.

It asks me for the start_date and end_date but then errors out. Never gets far enough to ask me for YearMonth

Posting your question more than once gets you fragmented answers

This was the first time I've used this forum, I wasn't sure which would be better to put it in. I started in the Access forum, then saw the SQL forum.

Anyway, you answered in the other forum:



This is not a server app though. I'm trying to run this directly in Access. It is to help automate someones job so that she doesn't have to do a bunch of manual calculations and entries every month.

If this were a server app (ASP or JSP) I could work around it... with simpler SQL (which would be a good thing for me!) but I need this to run directly in Access.

Thanks

I can convert the first query from a select to a make table query. Then I can rewrite the update query to call from the table instead... This will work, but it means that my user has to go through 2 steps and in the correct order. Since it is just one user, and she only sits 50 feet away from me, it's not that big of a deal, but I would still like to figure it out so that she only has to run one query and it takes care of itself.

If anyone knows how to make Access understand that I'm not trying to update the non-updateable query, I'd still love to know that.

Thanks

You have two options.

1. Create a form that has 3 text fields on them that hold Beg_Date, End_Date, YearMonth and link you queries to this form for the parameter values and have a button on the form that executes the second query

DoCmd.OpenQuery "queryName", acViewNormal


2. Write one query the combines both actions together (it is possible)

We can help you with either option, but give the first option a try.

s-