Query for data by year(date) and primary key

[Guest]

I have measurements of springflow for a few thousand unique site nos. Each
measurement has a dateof record yyyy/mm/dd. Also, each measurement has its
own source code. I need to do a check for records for all sites that have a
different source code, but the same year. basically, the table i am working
with has a field for the site number, the source of the measurement, the
date, the precision of the date (yearly, monthly, daily, other), the value of
the measurement, the type of measurement, and remarks. What I'd like to find
are measurements that have a different reporting agency, but may be based on
the same original record. the values will not be exactly the same for these
because of unit conversion and rounding errors. But what would help is to be
able to see all the records for a site that have the same year, but a
different source code. Some of the data have the exact same date, but some
are only reported as the value for a certain year.

Then I would like to be able to delete all the records for a particular site
from the source I consider least valuable (it's the same one for every site,
the one that has the lowest reporting precision).

I am pretty unfamiliar with this type of querying, I have only used Access
for limited purposes such as address labels and contact lists.

 

[Guest]

You need to join the table to itself on the SiteNumber and Year being the
same, but the Source being different. I assume the date is a text field so
you can get the year by means of the Left function. To differentiate the
instances of the table you give each one an alias. The query would go
something like this:

SELECT T1.*,T2.*
FROM YourTable As T1, YourTable As T2
WHERE T2.SiteNumber = T1.SiteNumber
AND LEFT(T2.YourDateField,4) = LEFT(T1.YourDateField,4)
AND T2.Source <> T1.Source
ORDER BY T1.SiteNumber;

What this will give you is a result table with each row made up of the data
form one row along with the data from every other row in the table for that
site/year where the source differs. So you'll have multiple rows per
site/year, the number depending on how many matches there are in other rows
for that site/year combination.

Once you've identified the source on the basis of which you want to delete
rows for a particular site you can do this with a query with parameters which
prompt you for the site number and source, e.g.

DELETE *
FROM YourTable
WHERE SiteNumber = [Enter Site Number:]
AND Source = [Enter Source:];

Be sure you back up the data first!

You could also enter multiple site numbers when prompted if you wanted to
delete all rows for multiple sites where the source was the same, but that
requires some VBA functions to be created first. You could avoid having to
write any VBA code by putting the actual values into the query:

DELETE *
FROM YourTable
WHERE SiteNumber IN(42, 99, 123)
AND Source = 666;

but that's less flexible. However, you could if you wished do this by
keeping the query open in design view and just keep changing the values and
then clicking the Run button on the toolbar; you don't have to save the query
before running it.