Newbie - Need Help Calculating Values

[hobbse66]

Hi All,
Still new to Access and could use some help. I have a table that lists locations (allows duplicates), sample numbers (no duplicates), length, sample assay values, and length x assay. See the example below:

(Location) (Sample_ID) (Length) (Sample_Assay_ppm) (Length*Assay)

5400JS123 fc1234 0.5' 1.22 0.61
5400JS123 fc1235 2.3' 0.41 0.943

5400JS456 fc1236 1.1' 0.12 0.132
5400JS456 fc1237 2.9' 4.23 12.267

What I need is a way to add the total length of the samples together, add the length*assay values together and then divide that value by the total length. But** only if they are from the same location (5400JSxxx).

How do I go about setting this up?

Thanks ahead of time

 

[JHB]

Use a query.
Do you have some more sample data, in a MS-Access table or Excel then post them (zip it), and also which result you expected to get.

 

[Mihail]

I have a table that lists locations (allows duplicates), sample numbers (no duplicates), length, sample assay values, and length x assay.

Remove the red field from your table.

 

[hobbse66]

Uploaded an excel doc for reference

 

[Mihail]

Do not store a calculated field in a table.

 

[hobbse66]

Mihail,
Thank you, I did not think it would be that easy.

 

[JHB]

Open the attached database and run the query in it.

 

[JHB]

Mihail only for info: No need for 2 queries.

SELECT Table1.Heading, Sum(Table1.Length) AS [total sum of lengths], Sum([Length]*[Au_opt]) AS [total sum of Au_ppm], Sum([Length]*[Au_opt])/Sum([Length]) AS [Diluted Grade]
FROM Table1
GROUP BY Table1.Heading;

 

[Mihail]

Yep. As I always said:
Amount_Of_Work = 1 / Skill

The formula is available for anything

 

[Mihail]

@JHB
BTW: Why you don't post .mdb files ? My 2007 wasn't able to open your .accdb.
Thank you.

 

[JHB]

@JHB
BTW: Why you don't post .mdb files ? My 2007 wasn't able to open your .accdb.
Thank you.

I'll keep it in mind next time.
Happy New Year and luck with you job searching in 2014.

 

[Mihail]

Thank you very much ! I really need some luck here

All the best to you and to your family !