Check for variations query?

[510sx]

Hi,

I have a simple SELECT query that returns a set of ProductCode based on the PalletNumber I enter. 98% of time the ProductCode are all the same, which is expected. Sometimes (2% of time), it returns the expected ProductCode and other Product. I like to come up a query to check the result if it has more than one Productcode in it. Is there a function I could use for this problem?

 

[MSAccessRookie]

I am not sure that you have provided enough information here. For instance, we do not know what SQL code you have used so far, so we would be be in a position to make realistic suggestions for alternatives. If you are able to post your SQL Code, then perhaps we can better assist you.

 

[510sx]

Like I said it just a simple select query

Select ProductCode from ProductCode where PalletNumber = 23456

usually it returns

1216
1216
1216
1216
.
.
.

but sometimes it returns

1216
1216
1216
1216
1224
1224
.
.
.

I need to build a query that detects the PalletNumber contain more than one product. I like to see it returns Yes/No, or 1, 2 or 3; depends on how many different ProductCode on the same pallet. I've tried the Dcount function, but it doesn't return how many product code. I hope this clear things up.

 

[John Big Booty]

Have a look at GroupBy (totals) queries.

You should be able to achieve your goal with one of these. Bear in mind however that you will not be able to do this with a single query but will need to use a number of nested queries.

 

[510sx]

I use running total before and I don't see why it would apply to this problem. Anyways, I will look it up some more. It's a good start.
Thanks John

 

[highandwild]

Set up a query containing the palletcode and the productcode and groupo on the two fields. Save the query.

Set up another query based upon the above query, group on palletcode and a count on the productcode field. That will give you the number of different productcodes on each pallet.

 

[510sx]

Set up a query containing the palletcode and the productcode and groupo on the two fields. Save the query.

Set up another query based upon the above query, group on palletcode and a count on the productcode field. That will give you the number of different productcodes on each pallet.

ya, got it working... like you said using two queries

Is it possible to combine two queries to one?



SELECT PalletData.ProductCode
FROM PalletData
GROUP BY PalletData.ProductCode, PalletData.PalletTagNumber
HAVING (((PalletData.PalletTagNumber)=[]));


SELECT Count(TestProductCode.ProductCode) AS CountOfProductCode
FROM TestProductCode;

 

[MSAccessRookie]

Although MS Access does not seem to like it as much as SQL Server, or some of the other SQL systems, you could possiby create a Query with a Built-In Sub-Query. Something like the following (untested) code:

SELECT Count(TestProductCode.ProductCode) AS CountOfProductCode
FROM (SELECT PalletData.ProductCode FROM PalletData
GROUP BY PalletData.ProductCode, PalletData.PalletTagNumber
HAVING (((PalletData.PalletTagNumber)=[]))) As TestProductCode;