Formulas in Access - Pulling fields first

[sean98]

It appears that when I try and calculate a formula in a field within access I need to pull in the fields used in the calculation into the query in order for the calculated field to work.

Example

Fields
COGS_UNIT
TXN_QTY

I want to calculate the total COGS for all units purchased so I set up

TTL_COGS: (COGS_UNIT*TXN_QTY) as an expression in the new table I am trying to create. The query will not work as I have not included the COGS_UNIT and TXN_QTY fields in my query. If I pull them in the query works, but I do not need the individual COGS_UNIT and TXN_QTY data in my table. Any thoughts on how I can run without having to pull in the individual fields as well. I understand that unchecking the show box will exlcude the results from the table but it adds to the query time if I am pulling a number of fields thus I would prefer to leave them out if possible.

I hope this makes sense.

Thanks
Sean

 

[Corticus]

If you need to use a field in a calculation, you have to pull it into that query. I don't know of any way around this.

These queries are not actually taking up memory, however, and the dynaset is only being generated at the time the query is run, and your really just looking at the data in the table, no data is ever actually pulled into a query.

It sounds like your thinking of a query as a table...its not. You could have a hundred queries all referring to every field in a big 'ol table, but those queries are nothing more than SQL expressions (in design mode, go view|SQL):
SELECT blah blah blah
FROM blah blah blah
WHERE blah blah blah

Unlike spreadsheets, one of the main points of a database is to normalize data, which to a large extent involved the elimination of redundant data, and so enters SQL. Also the GUI is nice.

Regarding time, if the field is needed for the calculation, you just have to wait. Consider joining tables on numeric fields, and perhaps streamlining your data a bit (which is impossible to give explicit advice on since I don't know what you're dealing with..)

HTH,

 

[sean98]

I appreciate your feedback. I now know a bit more about access. Maybe someday that will be a good thing.

Cheers!