Simple? query question

[NaKin]

I can't believe I'm asking this. I have a feeling it's really easy, but I'm having a brain fart. I've tried a bunch of things and nothing is working. All I want is to see the loads without water in it.

2 Tables. Loads and MaterialsByLoad. All loads have more than one material. No matter what i do, when I ask for the loads without water I get all the records that aren't water. Played with joins, played with Distinct, nuthin'. How would you do this? I can try and post some of the database if you're interested.

Thanks

 

[jfgambit]

Can you post the SQL from the Query?

 

[Kraj]

Your post is very confusing, but I think I understand:

You have a table that lists each load. You have a table that has a record for each material in a load. You are making a query to find every record in the first table that does not have a corresponding entry of "water" in the second table, but the results you are getting is every record in the second table that is not "water". Is that right?

If that is correct, what it sounds like to me is your tables do not have any relationships defined, so the query does not know that "load" in the first table is the same as "load" in the second table.

To change this, go to Tools --> Relationships. You should see your two tables and there are no lines connecting them. Simply click on your key field in the first table (loadID or whatever it is) and drag it onto loadID on the second table. That should work.

 

[NaKin]

You have a table that lists each load. You have a table that has a record for each material in a load. You are making a query to find every record in the first table that does not have a corresponding entry of "water" in the second table, but the results you are getting is every record in the second table that is not "water". Is that right?

That's correct, if there is water in the load I don't want the load included. I have the relationship defined. Loads is the one MaterialByLoads is the many. All the fields for the query come from the Loads table EXCEPT for the "Not water". You can have the sql, but obviously it's wrong. I've tried with distinct, without distinct, left & right outer joins and it is always pulling in each line of MaterialsByLoad. Good lord, I've done a lot more complex stuff than this with no problem! I feel like a real newbie.

SELECT DISTINCT Count(tblLoads.[ID#]) AS [CountOfID#], tblLoads.PlantNumber, Sum(tblLoads.LoadSize) AS Drymetres
FROM tblLoads INNER JOIN tblMaterialByLoad ON tblLoads.[ID#] = tblMaterialByLoad.loadsID
WHERE (((tblLoads.LoadSize)<1) AND (Not (tblMaterialByLoad.MaterialDesc)="WATER"))
GROUP BY tblLoads.PlantNumber;

 

[NaKin]

Thanks Pat, It came to me last night that I might have to use two queries. I'll give your way a go and see how it all works out.