A real doozy

[Liam Murphy]

First off, I think I finally made a normalized database, my previous attempts of creating databases were not, but they are functional.

In my newest project, I needed to create a database that tracks the locations of items inside a space, and the current state of those items. Each item has up to four
possible states, and the date it was installed/ removed, and who did the installation. That part of it was easy. The difficulty lies in the report I need.

The report I need to generate will be a "map" of the locations of currently installed items, with a corresponding color to each possible state of the item.
What I would like to do is to use a .jpg or .bmp as the background (map), and when I execute the report, if there is an item installed, it shows a colored dot where
that item is. There are 200 possible locations, so I imagine I would have the dots always on the map, but change the background color from transparent to a
corresponding color depending on the date; i.e. if location 1 has not had an item installed (no installation date and no removal date), it remains
transparent, location 2 had an item installed 2 weeks ago and has never been used, so it comes up green, location 3 has an item installed, and has been used, so
it shows up yellow, location 4 has an item installed, used, modified, so it shows up purple, and location 5 had an item installed, but has been removed, so it shows
up red. So on and so forth for all 200 locations. If this isnt possible, I will just do the work myself, print our a report of the states of the locations, and
put dots on paper, but there has to be an automatic way of doing this digitally.

 

[Liam Murphy]

I think I can just use a text box to do what I want, with conditional formatting, but I dont quite know how to write the expression to format the condition.
For example, item 1 is black text with yellow background
when it's status is "installed". Will this work?

 

[Liam Murphy]

An example:

In a text box, can I create a conditional format that would highlight the background of the name of the item using an expression in the conditional format like this:

[Location_tbl].Locations "XX 1" And [Location_tbl].Status "New Installed"

I am missing something here, as it tells me that there is an error, possibly an operand without an operator (Invalid syntax).

 

[SriramNA]

Maybe you can try "Analyze it with Excel" using a mapping add-in:
http://freegeographytools.com/2010/excel-add-on-for-map-and-data-visualization

--
Sriram


"Liam Murphy" wrote in message
The report I need to generate will be a "map" of the locations of currently
installed items, with a corresponding color to each possible state of the
item.
What I would like to do is to use a .jpg or .bmp as the background (map),
and when I execute the report, if there is an item installed, it shows a
colored dot where
that item is. There are 200 possible locations, so I imagine I would have
the dots always on the map, but change the background color from transparent
to a
corresponding color depending on the date; i.e. if location 1 has not had an
item installed (no installation date and no removal date), it remains
transparent, location 2 had an item installed 2 weeks ago and has never been
used, so it comes up green, location 3 has an item installed, and has been
used, so
it shows up yellow, location 4 has an item installed, used, modified, so it
shows up purple, and location 5 had an item installed, but has been removed,
so it shows
up red. So on and so forth for all 200 locations. If this isnt possible, I
will just do the work myself, print our a report of the states of the
locations, and
put dots on paper, but there has to be an automatic way of doing this
digitally.