1

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 " & [Tam_Liste]![ProductID1] & " " & [Tam_Liste]![ProductID2] & " " & [Tam_Liste]![ProductID3]) AS item_sku, Tam_Liste.[Place1]
FROM Tam_Liste;
1

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 " & ProductID1 & " " & ProductID2 & " " & 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.

  • I got the idea. Thank you very much. It worked as I wished. – Gary Aug 16 '16 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.