Results 1 to 9 of 9
  1. #1
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20

    Splitting data into separate columns

    I have a table with data in a format like: AA01, AA02, AA03... AA10, BA01, BA02, BA03... BB01, BB02, BB03, etc.



    How can I set up a report so that each collection of data that starts with the same two characters is in its own column?

    Is there a way to do it with a single main report? Or do I need to set up subreports for each column?

    If it helps, the number of entries for each column is the same. I know I can achieve what I want by creating a multi-column report and adjusting the size of the page so it wraps at the correct points, but that seems prone to error.
    - Dave

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please tell us more about the purpose of the database and the table, fields and field values involved in plain English.

  3. #3
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    Quote Originally Posted by orange View Post
    Please tell us more about the purpose of the database and the table, fields and field values involved in plain English.
    It's a database of cemetery plots laid out in a grid pattern, like a spreadsheet. I want to populate each box with the owner of the plot.

    The table is a list of the plots and the owners of each one. I want present the data in a grid as the cemetery is actually laid out, not as one long list which is the default view for the report.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you could use a crosstab query

    set the row header as a formula right(myfield,2)
    set the column header as a formula left(myfield,2)
    set the value as first (or last/max/min) as myfield & " " & owner

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may be interested in the dialog in this thread
    It has some approaches with text boxes and events. But Ajax's suggestion may be easier.

    I'd also suggest you look at the database demo by ChrisO here. It is only the database itself and how the info is presented that I'm suggesting you look at. It involves coding behind the scene and may be overkill, but good demo.

    I recall a few posts over the years dealing with Cemeteries. One I recall was a software product called Stone Orchard.

    Good luck.

    Update: I didn't realize this current post is related to this post.
    It's helpful to readers if you identify related posts (same topic) in other threads you may create.

  6. #6
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    Thanks for your suggestions. I think the crosstab is the way to go. I need to see if I can get the right data into the query and have it copy and retain the necessary formatting going into Excel. I'll probably need an extra query or two getting there, but it should work.

    If that doesn't work I'll explore some of the other options linked above.
    - Dave

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    as I suggested in your other thread - include in your data the 'spreadsheet co-ordinates' (column/row) and you can place each value wherever you like

  8. #8
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    Quote Originally Posted by Ajax View Post
    as I suggested in your other thread - include in your data the 'spreadsheet co-ordinates' (column/row) and you can place each value wherever you like
    Yes - that's my plan. My concern isn't over the data placement - it's the formatting. I need to get the cells color coded and stuff. As long as I can embed the information I need to do that in the cell data so an Excel macro can update the format, then I'm good.

    As I mentioned earlier - I can use conditional formatting on an Access report to get exactly what I need, but the formatting is lost when I move to Excel or Google Sheets. As long as I can bridge that gap, I'm golden.
    - Dave

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you cannot export formatting of this type to excel. Export format is referring to the format of characters - font, size, bold/italic etc.

    If your plot layout remains static, suggest create an excel template with the excel formatting rules with something like dlookup acting on a table in each cell, then just refresh the table by exporting

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

Similar Threads

  1. Replies: 3
    Last Post: 06-19-2019, 08:59 AM
  2. Replies: 3
    Last Post: 10-19-2018, 04:49 PM
  3. Replies: 2
    Last Post: 06-02-2017, 09:30 AM
  4. Replies: 5
    Last Post: 03-29-2012, 09:21 PM
  5. Splitting a field to separate fields
    By Petefured in forum Programming
    Replies: 8
    Last Post: 06-08-2009, 04:11 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