A
aero-spaces
I am trying to create a query that will show me all parts in stock that are
older than a certain age (in this case, one year). When I run the query, I
get results that don't match up with the results I get by looking at it by
hand. Most often, it shows a part as being old, when it really isn't. I've
looked at several other posts on these boards regarding duplicate records,
but I can't seem to find my answer. I'll give you some sample data followed
by my current query.
Table "Receiving"
Part Number, Heat Lot, Date Received, Location
12345, ABCDE, 4/6/2007, OUT
12345, FGHIJK, 5/2/2007, OUT
12345, BCDEF, 8/8/2008, 1W
12345, BCDEF, 8/8/2008, 1W
Query "In Stock Inventory"
Part Number, Heat Lot, Qty, Location
12345, BCDEF, 2, 1W
When I run the query below, I get the following:
Part Number, Heat Lot, Qty, Location, Date Received
12345, BCDEF, 2, 1W, 4/6/2007
12345, BCDEF, 2, 1W, 5/2/2007
As you can see, not only is it duplicating the total number of parts in
stock, but it's also saying that lot BCDEF was received on 4/6/07 and 5/2/07,
which it clearly wasn't. I'm sure this is a simple JOIN problem or GROUP
problem, but I can't seem to put my finger on it. Any ideas?
SELECT [In Stock Inventory].[Part Number], [In Stock Inventory].[Heat Lot],
[In Stock Inventory].Qty, [In Stock Inventory].Location, Receiving.[Date
Received]
FROM [In Stock Inventory] LEFT JOIN Receiving ON [In Stock Inventory].[Part
Number] = Receiving.[Part Number]
WHERE (((Receiving.[Date Received])<(Date()-365)))
GROUP BY [In Stock Inventory].[Part Number], [In Stock Inventory].[Heat
Lot], [In Stock Inventory].Qty, [In Stock Inventory].Location,
Receiving.[Date Received];
older than a certain age (in this case, one year). When I run the query, I
get results that don't match up with the results I get by looking at it by
hand. Most often, it shows a part as being old, when it really isn't. I've
looked at several other posts on these boards regarding duplicate records,
but I can't seem to find my answer. I'll give you some sample data followed
by my current query.
Table "Receiving"
Part Number, Heat Lot, Date Received, Location
12345, ABCDE, 4/6/2007, OUT
12345, FGHIJK, 5/2/2007, OUT
12345, BCDEF, 8/8/2008, 1W
12345, BCDEF, 8/8/2008, 1W
Query "In Stock Inventory"
Part Number, Heat Lot, Qty, Location
12345, BCDEF, 2, 1W
When I run the query below, I get the following:
Part Number, Heat Lot, Qty, Location, Date Received
12345, BCDEF, 2, 1W, 4/6/2007
12345, BCDEF, 2, 1W, 5/2/2007
As you can see, not only is it duplicating the total number of parts in
stock, but it's also saying that lot BCDEF was received on 4/6/07 and 5/2/07,
which it clearly wasn't. I'm sure this is a simple JOIN problem or GROUP
problem, but I can't seem to put my finger on it. Any ideas?
SELECT [In Stock Inventory].[Part Number], [In Stock Inventory].[Heat Lot],
[In Stock Inventory].Qty, [In Stock Inventory].Location, Receiving.[Date
Received]
FROM [In Stock Inventory] LEFT JOIN Receiving ON [In Stock Inventory].[Part
Number] = Receiving.[Part Number]
WHERE (((Receiving.[Date Received])<(Date()-365)))
GROUP BY [In Stock Inventory].[Part Number], [In Stock Inventory].[Heat
Lot], [In Stock Inventory].Qty, [In Stock Inventory].Location,
Receiving.[Date Received];