Query returning duplicate/unexpected results

[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];

 

[KARL DEWEY]

You need to join the Heat Lot and location also.

 

[aero-spaces]

Wow, I thought I'd tried that... I guess not! At first I thought it still
wasn't working correctly, but then I noticed some problems with the original
data.

So, thanks on two fronts!

 

[Jerry Whittle]

[In Stock Inventory].[Part Number] = Receiving.[Part Number]

Are either of the Part Number field in either table the primary key?
Actually I see that In Stock Inventory is query, but is that Part Number the
primary key for the source table?

If not you probably have a Cartesian Product being returned. The join is
returning the data for any match of the Part Numbers as long as the return
date is correct.

From your sample data, it looks like you need to join the Heat Lot fields
also. Even that could be flakey depending on if the combination of the two
fields is a PK or at least a unique constraint in at least one of the tables.