Joining a Query and a Table - not working!

[Dave]

The Table:

BoxCount
pkey -> PlanterName , text
pkey -> BoxType, text
Date, datetime
BoxesReturned, number
Comments, text

There are 3 queries I use at different times throughout the process,
but basically I have one query that I want to join a different query
to that table:

Queries:

BoxReturn2

SELECT Sum(BoxReturn.BoxesShipped) AS SumOfBoxesShipped,
Abs(Sum(BoxReturn.RemainderShipped)) AS SumOfRemainderShipped,
BoxReturn.PlanterName AS PlanterName, BoxReturn.BoxType AS BoxType
FROM BoxReturn
GROUP BY BoxReturn.BoxType, BoxReturn.PlanterName;


BoxReturn3

SELECT *
FROM BoxReturn2 INNER JOIN BoxCount ON
(BoxCount.PlanterName=BoxReturn2.PlanterName) AND
(BoxCount.BoxType=BoxReturn2.BoxType);

NOW, BoxReturn3 returns no records at all. I'm gettin pissed. I
tried it with test tables, same thing, and it worked fine!

Please help me solve this problem. Also let me know if you need more
information.

Dave

 

[Ken Snell]

How many records are returned by your BoxReturn2 query ? Are you sure that
it's returning any records at all?

You don't say what the table BoxReturn is....is that another query? Are
there any records in BoxReturn?

 

[Dave]

How many records are returned by your BoxReturn2 query ? Are you sure that
it's returning any records at all?

You don't say what the table BoxReturn is....is that another query? Are
there any records in BoxReturn?

All the appropriate records are returned by the BoxReturn2 query. It
works fine. Box Return is another query. There are plenty of records
in there. I will paste the structure of BoxReturn and things that are
related to it.

Thanks for your help Ken, and anyone else. This is really important.

Queries:

BoxReturn

SELECT CropLocations.BoxType, Shipping.BoxesShipped,
Shipping.RemainderShipped, Loads.PlanterName, Shipping.Load
FROM CropLocations, Shipping, Loads
WHERE CropLocations.Location=Shipping.Location And
CropLocations.CropID=Shipping.CropID And
CropLocations.PrevLocation=Shipping.PrevLocation And
Loads.Load=Shipping.Load And Loads.ShippedDate Is Not Null;

The tables referenced in this query are pretty straight forward I
believe, let me know if you want them as well. Box Return returns all
the records I want as well (same as BoxReturn2, it's just BoxReturn3
that isnt working).

Basically, this whole thing is calculating howmany of each type of box
has been shipped to a specific Planter (PlanterName). It is setup
with records from shipping connected to records from load (many
shipping in load) with a foreign Key "Load".

The BoxReturn query is used to get all the information from those
records. BoxReturn2 groups it by Planter and BoxType. This is for
boxes shipped. Boxes returned are stored in the "BoxCount" table I
have specified before.

In BoxReturn3 I want to join the info for boxes sent and received so I
can make a report of it all showing the balance etc. It should be
easy cause the BoxCount table and the BoxReturn2 Query have the same
info to join (Planter and BoxType). Planter and BoxType are pkey in
the BoxCount table.

I REALLY REALLY hope this helps.
Dave

 

[Dave]

bump

 

[Ken Snell]

Thanks for the additional info. The only thing that I can think of at the
moment is that you have dissimilar field formats and/or size for the
CropLocations.BoxType field and the BoxCount.BoxType field. When that
happens, you may not get the exact match that you're seeking in the third
(BoxReturn3) query.

If the fields are the same in format and size, then I don't see anything
obvious about the queries that would show records in the first two queries
and not in the third...other than if the values of the second query's
linking fields don't match any of the values of the BoxCount table's fields
to which you're linking.

Post back with your findings.