| Author |
 Topic  |
|
|
jmiralles
Member
176 Posts |
 Posted - 07/21/2008 : 3:53:38 PM
|
Is there a report that I can run to show me which inventory items do not have a primary supplier selected? I'd like to be able to see them on one report so that I can have someone fix them. If there is something that already exists where I can see these skus, please let me know. I also know how to make a report using one table without relationships or anything so I could possibly do this if the report is not very involved. Let me know!
Thanks! |
|
|
Barney Stone
Administrator
USA
5517 Posts |
Posted - 07/21/2008 : 4:14:12 PM
|
If you know how to create a new query, set this as its recordsource:
SELECT InventoryQuery.SKU, InventorySuppliersQuery.SupplierID, InventorySuppliersQuery.PrimarySupplier
FROM InventoryQuery LEFT JOIN InventorySuppliersQuery ON InventoryQuery.SKU = InventorySuppliersQuery.LocalSKU
WHERE (((InventorySuppliersQuery.SupplierID) Is Null) AND ((InventorySuppliersQuery.PrimarySupplier)=True));
When you run the query it should give you a list of SKUs with no primary supplier. |
Barney Stone, President
Stone Edge Technologies, Inc.
610-994-3699 ext. 111 |
 |
|
|
jmiralles
Member
176 Posts |
Posted - 07/21/2008 : 4:55:25 PM
|
I have the design view of queries opened. I have added the 2 queries and have pulled the sku, primary supplier, and Supplier ID down into the boxes, but I am having troubles doing the next part "left Join" etc. I assume this is creating realationships? Can you help me continue with this? I ran just what I have and it seems to be working, but I want to make sure I do the entire thing you need me to do.
Thanks! |
|
|
|
Barney Stone
Administrator
USA
5517 Posts |
Posted - 07/21/2008 : 5:21:37 PM
|
| Actually, if you just go to the SQL view of the new query, you can paste in the select statement above and it will take care of it for you. |
Barney Stone, President
Stone Edge Technologies, Inc.
610-994-3699 ext. 111 |
|
|
|
jmiralles
Member
176 Posts |
Posted - 07/22/2008 : 3:09:14 PM
|
| I pasted it in the SQL view and I don't get any results. I clicked on run again and I get a blank table. Do you think there is something missing from the formula or does that mean I don't have any to view? |
|
|
|
Barney Stone
Administrator
USA
5517 Posts |
Posted - 07/22/2008 : 3:26:41 PM
|
| Probably means that you don't have any products without primary suppliers. Try creating one, and see if it shows up when you run the query. |
Barney Stone, President
Stone Edge Technologies, Inc.
610-994-3699 ext. 111 |
|
|
|
jmiralles
Member
176 Posts |
Posted - 08/06/2008 : 4:18:37 PM
|
| Ok, I ran into one that had a supplier listed, but the little check by primary supplier wasn't checked. It didn't show up in my query. I'm wanting a query to see the ones where the little checkbox isn't checked b/c we are running into purchasing issues when this little box isn't checked. Any other ideas? |
|
|
|
pghquest
Advanced Member
USA
1268 Posts |
Posted - 08/06/2008 : 4:25:26 PM
|
| on the view inventory screen, I'd like the ability to search by primary supplier. This seemed like a good place to post it rather then starting a new thread. |
|
|
|
JaredSE
Senior Member
576 Posts |
Posted - 08/12/2008 : 09:37:27 AM
|
| Products with no primary supplier are listed on the Inventory Value - New report if you have version 5 or higher. |
|
|
|
jmiralles
Member
176 Posts |
Posted - 01/19/2009 : 4:47:41 PM
|
I ran the query and nothing comes up. I run the inventory value - new report and I get 94 pages of results supposedly with no primary supplier set. I go in and check a few and they both have the primary supplier field checked. What gives?
How does one truly see if no primary supplier is checked? |
|
|
|
JaredSE
Senior Member
576 Posts |
Posted - 01/22/2009 : 3:40:38 PM
|
| I just reviewed the report and it should work. I would need to see your data and have you point out a particular example to determine why it wouldn't be working. |
|
|
|
rthrower
New Member
6 Posts |
Posted - 11/03/2009 : 4:05:04 PM
|
| I am bringing this thread back up to see if there is an answer to this. We are trying to identify the same thing - items with no primary supplier associated. No luck with either the listed report or the query listed above. The query did run but produced no results (which we know cannot be the case). Has anyone found a solution to this? |
|
|
|
pghquest
Advanced Member
USA
1268 Posts |
Posted - 11/04/2009 : 09:43:04 AM
|
I have a query written that pulls up items without a primary..
If you can write a query, inventorysuppliers table, hit sum for "primarysupplier", and then view all where sum = "0".. |
|
|
|
rthrower
New Member
6 Posts |
Posted - 11/04/2009 : 09:54:02 AM
|
| Thanks pghquest, we will give that a try. |
|
|
| |
Topic |
|
report for primary supplier
