Help with using the results of one query in another query

[blake15]

Hey,
I'm desperate for a clue in on how ms access can do this: I'd like take the maximum value from one field and then populate another field by dividing the values of the first field by the max.

An Example:

Field 1 Field 2
1 = 1/3
2 =1/3
3 =3/3

Rather than just using an update query to populate field 1 by dividing it by three, I'd like to divide it by the max, so changes can be made on the fly as the max value might change.

Any help would be greatly appreciated!

[blake15]

ps I am using MS Access 2003 and I've got all my data in a single flat table with no set relationships

perhaps I need to establish relationships to get the single max value back into a query to update records in the master table?

Using the expression builder I have attempted to use an update query to do this:

[master]![Chin_Abun]/Max([master]![Chin_Abun])

where master is my table and "Chin_abun" is my field but it returns an error of that my "specific expression does not contain an aggregate function..."

What does that mean?

So is there a way to pass through the max value in another table and then reach out and grab it to divide that way?

[blake15]

Hey, I figured it out myself and here is an explanation of what wasn't working and what worked in case someone else is at the same holdup.

The goal: use a calculated value (a single max record) that is output in a select query in a mathematical expression with another table.

The problem: I needed to establish a "relationship" in access between the output max value with the original table it was derived from.

The solution: when designing an update query that would divide a field by its max value I brought in the master table and the query table and established a one-to-many link between the two tables. Before doing this I had to create a new field in the master and populate it with dummy data, ie coding all records as "1". Then in the original max output select query I used the "totals" function to define the max and then "grouped" by the dummy data field. This allowed, then, in the final query to establish the one-to-many link between the two tables by dragging the field records between the two table.

Once I did this I could then run the query successfully, in essence doing

New field to update= Value (in master table) / Max value (from query output)

[ADezii]

Hey,
I'm desperate for a clue in on how ms access can do this: I'd like take the maximum value from one field and then populate another field by dividing the values of the first field by the max.

An Example:

Field 1 Field 2
1 = 1/3
2 =1/3
3 =3/3

Rather than just using an update query to populate field 1 by dividing it by three, I'd like to divide it by the max, so changes can be made on the fly as the max value might change.
Any help would be greatly appreciated!

Assumptions:
__1 Table name is tblTest
__2 In the Query Grid, add Field 1 and a Calculated Field called CalcField (or any Name of your choosing) with the following expression:

CalcField: DFirst("[Field 1]","tblTest")/DMax("[Field 1]","tblTest")

OUTPUT:

Field 1    CalcField
1    0.333333333333333
2    0.333333333333333
3    0.333333333333333

[NeoPa]

Hey, I figured it out myself and here is an explanation of what wasn't working and what worked in case someone else is at the same holdup.

The goal: use a calculated value (a single max record) that is output in a select query in a mathematical expression with another table.

The problem: I needed to establish a "relationship" in access between the output max value with the original table it was derived from.

The solution: when designing an update query that would divide a field by its max value I brought in the master table and the query table and established a one-to-many link between the two tables. Before doing this I had to create a new field in the master and populate it with dummy data, ie coding all records as "1". Then in the original max output select query I used the "totals" function to define the max and then "grouped" by the dummy data field. This allowed, then, in the final query to establish the one-to-many link between the two tables by dragging the field records between the two table.

Once I did this I could then run the query successfully, in essence doing

New field to update= Value (in master table) / Max value (from query output)

There seems a marked difference in style between your first two posts and the third ;)
Who cares where you got it from, you got your answer, and better than that, you were very helpful and posted a very well explained solution back in the thread so others could follow and get help too. Nice one.
If you need any help implementing this (I guess not but just in case) let us know. We would need some info about how the records are grouped that you want to process. Otherwise the info's all well stated.
BTW It is possible to do this in a single query if you use sub-queries (Subqueries in SQL). We can also help with the implementation of these with the required info.