Solved

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

Posted on 2016-07-19
3
50 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 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

14 Experts available now in Live!

Get 1:1 Help Now