Go Premium for a chance to win a PS4. Enter to Win

x
?
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
?
105 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 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 40

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

926 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