Search results

Then you can forget about a reasonable query. SQL works row-oriented, not column-oriented. But with VBA and the appropriate skills you can solve everything, even if it is more complicated and time-consuming. With such an Excel-typical structure, you can also switch to Excel for processing. But...

Your table does not correspond to the expected table design for a database. http://www.allenbrowne.com/casu-23.html The first correct step should be to break down this table into a many-to-many relationship. This cannot be used in a useful way. In the table for frequencies there should be a...

SELECT Name, Database FROM MSysObjects WHERE Database > "" This allows you to determine the linked tables and the associated backend. You could also process this list directly via Recordset. You can also count easily, using a Count in the query or a RecordCount in the recordset.

This is what I call an exclusive opinion. I can only shake my head at what some people think. For those who can read: My suggestion corresponds to the wording of the question. Or should I understand the counter-discussion to mean that it is a huge effort for those involved to write a delete...

You can also create a pivot table = crosstab query directly in Access. One of the above queries saved as Qx: TRANSFORM MIN(M.Profit) AS X SELECT M.Supplier, M.SUP_ID FROM mytable AS M INNER JOIN Qx ON M.SUP_ID = Qx.SUP_ID GROUP BY M.Supplier, M.SUP_ID PIVOT...

SELECT M1.Supplier, M1.SUP_ID FROM mytable AS M1 WHERE M1.Product = "Lemonade" AND NOT EXISTS ( SELECT NULL FROM mytable AS M2 WHERE M2.SUP_ID = M1.SUP_ID AND M2.Product = "Tea"...

That's not OK. A query that produces unwanted duplicates should be reworded. This is about a table that contains duplicates, for whatever reason. The ID with autonumber was just one possible example to differentiate records. Maybe there are already existing usable fields such as a timestamp...

A query is mass data processing - all at once. Therefore, you immediately need a secure and reliable attribute for a first or second occurrence in order to be able to delete accurately. I don't trust myself to derive a reliable rule from a random(?) example.

Who is the question for? EXISTS is part of the ANSI89 language set that Jet-SQL (=Access-SQL) uses. So you can use it, and I haven't seen a case where it didn't work if you use it correctly, i.e. in conjunction with a correlated subquery. I have already written a lot of queries so that the one...

I'm very sure of that.

https://www.access-programmers.co.uk/forums/threads/how-can-i-delete-duplicate-records-from-a-table.331088/#post-1919658

But you should also be able to do a delete query on a table, it's not a big challenge. If you always need a new table to display results, that's not beneficial.

A concrete proposal can only be created based on a specific specification. The SQL statement is a fairly comprehensive definition.

With a unique index on the field or field combination that contains the duplicates, you can safely prevent unwanted duplicates in the table.

SQL IS easy. When telling stories about queries, it's helpful to show the SQL statement itself. You should also take into account that queries rely on table data. If there are already unwanted duplicates, you should apply your leverage there and correct them.

However, with the UNION query you subsequently lose all index usage. A real database regularly comes with a lot of data, and you need some resources to achieve satisfactory performance. What does that mean? If you have or can compile the full path in a file system in the database, you can open...

This is a problem of our own making. If necessary, you should move the attachments to additional backends, but not the actual data.

If you only want to delete parts of them, you need an attribute that allows you to distinguish between the records and can therefore be used to determine which records you want to delete or which records you want to keep. A typical field would be an ID with a continuous number (autoincrement)...

You could show these examples.

In the import specification, change the data type for this column to text. Text can initially record everything. Then, to link the text table, use a query like this: SELECT T.[FieldList] FROM...