Showing A Product In More Than One Place On Report.

[winston1984]

I only have one instance of a Product in the database. Each part has a partNumber, etc, and a ProdGroup ( a 4 digit numerical number corresponding to a Grouping, e.g. Handlebars ), then two more fields are DuplicateGroup1 and DuplicateGroup2. These are 4 digit numerics as well. For example, a set of handlebars can be Group 400 (BMX) but DuplicateGroup1 300(Mountain Bike). I have a report which generates a price list and orders the products in Group Order, so grouped up so the reader can see parts for different types of bikes and groups. But what I want, in the report is to have one product (handlebars) showing in the BMX section and also in the Mountain Bike section. But there is only one instance of the PartNumber. My query is below, but I still only get one instance and it it not shown in the report for Duplicate Groups.

SELECT dbo_Product.IDPartNumber
FROM (dbo_ProdGroup INNER JOIN (dbo_Product INNER JOIN dbo_Stock ON dbo_Product.IDPartNumber = dbo_Stock.IDPartNumber) ON dbo_ProdGroup.ID = dbo_Product.GroupCode) INNER JOIN dbo_Brand ON dbo_Product.ProductBrand = dbo_Brand.Brand
WHERE (dbo_Product.IDPartNumber=[dbo_Stock].[IDPartNumber]) AND (dbo_Product.ProductBrand=[dbo_Brand].[Brand]) AND ((dbo_ProdGroup.ID=[dbo_Product].[DuplicateGroup2]) OR (dbo_Product.GroupCode=[dbo_ProdGroup].[ID]) OR (dbo_ProdGroup.ID=[dbo_Product].[DuplicateGroup1]))

Thanks for the help.

 

[JonFer]

Please post some example records and the desired output.

John

http://www.interautomateconsulting.com