Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    ruiter is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2016
    Posts
    12

    paste excel column data into query?

    I would like to copy and paste the data from an excel column into a column in my Access query. When I try to do this it takes all the data and tries to put it into one field. I know how to paste into a regular table but it doesn't seem to work for a query.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If their are multiple records in your Access query and Excel data, how are you ensuring that the correct values got to the correct records?
    Can you explain in a bit more detail exactly what you are trying to populate (there are probably better ways of doing it)?

  3. #3
    ruiter is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2016
    Posts
    12
    Hi Joe, thanks for responding! First let me say I am not trained in Access, only self-taught to the point of (usually) being able to get done what I need. So be patient Here's my dilemma more specifically: my database contains information about music recordings, and the items (album label number) on which those recordings have been used. Frequently I get long lists of requests in excel format asking for information per album (label number) . Instead of my retyping each album number from those Excel inquiries into my Access query column, I'd like to just be able to copy the Excel column in the original request and paste it into my query under the album number field column. I would be so grateful if you help me with this!

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So, is there some field in both the Access query and Excel table that the data "matches" up on (i.e. some ID field)?
    If so, you can simply import or link the Excel file into a new Access table, and then do an Update Query between this new table and your original Access table/query.

  5. #5
    ruiter is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2016
    Posts
    12
    Yes, there is. I have never used Update Query, I will play around with it and hopefully have some success. If not, I will write back. Thanks again!

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    See here: https://www.safaribooksonline.com/li...7/ch01s06.html
    and here: http://www.techonthenet.com/access/q...date2_2007.php

    Post back if you run into specific problems trying to do it.

  7. #7
    ruiter is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2016
    Posts
    12
    Thanks again... Still having trouble, I probably didn't explain it well. I don't want to update any data, any fields, any tables. I want to do a query based on, let's say, a certain group of album label numbers that returns certain information about those albums. Let's say I've saved the query that returns the information I need as "Query 1". Then I get a request in Excel for information about a long column of album label numbers that I want to run in "Query 1". If it were just say 10 items, I would type those album label numbers into Query 1 in the appropriate column/field ("album label number" in this case). Then Press run (!) and get my info in table format. But when the inquiry is a very long list in Excel, rather than typing in the whole list, I'd like to cut and paste the Excel column of label numbers and place it in the album label nr column in query1. Or in some fashion be able to take the info from Excel and put it into my "album label number" column in "query1" AgainI don't want to update any data, or any tables. I only want to run my query with the Excel data. Sorry for my nontechnical explanation...

  8. #8
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    you may want to Link the Excel Object into Access. This will let you pull in the data like its a table, and then build a query to filter down the data that you want.

    http://answers.microsoft.com/en-us/o...9b31bf5?auth=1

  9. #9
    ruiter is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2016
    Posts
    12
    Thanks, I will try this later today!!

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It is important to understand exactly what a query does in Access. It simply returns field values from underlying tables/queries used in the query, or it returns calculated fields. You cannot paste anything into it that doesn't exist in one of the the underlying fields (at least not without updating the underlying value).

    If you just have an Access table/query of a bunch of potential records, and only want to return details of the ones in your Excel data, you would either import or link that Excel data into an Access table, and then do a query where you link the field showing the Excel value you want to the Access query/table fields that holds those field values. That will return just those specific records.

    If you continue to have issues, I think it would be very beneficial if you could post some sample of:
    - what your Access query looks like
    - what your Excel data looks like
    - what your expected output should look like

  11. #11
    ruiter is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2016
    Posts
    12
    Thanks, Joe, I will try this later or tomorrow, if I can't figure it out, I'll try to get you the query / excel data / and expected output. I really appreciate the time!

  12. #12
    ruiter is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2016
    Posts
    12
    Click image for larger version. 

Name:	Screenshot - 4_29_2016 , 6_44_05 AM.png 
Views:	8 
Size:	32.0 KB 
ID:	24502Click image for larger version. 

Name:	Screenshot - 4_29_2016 , 6_43_06 AM.png 
Views:	8 
Size:	12.7 KB 
ID:	24503Click image for larger version. 

Name:	Screenshot - 4_28_2016 , 6_09_06 PM.png 
Views:	8 
Size:	77.5 KB 
ID:	24504 Hi, I'm not having any luck with this, due, I'm sure to my lack of formal Access training. I've attached my query, a sample Excel table (in reality it would be a much longer list or else I would just type the numbers in myself) and the expected results. I did link/import the excel file and tried to make a query linking the Excel field to the matching field in Access (Tr_Nr) but I can't figure out how to populate the query with the actual data (label numbers) from the excel file. Again, I'm basically trying to copy the column in Excel to the Tr_Nr column in the Access query. Any help will be tremendously appreciated.

  13. #13
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Again, I'm basically trying to copy the column in Excel to the Tr_Nr column in the Access query. Any help will be tremendously appreciated.
    That doesn't seem to make much sense. In your Excel file, it seems you just have a single column listing numbers. So those match up to your "Tr_Nr" field in Access?
    Do you really want to paste them into your query (if so, where?), or do you just want to use that to decide which records from your Access query you want to show/return (i.e. the query should only return records where the Tr_Nr field is equal to 25429, 871780, or 25397).

    Note one potential issue I see. It looks like your numbers in Excel are formatted as numbers (as is usually indicated by the values being right-justified in the cell), but the ones in Access appear that maybe they are formatted as text (as is indicated by their left-justification). They will need to be the same data-type in order to link them up (can't link number fields to text fields).

  14. #14
    ruiter is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2016
    Posts
    12
    Thanks for pointing out about the numbers vs. text. The Tr_Nr field is formatted also as a number. The answer to both of your questions is yes ... if i pasted that column from excel into my access query in the column Tr_Nr, it would return the info that I want. But also yes, as end result I want my Access query only to return records where the Tr_Nr Field is equal to that list of numbers from the excel file.

  15. #15
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Please do the following:
    - Switch your current query to SQL View, and copy and paste the SQL code here.
    - Let us know what the table name you are giving the linked Excel is Access is, and what field name it uses for the data in this table.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-22-2015, 11:13 AM
  2. Replies: 4
    Last Post: 04-29-2014, 04:33 AM
  3. Replies: 4
    Last Post: 12-19-2013, 01:18 PM
  4. SQL Update query using data in Excel column
    By tylerg11 in forum SQL Server
    Replies: 2
    Last Post: 09-03-2013, 05:10 PM
  5. Paste Data From Excel To Access Form
    By fanzak in forum Programming
    Replies: 2
    Last Post: 08-16-2010, 02:40 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
  •  
Other Forums: Microsoft Office Forums