A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Adding a new record in alpha with an exception



 
 
Thread Tools Display Modes
  #1  
Old May 29th, 2006, 05:50 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 29th, 2006, 06:07 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 30th, 2006, 11:15 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 30th, 2006, 11:22 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 30th, 2006, 12:24 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 30th, 2006, 12:48 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 08:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.