Solved

How to only show certain fields based on a query in MS Access 2016/Office 365 Sharepoint

Posted on 2016-07-19
3
63 Views
Last Modified: 2016-08-05
I'm trying to build a web app for our office 365 Sharepoint site where users can query our current stock levels at different warehouses. So far I've got a single table with info on the warehouse and its current stock levels. Items that have no stock are given a 0. I can build a view where a user enters the item they are looking for and it returns all warehouses that have that item in stock. The problem is, it returns the whole record including all the 0 items. I know I can build a query where it only shows me that one field, but I want to be able to only show the items the warehouses have in stock. For example, Atlanta has 2 shirts, 0 hats, 4 pants. New York has 0 shirts, 0 hats, 5 pants. LA has 1 shirt, 1 hat, 2 pants. Right now if I query pants, it correctly returns all 3 records. What I want it to do is to return the 3 records but only show the items that are in stock. So for that example it would return Atlanta 2 shirts, 4 pants, New York 5 pants, LA 1 shirt, 1 hat, 2 pants.

I admittedly do not have much experience at all with Access or databases in general. Please let me know if this is not possible or if the database needs to be designed completely different. I had a database class back in college but that was over 10 yrs ago and I haven't used it once in my job before now :)
0
Comment
Question by:Kyle King
3 Comments
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 41719821
What I want it to do is to return the 3 records but only show the items that are in stock. So for that example it would return Atlanta 2 shirts, 4 pants, New York 5 pants, LA 1 shirt, 1 hat, 2 pants.

Web apps are uncommonly used monsters but what you want is a little unclear.
Queries are typically rectangles, the selected columns X the returned rows.
Now, you can do things to show something different than zero, but you cannot really break that 'rectangular' paradigm for the query

Presentation is a different story.
On a form, if you want the controls that display things to be Visible = False when the quantity is zero, that's a different thing altogether.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41721350
The query should select only items that have an instock quantity > 0.

Select ... From YourTable Where Quantity > 0;
0
 

Author Closing Comment

by:Kyle King
ID: 41744370
Thanks Nick...that's exactly what I was looking for! Not exactly how to return something other than the full record but rather to only display certain fields. Thanks!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary In SharePoint 2010 it is easy to create custom color themes to jazz up a site. Theme colors can also be created in PowerPoint 2010 with a few clicks. But how do the chosen colors actually look in the SharePoint site? The attached PowerPoint…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now