Populating a Column as the Result of a Calculation Query

[SYme]

Quick Question for whomever is awake....

I have two tables in this format:

TABLE1
ID - Key
Date - Key
ValueA

and

TABLE2
ID - Key
Date - Key
ValueB
ColumnC

What I am trying to do in Access/Sql is create a query that will Divide TABLE1.ValueA by TABLE2.ValueB and write the results to ColumnC, for each Date.

Cheers,

Simon

 

[scriverb]

Here is SQL to do what you want. I have added some criteria conditions to check the VALUEA and VALUEB fields for valid data. No Nulls or TABLE2.VALUEB <> 0 otherwise you would get a divide by zero error.
UPDATE Table2 INNER JOIN TABLE1 ON TABLE1.DATE = TABLE2.DATE SET TABLE2.COLUMNC = TABLE1.ValueA/ TABLE2.ValueB
WHERE Not IsNull(TABLE1.VALUEA) or Not IsNull(TABLE2.VALUEB) or TABLE2.VALUEB <> 0;

Let me know how this works for you.


Bob Scriver

 

[SYme]

I got the Query in fine - however...

Whan I run the Query it prompts me for a Date input. I actually want it to apply the calculation to all dates in the two tables (i.e. all rows). Is there a setting to do that?

Thanks again.

Simon

 

[SYme]

I have modified the query, and it works great. It still prompts for a specific date. How can I get it to run for all dates in the table?

Here is what I have now:

UPDATE FieldProd INNER JOIN GROUPSUM ON GROUPSUM.Date = FieldProd.Date SET FieldProd.AllocFactor = GROUPSUM.Sum_Oil_Vol / FieldProd.FieldOilVol;

Thanks once again,

Simon

 

[THoey]

Sorry, I do not have an answer for your specific problem, but I am going to voice two concerns I see.

First off, DATE is a reserved word in Access and you should not name any fields that. Access can get confused and think you mean the DATE function.

Second, you normally do not want to store calculated fields in a database. The main reason for this is that if the dat ever changes you have to remember to rerun your query to recalculate this field. Better to have the value calculated and then displayed in your form/report as needed. Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[SYme]

Thanks. I appreciate your comments.

However I do not have the option of Modifying the Column called &quot;Date&quot;. Can that problem be avoided by placing &quot;[ ]&quot; around the table.colname?

Is there a statement to select all the rows in the table? I also do want the results populated permanently, so your other comment should not become an issue.

Thanks again,

Simon

 

[scriverb]

The structure of the query SQL is appropriate to update all rows in the tables BUT as THoey has stated ACCESS is getting confused by the field name DATE. This is an absolute DO NOT USE THIS NAME AS A FIELD NAME. Yes, ACCESS allows you to create the field in the table but them when you want to use it later in expressions and SQL it gets really confused. I know that this will be a pain but you should change the name of the field DATE to something like fldDATE. Then change the reference in all of forms, queries, report, modules, etc.

I have seen this before and reserved words used as field names are a problem, even if you put brackets around them. Brackets only identify the confines of a name. You can have a field name(although not recommended) as Date of Transaction and because of the spaces SQL would not be able to interpret this as a field name. You need to bracket that field name [Date of Transaction] so that ACCESS understands that whatever is in the brackets should be considered a full field name.

Sorry to be the bearer of bad news but these are just some of the rules that have to be lived by in ACCESS. Bob Scriver

 

[SYme]

At least I know the &quot;rules&quot; now.

I appreciate all your comments, and I'll see what I can do.

Thanks to all.

Simon