Query Xtab type output

[nathansav]

HI

I have a need to create a report, which i will simplify for the purposes of this.

I have an orders table, which has 2 fields, cust_ID and Product_ID, both are numbers. I have a products table, which has 3 fields, Product_ID autonumber PK, Product_Name text and Licence Yes/No.

The idea is that a customer can buy software product A and a variety of differnet licences for that product, so they can have licence 2 and 3 of avaiable versions 1 to 5.

What i would like to achieve is this

Cust_ID Product_Name Licence Prod 1 Licence Prod 2..5
Cust 1 Software v1 Yes No Yes Yes No No


I can attach the database i have if needed, many thanks.

 

[JamesMcS]

Hi - I don''t think you can achieve this with the fields you have. You're trying to create a query to show which customer has licences for which product - but you don't have a field you can join on to be able to do this in either of those tables.... What you need is a customers table, a products table and a licences held table. Then you can join the three together to get what you want.