count query.

[John Sh]

1) I need to isolate numbers where the integer value is different to the double value, I.e. 123 <> 123.1.
2) store the integer values in a table.
3) delete any numbers that occur more than once. I.e 123.1, 123.2,123.3 stored as 123, 123, 123 would be deleted.
4) The final table will be used in a form to highlight areas where there should be more than one entry, such as 123.1, 123.2
I can achieve 1) and count the occurrences in 2) but draw a blank when updating the table with the count.

INSERT INTO tempCollect ( Accession )
SELECT Int([AccessionNumber]) AS Expr1
FROM Herbarium_Collection
GROUP BY Int([AccessionNumber]), Herbarium_Collection.AccessionNumber
HAVING (((Herbarium_Collection.AccessionNumber)<>Int([Herbarium_Collection]![AccessionNumber])));

SELECT tempCollect.Accession, Count(tempCollect.Accession) AS CountOfAccession
FROM tempCollect
GROUP BY tempCollect.Accession;

 

[June7]

Saving calculated data, especially aggregate, is usually unnecessary and can be bad. Calculate aggregates when needed.

Access SQL does not permit UPDATE action when aggregate query is source. Would have to use VBA or domain aggregate function.

Your first SQL makes no sense to me. Provide sample data and desired result. Build tables in post or attach file.

 

[MarkK]

I don't understand this overall requirement. You delete 123.1 so you can find areas where 123.1 should exist? Seems to me if you don't do 3), then you don't have to do 4), and then 1) and 2) are pointless, and *poof*, the whole problem evaporates.
So I don't get it.

 

[John Sh]

Saving calculated data, especially aggregate, is usually unnecessary and can be bad. Calculate aggregates when needed.

Access SQL does not permit UPDATE action when aggregate query is source. Would have to use VBA or domain aggregate function.

Your first SQL makes no sense to me. Provide sample data and desired result. Build tables in post or attach file.

It's a bit complicated and specific to the data with which I am working.
In plant collection, each collected specimen has an accession number. eg 123
If more than one specimen is taken from the same plant at the same time they then become 123.1 and 123.2 etc.
There can be no duplicated accession numbers.
Due to inconsistencies in previous data entry we have a number of entries like 123.2 with no corresponding 123.1 or similar.
I am trying to isolate such incorrect entries and put them into a table so corrective action can be taken, but more importantly identify and advertise the existence of these errors.
The constructed table will be dynamic if I can do what is necessary.
I have seen an aggregate, count, query used as a data source in an update query but can't seem to get it right.
The code I posted works as required, I just need to use the count query as data for a delete query that will get rid of the records with multiple dot point entries and keep only those with a single dot point entry.

 

[June7]

I have seen an aggregate, count, query used as a data source in an update query but can't seem to get it right.

Was this for Access database or SQLServer or other platform? If you can find what you saw, please post link.

UPDATE with aggregate data is a common topic.

Per MS docs https://learn.microsoft.com/en-us/office/troubleshoot/access/errors-updating-query-form-data

Also, you cannot update a query that references a field in the Update To row from a crosstab, a query, a select query, or a subquery that contains totals or aggregate functions.

 

[MarkK]

Moving data around in a relational database, as you are experiencing, is a difficulty kind of like paddling upstream. What I would be more likely to recommend is that you use a query to identify problem rows or areas in your existing table, and present your number 4) as queried from source using such a query.

One option you can implement along these lines is to add a field (or more) to the table, and then run a process (perhaps using the query above) that marks problematic rows in this new field. Think about it as saving the error state of data as an integrated part of itself. Some of the fields in the row describe the accession and the specimen, but now there is also data in the row that describes its validity. Then, to return a clean data-set as it would have appeared after your deletions in number 3), simply write a query that filters out the error-marked rows. For your presentation of those rows as per your 4), write a query that selects them.

This is a lower friction approach that does an absolutely accurate preservation of the historical data, and may also give you all the leverage you need to manage/present/exclude/process the errors going forward.

Store your data, but store the state of your data with your data. Now you can manage your data by itself, as you would expect, but you can also manage your data by state.

hth

 

[Mark_]

From your description, you are trying to make a second table with JUST the exceptions which have been removed from the original. Wouldn't it be easier to just set up two queries? One shows just the good records, the second shows just the bad ones?

 

[John Sh]

From your description, you are trying to make a second table with JUST the exceptions which have been removed from the original. Wouldn't it be easier to just set up two queries? One shows just the good records, the second shows just the bad ones?

Hi Mark.
Not quite.
The entries in error are still part of the table. I have solved the problem by using four queries;
The first copies all accession numbers that have a dot point and appends both the double and integer values to a temp table.
Next group and count the instances of each integer in a group, append that result to the final table.
Step 3 delete all instances from the final table where the count exceeds 1.
Lastly Update the final table with the doubles from the temp table that correspond to the remaining integers.
The final table is then used in a simple continuous form where double clicking a number opens the data entry form at that record.
From here, corrective actions can be taken.
Any corrections made are noted as part of the record and also recorded in a history table.

 

[John Sh]

Was this for Access database or SQLServer or other platform? If you can find what you saw, please post link.

UPDATE with aggregate data is a common topic.

Per MS docs https://learn.microsoft.com/en-us/office/troubleshoot/access/errors-updating-query-form-data

This was for an Access db but a long time ago. someone from this forum wrote two queries.
The first did the count and then was use as a data source for the second.
Unfortunately I do not have copies any more.
From what I know now, the second query may well have been an append query.
John