update query

[Maggs]

Hi I Have a table as below

Term Round SourceID TotalEncounters TotCount
1 ____45 ____1 ___1200 ____________ 9
1 ____45 ____2 ___1200 ___________ 10
2 ____45 ____1 ____30 ____________ 19
2 ____45 ____2 ____30 _____________4
3 ____45 ____1 ___316 ____________ 10
3 ____45 ____2 ___316 _____________5

how do I write an update query that updates Term 3 only with:
the TotalEncounters of Term 1 added to Term 3 grouped by sourceID and TotCount of
Term1 added to Term 3 grouped by sourceID. I want this query to run everytime the
table gets populated.

thanks in advance
Maggs

 

[plog]

I'm not exactly following what you want the final result to be. Using the sample data you've already provided could you show what the result would be?

 

[Maggs]

Hi plog the final result go from:

Term Round SourceID TotalEncounters TotCount
1 ____45 ____1 ___1200 ____________ 9
1 ____45 ____2 ___1200 ___________ 10
2 ____45 ____1 ____30 ____________ 19
2 ____45 ____2 ____30 _____________4
3 ____45 ____1 ___316 ____________ 10
3 ____45 ____2 ___316 _____________5

To:

Term Round SourceID TotalEncounters TotCount
1 ____45 ____1 ___1200 ____________ 9
1 ____45 ____2 ___1200 ___________ 10
2 ____45 ____1 ____30 ____________ 19
2 ____45 ____2 ____30 _____________4
3 ____45 ____1 ___1516 ____________19
3 ____45 ____2 ___1516 _____________15

1516 = 1200 + 316, 19 = 9 + 10, 10 = 10 + 5

thanks
Maggs

 

[plog]

What happens when the table gets populated? Does the old data get cleaned out? Generally you don't store calculated data, you calculate it when you need it. If you could explain the whole process a little more I can help you with a solution.

As it is now, what you want to do is going to require a few sub-queries and a temporary table--the process is complex and ugly. It may be simpler to just create a query to generate the data that you want when you want.

 

[Maggs]

Hi Plog
The old data for term 3 will get cleaned out and replaced.
This table was created from a query and then parts of it populate a graph in crystal reports.
This table is used in several different reports.
The query that creates this table is run at the start of each round so populates the data for the current round.

here is the code the populates this table:

SELECT Assistance.Term, [SysRound]![Round] AS Round, Assistance.AreaID, Sum(Assistance.Encounters) AS TotEncounters, Sum(Assistance.AssistanceCount) AS TotCount INTO AssistanceTot
FROM Assistance, SysRound
WHERE (((Assistance.Round)<[SysRound]![Round]))
GROUP BY Assistance.Term, [SysRound]![Round], Assistance.SourceID;

Could this query that made the table be easily modified? I have just taken over this project and I have no idea why this table was created instead of a query being run as needed (very little documentation). So don't know if there was a reason for creating this table instead of the query as still in the figuring out stage. I hope this info helps.

thanks
Maggs

 

[Maggs]

query instead of a table

Hi
After looking at the code and the report I have found out that there is no reason why this table could not be a query.
so does anyone have any ideas on how to pull the required data as a query.

thanks
Maggs

 

[plog]

The problem isn't query/table, its getting Term 3 to hold the correct data since iits current value is based on its past value plus another Term's value. The key is rolling Term 3 data back to the very beginning and understanding how it gets initialized with data.

Using your very first post you had this:

Term Round SourceID TotalEncounters TotCount
3 ____45 ____1 ___316 ____________ 10
3 ____45 ____2 ___316 _____________5

How did that data get there? In the next pass, its going to hold that data plus Term 1 data--doing that is easy. The main problem is starting off Term 3 correctly.

 

[Maggs]

how did it get there

Hi plog

This table was populated from table called Assistance using this query:

SELECT Assistance.Term, [SysRound]![Round] AS Round, Assistance.AreaID, Sum(Assistance.Encounters) AS TotEncounters, Sum(Assistance.AssistanceCount) AS TotCount INTO AssistanceTot
FROM Assistance, SysRound
WHERE (((Assistance.Round)<[SysRound]![Round]))
GROUP BY Assistance.Term, [SysRound]![Round], Assistance.SourceID;

Is this the info you are looking for?

Thanks
Ian

 

[plog]

Not really. In your first post you say Term 3 is calculated every time some table gets populated. The method to do this involved adding the current value of Term 3 to the new value of Term1. How does that initial value for Term 3 get there?

You've made it sound like its an iterative process--> have a value for Term 3, append more data, recalculate Term 3, append more data, recaluclate Term3, etc. etc.

 

[Maggs]

not appended

Hi Plog

At the moment the table in the first post is dropped and then recreated by using the query mentioned in one of the other posts, with the terms being separate as displayed. Data is not appended.

The combining of Term1 into Term 3 data is just done once, that is after the table has been recreated.

thanks
Maggs

 

[plog]

Ok if this is the case, here's what you need to do:

Create a query called 'TotalCounts_sub1' using this SQL:

SELECT 3 AS Term, YourTableName.Round, YourTableName.SourceID, YourTableName.TotalEncounters, YourTableName.TotCount
FROM YourTableName
WHERE (((YourTableName.Term)=1));

Create a query called 'TotalCounts_sub2' using this SQL:

SELECT *
FROM YourTableName
UNION ALL SELECT * FROM TotalCounts_sub1;

Create a query called 'TotalCounts' using this SQL:

SELECT totalCounts_sub2.Term, totalCounts_sub2.Round, totalCounts_sub2.SourceID, Sum(totalCounts_sub2.TotalEncounters) AS SumTotalEncounters, Sum(totalCounts_sub2.TotCount) AS SumTotCount
FROM totalCounts_sub2
GROUP BY totalCounts_sub2.Term, totalCounts_sub2.Round, totalCounts_sub2.SourceID;

In all of those queries, replace 'YourTableNameHere' with the name of your actual table. When you run 'TotalCounts' it should hold the data you wanted based on the initial data.

 

[Maggs]

Thanks

Hi Plog
Thanks a lot, the reports are populating with the correct data!!
This is a short term fix. You did say that this was a messy way of doing things. How would you recommend that I proceed to do it in a "less messy" way.

again thanks, I appreciate your help.

Maggs

 

[plog]

I think the solution I gave is the best solution to this. My messy/inefficient comment was based on an incorrect understanding of how the process worked.