How to simplify this query? I am somewhat new to Access and I need help with a query that Access does not execute because of Not enough space on temporary disk.

I have one table. A product may have up to 3 IDs and a place to sell. If there is a place to sell, the name will be shown along with a combined identifier consisting of the three IDs plus the "k1" prefix. An example of the resulting combined identifier would be "k1 3232 232 1231233" (if it has three IDs).

The column names are ProductID1, ProductID2, ProductID3, Place1.

This is the query:

SELECT DISTINCT IIf(IsNull([Tam_Liste]![Place1]),"","k1 " &amp; [Tam_Liste]![ProductID1] &amp; " " &amp; [Tam_Liste]![ProductID2] &amp; " " &amp; [Tam_Liste]![ProductID3]) AS item_sku, Tam_Liste.[Place1]
FROM Tam_Liste;
For every row where Place1 is null, you are returning an empty string as item_sku. So without DISTINCT all such entries would be returned as

item_sku  Place1
--------  ------
          NULL
          NULL
          NULL
          NULL
          NULL
…         …


and DISTINCT condenses them to just one row. Do you need that row in the output? To me, it does not make much sense to keep it. I would get rid of it with a simple filter:

SELECT DISTINCT
  "k1 " &amp; ProductID1 &amp; " " &amp; ProductID2 &amp; " " &amp; ProductID3 AS item_sku,
  Place1
FROM
  Tam_Liste
WHERE
  Place1 IS NOT NULL
;


Furthermore, I would also consider moving the concatenation bit to the front end and use the database only as a data source*. My final query, therefore, would look like this:

SELECT DISTINCT
  ProductID1,
  ProductID2,
  ProductID3,
  Place1
FROM
  Tam_Liste
WHERE
  Place1 IS NOT NULL
;


I am not 100% sure but it seems likely to me that the above query would require less temporary disk space than the original one.



*I realise that things may work differently with Access, as it can be used both as a back end and as a front end. Nevertheless, I would still keep this option in mind.