Kind soul help with an access query / issue?

[nightmare99]

I have an access db that I have cobbled together which I use to print stock labels from Sage product data, on the whole it works and I can print my labels by typing individual stock codes and printing a qty.

I have been trying to get a solution where by I can select a purchase order and it will output the correct qty of labels for each stock item on a purchase order.

The closest I can get is a query which returns all of the stock items on the purchase order but each one is displayed once where I would like each item returned x the qty that stock item is on the purchase order.

Do I need to use something called a recordset? I have spent a fair amount of time googling it to no avail, below is my query which almost gives me what I want

SELECT POP_ITEM.ORDER_NUMBER, POP_ITEM.STOCK_CODE, POP_ITEM.DESCRIPTION, POP_ITEM.QTY_ORDER, [STOCK Query].SALES_PRICE, [STOCK Query].STOCK_CAT_NAME
FROM POP_ITEM INNER JOIN [STOCK Query] ON POP_ITEM.STOCK_CODE = [STOCK Query].STOCK_CODE
WHERE (((POP_ITEM.ORDER_NUMBER)=[Forms]![Stock Label Printing PO]![cboStockCode]));

please ignore the stupid combo box name it should really be cboPurchaseOrder or something like that, it was copy/pasted from another form.

Any ideas or pointers, thank you in advance

 

[Gornall]

I really hate Access so it's been a while.

The inner join will display all of the items listed in both tables/queries your using.

In this case it is POP_Item and Stock_Query

With out seeing the contents my best guess is that neither of them tables/queries have the full list of product? E.g. they are both aggregated and such the results you are getting are a result of that aggregation.

e.g. it says stock code 1 qty 10. Which will give you 1 record not ten..hope that makes sense.

Are you able to trust me a dummy copy of the database? e.g. fake records the simulate the scenario?

I can have a look for you.

 

[nightmare99]

Thanks for the offer, I will have to convert the linked tables to static and put some dummy data in. I will trust you a skydrive link at some point.

The Stock_Query basically gives me everything I want but I would like each record x the qty in the POP_Item table.

 

[Gornall]

Ok no worries.

I'll have a look when I can, can't guarantee how much time I can spend on it as I'm back at work tomorrow.