Query returns incorrect data

[aero-spaces]

I am building a warehouse management database from the ground up. The problem
I am running into involves the 'location'.

Problem #1- I have an "Upcoming Shipments" table which is just data pasted
from an external spreadsheet e-mailed from clients. I then have a query that
pulls part length from table "Part Numbers" and the 'location' from table
"Receiving". When this query returns its results, it will list all of the
locations the part is in, not just the one I want. Example:

If I need to ship Part Number 1234567, Lot Number A11111 and that is found
in location 5555, it will return that location, but it will also return any
other location that Part Number 1234567 is found, i.e. if Lot Number B99999
is in Receiving, the query will tell me that Part Number 1234567, Lot Number
A11111 is in both 5555 AND Receiving.

Problem #2 is similar - My In-Stock Inventory is basically a query that
subtracts what we've shipped from what we've received. It normally works
great, but in times where we receive a part with the same part & lot numbers
as one we have on the shelf, it will sum them and say that both locations
have the sum. Example:

We have 5 pcs of part number 1234567, Lot Number A11111 in location 5555. We
receive 7 pcs of the exact same part & lot number. Now my In-Stock Inventory
query says that I have 12 pcs in location 5555 and 12 pcs in receiving.

For reasons I don't want to get into, I can't have a table that just has
part number listed once and a permanent warehouse location (because they
don't have permanent locations). So, I would like to be able to have Access
treat Fields A, B, & C as "locked" together.

Any ideas?

 

[Jerry Whittle]

Show us the SQL. Open the queries in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too

 

[aero-spaces]

Show us the SQL. Open the queries in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too

SELECT [Upcoming Shipments].[Shipment Date], [Upcoming Shipments].[Ship to],
[Upcoming Shipments].[Part Number], [Upcoming Shipments].Qty, [Upcoming
Shipments].[Heat/Lot], [In Stock Inventory].Location, [Part
Numbers].[Material Length]
FROM ([Part Numbers] INNER JOIN [Upcoming Shipments] ON [Part Numbers].[Part
Number] = [Upcoming Shipments].[Part Number]) INNER JOIN [In Stock Inventory]
ON [Part Numbers].[Part Number] = [In Stock Inventory].[Part Number]
GROUP BY [Upcoming Shipments].[Shipment Date], [Upcoming Shipments].[Ship
to], [Upcoming Shipments].[Part Number], [Upcoming Shipments].Qty, [Upcoming
Shipments].[Heat/Lot], [In Stock Inventory].Location, [Part
Numbers].[Material Length]
HAVING ((([In Stock Inventory].Location)<>"OUT" Or ([In Stock
Inventory].Location) Is Null))
ORDER BY [Upcoming Shipments].[Shipment Date], [Upcoming Shipments].[Ship to];

I guess I had forgotten that the Location in the Upcoming Shipments table
actually comes from the In Stock Inventory, not Receiving as I indicated in
my first post. The only primary key involved is "Part Number" in the Part
Numbers table. The Part Number field is connected to Part Number field in
both the In Stock Inventory query and Upcoming Shipments table both listed as
a "#1" join type.

If I can give you more information to clarify, please let me know.

Thanks!

 

[Jerry Whittle]

Your query makes no mention of the Lot Number field. You need to add it to
the Select, Having, and group by portions of the sql.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Show us the SQL. Open the queries in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too

SELECT [Upcoming Shipments].[Shipment Date], [Upcoming Shipments].[Ship to],
[Upcoming Shipments].[Part Number], [Upcoming Shipments].Qty, [Upcoming
Shipments].[Heat/Lot], [In Stock Inventory].Location, [Part
Numbers].[Material Length]
FROM ([Part Numbers] INNER JOIN [Upcoming Shipments] ON [Part Numbers].[Part
Number] = [Upcoming Shipments].[Part Number]) INNER JOIN [In Stock Inventory]
ON [Part Numbers].[Part Number] = [In Stock Inventory].[Part Number]
GROUP BY [Upcoming Shipments].[Shipment Date], [Upcoming Shipments].[Ship
to], [Upcoming Shipments].[Part Number], [Upcoming Shipments].Qty, [Upcoming
Shipments].[Heat/Lot], [In Stock Inventory].Location, [Part
Numbers].[Material Length]
HAVING ((([In Stock Inventory].Location)<>"OUT" Or ([In Stock
Inventory].Location) Is Null))
ORDER BY [Upcoming Shipments].[Shipment Date], [Upcoming Shipments].[Ship to];

I guess I had forgotten that the Location in the Upcoming Shipments table
actually comes from the In Stock Inventory, not Receiving as I indicated in
my first post. The only primary key involved is "Part Number" in the Part
Numbers table. The Part Number field is connected to Part Number field in
both the In Stock Inventory query and Upcoming Shipments table both listed as
a "#1" join type.

If I can give you more information to clarify, please let me know.

Thanks!

 

[aero-spaces]

Your query makes no mention of the Lot Number field. You need to add it to
the Select, Having, and group by portions of the sql.

Well, your response prompted me to double check a few things, and in so
doing, I noticed that for some reason, my lot number fields were not joined
between the Upcoming Shipments and the Current Inventory. Having fixed that,
the Shipment Planning query works as intended (although now I have to have
another query that checks for errors/missing records).

I'm still having trouble with my Current Inventory query (Problem #2 above).
Here's the SQL for that one:

SELECT [Total Received].[Part Number], [Total Received].[Heat Lot], [Total
Received]![SumOfQty Received]-NZ([Total Shipped]![SumOfQty Shipped]) AS Qty,
Receiving.Location
FROM ([Total Received] LEFT JOIN [Total Shipped] ON ([Total Received].[Heat
Lot] = [Total Shipped].[Heat/Lot Number]) AND ([Total Received].[Part Number]
= [Total Shipped].[Part Number])) INNER JOIN Receiving ON ([Total
Received].[Part Number] = Receiving.[Part Number]) AND ([Total
Received].[Heat Lot] = Receiving.[Heat Lot])
GROUP BY [Total Received].[Part Number], [Total Received].[Heat Lot], [Total
Received]![SumOfQty Received]-NZ([Total Shipped]![SumOfQty Shipped]),
Receiving.Location
HAVING ((([Total Received]![SumOfQty Received]-NZ([Total Shipped]![SumOfQty
Shipped]))>0) AND ((Receiving.Location)<>"OUT"))
ORDER BY [Total Received].[Part Number], [Total Received].[Heat Lot];