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

    Report data as a color-coded grid

    I'm working on a database, which happens to be for a cemetery. Currently, the cemetery information is maintained in an Excel spreadsheet where each cell represents a plot. The plots are laid out roughly in the design of the actual physical location and color-coded (by hand) indicating available, sold, occupied. This graphical representation of the land use provides an easy view to the management as well as prospective customers.

    The database is designed to gather, store and report the information above as well as many other aspects of cemetery operations (such as sales of plots, contact information for owners, etc.).

    My question is this... how can I take the data in my database (which includes the disposition of each plot) and create a graphical representation of the land similar to what is currently being done manually with the Excel spreadsheet? I can continue to use Excel (in a more automated way) or do this entirely in Access. There's no need to keep the spreadsheet if Access can do it independently.

    One other point of note... each plot is assigned an alphanumeric value, like FP18 or FW26. These values are similar to the layout of cells on a spreadsheet, but would need a lookup table or some other mechanism to draw a direct relationship to cells on a spreadsheet.



    Has anyone done anything similar to this or can anyone provide advice on the easiest method to achieve this goal?
    - Dave

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,031
    By hand means selecting color from the color fill tool?

    Have you looked at Conditional Formatting in Excel? It is also available in Access.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    Quote Originally Posted by June7 View Post
    By hand means selecting color from the color fill tool?

    Have you looked at Conditional Formatting in Excel? It is also available in Access.

    Yes - "by hand" means going to each cell and changing the fill color manually as needed.

    I'm somewhat familiar with conditional formatting, but I'm not even at that point yet.

    My first question is how do I generate a report in the format of an editable Excel spreadsheet and color the cells? I know I can export the results of a query as a spreadsheet, but how do I create a report in Access that's a spreadsheet that I can manipulate based on queries?

    And, again, I'm assuming that the spreadsheet path is the easiest because it's what's been done. If there's another way to achieve a similar goal, I'd be open to those ideas as well.
    - Dave

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,031
    Reports are created by placing textboxes on report object and binding the textboxes to fields of the report's RecordSource. The RecordSource can be a table or query. The textboxes can be positioned and formatted to give the appearance of a grid. If you want a report that graphically represents the physical layout of cemetery plots, I expect it could be accomplished. Might be just a CROSSTAB query or might be some elaborate VBA procedure involved.

    A report cannot be altered when it is opened. A rendered report is essentially a static object - the data it displays cannot be changed through the report. Tables hold data and forms are used as data entry/edit interface. Enter data into tables and use queries to manipulate the data. Additional formatting and data calcs in report design can further manipulate the data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    Quote Originally Posted by June7 View Post
    Reports are created by placing textboxes on report object and binding the textboxes to fields of the report's RecordSource. The RecordSource can be a table or query. The textboxes can be positioned and formatted to give the appearance of a grid. If you want a report that graphically represents the physical layout of cemetery plots, I expect it could be accomplished. Might be just a CROSSTAB query or might be some elaborate VBA procedure involved.

    A report cannot be altered when it is opened. A rendered report is essentially a static object - the data it displays cannot be changed through the report. Tables hold data and forms are used as data entry/edit interface. Enter data into tables and use queries to manipulate the data. Additional formatting and data calcs in report design can further manipulate the data.
    I expect it can be accomplished too, but the question is how to do it.

    Maybe if break my request down to something more simple it will be easier to find a solution...

    I have a table with 4 columns. The first two columns are X & Y values representing cells on a grid (could be a spreadsheet). The third column is a text value to be displayed in the cell and the fourth column is a letter or number that indicates the color coding for that cell.

    How can I take the data in that table and turn it into a report where each value is placed according to its X & Y coordinates?
    - Dave

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,031
    Consider:

    Table1:
    X Y Status
    1 1 A
    1 2 S
    1 3 O
    2 1 S
    2 2 S
    2 3 O
    3 1 A
    3 2 S
    3 3 O

    CROSSTAB Query:
    TRANSFORM First(Table1.Status) AS FirstOfStatus SELECT Table1.X FROM Table1 GROUP BY Table1.X PIVOT Table1.Y;
    X 1 2 3
    1 A S O
    2 S S O
    3 A S O
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    I think you're on to something there. I'll have to fool around with it a little more to see if I can get it all to work, but that makes sense to me and looks like it may give properly-formatted output.
    - Dave

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,031
    One problem you might encounter.

    Tables and queries are limited to 255 fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    How many boxes/locations you need to show on report ?

  10. #10
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    About 550. That limit is going to be a problem.

  11. #11
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    To the best of my knowledge, access allows 750 controls on a form/Report. Unbound controls (text boxes without labels , rectangles) may be an option.further, as their location (plan) will not be dynamic, once set, you will not require any code to manipulate their position on form. Use the primary key as the tag for the control.
    Using text boxes -
    1.Based on the data in table you can loop through the controls applying the color coding
    2.Assign a value to the text box in code and use conditional formatting based on it.
    3. Use a Dlookup expression ( to get the plot occupancy status ) as control source of the text box and apply conditional formatting based on its value.
    Using rectangle -
    Change the rectangle background color.
    In both above cases, the control's tag property can be put to use
    Say the control names you choose are Plot1 to Plot550 then
    For i = 1 to 550
    Me("Plot" & i).BackColor=............
    Next i
    You can easily find the required formatting to be applied. Hope this helps.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,031
    You have 550 plots? 550 seems like a small cemetery. Did you mean 550 columns? How many rows?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 2
    Last Post: 08-14-2014, 11:49 AM
  2. Replies: 3
    Last Post: 05-30-2013, 08:03 AM
  3. Using a Grid in a Report
    By Huddle in forum Access
    Replies: 2
    Last Post: 05-15-2012, 02:24 PM
  4. Report in Grid.
    By Robeen in forum Reports
    Replies: 2
    Last Post: 05-10-2011, 07:34 AM
  5. Grid lines vs Grid Dots
    By dharriet in forum Access
    Replies: 0
    Last Post: 10-14-2008, 09:17 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 - Senior Forums