Insert into query

[mihikatha]

I am new to LibreOffice.
In MS Access I used the following query to enter data to a file, while quering another file. But in LibreOffice Base, I am not allowed to enter data. Only view is possible.

SELECT [Income/Expense].EDate, [Income/Expense].Item, MainLine.[Main Line], [Income/Expense].Sub, [Income/Expense].Received, [Income/Expense].AUDR, [Income/Expense].Paid, [Income/Expense].AUDP, (Month([Edate])) AS EMonth
FROM MainLine INNER JOIN [Income/Expense] ON MainLine.Item = [Income/Expense].Item
ORDER BY [Income/Expense].EDate;

Is there an alternative way?

[frofa]

Hi mihikatha :

Generally, a result set is editable (writable) if it is derived from a single table of an editable source and includes the table’s primary key, but there are exceptions to this rule - see this discussion and also this discussion about trying to edit a result set from multiple tables. I think these constraints are imposed by the database ‘engine’, not Base (maybe others might advise otherwise?).

UPDATE (8 June 2014): On re-reading through this second reference (above), I note the query in the attached sample database is just a 2-table join like yours, so in theory your joined result-set should be editable.

Looking at your query code, however, I note the square brackets around the table-names, and the slash / within (what I presume is) a table name - namely you have [Income/Expense]. I would be inclined to use table-names WITHOUT slashes, and anyway to put everything in double quotes when entering your code using Edit in SQL View - like this:

SELECT "income_expense"."EDate", "income_expense"."Item", "MainLine"."Main Line", "income_expense"."Sub", "income_expense"."Received", "income_expense"."AUDR", "income_expense"."Paid", "income_expense"."AUDP", (Month("Edate")) AS "EMonth" FROM "MainLine" INNER JOIN "income_expense" ON "MainLine"."Item" = "income_expense"."Item" ORDER BY "income_expense"."EDate";

Obviously you might need to change your table name from Income/Expense to income_expense.

IMPORTANT: Also make sure the button ‘Edit SQL command directly’ is set to OFF (as mentioned in the second post above)!

[mihikatha]

I tried with names (Filed/Table) in double quots. Result is the same. Can not add new records.

[frofa]

As mentioned, make sure the button ‘Edit SQL command directly’ is set to OFF? If that doesn’t work, try posting a ‘sample version’ of your database for us to look at (note: make sure you remove any sensitive or private data first!).