1

I'm not sure if there is a way to get this to work, but I've got my fingers crossed:

I have a database which is keeping track of Inventory for a school district. Part of what is being tracked is the special (non image) software that is installed on a device. No computer can have more than 5 extra software, so I have 5 fields, Software1 - 5 which all lookup to a Software table that contains the possible software to choose from.

What my client wants, is to be able to find all devices that have a certain software installed. Of course, Access Web Apps do this almost without having to ask, by creating a popup view when clicking on a lookup field. The problem I have is that because I have 5 separate fields, and a particular software title could be in any one of those 5 fields, I have no way of finding ALL the devices that have that software in ANY of the 5 fields. I can only find the devices with that software in the SAME field that was clicked on.

For example, each device has a Model number - something also related to another table. My popup view for the Model has a subview which links the (parent) ID field from the Model table, to the (child) Model field in the Inventory table. This way, when a Model is clicked, in the subview I see all devices of that same model.

But, for the Software fields, when I link the parent ID field from Software, I can only relate ONE child field to the view, meaning only one of the 5 Software fields in the Inventory table. So if XYZ Software is in the Software1 field, and is clicked on, it won't find any devices that have XYZ Software in the Software2, 3, 4, or 5 fields.

I have "hacked" it into a fairly ugly looking thing where I use the same popup for each of the 5 fields. The popup actually has 5 separate subviews, each linked with one of the 5 child Software fields (so you could have 2 devices in the first subview that have XYZ Software in Software1, and then see another device in the second subview that has XYZ Software in Software2, etc). But this looks really bad and I would like to do it a better, more cohesive way. Every idea I come up with (use a query, use a subview within a subview, etc.) still gets snagged when I have to link that subview to only one child field.

I wish the web apps could have multi-value lists like desktop databases, but alas, not at this time. I would love to share a screenshot of my current popup view so it's explained a little better, but I don't have enough rep at this point, so please excuse my lowly-ness.

Does anyone have any ideas on how to get around this situation? Is there a way to set up a query for the subview that I'm missing? Or a way to hack it some other way? I'm all ears for suggestions!

Thanks in advance,

~SetsunaMH

SetsunaMH
  • 23
  • 7

2 Answers2

0

I know it's been almost a year since I asked this, but since no one ever offered an answer, I figured it would be good to post what I did as a workaround.

I had no way to build a query that would search all 5 fields for the software to display them - every attempt I made would not work. What I wound up doing was what I mentioned in my original post: creating a special popup view, with 5 sub-views: each one an individual query for each of the 5 software fields. Clunky, and kind-of a pain, but it worked for my client (the database was a stop-gap until they could integrate another system, so it didn't need to be 100% perfect anyway).

Moral of the story: I hope Web Apps will have multi-value lists in the future... :)

SetsunaMH
  • 23
  • 7
0

In my opinion, your table structure is flawed. You should have 3 tables. One for devices ("tblDevices"), another for Software ("tblSoftware") another "tblJoin" to establish a many-to-many relationship. You need this because it is entirely possible to install the same software on multiple devices.

tblDevices

DevID - AutoNumber - PK

Model - Text (add other fields as necessary to describe Device-related data.)

tblSoftware

SWID - AutoNumber - PK

Software - Text (ONE field here, not FIVE) (add other fields as necessary to describe Software-related data.)

tblJoin

JoinID - AutoNumber - PK

DevID - Whole Number

SWID - Whole Number

(You CAN add additional fields here if you feel the need to, but I'd try to avoid doing so if possible.)

Screenshot[![][1]]2

I know the screenshots are sadly lacking in way of explaining. I have tried something new and have posted a thread in the Microsoft Community, asking for someone to verify that my plan to upload a SHAREABLE app will work. http://answers.microsoft.com/en-us/msoffice/forum/msoffice_sharepoint/how-to-publicly-share-an-access-2016-sharepoint/607c9f4e-476a-4849-9207-0c7c7f5b2924?tm=1472316250163

Don Leverton
  • 19
  • 5