TCLynx wrote:I did basically type out these examples (some info taken from a real source but it is really a fictitious example.)
I wanted to know where the real data come from. I guess you import these data from a csv file. Or do you copy/paste it from some website? As already stated, a pivot table can split up categories into columns, so it would generate something like example B from example A but not the other way round.
The attached database has 2 tables "Import" and "Data". Import is where you can paste spreadsheet data. "Data" is the resulting table where I already imported your example data.
There is a form with no form controls. It is a text document containing a script.
1) Copy the new import data from spreadsheet.
2) In the main database window select the icon of the "Import" table (don't open it).
3) Paste
4) In the dialog check "first row has column headers" if you included the headers in step 1). Confirm with [Create...]. The column order is the same as in your example data.
5) Copy the SQL script from the form.
6) In the database main window call Tools>SQL.....
7) Paste and execute the script.
What the script does is more or less self-explaining:
Code: Select all
INSERT INTO "Data"(SELECT "Date","Ref","Name","Item","Total",NULL FROM "Import" where "Item"='Order');
INSERT INTO "Data"(SELECT "Date","Ref","Name",'Fee',"Fee",NULL FROM "Import" where "Fee">0);
INSERT INTO "Data"(SELECT "Date","Ref","Name",'Sales tax',"Sales tax",NULL FROM "Import" where "Sales tax">0);
INSERT INTO "Data"(SELECT "Date","Ref","Name",'Shipping',"Shipping",NULL FROM "Import" where "Shipping">0);
DELETE FROM "Import";
It inserts new records from the first 5 fields where column "Item" has the string 'Order'. The 6th column is an auto-ID (kind of row number) and receives a NULL value. The database engine takes care of that value.
Then it inserts new records from the first 3 fields, the text 'Fee' and the "Fee" value where column "Fee" has a value >0.
Then it inserts new records from the first 3 fields, the text 'Sales tax' and the "Sales tax" value where column "Sales tax" has a value >0.
Then it inserts new records from the first 3 fields, the text 'Shipping' and the "Shipping" value where column "Shipping" has a value >0.
Finally it deletes all data from the "Import" table.
------
I have added a unique index on columns "Ref" and "Item" which means that the table refuses to store duplicates with the same ref and item name. This means you get the following error message when you try to import the same example data again:
"Violation of unique index "uniqRef": duplicate value(s) for column(s) "Ref","Item""