query

[spinard]

Hi,
how are you guys??
Here's my problem:
I have two columns in my database : Products and Componants. A product
is actually built from many componants. 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 componants for any
given products. I want to be able to, for a given list 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

thank you all for your advices
Pinner

[Jim McColl]

<sp*****@uapinc.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Hi,
how are you guys??
Here's my problem:
I have two columns in my database : Products and Componants. A product
is actually built from many componants. 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 componants for any
given products. I want to be able to, for a given list 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

thank you all for your advices
Pinner

One easy way would be to use a report. Sort by product and create a product
header in the report and then show the component in each detail line.

[spinard]

hi,
I must not use a report. I have to work with the query since I'll have
to export it in Excel. I'm not trying to display data on a report or
something like that, I have other data attached to my file and I'll
have to run calculations (on products and componants) after my list is
updated with the correct information

thanks

[Randy Harris]

<sp*****@uapinc.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Hi,
how are you guys??
Here's my problem:
I have two columns in my database : Products and Componants. A product
is actually built from many componants. 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 componants for any
given products. I want to be able to, for a given list 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

thank you all for your advices
Pinner

Pinner, this is not clear. Your example shows a single line for Product 1,
with no components, but multiple lines for Product 2, each with a single
component. Why is Product 1 treated differently from Product 2?

[Saintor]

If you are using Access 2002+, I suggest that you learn about pivot tables
view in queries. They are extremly flexible.

If not, I see two other ways. One would be to use the 'unique values'
property of the query. The other is the use of "totals" button in the
query.

<sp*****@uapinc.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Hi,
how are you guys??
Here's my problem:
I have two columns in my database : Products and Componants. A product
is actually built from many componants. 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 componants for any
given products. I want to be able to, for a given list 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

thank you all for your advices
Pinner

[spinard]

There are componants that I must not take into consideration for
specific Products for my calculations. Product #1 is one of them, but
not #2. That's why Product #2's componants are still displayed.
So, product #1 is still shown (one time instead of four), but without
its componants and product #2 is showed as the same.

Hope it's clear now
thx

[spinard]

I just read on "Unique values" and that would probably do the job.
Last question, can we use that for a specific range of products?

I'll try running another query and attach it to my database

thx

[spinard]

I just read on "Unique values" and that would probably do the job.
Last question, can we use that for a specific range of products?

I'll try running another query and attach it to my database

thx

[spinard]

I'm not finding any method to use "Unique values" fonction for a
specific range of products.
Is there someone taht could give me a clue on that?? or maybe "Unique
values" is not what I have to use

[Randy Harris]

<sp*****@uapinc.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

There are componants that I must not take into consideration for
specific Products for my calculations. Product #1 is one of them, but
not #2. That's why Product #2's componants are still displayed.
So, product #1 is still shown (one time instead of four), but without
its componants and product #2 is showed as the same.

Hope it's clear now
thx

Still not sure I'm getting the picture. You can have a query easily select
only the products that you need in the output.

SELECT Product, Component
FROM Products
WHERE Product = "2"

Or, if you need multiple products in the output

SELECT Product, Component
FROM Products
WHERE Product IN ("2", "5", "9")

Or, you could add a Yes/No field to your table and select only the products
that are "Active".

Or, create another table to list the "Active" products, then use

SELECT Product, Component
FROM Products
WHERE Product IN (SELECT Product FROM ActiveProducts)

Any of these getting close to what you had in mind?

Randy