Updateable Query

[Newbie]

I have a table - tblSummary - with four fields:
JobNo-----PrimaryKey
Sales
Costs
Retention

I have a query - qSales - that has two fields:
JobNo
TotalSales

I want to update tblSummary with the TotalSales figure from qSales.
I have tried an update query but it comes back with the message that I need
to use an updateable query???
The JobNo only appears in both sides once.

If I put the results of the query into a table and then use the table to
update it works fine eg

UPDATE tblSales INNER JOIN tblProjectSummary ON Sales.JobNo =
tblProjectSummary.JobNo SET tblProjectSummary.Sales = [TotalSales];

How can I do this without having to create the Sales table first i.e.
updating the tblSummary straight from the qSales query

Thanks

 

[Jeff Boyce]

Sales ... TotalSales ... qSales

We're not there. We don't know where your data is coming from, nor what
form it is in. Post the SQL of your query to give us a bit more of a clue.

So, why both with a "summary" table if you have "detail" records. You can
use queries to derive that data any time you need it, if you have detail
records. Besides, if you summarize, load the summary table, then find/fix
an error in the details, you have to re-summarize and re-load the summary
table! Queries summarizing detail records are much easier.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

You could try use something like:

UPDATE tblSummary
SET Sales = DSum("TotalSales","qSales","JobNo=""" & JobNo & """");

If JobNo is numeric, try:
UPDATE tblSummary
SET Sales = DSum("TotalSales","qSales","JobNo=" & JobNo);

I trust you know why you want to store a calculated value....?

 

[Newbie]

The summary table is needed because i need to include all types of query
results against a jobno - it probably can all be done in one query with
subqueries etc but for now I need a table and then i can see what is going
on

Original job no for eg is HF000A6078 and HF000B6078. I want a summary of
HF00006078 hence the function in the query.
Here is the SQL for ProjectSales

SELECT dbo_Ccs_Quotes.[Ccs_Quo_Ref_Number#1],
Sum(dbo_Ccs_S_Invoice_Line.Ccs_Sil_Net_Reten) AS SumOfCcs_Sil_Net_Reten,
PosAlpha([Ccs_Quo_Ref_Number#1]) AS JobNo
FROM dbo_Ccs_Quotes INNER JOIN dbo_Ccs_S_Invoice_Line ON
dbo_Ccs_Quotes.Ccs_Quo_Quote_Number =
dbo_Ccs_S_Invoice_Line.Ccs_Sil_Quote_Number
GROUP BY dbo_Ccs_Quotes.[Ccs_Quo_Ref_Number#1],
PosAlpha([Ccs_Quo_Ref_Number#1]);

This is then used in the qSales query like so:

SELECT ProjectSales.JobNo, Sum(ProjectSales.SumOfCcs_Sil_Net_Reten) AS
TotalSales
FROM ProjectSales
GROUP BY ProjectSales.JobNo;

tblProjectSummary is simply a table containing all the amended job nos eg
HF00006078

Hope this helps

Sales ... TotalSales ... qSales

We're not there. We don't know where your data is coming from, nor what
form it is in. Post the SQL of your query to give us a bit more of a
clue.

So, why both with a "summary" table if you have "detail" records. You can
use queries to derive that data any time you need it, if you have detail
records. Besides, if you summarize, load the summary table, then find/fix
an error in the details, you have to re-summarize and re-load the summary
table! Queries summarizing detail records are much easier.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table - tblSummary - with four fields:
JobNo-----PrimaryKey
Sales
Costs
Retention

I have a query - qSales - that has two fields:
JobNo
TotalSales

I want to update tblSummary with the TotalSales figure from qSales.
I have tried an update query but it comes back with the message that I
need to use an updateable query???
The JobNo only appears in both sides once.

If I put the results of the query into a table and then use the table to
update it works fine eg

UPDATE tblSales INNER JOIN tblProjectSummary ON Sales.JobNo =
tblProjectSummary.JobNo SET tblProjectSummary.Sales = [TotalSales];

How can I do this without having to create the Sales table first i.e.
updating the tblSummary straight from the qSales query

Thanks