Running Calculations with only certain points.

[ZachAtaiyan]

Hello all!



I have a single table with 3 main types of record IDs; FS, FA, and LS. with each record, I will have a number "Score" associated with it. I am looking to sort the scores by the Record ID and run 3 separate calculations, one for each set of data. I would like to then associate the values back to another table.

Is this possible to run in a single query? If so, is there a direction you would recommend that I start in?

Thanks!

[burrina]

We would need to see your table(s) preferably in a query. Of course you can simply set the RecordID to sort either Ascending or Descending in the query grid.
Are these 2 tables not linked by their corresponding key?

[ZachAtaiyan]

Are these shots helpful?

[burrina]

Add your ID to the query grid and sort with either Ascending or Descending.

[ZachAtaiyan]

Once I have the data sorted, how do I tell the query to find the average of only the values that start with "FS-"?

[burrina]

Here is an example of the syntax:

Avg([FS])

HTH

[ZachAtaiyan]

Thank you for that, but how do I specify what it is finding the average of for those values?

[burrina]

Read this article: http://office.microsoft.com/en-us/ac...001100399.aspx

[ZachAtaiyan]

Dave,

The article gave some perspective but the function it uses can not be used in Access projects.

All I am looking to do is average certain values in one field based on selected criteria from another field.

If[PartID = 'FS-'] Then Avg[Scores whose identifiers begin with FS-]

I dont know how to do this in access...any other ideas with the new explanation? I really appreciate it!

[burrina]

What data type is FS ? Here is another Example:

HTH

[ZachAtaiyan]

What data type is FS ? Here is another Example:

HTH

If I understand what you are asking correctly, the data type is text

[ZachAtaiyan]

Question: If I was to use the method above, would I be able to add criteria to designate "FS-"?

[ZachAtaiyan]

So this is what I have so far:



Now how do I find the average of 750, 162, and 162? HELP PLEASE!

[June7]

Sounds like a CROSSTAB query. This would require creating a field in query with expression that extracts the alpha part of the PartID.

Or emulate CROSSTAB with expressions. Review: http://www.datapigtechnologies.com/f.../crosstab.html


BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[ZachAtaiyan]

UPDATE:

I found a solution for what I was looking for. For each FS, LS, and FA, I made an Additional identifier. I used that Identifier to sort scores into those categories in a crosstab query and then used a normal query to find the average of those values.

Much easier than I was thinking....Thanks for the point in the right direction June7!