Recent content by ebs17

Since this will probably take longer, I have written down my thoughts in the attached demo. It will be shown: 1) how I imagine a useful may-to-many relationship, 2) how to UNPIVOT the original table, 3) how the data in the resulting list is divided into the tables of the relationship, including...

SELECT P1.ProductName AS Product1, P2.ProductName AS Product2, P1.Size - P2.Size AS Diff FROM tblProducts AS P1, tblProducts AS P2 WHERE P1.ID < P2.ID ORDER BY Abs(P1.Size - P2.Size) Here, similarity to a number was interpreted as size.

A real example should be based on a real database. So upload an example database with some representative records here, including the existing indexes and relationships.

Is the table an export/import or a direct link to SAP? Furthermore, if you have a view, a query or a report in front of you, it also looks like a table. As well as: Not only developer gods work in SAP. Great software and software names do not guarantee outstanding performance. This table is a...

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