[Solved] Query/report for a subset, not entire table

[hectorheadgear]

Hello everybody! I'm sure this is a silly question, but I'm new to this kind of software.
I have created a table of songs which includes the name of the song, its key, its tempo and capo position. I want to be able to create a report/query where I simply return a list of songs in the key of "B" or a tempo of 120, etc. I have tried to use the query wizard, and instead of the desired result, it's just sorting the whole table by key alphabetically. So instead of getting just 5 songs in the key of B, I'm getting all the songs sorted by key alphabetically.
I hope you can help; it would be a real help for me to have this functionality. Thank you in advance!

Edit: Changed subject, was Song List  Make your post understandable by others  -- MrProgrammer, forum moderator

[UnklDonald418]

It would be best if you could upload a sample of your database.
[Forum] How to attach a document here
Then we can help you design queries to achieve the results you need.

[Villeroy]

Create a query in SQL mode and try any of these:

Code: Select all

SELECT * FROM "Table" WHERE "KEY" = :Enter_Key
SELECT * FROM "Table" WHERE "TEMPO" = :Enter_Tempo
SELECT * FROM "Table" WHERE "TEMPO" = :Enter_Tempo  AND "KEY" = :Enter_Key
SELECT * FROM "Table" WHERE ("TEMPO" = :Enter_Tempo OR :Enter_Tempo IS NULL)  AND ("KEY" = :Enter_Key OR :Enter_Key IS NULL)

Replace the double-quoted names with the actual ones.

[hectorheadgear]

Here is my database. Thank you for any help you can provide me with!

[Villeroy]

TABLES
The column types should be as small as possible and as large as necessary. This makes your table efficient with many thousands of rows.
The Tempo has to be an integer number otherwise the figures would be sorted alphabetically (90 is greater then 100). I removed the text "/" in one row and applied column type "Small Integer".
I turned all remaining text columns into VARCHAR_IGNORECASE so they compare case-insensitively. If you have a reason why you want "A" treated differently from "a", this can be fixed so the form would work anyway. It was just easier for me to do it this way.
I reduced Key and Postion to max.3 characters.

I added a filter table with one row and various text and integer columns.

QUERIES
I added a query which explicitly selects the filter row where ID=0, so you may add more rows with filter criteria and this one is still identifyable.
I added a parameter query which selects all columns from the Materials table. When you open this query you will be prompted for various parameters.

FORM
The form has the filter table query as a main form and the parameter query as subform. The parameters are substituted by the corresponding columns of the parent form. I defined a default sort order (Key, ascending) to the subform. This order is applied every time when you load the form. It can be overriden for the current session.

You enter criteria into the green boxes, click [OK] and the grid shows the resulting rows. Missing criteria are ignored. The cancel button resets the criteria form to its previous state.

Further reference: [Example #1] Filter/Search with Forms (leveraging SubForms)

[hectorheadgear]

Thank you for your help. This problem is solved!