+ Reply to Thread
Results 1 to 3 of 3

Table dependent on a dependent query table

  1. #1
    Registered User
    Join Date
    12-24-2018
    Location
    NC, US
    MS-Off Ver
    office 2016
    Posts
    12

    Table dependent on a dependent query table

    Hello all,

    As the title of this thread reads:

    I have two data sources that merged create a single table conformed and filtered to my desired layout. Think of this as the master table that all subsequent tables will draw from based off of their own individual parameters. My goal is to automate the process of importing data and scrubbing it only once, after merged. I currently have to remove certain rows, usually only 6-20 out of potentially 1500-5000 rows which is why I haven't bothered seeking council on creating a step that does this in the midst of merging the data (if even possible). Any ways, lets say for simple math I refresh my query and it pulls in 100 lines of data and I have to delete 10 lines leaving me 90 lines of data that all my other tables will in some way or another use. If I try to run a query from that table, after its been "scrubbed", it still pulls from the data connection it has, this pulling the 100 rows of data instead of the desired 90. If I refresh the data from the merged query table, I am aware it would revert back to 100 rows of data.

    Bottom line: Is there a way to have a data query connection within a separate worksheet that will pull from that table, creating its own table based on that queries separate parameters which would be individually refreshed after the scrubbing process puling only the remaining data?

    Thanks in advance for anyone who takes the time to address my inquiry!

  2. #2
    Valued Forum Contributor
    Join Date
    02-06-2014
    Location
    N/A
    MS-Off Ver
    N/A
    Posts
    373

    Re: Table dependent on a dependent query table

    Are you using "Data > From Other Sources > From Microsoft Query" ? How are you connecting to the data? My first thought it to modify the SQL parameters that are bringing in the data so that you don't need to manipulate the data once it's queried.

  3. #3
    Registered User
    Join Date
    12-24-2018
    Location
    NC, US
    MS-Off Ver
    office 2016
    Posts
    12

    Re: Table dependent on a dependent query table

    Thanks for the response!

    Currently, I have two sources of data: 1) A CSV file located on the server that is created as an output file from a report automatically. 2) a table within the workbook that serves as a VLookup data table. Combined I merge those two sources creating a table with the data in the format that I need. The only that is providing me trouble, is this:

    The final part of the cleaning process of the data is a more hands on approach at the moment. Imagine that one row of data is a transaction for a 100 units with a reference number of 12345 but at some point during the same period, there was a reversal for that transaction. That reversal transaction would result in a new line of data with a -100 units and a new reference number of 12345C. For the reports that must be submitted, they can't include the ones that "wash" or cancel each other out. At the moment, I am not aware of a way to "remove duplicates" in this sense. There are also times there will be a reallocation in addition to both of the previous rows I mentioned resulting in an additional row with a reference number of say 12345A, where the original reference number was posted, then reversed, then reallocated. The reallocation reference number must stay in the report as it will belong on one or more of the reports that will all pull from the raw data that is presented through the merged query.

    Any additional thoughts?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dependent drop down in excel table 2 conditions
    By faodavid in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-15-2016, 12:57 PM
  2. Replies: 0
    Last Post: 10-10-2015, 09:51 AM
  3. Dependent lists - unsorted table
    By Itolond in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-15-2014, 03:13 AM
  4. [SOLVED] Dependent Table Analysis Problem
    By RaydenUK in forum Excel General
    Replies: 17
    Last Post: 03-29-2014, 11:54 PM
  5. Update dependent cells when table is sorted
    By matthew.pringle in forum Excel General
    Replies: 0
    Last Post: 01-25-2011, 04:42 PM
  6. sorting table by dependent column
    By chewwy in forum Excel General
    Replies: 3
    Last Post: 06-24-2010, 08:09 AM
  7. Replies: 0
    Last Post: 03-30-2005, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1