Solved

How to create memo field with make table query?

Posted on 2004-10-11
14
740 Views
Last Modified: 2012-05-05
I would like to create a memo field instead of the default text field with a make table query. How can I do this in one step?

Thanks in advance for your time and help.
0
Comment
Question by:smoyano
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
  • +1
14 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 12281126
Don't follow you. On my machine a typical maketable query is:

Select myOldTable.* into myNewTable from myOldTable;

Where does the default text field come into it?

0
 
LVL 18

Expert Comment

by:Data-Man
ID: 12282060
If the underlying fieldsize is of type memo, then that is what will be created when you run the make table query.

Mike
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12285871
Not sure about your exact needs, but the following query creates a table with a memo field.

Create table Table1 (Field1 memo);
CREATE TABLE [TABLENAME] ([FIELDNAME] [TYPE]);

Hope this answers your question :)
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:smoyano
ID: 12287952
I apologize for being unclear. I am using a make table query and the there is no underlying field for one of the columns. It is a blank which will be populated in a latter step. It is defaulting to text 255. Can I specify in the query grid cell that I want this to be a memo field?

We are selecting into table [Observer Awards] and the Null as Researchers defaults to 255 characters.

SELECT DISTINCTROW SPROJ_tAwards.AWARD_ID AS A_ID, Null AS Researchers INTO [Observer Awards]
FROM  etc.

Thanks for your time and help.
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 12288003
What version of Access are you using...you may be able to run a SQL statement that will alter the column to be of type memo.

Mike
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12288080
Maybe you can set it in the main template (Like the Normal.Dot in Word). Don't really know if you can do that. But if you are sure you want it to be a memo field in the future, why not specify it while creating the table. Text fields are smaller in size and hence are shown as default. It could also be that the field is in reality a null field, but Access shows it as a text field in order to show something. Even if it is a text field now, you can always use an Alter table query in the future.
0
 

Author Comment

by:smoyano
ID: 12288420
I am using access 97.
0
 

Author Comment

by:smoyano
ID: 12288725
eeshahidt,

How do I specify memo type in the make table query? That is exactly what I want to do.
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 12288737
Menu bar, Tools, Options, Tables/Queries tab, set Default Field Type - Memo. Click Apply.

Now when you execute the maketable query, the field should default to Memo.
0
 

Author Comment

by:smoyano
ID: 12289101
GRayL,

Still text 255.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 12289866
I'm running A2000 under Windows XP Home and using null I get a binary type 510 bytes long.  I changed null to "" and then got text.  I could not generate a situation where the datatype defaulted to the memo as specified in options. Sorry. I then tried to creat a query parameter:

parameters fldMemo memo;
select *, fldMemo into myNewTable from myTable;

It would not accept the word memo in the parameters clause.  I could use text, char, number, etc. but not memo.  Go figure!  Anyone else with a idea?
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 12290084
Run the make table query and then run an alter statement.

Mike
0
 
LVL 44

Expert Comment

by:GRayL
ID: 12290744
Mike:  The customer wrote: " How can I do this in one step?"
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 12290786
sorry...I missed that part....not sure it can be done in 1 step

Mike
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question