Picking Single Records To Query

[SteveF]

Hi All, hope this makes sense.

I'm trying to set up a PLU comparison report, it will compare till sales to consumption through a stock movement report.

The consumption is in a query, which draws its information from a product table. What I want to be able to do is report on a user-specified sample rather than the whole product list.

So, I guess my question would be:

How can I pick individual lines from one query or table and work on them in another query?

Thanks for help, as ever.

 

[jal]

The consumption is in a query, which draws its information from a product table. What I want to be able to do is report on a user-specified sample rather than the whole product list.

I don't see the problem. You evidently want to begin by filtering the Product table. This is precisely the sort of thing databases are designed for. Typically an Access forms-designer (which I am not) will place a combo box on a form populated with values for the user to choose from, and then will filter based on the user's selection. So if the user selects a product from the cbo, you can begin building a query in VBA such as the following (creates a table called ProductSelected)

Dim SQL as string
SQL = "SELECT * INTO ProductSelected FROM Products WHERE ProductID = @ProductID"
Dim qdef as Dao.QueryDef
Set qdef = CurrentDB.CreateQueryDef(", SQL)
qdef.Parameters["@ProductID"].Value = cboSelectProduct
qdef.Execute

If you don't want to use VBA, you might still be able to get most of the job done using Access forms, although I woudn't be much help there.

 

[SteveF]

The problem is my lack of knowledge and experience

I will have a look at the product table and filters as suggested. Thanks for the code, I'm trying to soak up as much info as possible and this really helps.