Results 1 to 12 of 12
  1. #1
    newdba12345 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    6

    Question Avoiding blank cells when updating master table from several secondary tables.

    Hello everyone,


    I have a (hopefully) simple question regarding copying a single column of data from several tables into one master table, so I can see if/where they match if at all but I want to see all of the data selected from the other tables in the master table regardless of whether they do match or not.

    I have that working and can see all of the columns I want in the master table, but the problem is I am using append queries to populate the master table but it's creating the entries in a staggered format. So the first column I can see the data without scrollling down, but the 2nd column doesn't start showing data until the first columns data ends and so on for the other columns.

    So to view all returned records in all columns I have to scroll down the entire page, with each column in turn having data and then being empty when the next column has data and so on.

    Am I importing this data in the wrong way in the first place, or can I do something to tidy-up the data once it's all in the master table?

    I've looked for a way to delete the individual empty cells in each column so they then all start at the top but cannot find a way to do that, I've also looked at sorting on individual columns but can't find a way to do that either.

    I'm hoping I'm missing something obvious so any ideas appreciated, I'm on the verge of exporting the master table to Excel and sorting the columns there to get rid of the empty cells and just re-importing but that seems like giving-up.

    Thanks in advance,
    Mike.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    help to see some example data, but suspect the solution is to use a union query

  3. #3
    newdba12345 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    6
    Thanks for the speedy reply, here's a screen grab of the form that presents the data so you can hopefully see what I mean.

    This data comes from several sources with tables with different layouts/no indexes etc., so copying all of the data to a single table first seemed easiest and gave me almost what I require. Note: I am copying just one column from each of the diferent tables into its own column in the master table.

    I just need to eliminate the empty cells at the top of the second column/thrd column etc. so the data moves to the top of each subsequent column and I'll be happy.

    Thanks again.

    Click image for larger version. 

Name:	staggered data.jpg 
Views:	17 
Size:	109.0 KB 
ID:	45657

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps you could have a tableForAnalysis with 2 fields

    DataValue ---------the value you have been importing
    SourceTable ------the tablename where the data was imported from

    Your table after import could look something like: (mock source names)

    Code:
    Dock Sud   Sourcetable1
    MuSa         SourceTable9
    SJK            Sourrcetable5
    Mexico       Sourcetable1
    Mexico       SourceTable9
    ....
    You could then sort by DataValue or SourceTable as appropriate.
    You could also include Date or other info if important to you

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    still not clear to me the requirement - are you just trying to get a list of values? - i.e. instead of moving the second column up, you move it to the left where there are values?

  6. #6
    newdba12345 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    6
    Hi Ajax, I just want to remove the empty cells on top of the data in the second column onwards but keep the columns and data separated as they are if that's possible.

    So I want it to look like this:

    Click image for larger version. 

Name:	Image2.jpg 
Views:	17 
Size:	104.4 KB 
ID:	45658


    Thanks.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why are these imported values in different columns??

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Just what does that data represent.?
    Why different fields?
    How is this data meant to be used?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    still not clear to me the requirement - are you just trying to get a list of values? - i.e. instead of moving the second column up, you move it to the left where there are values?

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    quite simply, it can't be done since there is nothing to go on. No unique ID or table name, no way to relate Assane and Almirata to Doc Sud.

    And even if you could, how does that help you on matching? Are there imperfect matches as a result of typo's etc?

    This feels like an excel approach to the problem or at best way to get a visual view of the data on one screen, So on the basis of your requirement 'so I can see if/where they match if at all' you are going about this the wrong way. Copy your table to excel, then copy paste each column 'up' to eliminate the blank cells - but still don't see how this helps you to identify matching

    If you want to stick with Access, best I can suggest is you will need to create a form with three subforms, each displaying one of the columns, excluding the blanks. If the entries all fit on one screen then there is no need to scroll. If you need to be able to scroll then you will need a relatively complex vba method of synchronising the scrolling of the three subforms.

    If you want to look at a different way of identifying matches, then you would use a query (google sql left join) but not enough information to suggest what this query might look like

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    We need to hear from the OP and gt some details on the requirement. He is telling us HOW he wants output formatted, but we don't know WHAT the business requirement is.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I agree with Ajax re Excel. OP is trying to create records with relations that don't exist. If this is a one off thing I'd just dump it into Excel, drag the ranges side by side, save the workbook and import or link the spreadsheet in Access. Voilaˋ - you have your table. If all I was going to do from there was look at it, I might not even import it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 7
    Last Post: 08-05-2020, 11:34 AM
  2. Avoiding blank choices in a Combo Box
    By Lou_Reed in forum Access
    Replies: 4
    Last Post: 05-09-2017, 12:11 PM
  3. Replies: 3
    Last Post: 01-02-2014, 02:07 PM
  4. Replies: 1
    Last Post: 08-19-2013, 10:59 AM
  5. Replies: 2
    Last Post: 03-13-2013, 06:30 AM

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