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
|
|||
|
|||
Selecting Distinct Memo Fields
I know that an error will occur when setting up a Query to display Distinct
records of a Memo field. I also know that there is a work around this, which involves referencing the original table containing the memo field. My problem is as follows. I have a table containing 3 fields, the last two being Memos. Field 1 (Key): Sample# Field 2: Labeling Field 3: Core Labeling I would like to settup a query in which only distinct records (non truncated) of either Field 2 or Field 3 may be dispalyed. I need this because I would like the user to have the option of selecting from a list of existing Labeling and Core Labeling entries when entering data, rather than typing it all out again. What i've done but hasn't worked to my full satisfaction: I Created a Query (qryLabeling) that selects all the entries for the Labeling field. SQL: SELECT tblLabels.Labeling FROM tblLabels; Then I created a Query (qryTest) that includes the query above and the Original table which displays Distinct records of one of the Labeling fields. This works, but produces trunkated data because as I already know access can only do comparisons 255 characters in length. SQL SELECT DISTINCT qryLabeling.Labeling FROM qryLabeling, tblLabels; So does anyone know how to solve the problem of displaying Full Length Unique (Distinct) memos? I would really really appreciated if someone can help me with this. |
#2
|
|||
|
|||
Selecting Distinct Memo Fields
There really isn't any way of doing this that I know of.
You can do something like the following to get the first 750 characters (and you could obviously extend this to more parts). SELECT DISTINCT Mid(MemoField,1,250) as Part1 , Mid(MemoField,251,250) as Part2 , Mid(MemoField,501,250) as Part3 FROM SomeTable Hope that helps to give you some ideas. But this solution will probably be fairly slow. John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County Daffy wrote: I know that an error will occur when setting up a Query to display Distinct records of a Memo field. I also know that there is a work around this, which involves referencing the original table containing the memo field. My problem is as follows. I have a table containing 3 fields, the last two being Memos. Field 1 (Key): Sample# Field 2: Labeling Field 3: Core Labeling I would like to settup a query in which only distinct records (non truncated) of either Field 2 or Field 3 may be dispalyed. I need this because I would like the user to have the option of selecting from a list of existing Labeling and Core Labeling entries when entering data, rather than typing it all out again. What i've done but hasn't worked to my full satisfaction: I Created a Query (qryLabeling) that selects all the entries for the Labeling field. SQL: SELECT tblLabels.Labeling FROM tblLabels; Then I created a Query (qryTest) that includes the query above and the Original table which displays Distinct records of one of the Labeling fields. This works, but produces trunkated data because as I already know access can only do comparisons 255 characters in length. SQL SELECT DISTINCT qryLabeling.Labeling FROM qryLabeling, tblLabels; So does anyone know how to solve the problem of displaying Full Length Unique (Distinct) memos? I would really really appreciated if someone can help me with this. |
Thread Tools | |
Display Modes | |
|
|