How Do I Insert Blank Records in an Append Query

[GirthJohnson]

I've written a print program in another language to write every two records (orders) in a table on to a single sheet.

The problem is that the database file provided has customers with double orders and single orders mixed together. The VIPP program is unable to detect when there's a single record for a customer. Since I can't change the print program I need to change the data file so that each customer has two records.

I want to import the data file into an Access table and insert a blank row after single customer entries which I know violates database forms and normalization. Once I have two records for all customers, I can export the table out and insert the new records into the data file for print production.

Any takers?

[Stewart Ross]

There is no such thing as a blank record in a database - you are quite correct that storing such a thing would violate database design principles. Further, relational databases have no inbuilt concept of record position, as they are based on the principles of set theory where set membership has nothing to do with set position. It cannot be assumed that if you were able to write marker records of some kind that would indicate some form of 'blank' to be printed that these would be placed in the correct position that you need them, at least not without knowing a lot more about the sort order applied to the records stored.

-Stewart

[ADezii]

Stewart pretty much says it all. What you can do is to Insert a 'Blank' Record with a Unique Identifier such as OrderID to uniquely identify to whom/what the Blank Record refers to. This is accomplished by Inserting NULL Values into the appropriate Fields. This all depends on the Fields that will be accepting NULLs. They cannot be the Primary Key or component thereof, Uniquely Indexed, Required, etc. Here is how this can be done:

CurrentDb.Execute "INSERT INTO tblOrders ([OrderID], [Customer], [Order Date]) VALUES (9999, NULL, NULL)", dbFailOnError

[Anas Mosaad]

An old trick that might help. Create a new object of type view (query) that selects only the desired records and name it the same name as the object being selected from -of course you have to rename that object first.
For example, this query gets only customers with N entries:

select c_id from customers group by c_id having count(c_id) = [N]
 

[Rabbit]

I agree with not storing those blank records. Instead you can use a union query to add those blank records when needed.