2 records with the same id number in query

[rainbows]

confused as to why i get 6 results yet only 5 in table and form
STOCK NUMBER gt2920 HAS 5 transactions the line numbers are 18.19.20. 24 and 26

the table also has 5 transactions

yet the results in the query has 6 ,, line 18 in twice

any idea what could be wrong ??

Thanks steve

 

[Gasman]

And that is the only record that has two batch numbers?

Really would help if you showed the query sql?

 

[rainbows]

i had not noticed that and yes it has done the same where i have 2 batch numbers to the same record. is it possible to only get the one record?

SELECT StockLevels.StockID, StockLevels.StockNo, StockLevels.OnOrderQty, StockLevels.Allocation, StockLevels.allocated, StockLevels.StockQTY, StockLevels.batchno.Value, StockLevels.batchno
FROM StockLevels
WHERE (((StockLevels.StockNo)=[Forms]![stocklevels]![Stocklist For Purchasing].[Form]![StockNumber]));

 

[rainbows]

thanks for your help i have solved it the query had the field batchno.value in it , when i removed that it was ok

 

[CarlettoFed]

Try modifying the query by entering DISTINCT.

SELECT DISTINCT StockID, StockNo, OnOrderQty, Allocation, allocated, batchno
FROM StockLevels
WHERE StockNo=[Forms]![stocklevels]![Stocklist For Purchasing].[Form]![StockNumber];

 

[GPGeorge]

confused as to why i get 6 results yet only 5 in table and form
STOCK NUMBER gt2920 HAS 5 transactions the line numbers are 18.19.20. 24 and 26

the table also has 5 transactions

yet the results in the query has 6 ,, line 18 in twice

any idea what could be wrong ??

Thanks steve








View attachment 103168
View attachment 103169


View attachment 103170

View attachment 103171

It's worth pointing out that this table includes a Multi Value Field, BatchNo. And it also contains TWO values for BatchNo in the record which is repeated in the original query. That's not a coincidence. You are asking for the BatchNo.Value in the query, which returns both of those BatchNo values. Hence the repeated line in the query results. I would not have predicted it would be presented exactly the way it does, but it is totally predictable that it does return two lines for that record with two BatchNo values.