By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 IT Pros & Developers. It's quick & easy.

Database displays products when none in stock

P: 28
I have created a report that displays a "Product Summery".
The problem is that it displays all products even when there is none in stock.
Is ther a way to get the report to display only the products in stock ie. greater than zero?
Mar 19 '10 #1
Share this Question
Share on Google+
13 Replies


100+
P: 109
Simple solution would be to base the report off of a query where the criteria for stock level is >0. This should be quite straightforward but if you get stuck, post up your database structure and I/someone will be able to provide an answer!
Mar 19 '10 #2

P: 28
When i say created a report i should have said "have a report" that came with the database when i down loaded it.
Hopefully i have attached a zipped copy of the database so you can see it.
I am reluctant to mess with the format cos it works for me, I can just see that later on when there is more on the database the report will be endless so it just made sence to remove all those with zero holdings? Iff its possible.
Ezzz
Attached Files
File Type: zip Copy of Working Inventory management database.zip (2.73 MB, 55 views)
Mar 19 '10 #3

100+
P: 109
I'm afraid I can't open your attachment because I am using Access 2000. If you can convert it, then I'll take a look.
Mar 19 '10 #4

P: 28
Hopefully the attached is converted to 2000.
Ezzz
Attached Files
File Type: zip db1.zip (473.5 KB, 46 views)
Mar 19 '10 #5

100+
P: 109
Ah, this is very easy to solve!

Open the Report in Design View, Open Properties and select the Data tab. Click the three dots next to Record Source...

In the 'Units in Stock' criteria, simply enter >0

That should prevent products showing up if their stock level is 0!
Mar 19 '10 #6

100+
P: 109
However, isn't there going to be problems when you want to see what products are out of stock and need to be re-ordered?
Mar 19 '10 #7

NeoPa
Expert Mod 15k+
P: 31,186
You could add criteria to the Filter property of the report.

If the field is named [Units in Stock] then the value would be :
Expand|Select|Wrap|Line Numbers
  1. [Units in Stock]>0
Ensure FilterOn is also set. Your report (once saved) should now always show just those records you require.
Mar 20 '10 #8

P: 28
Thanks my report now works a treat.
One more question if I may as you have a copy of the database.
On the Products form is there a way to group all the like products (like unit 1 etc) together and then display them in serial number order?
Regards
Ezzz
Mar 22 '10 #9

100+
P: 109
You've lost me! Could you explain what you mean more clearly please? Or an example of the output you desire
Mar 22 '10 #10

P: 28
My apologies.
I have started to populate the database with various items.
There are about 6 unit 1's with different serial numbers then 6 unit 2's with different serial numbers etc up to unit 5 then 6 RFCU's with different serial numbers and so on. If I populate the database with each item in a random order thats how they appear when scrolling through the database.
What I would like is if say all the unit 1's appeared together then all the unit 2's etc and if possible they were displayed in serial number order.
I tried to link the products and inventory tables through the categories box but just ended up crashing the database.
My knowledge is limited.
Regards
Ezzz
Mar 22 '10 #11

100+
P: 109
I'm about to head out of the office now so will have a proper look tomorrow but for the time being try looking at 'Order By' in the Data tab of the Form property. (If you enter in a field name in the Order By property, it will order the records in alphabetical order of that field.)
Mar 22 '10 #12

NeoPa
Expert Mod 15k+
P: 31,186
@Ezzz
You should understand that most experts don't download databases lightly. It's so much more time-consuming than a properly worded question. I appreciate it becomes necessary sometimes, but just so you understand, most experts will avoid it where possible.
@Ezzz
It sounds like you want to sort the data in the underlying record source (or the form itself using the .OrderBy property). We'd need more details if this is not enough of a pointer for you.
Mar 22 '10 #13

P: 28
hedges98
I have ordered the form using [ProductName], via the data tab, as you suggested and it now works a treat. Many thanks for all your help and patience.
Regards
Ezzz
Mar 24 '10 #14

Post your reply

Sign in to post your reply or Sign up for a free account.