newbie in a panic

[dottyeel]

I have no idea which section I should post so hopefully 'general' will be ok.

I got a load of information sent to me in Access and, after spending many many hours going through help files etc, I am no closer to getting my head around it all.

The data:
I have sampling points from rivers where the nitrate level was tested which are referenced by a national grid ref (NGR), and an east and a north grid reference. Each sampling point was tested every month for a year.

So I have 4 columns -
NGR
East
North
Nitrate level

...so there are 12 identical NGRs (one row for the same site tested 12 times) in the first column and the same for the east and north columns and 12 nitrate values in the final column.

My question? How do I get access to find the 12 identical NGRs and then find the average of the 12 nitrate values for that site and to keep the eastings and northings associated with the final output?

I would have no trouble if I only had a couple of sites to do but there are 760 sites (>9100 rows of data) for the Anglian region alone and I have another 7 regions to process...

I'm sorry if this is not very clear. I was given the data on Friday and since I am supposed to have something to show for tomorrow I'm getting a bit stressed out - 48 hours isn't long enough to learn Access!

Many thanks

dottyeel

 

[mark curtis]

zip up the db and post it here and I will look at it for u?

 

[dottyeel]

Thank you for your kind offer Mark but even zipped it's >600k and that's too big to be posted...

I have a felling I need to build a query but it's how to tell it to find identical values in one column and then find the average of a set of coresponding values that's got me stuck.

Thanks again

dottyeel

 

[mark curtis]

use a totals query and select group by in the Totals line for NGR. Nitrate level field select avg.

 

[llkhoutx]

Repair, compact and then zip your mdb to get it down to posting size.

 

[dottyeel]

Thankyou for the help, hopefully this zip file will post...

nope, it's 104k...

I'll have a go at getting that query right.

dottyeel

 

[dottyeel]

ok, cut out all the extra stuff in it and I think it's small enough...

dottyeel

 

[llkhoutx]

I'm looking at it now, stay tuned.

 

[dottyeel]

thank you

 

[llkhoutx]

Take a look at the query I built.

I clicked the Sigma symbol in the Tool bar to group the data.
Not the row in the query titled "Total;" - 3 columns a "Group By", one is "Avg" and one is count to count the records in each grouping.

Also note that you can sort your resulting data with the "Sort:' row by selecting "Ascending" or "Descending."

 

[dottyeel]

Thank you llkhoutx for helping.

OK, I'm not too sure if I'm being incredibly stupid or what...
but the file 'chemical update' seems to have 'CallerCallee' data, no chemical data. Is that right?

dottyeel

 

[bradcccs]

That would have to be one of your most cryptic demo's Ilk.

 

[llkhoutx]

Sorry, I posted the wrong file. Here's the correct one.

 

[llkhoutx]

Now here it really is.

 

[dottyeel]

llkhoutx thankyou!

I'm going to take my time to get my head around what you did but at first glance it sort of makes some sense. It appears that 'sort by' automatically groups like with like. Is that right? I thought I had to specify how it should group the NGRs.

I will have a go with the other databases tomorrow, I'll get the idea with more practice and at least I can go into work tomorrow with an ArcView map of one region's nitrate levels!

Thank you for your time and patience, it is much appreciated.

dottyeel