?
Solved

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

Posted on 2016-07-19
3
Medium Priority
?
93 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 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 38

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

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