473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

query

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

Dec 5 '05 #1
9 2525

<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.

Dec 5 '05 #2
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

Dec 5 '05 #3

<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?

Dec 5 '05 #4
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

Dec 5 '05 #5
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

Dec 5 '05 #6
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

Dec 5 '05 #7
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

Dec 5 '05 #8
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

Dec 5 '05 #9

<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
Dec 5 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: jaysonsch | last post by:
Hello! I am having some problems with a database query that I am trying to do. I am trying to develop a way to search a database for an entry and then edit the existing values. Upon submit, the...
29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
3
by: Harvey | last post by:
Hi, I try to write an asp query form that lets client search any text-string and display all pages in my web server that contain the text. I have IIS 6.0 on a server 2003. The MSDN site says...
4
by: Diamondback | last post by:
I have two tables, WIDGETS and VERSIONS. The WIDGETS table has descriptive information about the widgets while the VERSIONS table contains IDs relating to different iterations of those widgets...
14
by: Dave Thomas | last post by:
If I have a table set up like this: Name | VARCHAR Email | VARCHAR Age | TINYINT | NULL (Default: NULL) And I want the user to enter his or her name, email, and age - but AGE is optional. ...
0
by: starace | last post by:
I have designed a form that has 5 different list boxes where the selections within each are used as criteria in building a dynamic query. Some boxes are set for multiple selections but these list...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
4
by: Stan | last post by:
I am using MS Office Access 2003 (11.5614). My basic question is can I run a query of a query datasheet. I want to use more that one criteria and can not get that query to work. I thought I...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.