Joined tables in Query, count from one

[kvar]

I have two joined tables, one to many. The problem I'm having, is the field that they are joined on, is the one I'm trying to count. So access is counting them in the wrong table, basically.

Here is the SQL:

SELECT DISTINCTROW Contracts.ProductID, Count(Contracts.[Contract No]) AS [CountOfContract No], Min(ItemsOrdered.[Line Start Date]) AS [MinOfLine Start Date]
FROM Contracts INNER JOIN ItemsOrdered ON Contracts.[Contract No] = ItemsOrdered.[Contract No]
WHERE (((Contracts.[Date Activated in OKS]) Is Null))
GROUP BY Contracts.ProductID;

Somehow, that returns the count (I checked by hand) from the ItemsOrdered table. I've tried deleting the join of the tables, but that definitely didn't work. I've tried adding the Contract No field from ItemsOrdered and setting it to Group By....nope.

Not sure what else to try here. Do I need to remove the Items Ordered table from the query? And if I do that how do I get the field that I need from it?
I have searched and found references fro sub-queries, and adding Distinct to the count. But none of the examples I've found seem to work for this one.

Any ideas would be much appreciated, Thanks!!

 

[jdraw]

I have two joined tables, one to many. The problem I'm having, is the field that they are joined on, is the one I'm trying to count. So access is counting them in the wrong table, basically.

Here is the SQL:

SELECT DISTINCTROW Contracts.ProductID, Count(Contracts.[Contract No]) AS [CountOfContract No], Min(ItemsOrdered.[Line Start Date]) AS [MinOfLine Start Date]
FROM Contracts INNER JOIN ItemsOrdered ON Contracts.[Contract No] = ItemsOrdered.[Contract No]
WHERE (((Contracts.[Date Activated in OKS]) Is Null))
GROUP BY Contracts.ProductID;

Somehow, that returns the count (I checked by hand) from the ItemsOrdered table. I've tried deleting the join of the tables, but that definitely didn't work. I've tried adding the Contract No field from ItemsOrdered and setting it to Group By....nope.

Not sure what else to try here. Do I need to remove the Items Ordered table from the query? And if I do that how do I get the field that I need from it?
I have searched and found references fro sub-queries, and adding Distinct to the count. But none of the examples I've found seem to work for this one.

Any ideas would be much appreciated, Thanks!!

Are you trying to determine a count of Contracts or Order items on Contracts? It isn't clear. at least to me, what you want to count.

 

[kvar]

Sorry about that.
I am trying to count the Contract No field in the Contracts table. In that table, it is the primary key, so they are all unique, no need to worry about using distinct, etc. (so I thought!)
There is also a Contract No field in the ItemsOrdered table. That is the field the two tables are joined on. But in the ItemsOrdered table, the same Contract No will appear as many times as there were lines in the order. Each line is a different item ordered (hence the name!) so under one contract number, you could have a lot of items.

Does that make more sense??

 

[jdraw]

Sorry about that.
I am trying to count the Contract No field in the Contracts table. In that table, it is the primary key, so they are all unique, no need to worry about using distinct, etc. (so I thought!)
There is also a Contract No field in the ItemsOrdered table. That is the field the two tables are joined on. But in the ItemsOrdered table, the same Contract No will appear as many times as there were lines in the order. Each line is a different item ordered (hence the name!) so under one contract number, you could have a lot of items.

Does that make more sense??

Yes it makes sense. I'm not sure what your Contract entity is, but that's beside the point.
Typically you have Customers, Orders and OrderItems for example.

1 customer could have 1 or more Orders. Each Order could have 1 or more OrderItems.

 

[kvar]

Basically that's it.
But I just need to count the number of Contracts (meeting the criteria of course).

But instead of getting the Count of Contract No FROM Contracts, it's giving me the Count of Contract No FROM ItemsOrdered.

Is there some property in the join that I can change? Like I said, I tried deleting it, but that gave me an even bigger number.

I also tried setting Unique records to Yes, and I've tried setting Unique Values to Yes. Neither of those changed anything.

I'm beginning to think that a subQuery might be what I need. Select Distinct Contract No FROM ItemsOrdered, then the other query would be the count and the criteria. But I have no clue how to do that!

 

[jdraw]

Basically that's it.
But I just need to count the number of Contracts (meeting the criteria of course).

But instead of getting the Count of Contract No FROM Contracts, it's giving me the Count of Contract No FROM ItemsOrdered.

Is there some property in the join that I can change? Like I said, I tried deleting it, but that gave me an even bigger number.

I also tried setting Unique records to Yes, and I've tried setting Unique Values to Yes. Neither of those changed anything.

I'm beginning to think that a subQuery might be what I need. Select Distinct Contract No FROM ItemsOrdered, then the other query would be the count and the criteria. But I have no clue how to do that!

What exactly are the criteria? Why do you have to go to the second table?

Select count(contractNo) from Contracts
WHERE someFieldInContracts = XXXX

 

[kvar]

I have to use both tables because there is a field in the second table that I need the Min of. To show the oldest age of the contracts in each group.