Query Calculation Saved to Table

[usafguy]

I really need help with this one. I have several places within my database that have calculations within a query. Now, I have to query on that calculation result in reports. I am trying to create an update query but, this is my first time doing this. Let me explain:

There are 4 queries and one table. Each of the 4 queries have a addition calculation that I would like to report to the table.

Query 1: Upgrade 2T2 5lvlQry
Query 2: Upgrade 2T2 7lvlQry
Query 3: Upgrade 2T0 5lvlQry
Query 4: Upgrade 2T0 7lvlQry
Table : UpgradeMain

All of the queries have a column labeled "Core Tasks". Below is an example of the code that doesn't work. Does anyone have any ideas on the best way to write the code?

UPDATE (UpgradeMain INNER JOIN [Upgrade 2T2 5lvlQry] ON UpgradeMain.SSN=[Upgrade 2T2 5lvlQry].SSN)
INNER JOIN [Upgrade 2T2 7lvlQry] ON UpgradeMain.SSN=[Upgrade 2T2 7lvlQry].SSN SET
[Upgrade 2T2 5lvlQry].[Core Tasks] = UpgradeMain.[Core Tasks],
[Upgrade 2T2 7lvlQry].[Core Tasks] = UpgradeMain.[Core Tasks],
[Upgrade 2T0 5lvlQry].[Core Tasks] = UpgradeMain.[Core Tasks],
[Upgrade 2T0 7lvlQry].[Core Tasks] = UpgradeMain.[Core Tasks];

 

[BNPMike]

We'd need to see the code (ie of the queries) but it looks as though:

1) You are actually doing 4 separate tasks. Why not run 4 queries?

2) You seem to be assigning from UpgradeMain to whatever is in the queries which is not how your description above reads.

Can you tell us more about what you are trying to do?

 

[PHV]

You have to do 4 updates queries as you always modify the same field:
UPDATE UpgradeMain INNER JOIN [Upgrade 2T2 5lvlQry] ON UpgradeMain.SSN=[Upgrade 2T2 5lvlQry].SSN
SET UpgradeMain.[Core Tasks] = [Upgrade 2T2 5lvlQry].[Core Tasks];
UPDATE UpgradeMain INNER JOIN [Upgrade 2T2 7lvlQry] ON UpgradeMain.SSN=[Upgrade 2T2 7lvlQry].SSN
SET UpgradeMain.[Core Tasks] = [Upgrade 2T2 7lvlQry].[Core Tasks];
UPDATE UpgradeMain INNER JOIN [Upgrade 2T0 5lvlQry] ON UpgradeMain.SSN=[Upgrade 2T0 5lvlQry].SSN
SET UpgradeMain.[Core Tasks] = [Upgrade 2T0 5lvlQry].[Core Tasks];
UPDATE UpgradeMain INNER JOIN [Upgrade 2T0 7lvlQry] ON UpgradeMain.SSN=[Upgrade 2T0 7lvlQry].SSN
SET UpgradeMain.[Core Tasks] = [Upgrade 2T0 7lvlQry].[Core Tasks];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cop4u2nite]

Hi,

I'm setting up a database for a local P.D. I have created a table called Arrest table. Within the table I have multiple columns. The columns are titled: Charge, Inmate Name, Arresting Ofcr, Id, Backup Officer, Bkup ID, Date, Booking number and Reporting District.

Ok, heres the deal. What I'm trying to do is run a query where I can see how many arrest a certain ofcr has made.

How many drug arrest we had during a certain time period. How many arrest were made during a certain time period for Burglary.
How many times did a Arresting ofcr have a backup officer assist with an arrest.

The content within the column "Charge" stores the law code violation number. "Officer" stores last names, "Booking number" stores a number, "Date" is numerical as well. "Inmate" is name.

Can someone help?

Thanks...JG...Los Angeles Police

 

[usafguy]

All,
What I am trying to do is update a column within a table (UpgradeMain). I want to update a column with a calculation result that is located in the query. The calculation basically adds up the fields that are checked for each SSN within the query. Now there are 4 queries with a calculation in each. The different queries are queried with different SSN's. The reason that I have 4 queries is because they are in different departments but, that is beside the point.

So, 4 queries and 1 table are going to be in the update query. I want to update the table with these 4 queries. The table contains all of the SSN's and the field marked "Core Tasks" among other necessary things. The queries have the calculation with the field named "Core Tasks". The code looks something like Core Tasks: [4-1]+[4-2]etc... and it works. Now, I just need to get the result in the table instead of sitting in the query so that I can build reports on what core tasks everyone has completed. Someone mentioned that I would need 4 different update queries because they would be stepping over eachother but, this is not true. Each of the queries has a different selection of SSN's (the update main table is the master table and contain all of the SSN's while each query only has a select few) and none of them overlap. So, if I ran the update query, it would only update the selected SSN's once. I plan on running the Update Query every time that the report is opened.

Anyways, I believe that the only way that I can get data from a query into a table (that is calculated ina query) is by making a update query. This is why I am trying to accomplish this.

By the way, I have heard of people doing calculations in a table...anyone know how to do this? It would make my life so much easier.

Thanks for all the help!

 

[usafguy]

I know someone out there knows. All I want to do is take a query calculation and save the results in the table. Someone out there has to be doing the same thing somewhere.

Thanks