If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Adding a new record in alpha with an exception
Does anyone know if this is possible:
I want to create a form that will add a new record to a table that has only one field, eg category; and I want these to be listed alpahabetical. However, there are four categories that I always want to be at the bottom of the list, irrespective of where they fall alphabetically, eg Books (alphabetical) DVDs (alphabetical) Films (alphabetical) Plays (alphabetical) Family DVDs (not alphabetical - at bottom of choose list) Family films (not alphabetical - at bottom of choose list) I've been playing around with this for ages but can't find a way to do what I want to. Does anyone have any ideas? Many thanks Aehan |
#2
|
|||
|
|||
Adding a new record in alpha with an exception
You will need two fields. One call TopSort and make it a number field.
Default to 0. For those Family items use 1 in the field. In design view click on menu VIEW - Indexes. Name the index MySort and add the number field and then the category field. In your queries always sort on the two fields. "aehan" wrote: Does anyone know if this is possible: I want to create a form that will add a new record to a table that has only one field, eg category; and I want these to be listed alpahabetical. However, there are four categories that I always want to be at the bottom of the list, irrespective of where they fall alphabetically, eg Books (alphabetical) DVDs (alphabetical) Films (alphabetical) Plays (alphabetical) Family DVDs (not alphabetical - at bottom of choose list) Family films (not alphabetical - at bottom of choose list) I've been playing around with this for ages but can't find a way to do what I want to. Does anyone have any ideas? Many thanks Aehan |
#3
|
|||
|
|||
Adding a new record in alpha with an exception
Thanks. I did put an Order numerical field on originally, and I placed the
categories I wanted to keep as non-alphabetical at the bottom of the list numerically, then I used a query to sort the list. However, I couldn't then find a way of automatically inserting new records above the non alpha ones sor that it would sort everything else as alpha (gets more complicated every second, sorry!). I changed the sort to your suggestion, however, this always places the non-alphabetical items at the top of the list rather than at the bottom, so athough it keeps them together, it doesn't actually do what I want it to. It is actually for our family business and the non-sort itmes are the in-house categories, so we don't want them at the beginning of the list. Do you know of a way to force them always to be at the end? There may be a different way of doing it. Thanks Aehan "KARL DEWEY" wrote: You will need two fields. One call TopSort and make it a number field. Default to 0. For those Family items use 1 in the field. In design view click on menu VIEW - Indexes. Name the index MySort and add the number field and then the category field. In your queries always sort on the two fields. "aehan" wrote: Does anyone know if this is possible: I want to create a form that will add a new record to a table that has only one field, eg category; and I want these to be listed alpahabetical. However, there are four categories that I always want to be at the bottom of the list, irrespective of where they fall alphabetically, eg Books (alphabetical) DVDs (alphabetical) Films (alphabetical) Plays (alphabetical) Family DVDs (not alphabetical - at bottom of choose list) Family films (not alphabetical - at bottom of choose list) I've been playing around with this for ages but can't find a way to do what I want to. Does anyone have any ideas? Many thanks Aehan |
#4
|
|||
|
|||
Adding a new record in alpha with an exception
KARL DEWEY wrote: You will need two fields. One call TopSort and make it a number field. Default to 0. For those Family items use 1 in the field. Are you advising the OP to create a redundant second column for their one-column table? I would not recommend this. I would instead recommend they determine the sort order based on the data (assuming the 'bottom' categories also sort alphabetically among themselves e.g. CREATE TABLE MediaCategories ( media_category_name VARCHAR(30) NOT NULL PRIMARY KEY ) ; INSERT INTO MediaCategories VALUES ('Films') ; INSERT INTO MediaCategories VALUES ('Family films') ; INSERT INTO MediaCategories VALUES ('Books') ; INSERT INTO MediaCategories VALUES ('Family DVDs') ; INSERT INTO MediaCategories VALUES ('Plays') ; INSERT INTO MediaCategories VALUES ('DVDs') ; SELECT media_category_name, 1 AS category_sort_order FROM MediaCategories WHERE media_category_name IN ('Family DVDs', 'Family films') UNION ALL SELECT media_category_name, 0 AS category_sort_order FROM MediaCategories WHERE media_category_name NOT IN ('Family DVDs', 'Family films') ORDER BY 2, 1; The disadvantage here is that the 'bottom' (compulsory?) categories are hard-coded into the SQL. An alternative approach would be to use a second table to model the compulsory categories. KARL could be assuming that compulsory categories do not sort alphabetically among themselves, rather have an explicit sort order; adding a column to this table would avoid the redundancy of his one-table two-column solution: CREATE TABLE CompulsoryMediaCategories ( media_category_name VARCHAR(30) NOT NULL PRIMARY KEY REFERENCES MediaCategories (media_category_name) ON DELETE CASCADE ON UPDATE CASCADE, category_sort_order INTEGER NOT NULL UNIQUE, CONSTRAINT compulsory_media_category_sort_order__positive CHECK (category_sort_order 0) ) ; INSERT INTO CompulsoryMediaCategories VALUES ('Family films', 2) ; INSERT INTO CompulsoryMediaCategories VALUES ('Family DVDs', 1) ; SELECT M1.media_category_name, IIF(C1.category_sort_order IS NULL, 0, C1.category_sort_order) AS category_sort_order FROM MediaCategories AS M1 LEFT JOIN CompulsoryMediaCategories AS C1 ON M1.media_category_name = C1.media_category_name ORDER BY IIF(C1.category_sort_order IS NULL, 0, C1.category_sort_order), M1.media_category_name ; Note in the above query it is good practice to expose the sort order to the front end application (it may even be more efficient to sort the resultset in the middleware e.g. adodb.recordset.sort). Here's some VBA code to recreate the above example: Sub testKARL() Dim cat As Object Set cat = CreateObject("ADOX.Catalog") With cat .Create _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\DropMe.mdb;" With .ActiveConnection ' Dummy one-row temp table for bulk inserts .Execute _ "CREATE TABLE DropMe (anything INTEGER);" .Execute _ "INSERT INTO DropMe VALUES (1);" .Execute _ "CREATE TABLE MediaCategories ( media_category_name" & _ " VARCHAR(30) NOT NULL PRIMARY KEY ) ; " .Execute _ "INSERT INTO MediaCategories (media_category_name)" & _ " SELECT DT1.media_category_name FROM ( SELECT" & _ " 'Films' AS media_category_name FROM DropMe" & _ " UNION ALL SELECT 'Family films' AS media_category_name" & _ " FROM DropMe UNION ALL SELECT 'Books' AS" & _ " media_category_name FROM DropMe UNION ALL" & _ " SELECT 'Family DVDs' AS media_category_name" & _ " FROM DropMe UNION ALL SELECT 'Plays' AS" & _ " media_category_name FROM DropMe UNION ALL" & _ " SELECT 'DVDs' AS media_category_name FROM" & _ " DropMe ) AS DT1;" ' Drop temp table .Execute _ "DROP TABLE DropMe;" .Execute _ "CREATE TABLE CompulsoryMediaCategories (" & _ " media_category_name VARCHAR(30) NOT NULL" & _ " PRIMARY KEY REFERENCES MediaCategories" & _ " (media_category_name) ON DELETE CASCADE" & _ " ON UPDATE CASCADE, category_sort_order" & _ " INTEGER NOT NULL UNIQUE, CONSTRAINT compulsory_media_category_sort_order__positive" & _ " CHECK (category_sort_order 0) );" .Execute _ "INSERT INTO CompulsoryMediaCategories VALUES" & _ " ('Family films', 2);" .Execute _ "INSERT INTO CompulsoryMediaCategories VALUES" & _ " ('Family DVDs', 1);" Dim rs As Object Set rs = CreateObject("ADODB.Recordset") With rs .CursorLocation = 3 ' client side .ActiveConnection = cat.ActiveConnection .Source = _ "SELECT M1.media_category_name, IIF(C1.category_sort_order" & _ " IS NULL, 0, C1.category_sort_order) AS" & _ " category_sort_order FROM MediaCategories" & _ " AS M1 LEFT JOIN CompulsoryMediaCategories" & _ " AS C1 ON M1.media_category_name = C1.media_category_name;" .Open .Sort = "category_sort_order, media_category_name" MsgBox .GetString(2, , vbTab & vbTab) ' clip string .Close End With End With Set .ActiveConnection = Nothing End With End Sub Jamie. -- |
#5
|
|||
|
|||
Adding a new record in alpha with an exception
Hi Jamie and Karl
Thanks to both of you, I have in the end used Karl's solution - I don't think I read it properly first, and it does what I want it to. Thanks to you too, Jamie, I did have a go, but I don't think my skills are at that level yet, I will have another look later when I have more time because I would like to understand it properly. I really appreciate you taking the time to look at my problem, and for replying to me. Kind regards Aehan "Jamie Collins" wrote: KARL DEWEY wrote: You will need two fields. One call TopSort and make it a number field. Default to 0. For those Family items use 1 in the field. Are you advising the OP to create a redundant second column for their one-column table? I would not recommend this. I would instead recommend they determine the sort order based on the data (assuming the 'bottom' categories also sort alphabetically among themselves e.g. CREATE TABLE MediaCategories ( media_category_name VARCHAR(30) NOT NULL PRIMARY KEY ) ; INSERT INTO MediaCategories VALUES ('Films') ; INSERT INTO MediaCategories VALUES ('Family films') ; INSERT INTO MediaCategories VALUES ('Books') ; INSERT INTO MediaCategories VALUES ('Family DVDs') ; INSERT INTO MediaCategories VALUES ('Plays') ; INSERT INTO MediaCategories VALUES ('DVDs') ; SELECT media_category_name, 1 AS category_sort_order FROM MediaCategories WHERE media_category_name IN ('Family DVDs', 'Family films') UNION ALL SELECT media_category_name, 0 AS category_sort_order FROM MediaCategories WHERE media_category_name NOT IN ('Family DVDs', 'Family films') ORDER BY 2, 1; The disadvantage here is that the 'bottom' (compulsory?) categories are hard-coded into the SQL. An alternative approach would be to use a second table to model the compulsory categories. KARL could be assuming that compulsory categories do not sort alphabetically among themselves, rather have an explicit sort order; adding a column to this table would avoid the redundancy of his one-table two-column solution: CREATE TABLE CompulsoryMediaCategories ( media_category_name VARCHAR(30) NOT NULL PRIMARY KEY REFERENCES MediaCategories (media_category_name) ON DELETE CASCADE ON UPDATE CASCADE, category_sort_order INTEGER NOT NULL UNIQUE, CONSTRAINT compulsory_media_category_sort_order__positive CHECK (category_sort_order 0) ) ; INSERT INTO CompulsoryMediaCategories VALUES ('Family films', 2) ; INSERT INTO CompulsoryMediaCategories VALUES ('Family DVDs', 1) ; SELECT M1.media_category_name, IIF(C1.category_sort_order IS NULL, 0, C1.category_sort_order) AS category_sort_order FROM MediaCategories AS M1 LEFT JOIN CompulsoryMediaCategories AS C1 ON M1.media_category_name = C1.media_category_name ORDER BY IIF(C1.category_sort_order IS NULL, 0, C1.category_sort_order), M1.media_category_name ; Note in the above query it is good practice to expose the sort order to the front end application (it may even be more efficient to sort the resultset in the middleware e.g. adodb.recordset.sort). Here's some VBA code to recreate the above example: Sub testKARL() Dim cat As Object Set cat = CreateObject("ADOX.Catalog") With cat .Create _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\DropMe.mdb;" With .ActiveConnection ' Dummy one-row temp table for bulk inserts .Execute _ "CREATE TABLE DropMe (anything INTEGER);" .Execute _ "INSERT INTO DropMe VALUES (1);" .Execute _ "CREATE TABLE MediaCategories ( media_category_name" & _ " VARCHAR(30) NOT NULL PRIMARY KEY ) ; " .Execute _ "INSERT INTO MediaCategories (media_category_name)" & _ " SELECT DT1.media_category_name FROM ( SELECT" & _ " 'Films' AS media_category_name FROM DropMe" & _ " UNION ALL SELECT 'Family films' AS media_category_name" & _ " FROM DropMe UNION ALL SELECT 'Books' AS" & _ " media_category_name FROM DropMe UNION ALL" & _ " SELECT 'Family DVDs' AS media_category_name" & _ " FROM DropMe UNION ALL SELECT 'Plays' AS" & _ " media_category_name FROM DropMe UNION ALL" & _ " SELECT 'DVDs' AS media_category_name FROM" & _ " DropMe ) AS DT1;" ' Drop temp table .Execute _ "DROP TABLE DropMe;" .Execute _ "CREATE TABLE CompulsoryMediaCategories (" & _ " media_category_name VARCHAR(30) NOT NULL" & _ " PRIMARY KEY REFERENCES MediaCategories" & _ " (media_category_name) ON DELETE CASCADE" & _ " ON UPDATE CASCADE, category_sort_order" & _ " INTEGER NOT NULL UNIQUE, CONSTRAINT compulsory_media_category_sort_order__positive" & _ " CHECK (category_sort_order 0) );" .Execute _ "INSERT INTO CompulsoryMediaCategories VALUES" & _ " ('Family films', 2);" .Execute _ "INSERT INTO CompulsoryMediaCategories VALUES" & _ " ('Family DVDs', 1);" Dim rs As Object Set rs = CreateObject("ADODB.Recordset") With rs .CursorLocation = 3 ' client side .ActiveConnection = cat.ActiveConnection .Source = _ "SELECT M1.media_category_name, IIF(C1.category_sort_order" & _ " IS NULL, 0, C1.category_sort_order) AS" & _ " category_sort_order FROM MediaCategories" & _ " AS M1 LEFT JOIN CompulsoryMediaCategories" & _ " AS C1 ON M1.media_category_name = C1.media_category_name;" .Open .Sort = "category_sort_order, media_category_name" MsgBox .GetString(2, , vbTab & vbTab) ' clip string .Close End With End With Set .ActiveConnection = Nothing End With End Sub Jamie. -- |
#6
|
|||
|
|||
Adding a new record in alpha with an exception
aehan wrote: I have in the end used Karl's solution Jamie, I did have a go, but I don't think my skills are at that level yet, I will have another look later when I have more time because I would like to understand it properly. That's OK ;-) I should warn you there is a flaw in KARL's design: TopSort can contain duplicate values other than zero. In my design the equivalent column is constrained as unique, therefore the sort order is *always* explicit. You could modify KARL's design by adding a further column so that one column determined whether the row was bottom/compulsory and one which determined their sort order. I'd post the code to demonstrate what I mean... but I don't think you'd appreciate it g. I actually think that if you took the time to understand my design you'd find the two table approach much simpler vbg. Jamie. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Report printing alpha instead of record order | WCDoan | Setting Up & Running Reports | 3 | March 27th, 2006 10:27 PM |
Adding tables | Gertjan | Running & Setting Up Queries | 1 | December 19th, 2005 05:20 PM |
Canceling adding a record | Dorian Chalom | Using Forms | 7 | May 4th, 2005 05:05 PM |
Bit more about adding a record.... | BruceM | Using Forms | 4 | November 8th, 2004 06:38 AM |
Taking data from one record, adding to previous record? Help! | Forrest Gump Ky | General Discussion | 1 | October 16th, 2004 11:59 PM |