Create a Query with an Auto-Incremented Field

[FullMoonMadness]

Hello,

I’m trying to create a form with a list box that I can use to filter by one field (“Store”) and have all transactions involving that store displayed in a subform. I was trying to use FrugalComputerGuy’s youtube tutorials (View by Department pt.1 and the next video), however I get stuck early on where he creates a table that lists the Filter-ID and Department-ID.

If I’m understanding his tutorial correctly, I’m going to need to assign a “StoreID” to each Store. I am able to create a query that provides a unique and sorted list of Stores, however I don’t know how to assign each store an ID or unique value.

I’ve looked at using variations of ROW_NUMBER() OVER(), and thought about calculating the ASCII value of the letters in the Store field, but haven’t had any success.

Details of my database:

• The data is in table “Transactions”
  ** the primary key is “PID”
• The field is called “Store”, and contains data like Walmart, Target, etc.
  ** some stores are listed multiple times
  ** this is not a static field as new stores are entered occasionally
• Data is entered into a form called “New_Transactions”
  ** There is a combobox on this form for “Store” where a new store might be entered (Costco)
  ** there are many other fields on this form pertaining to the transaction (Cost, Date, etc.)

Query to create a sorted list of unique stores:
SELECT
“Store”
FROM
“Transactions”
GROUP BY
“Store”
ORDER BY
“Store” ASC

How can I add another field (StoreID) to that query with a unique identifier? Should I be taking a different approach? Thanks!

[Wanderer]

Didn’t check the video, but I assume the tutorial has a separate table for the stores and other tables will only reference the StoreID.
.
You seem not to have a separate table for your stores, so there is no Id. If it is necessary to have a separate table for the stores only you can decide. It is the typical approach in relational databases, to use only an id, so you can alter/extend the actual data of the shop from address to tax-information easily…

[UnklDonald]

Even when using a ComboBox, saving the actual store name in the “Transactions” table opens the door for misspelled store names that can be difficult to locate. It also violates the First Normal Form, one of the rules of proper relational database design.

To create a table for Stores, select from the menu Tools >SQL

Execute in the Direct mode

CREATE TABLE "Stores"(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0)
NOT NULL PRIMARY KEY,
"StorNam" VARCHAR(50),
CONSTRAINT "SU" UNIQUE("StorNam")
) ;

Because we used the Direct mode Base needs to be informed about the new table

Select View>Refresh Tables

The new table will be empty, so to populate it again select Tools>SQL and execute

INSERT INTO "Stores" ("StorNam")
SELECT
“Store”
FROM
“Transactions”
GROUP BY
“Store”
ORDER BY
“Store” ASC

For a normalized “Transactions” table you would replace the actual Store Name with an Integer field which would save the ID of a store in the Stores table. Then instead of a ComboBox, use a ListBox to select the Store on your transaction entry form.

Some advantages of this approach are that the chance of spelling errors is reduced to practically nil plus the table size is smaller.

The disadvantage is that you need a separate Form to enter new stores. That could be a different Form document or if it is a common occurrence it could be placed on the same Form document as the transaction entry form using the Form Navigator.

[FullMoonMadness]

Thank you for your assistance!


I admit to hoping for a more automated solution. Maybe a variation of your answer will work. I could start by creating the empty Stores table as you described. Then, is it possible to place a button on my search form where pressing that button will execute an SQL statement to do the following?

1. clear all of the stores from the Stores table
2. repopulate it with with the stores found in the Transaction table (to capture any new stores)
3. refresh the form

Then I would use my drop menu to select my store so the data is loaded in the subform


Would that SQL statement need to delete all of the records, or would updating the Stores field with a null value work? Would the repopulation start with the first ID, or try to append(I’d prefer the former)?


Separately, I wanted to test the code you provided. I was able to execute the Create Table statement, and refresh the tables. However the Insert Into statement errors our with the message:

1: Unexpected token: “ in statement [INSERT INTO “Stores” (“StorNam”)
SELECT
] at /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/Object.cxx:173

Thanks again, I appreciate your help!