Results 1 to 8 of 8
  1. #1
    CP611 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55

    Transforming a spreadsheet - queries?

    Hi,
    I have one Excel spreadsheet that need to be transformed across into another spreadsheet. This is currently done using access which was made by someone else but I would like to give it a go myself (I'm a complete novice!). I would like to be able to input a spreadsheet, then run a query which moves columns around, adds more columns and concatenates others and then output it. Are queries the right things to be using, or is it relationships/expressions/macros?!

    Any help would be great, getting very confused! :)

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Definitely use queries. You can do calculations in queries, and place the fields in any order you want.
    Not sure what you mean by "adding columns" though. If they are calculated fields based off current data, you would just add them as calculated fields.
    However, if you need add other values that aren't calculated or hard-coded, you will probably need to add other tables, and then link them together.

    When you are finished, you can simply export the results of the query (by right-clicking on the Query name and selecting export, or setting up a TransferSpreadSheet macro).

  3. #3
    CP611 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    Thanks for that. The process at the moment transforms one spreadsheet into another, which has different headings to the original input file. The output file is then used to import into a CRM database which has a specific template. What I was doing originally is running a query with two tables (one table is the original file which contains data) the other table does not currently contain any data, but it contains the headers that are needed for the CRM template. But when I run the query it does not come up with any data, I think this may be because there is no data in the second table. I will also need to concatenate fields, for example in the input file there are 3 address lines, but it in the output file we need to concatenate address lines 1 and 2 with a pipe in between.

    The end result is to have a form which has a button to input a selected file, press process which runs the query and another button which then outputs the file to go into the CRM.


    Does this make it clearer? Any help would be great

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    But when I run the query it does not come up with any data, I think this may be because there is no data in the second table.
    Do a Left Join from the main table with data to the other, and it will return all the records from the main data table.

    I will also need to concatenate fields, for example in the input file there are 3 address lines, but it in the output file we need to concatenate address lines 1 and 2 with a pipe in between.
    It is easy to concatenate fields in a calculated field in your query, i.e.
    Code:
    Address: [Address Line1] & "|" & [Address Line2]
    The end result is to have a form which has a button to input a selected file, press process which runs the query and another button which then outputs the file to go into the CRM.
    You can create Macros which do most of these steps, and run these macros from your buttons. If you need to make it more dynamic, you can start with a Macro, and convert those Macros to Visual Basic (button on the Macro menu). This gives you the "basic" code you need, and you can modify it to make it more dynamic.

    Note: Unless you have an Action Query (i.e. Append, Make-Table, or Delete Query), you don't actually have to "run" the query. With a select query, it is real-time as you open, reference, export it. So all you need to do is export the Select Query.

  5. #5
    CP611 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    I've looked up a left join, where you right click on the join between the two related headers and determine which table it should choose from, so this is how you would get it to pull information from one table even if the record is blank. But what happens if new columns are being created which aren't in the original table, so there is not relationship that can be created between the two? Sorry if I'm being a complete idiot, I've never used Access or SQL before so learning!

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    A left outer join will return ALL records from one table (nothing will get dropped), and the matching data from the other table, if there is any. If not, it will just return NULLS in those fields for which there is no match.
    You can create one in Access without having any knowledge of SQL. Here is how you create a Left Join in Access: https://www.techonthenet.com/access/queries/joins2.php

    As you are experiencing, Access really isn't something that you can just expect to pick up and start using if you have no experience with it or of Relational Databases. The learning curve is MUCH steeper than it is for Excel.
    I would recommend picking up some introductory Access books, and maybe checking out some resources on-line, like this one: http://www.datapigtechnologies.com/AccessMain.htm

  7. #7
    CP611 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    Thanks for the links. we're slowly getting there. I've created an append query which seems to bring out the information I want. However now we need to format it correctly. I've worked out how to concatenate, now I want to change names etc to Proper case. The code that I have been using, which I've written into the field header within the query is StrConv([Surname], 3). However this brings out an error code when I try to run it saying incorrect syntax error relating to the comma. Any ideas about how to fix this?

    Thanks for all your help

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Is there a reason you do it through Access? You can create an ODBC query in your 2nd Excel workbook, which reads data from your 1st Excel workbook, makes various calculations with read data, and returns result a a table into 2nd workbook. The query reads freshest saved data from 1st workbook whenever the 2nd workbook os opened, or whenever user refreshes the query table in 2nd workbook.
    Last edited by ArviLaanemets; 11-08-2017 at 08:44 AM.

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

Similar Threads

  1. Transforming Rows into Columns
    By tonygg in forum Queries
    Replies: 12
    Last Post: 01-08-2017, 06:07 PM
  2. Exporting two queries into separate sheets on one spreadsheet
    By sam.eade in forum Import/Export Data
    Replies: 2
    Last Post: 07-07-2014, 09:53 AM
  3. Replies: 4
    Last Post: 06-10-2012, 02:29 PM
  4. Replies: 1
    Last Post: 03-05-2012, 04:21 PM
  5. transforming text to number
    By eladz949 in forum Import/Export Data
    Replies: 5
    Last Post: 01-05-2011, 04:06 PM

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