Results 1 to 7 of 7

How to copy data from a query to a table?

  1. #1
    Frog is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2018
    Posts
    3

    Smile How to copy data from a query to a table?


    Hello all,

    I'm a newcomer to Access, so please go easy on me I'm learning on the job, as my primary responsibilities fall outside of Access.

    My predecessor at my place of employment left some very involved instructions for how to carry out a certain email update processes for my employer's database, and I'm about half-way through completing them...but I'm getting hung up on one step that requires me to copy information from a query into a table.

    After creating and running a certain query, results come up in columns under a new tab called Query1. I'm not sure if this is considered a query, or query results displayed in a table, but as far as I can tell it's a query (please correct me if I'm wrong). Now the instructions tell me that since this query isn't editable, I'll have to copy the info from this query into a table in Access. I'm instructed to highlight all columns, click copy, go to the Create tab, select table, and paste all rows into the new table once it opens. So I create a new table via the Create tab, and it's called Table1. The first column automatically populates the header with "ID", and the second column with "Click to Add". The first cell under "ID" is filled with "(New)".

    And here's where the trouble starts. After selecting all the columns (I've tried a number of different methods, including selecting from the top left corner, using shift+click on columns, and using shift+click on cells beneath the column headings) and copying them, I'm unable to paste them into the new table I've creating. When I select either of the cells in the top left corner, the "Paste" option in the toolbar is grayed out except for "Paste Append". When I select "Paste Append," an error message comes up saying:

    "The name you supplied is a reserved word. Reserved words have a specific meaning to Microsoft Access or to the Microsoft Access database engine. If you use a reserved word, you may receive an error when referring to this field."

    I click OK, and this message comes up:
    "The field name you entered does not follow Microsoft Access object-naming rules. If you pasted the name from another application, try pressing ESC and typing the name again. For information about naming objects, click Help."

    The second message, but not the first, also comes up when I try pasting by right clicking and hitting "Paste as Fields" from the drop-down. I've learned from clicking "Help" that apparently fields can only contain up to 64 characters, and that periods (along with several other special characters) are not allowed. Some of the columns I'm trying to paste definitely contain periods (since one column contains email addresses) and/or more than 64 characters (since one column contains service descriptions which are multiple sentences long).
    Edit: Some of the cells contain more than 64 characters; none of the fields do. So I can see why this would be a problem.

    But somehow my predecessor used this process for years, successfully copying and pasting this info from a query to a table, which makes me think I have to be missing something. Until now I've had no problem adding this data to Access; the trouble only arises once I try to copy and paste from query to table. So do the "no periods" and "no more than 64 characters" rules only apply when pasting data?

    I'm pretty confused. My predecessor's instructions have been excellent so far, until this point...I just don't see how my she managed to do what she's describing. So I'm hoping one of you Access gurus can pinpoint what this rookie is missing and give me a heads up!

    Thanks for your help!
    Last edited by Frog; 12-04-2018 at 11:53 AM. Reason: Clarification

  2. #2
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,708
    There's a lot of information here and I'll try and cover the main points

    1. I would recommend that you watch some of the Access videos by Steve Bishop on You Tube (there are over 100 in all)
    These start with topics at beginner level and progress through to Advanced level.

    2. Your Query1 sounds like a select quewry showing a number of records based on fields from one or more tables.
    If you look at it in design view, you will be able to see how it is derived.

    3. Normally there should be no need to append these results to a table as you can just run the query as needed.
    If you do append them to a table, you will be duplicating data which is almost never a good solution.

    4. However if there is a very good reason to do so, save a copy of your query with a new name then, in the design ribbon change it to an append query.
    You will then need to enter the name of an existing table having the same field names as in your query.
    It is possible to rename your query fields if necesary but that can be explained later if needed

    5. If there is no suitable existing table, you would instead create a make table query the first time this is done.
    Enter the table name & run the query.
    In future you would normally append data to that tabble with an append query

    Although possible to do, you should never need to paste records into a new or existing table

    6. Field names should normally consist of letter & numbers only with the possible exception of underscores
    If the names contain any special characters such as % or spaces, there will be problems later on.

    7. There are a number of reserved words used by Access & these should never be used as field names e.g. SELECT, NAME, TYPE etc
    Instead use a descriptive name e.g. LastName instead of Name

    8. Field names cannot be longer than 64 characters. They should normally be as short as possible whilst ensuring their meaning is clear.
    If what you are seeing are >64 characters,you are probably looking at aliases ('nicknames') for those fields/objects.

    However, your predecessor did this, she made lots of basic mistakes & you would be wise not to follow that bad practice
    I'm sure I've omitted some things but that's probably more than enough for one reply.

    I expect others will plug any gaps/expand on my comments etc

    Good luck
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  3. #3
    Frog is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2018
    Posts
    3
    Thank you ridders52! Your response is extremely helpful - I appreciate the detail.

    I've realized that I miscommunicated some important details in my initial question. I mistook "fields" as meaning the empty spaces not only at the top of each column, but also in the cells. I see now that "fields" refers only to the former (I'm guessing heading would be an appropriate synonym?) In any case, I made the mistake of saying that some of my fields contain more than 64 characters, and this is not true. Some of my cells contain more than 64 characters, but I'm guessing this is OK. I'm going to edit my initial question to clear up this miscommunication.

    A few of the fields in my query do, however, contain periods. The thing is that these periods weren't added by me; they were added by Access in the creation of the query. For instance, the query created a field called "Links.Provider ID" when pulling from field called "Provider ID" within a table called "Links". This might be because during the query design process, I dragged connecting lines across the "Provider ID" field in three different tables, including the "Links" table (per my instructions). So that explains the periods - but I don't know how to avoid having a period in the field name if this is how Access wants to represent this process.

    Also, a few fields contain the forbidden word "Name" (not all caps though - is this OK?).

    Thanks again ridders52! I'm going to go through all the points you made, step by step, and try to apply each of them to the process I'm working on. And in the future I'll try to avoid having to copy and paste data from one table or query to another. I'll keep you posted as I make progress!

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,624
    you might find that a field or other object uses a name contained in this list, which you might want to bookmark
    http://allenbrowne.com/AppIssueBadWord.html

    A query presents what is probably most accurately described as a datasheet view, to answer that question. It is also a possible record source if you need to mine it for data, in which case, it's generally known as a "domain". A table is also a domain, and its view resembles a datasheet, although I wouldn't call it that.

    What often happens whether the practice is based on sound design or not, is that something changes elsewhere, such as in the source data, and a long practiced method suddenly fails. Thus if it worked for you before, it's probably nothing that you're doing wrong now. Agree with the comment that querying data only to store it in another table is not typical, but I have done that on rare situations. If you must do so, perhaps try to determine if there have been any changes upstream, which could be data input errors or the like. You will have to identify the conflicting names and fix them at one end or the other. It might be more prudent to simply use code to append the query results to a table that is never replaced. Macros are another possibility, but experienced developers tend to avoid them. As a result, knowledge of their use might be a bit limited in those circles.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start every sentence with, like, "so"?

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,624
    Tables have fields and records, analagous to Excel's columns and rows. A cell isn't a proper reference to the intersection of a table field and record; it is simply a field. What cannot contain more than 64 characters is a field NAME. The name of a field is one of its properties, which includes other things such as the type of data it holds. The number of characters that a field can contain depends on its data type property. I think short text is 255 while long text (formerly memo) is a whole lot more (too busy to look it up right now!).

  6. #6
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,708
    Just a brief response as Micron has already responded.
    Access will (unfortunately ) allow the use of reserved words such as Name for database objects and field names. In general, Access does not care about case.
    You really do need to do some research into database terminology such as fields, records, tables, queries, datatypes etc otherwise we are going to be miscommunicating badly.
    As I said before, I recommend looking at Steve Bishop's videos on YouTube - they are free - suggest you start at the first one

    For instance, the query created a field called "Links.Provider ID" when pulling from field called "Provider ID" within a table called "Links". This might be because during the query design process, I dragged connecting lines across the "Provider ID" field in three different tables, including the "Links" table (per my instructions). So that explains the periods - but I don't know how to avoid having a period in the field name if this is how Access wants to represent this process.
    The query should just have created a field called ProviderID from the Links table.
    If you are viewing the query in SQL view, it will be displayed as Links.ProviderID i.e. tablename.fieldname
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  7. #7
    Frog is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2018
    Posts
    3
    Thank you Micron and ridders52! You guys are right - I'll definitely have to take a look at those videos by Steve Bishop and get acquainted with the correct terminology. And I certainly have to gain a greater understanding of Access from the ground up before jumping into any of my own projects in Access.

    The weird thing is that even in regular Datasheet view, Access is displaying the field as Links.ProviderID...

    And thank you both again for this wealth of information! I'm definitely on a better track now. I'll see what I can do with this new knowledge and let you guys how it turns out

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-29-2018, 09:26 AM
  2. Replies: 18
    Last Post: 08-09-2018, 05:45 AM
  3. Replies: 11
    Last Post: 10-12-2017, 06:14 AM
  4. Copy data in table
    By Tommo in forum Access
    Replies: 3
    Last Post: 09-27-2015, 06:23 AM
  5. copy data from one table to another
    By Sureshbabu in forum Access
    Replies: 1
    Last Post: 01-08-2012, 12:27 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums