MS query just show one record

[herts2201]

Hi.
I have ms access database with one table called PRODUCTS with 4 column.
ID, BARCODE, DESC, SIZE.
I build a query which take information from a form. form gets value from the excel table called LNKBARCODE with one column BARCODE. when i run the form it gets all the records(ABOUT 100) from the LNKBARCODE. But when i run the query it only shows one record from the database. only first barcode in the form. if i highlight the second barcode in the form and then run the query it shows records for the second barcode. but only one record at a time. I want query should search and show records for all the barcodes which are in the LNKBARCODE table.

Any help will be much appreciated.

Thanks

 

[arnelgp]

maybe you are using single record form, not multi record or datasheet that is why it is coming like that.

 

[herts2201]

Thanks for reply. I used the form as datasheet as well. But no joy. I think I am making some mistake in query because the form shows all the barcodes but the query search only first record.
Thanks

Any other thoughts ?

 

[arnelgp]

thank you, please show me the sql for the query.

 

[herts2201]

Thanks for your reply.
Here is the sql of the query

SELECT PRODUCTS.ID, PRODUCTS.BARCODE, PRODUCTS.DESC, PRODUCTS.[ITEM CODE], PRODUCTS.[RETAIL PRICE], PRODUCTS.[UNIT SIZE], PRODUCTS.[CASE QUANTITY], PRODUCTS.[SINGLE QUANTITY], PRODUCTS.[CASE PRICE], PRODUCTS.CATEGORIES
FROM PRODUCTS
WHERE (((PRODUCTS.BARCODE)=[forms]![ORDERCODEFORM]![BARCODES]));

Thanks

 

[JHB]

Take away the WHERE part.

SELECT PRODUCTS.ID, PRODUCTS.BARCODE, PRODUCTS.DESC, PRODUCTS.[ITEM CODE], PRODUCTS.[RETAIL PRICE], PRODUCTS.[UNIT SIZE], PRODUCTS.[CASE QUANTITY], PRODUCTS.[SINGLE QUANTITY], PRODUCTS.[CASE PRICE], PRODUCTS.CATEGORIES
FROM PRODUCTS

 

[herts2201]

Thanks for reply.
If I took away the WHERE part, it did not search the database through the form.
Instead it showed the whole database. My task is to search the database by
Taking input from the form. It only showed records for first row and does not show any other rows which are present in the form.

Thanks

 

[JHB]

..It only showed records for first row and does not show any other rows which are present in the form.

What exactly do you mean by the above, (any other rows which are present in the form.)?

 

[herts2201]

Thanks for reply. Here is my situation. I have database of around 2000 products.
When I want to make order from the suppleir. I scan items which I need (about 100)with hand held scanner. Scanner collect the barcodes and when I connect this scanner with the pc, it transfer all the barcodes in excel file. I linked that excel file to my database. I created a form where these 100 barcodes shows. Now I wanted to search my database for these 100 barcodes. I made a query which take input from the form and search the database and should show the 100 products so I can email to my supplier. But instead of all 100 products my query only shows one product which is first barcode in the form. I hope you understand my plan.

Thanks

 

[arnelgp]

one barcode = one product.
dont tell me you have 1 product with multiple barcodes?

 

[herts2201]

Thanks for reply.
It's
One barcode= one product

No duplicates

Thanks

 

[JHB]

...Now I wanted to search my database for these 100 barcodes. I made a query which take input from the form and search the database and should show the 100 products so I can email to my supplier....

Then you should make a query which link the form's recordsource with the Product table, link field is the "Barcode" and the "Barcodes"

 

[herts2201]

Hi. Thanks for your help.
Would you please guide me bit more on this. I think I already link the two field if you see my sql statement. But no gain. If you have better way to connect them together, please help me

Thanks

 

[JHB]

... I think I already link the two field if you see my sql statement.

No you haven't link anything, you only have one criteria.
Show the form's Recordsource.
I've made a sample for you in the attached database, run the query in it.

 

[herts2201]

Thank you very much JHB. I will try this and let you know.

Thanks

 

[herts2201]

Thank you very much JHB

It works like a charm.

Many thanks for your help JHB.

And Thanks for all other forum members who try to help me.

Regards

 

[JHB]

You're welcome, good luck.

 

[herts2201]

Hi.
I have another problem if you can help me please.
let,s say i scan a some products for new order and from them some of the products i need to order more than one in quantity. At the moment if i scan same product 5 times and when i run the query it showed 5 rows of the same product.
for one item it is fine i can delete 4 rows and add a field QUANTITY in fifth row
and put 5 in the field.
but if it is 100 products it will become hard.
Is it possible that if i scan same product 10 times, it put 10 in QUANTITY field instead of showing 10 rows of the same product.

Thanks you very much.

 

[JHB]

Raw data:

Query to use:

SELECT BarCodes, Count(BarCodes) AS QUANTITY
FROM ScannedBarcode
GROUP BY BarCodes

 

[herts2201]

Thank you very much JHB.
I will try this and let you know if I have any problem
Regards

Thanks