Add superficial record through query

Question

I need to be able to on the fly create a placeholder record. So for example if I had myTable which had

-Apple 1
-Apple 2
-Apple 3
-Pear 1
-Pear 2

I need a query to be able to spit out.

-Apple 1
-Apple 2
-Apple 3
-Apple 0
-Pear 1
-Pear 2
-Pear 0

I would prefer not having to append actual blank records into the data, just have it appear in the query output. Is this possible?

Do you need to be able to edit the records? And how will the "query output" be used / presented? It matters, because there is no simple, built-in functionality to add records dynamically to standard query output. It will most likely require "tricks" and/or workarounds that honestly may be more work than just creating a temporary table and inserting records. — C Perkins, Jun 16 '17 at 15:57
Related question for [adding additional rows](https://stackoverflow.com/questions/42076780/add-a-blank-row-to-this-access-query), but it is prohibitive for many rows and does not support dynamic number of rows or data values. — C Perkins, Jun 16 '17 at 16:05
You may be able to add records to a disconnected ADO recordset after first populating it from your table. [Here's a related question](https://stackoverflow.com/questions/5641760/ms-access-form-bound-to-ado-disconnected-recordset) with relevant code, event though the precise reason is different. — C Perkins, Jun 16 '17 at 16:38
C Perkins, Gustav. The "Fake" record would not be used for editing, purely informational. I will be using the "0" value to key off a different joined table, and displayed in a non-editable form for the users. — L.P., Jun 16 '17 at 17:43

score 2 · Accepted Answer · answered Jun 16 '17 at 17:25

Consider a UNION query that adds a GROUP BY query to the recordsource using a scalar 0. Below assumes table has Fruit and Val columns. However, UNION will order your Val columns as 0, 1, 2, 3... and UNION ALL will leave rows unordered with both zeros at the very bottom two rows.

SELECT t.Fruit, t.Val
FROM Fruits t

UNION

SELECT t.Fruit, 0
FROM Fruits t
GROUP BY t.Fruit

To place a row underneath the highest value of each group such as for Apple at 1, 2, 3,-->[4]<-- use a Max(...) + 1 aggregate:

SELECT t.Fruit, t.Val
FROM Fruits t

UNION    

SELECT t.Fruit, Max(Val) + 1
FROM Fruits t
GROUP BY t.Fruit

Add superficial record through query

1 Answers1