query unique values

P

Pinner

Hi,
how are you guys??
Here's my problem:
I have two columns in my database : Products and Components. A product

is actually built from many components. The data is showed like this :

Products #1 #2 are formed by componants A, B, C, D and E,F,G,H


Products Comp.
1 A
1 B
1 C
1 D
2 E
2 F
2 G
2 H


As you can see, there is as many lines as there are components for any
given products. I want to be able to, FOR A GIVEN RANGE OF PRODUCTS,
to
only display one time the product AND without its componants.


After my query, access should display only this


Products Comp.
1
2 E
2 F
2 G
2 H

Product #1 is listed one time without its components

thank you all for your advices
Pinner
 
D

Douglas J Steele

What's the basis for only having 1 row returned for product 1, but still
having multiple rows for product 2?
 
P

Pinner

I have statistics to calculate on products and components, but I must
not take into consideration some components for a specific range of
products. My problem is that even if I must not take into
consideration some of the components for sales statistic, I have to
keep the product for stats. That's why Product #1 appears one time and
without its components.

hope its clear
 
D

Douglas J Steele

You're missing the point of my question.

How are we to know which products need to be collapsed, and which don't?

If all you wanted was Product 1 and Product 2, regardless of how many
components there were, you'd use:

SELECT DISTINCT Products
FROM MyTable

If there are rules to be followed as to which product, you can use something
like:

SELECT DISTINCT Products, Null AS Components
FROM MyTable
WHERE Products IN (list of products to collapse)
UNION
SELECT DISTINCT Products, Components
FROM MyTable
WHERE Products NOT IN (list of products to collapse)
 
P

Pinner

I finally created an excel file with the specific products, linked it
to my database, used this to delete the specific products : COMP:
VraiFaux(EstNull([f1]),[dbo_bom].[Item],"") (in english : COMP:
iif(isNull([f1]),[dbo_bom].[Item],"")
and used the query property Unique values set as Yes.

don't if you guys understand what I've done but it's working!!!!!!!

thanks for your advices!!
 
Top