Problem designing query

[chefrusso]

Hello everyone!

I've been having trouble designing a query and I'm hoping someone here can point me in the right direction.

I use an iPhone app which allows me to scan barcodes of items that I keep in my inventory so that I can add or remove them just by scanning the barcode. It works relatively well, but it requires me to import the inventory contained on my iPhone into the Access database. To avoid running into major problems, I've designed a few queries to analyze the data on the iPhone inventory to make sure it matches the data in the main Access database before I actually update it.

The main problem I'm having is that I use an inventory table and I also use an inventory detail subtable because I usually have to store products in more than one location. I've tried to design a query that will show me when items on the iPhone don't match the storage location in the main database. For items that are stored in more than one location, it returns the records in the alternate location, even though it is actually matched up in the primary location. I only want the query to display records that don't match anywhere so that I can adjust the location field either on my phone or on the main database.

For example, say I store BBQ sauce in both the fridge and the pantry. I use the iPhone to add or remove items from the pantry because that is where most of this activity occurs. The update query will only update the quantity from the iPhone inventory into the Access database where the locations match. Things get moved around from time to time, so I need to be able to check both tables and make sure the primary location matches in both tables so that the quantities will be updated accurately.

Is there a way to design a query that will display records that don't match a particular field if and only if they don't already match elsewhere in the table?

 

[ByteMyzer]

Can you list the tables to be queried and the fields in those tables, and maybe provide samples of the data in them, for reference?

 

[chefrusso]

I'm sure that will help clarify what I'm trying to do.

I use 3 tables in order to import my iPhone inventory into the main one:

Inventory
ProductID
ProductName
ProductDescription
Container
UnitSize
UnitPrice
FoodCategoryID
ReorderLevel
SupplierCategoryID
Discontinued
BarCode

Inventory Detail
DetailID
ProductID
LocationID
UnitsOnHand
DateModified

iPhone Inventory
ProductID - on my phone this is the barcode
Name - this is the same as [Inventory].[ProductName]
Description - same as [Inventory].[ProductDescription]
Category - same as [Inventory].[FoodCategoryID] I have a separate table for ID# and category names
InternalID - same as the ProductID in both internal inventory tables and the method by which I perform query actions in the main database
UnitSize - same as [Inventory].[UnitSize]
Quantity - same as [InventoryDetail].[UnitsOnHand]
UnitPrice - same as [Inventory].[UnitPrice]
Location - same as [InventoryDetail].[LocationID], again, I have a separate table with ID# and storage names
Par - same as [Inventory].[ReorderLevel]
Timestamp - [InventoryDetail].[DateModified]

I use the iPhone inventory table to primaily update the quantity on hand in the inventory detail table using the storage location as the key. In other words, the query will only update quantities where the locations match. Most of the time, this works flawlessly, but there are times where I need to change the location in either the iPhone table or the InventoryDetail table. If I forget to match the change in both tables, the quantities will no longer get updated.

What I'd like to do is have a query show me where the storage locations don't match between the iPhone inventory table and the inventory detail table. The problem I have is that since the items are stored in more than one location, the query I have created will return records of items in their alternate location, even though they are already matched in their primary location. So far, the only filter I have is "[iPhoneInventory].[Location]<>[InventoryDetail].[LocationID]"

Because the iPhone Inventory uses text fields and my database uses number fields, I use a macro to translate the text into their corresponding numbers before these comparisons are made. I'm only basically familiar with SQL, so I was hoping one of you would know how to approach this and explain it to me. Thanks in advance!

 

[ByteMyzer]

The first part of the query is straightforward (as I'm sure you have figured out). If it were simple a case of returning records where the locations do not match, your SQL might look something like this:

SELECT IHead.ProductID, IHead.ProductName, IDet.LocationID, iPhone.Location
FROM (Inventory AS IHead
INNER JOIN [Inventory Detail] AS IDet ON IHead.ProductID = IDet.ProductID)
INNER JOIN [iPhone Inventory] AS iPhone ON IHead.ProductName = iPhone.Name
WHERE IDet.LocationID <> iPhone.Location;

However, if you wish to exclude those products where you have multiple locations for a product, and one of the locations DOES match the location in the iPhone table, then you may need to add a NOT EXISTS clause with a sub-query to your SQL, something like the following:

SELECT IHead.ProductID, IHead.ProductName, IDet.LocationID, iPhone.Location
FROM (Inventory AS IHead
INNER JOIN [Inventory Detail] AS IDet ON IHead.ProductID = IDet.ProductID)
INNER JOIN [iPhone Inventory] AS iPhone ON IHead.ProductName = iPhone.Name
WHERE IDet.LocationID <> iPhone.Location
AND NOT EXISTS (
    SELECT IDet_2.ProductID
    FROM [Inventory Detail] AS IDet_2
    WHERE IDet_2.ProductID = IHead.ProductID
    AND IDet_2.LocationID = iPhone.Location);

 

[chefrusso]

That SQL statement worked like a charm. Thank you so much ByteMyzer. I've been trying to figure out a solution to this problem for ages. I figured there had to be some sort of code that could perform this operation. After using this query, it returned 6 results instead of the usual 100 or so and I was easily able to fix the errant records. I wish I had discovered this forum before; I think it would have saved me a lot of time and frustration. Thanks again!

 

[chefrusso]

OK, now I also have a minor tweak I would like to add to this query if I could. For the main database field, the query returns the text name of the storage location, but for the iPhone database field, the query returns the number of the storage location instead which requires me to manually lookup the location from my [StorageLocations] table so that I can see which location it is referring to. Is there any way for this query to display the text name of the storage location in both fields? There is the table [StorageLocations] that the query could use to reference the ID#'s and their corresponding names, if necessary.

 

[ByteMyzer]

What are the field names in Table StorageLocations?

 

[chefrusso]

Oh sorry. The StorageLocations table only contains 2 fields StorageID (an Autonumber field) and StorageLocation which is the name of the location. The StorageID field would match the location number stored in the iPhone table.

 

[ByteMyzer]

You might try something like:

SELECT IHead.ProductID, IHead.ProductName,
    IDet.LocationID, ILoc1.StorageLocation AS InventoryLocation
    iPhone.Location, ILoc2.StorageLocation AS iPhoneLocation
FROM (((Inventory AS IHead
INNER JOIN [Inventory Detail] AS IDet ON IHead.ProductID = IDet.ProductID)
INNER JOIN [iPhone Inventory] AS iPhone ON IHead.ProductName = iPhone.Name)
LEFT JOIN StorageLocations AS ILoc1 ON IDet.LocationID = ILoc1.StorageID)
LEFT JOIN StorageLocations AS ILoc2 ON iPhone.Location = ILoc2.StorageID
WHERE IDet.LocationID <> iPhone.Location
AND NOT EXISTS (
    SELECT IDet_2.ProductID
    FROM [Inventory Detail] AS IDet_2
    WHERE IDet_2.ProductID = IHead.ProductID
    AND IDet_2.LocationID = iPhone.Location);

 

[chefrusso]

That worked like a charm once again ByteMyzer. Thank you very much! It was kind of a pain having to look up the storage location code every time I ran the previous query to see what the number meant. So much easier to see the discrepencies between the two inventories. I'd love to learn more about SQL and its syntax, but I've found most of the material on the subject to be a little confusing. It's nice to get help from someone that has a great deal of experience with the language. Thanks again.