Solved

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

Posted on 2016-07-19
3
80 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 36

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

828 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