How do I select distinct for 2/5 columns queries in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Heres my setup:
col. 1 Part Name
2 Part Numbers
3 Area Name
4 number2
5 yes/no

I am trying to only select those that have "yes" for col. 5; and distinct
Part Name and Area name. For my case Part name has part number's associated
with it and its all clutter. How exactly would i need to type this in to
avoid the syntax error i keep getting.

Thanks, Justin
 
Sneed924 said:
Heres my setup:
col. 1 Part Name
2 Part Numbers
3 Area Name
4 number2
5 yes/no

I am trying to only select those that have "yes" for col. 5; and distinct
Part Name and Area name. For my case Part name has part number's associated
with it and its all clutter. How exactly would i need to type this in to
avoid the syntax error i keep getting.
Click to expand...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Not sure about what you're asking. Perhaps this solution:

SELECT [Part Name], [Area Name]
FROM table_name
WHERE [yes/no] = True
GROUP BY [Part Name], [Area Name]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQpTEsoechKqOuFEgEQL7zACgoifvSPcsmE3ppnB0m9I6DSE0bUsAoMK/
G3HalPW51tnO0K6QksceuEhr
=z33Y
-----END PGP SIGNATURE-----
 
What i was really going for is to have a query with all 5 components
included. The data base really contains around 20 fields, but these 5 are the
only ones i am interested in. I just want it filtered for duplicate part
names and area names. I still want to include the part numbers and such. See,
each part number base is specific to each part. They just have a million
different prefixes and i want to weed those out. Since the part name doesn't
change i was wanting to select distinct with that field.
I hope this helps make it more clear

MGFoster said:
Sneed924 said:
Heres my setup:
col. 1 Part Name
2 Part Numbers
3 Area Name
4 number2
5 yes/no

I am trying to only select those that have "yes" for col. 5; and distinct
Part Name and Area name. For my case Part name has part number's associated
with it and its all clutter. How exactly would i need to type this in to
avoid the syntax error i keep getting.
Click to expand...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Not sure about what you're asking. Perhaps this solution:

SELECT [Part Name], [Area Name]
FROM table_name
WHERE [yes/no] = True
GROUP BY [Part Name], [Area Name]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQpTEsoechKqOuFEgEQL7zACgoifvSPcsmE3ppnB0m9I6DSE0bUsAoMK/
G3HalPW51tnO0K6QksceuEhr
=z33Y
-----END PGP SIGNATURE-----
Click to expand...
 
What i was really going for is to have a query with all 5 components
included. The data base really contains around 20 fields, but these 5 are the
only ones i am interested in. I just want it filtered for duplicate part
names and area names. I still want to include the part numbers and such. See,
each part number base is specific to each part. They just have a million
different prefixes and i want to weed those out. Since the part name doesn't
change i was wanting to select distinct with that field.
I hope this helps make it more clear
Click to expand...

Not really!

If you have five records with the same partname but different part
numbers, you want to see the part numbers... but how? They're
different! Do you want to see five records, or one record? If one
record, how do you want to display the five different part numbers?


John W. Vinson[MVP]
 
For example:
Face sheet 29444 inside stufff stufff
Face Sheet 29444 ouside " "
Face Sheet 31444 inside " "
Face Sheet 31444 outside " "
......

Ranther than having it list 7 Face Sheets, i want it to select distinct the
part name and distinct area but still give the other columns. All that i
really care about is the last 3 digits anyway. the prefixes don't matter in
this case. All i want to see is:

Face Sheet 29444 inside stuff stuff
Face Sheet 29444 outside stuff stuff

That way its not as cluttered. Is it possible to do it like this.
 
For example:
Face sheet 29444 inside stufff stufff
Face Sheet 29444 ouside " "
Face Sheet 31444 inside " "
Face Sheet 31444 outside " "
.....

Ranther than having it list 7 Face Sheets, i want it to select distinct the
part name and distinct area but still give the other columns. All that i
really care about is the last 3 digits anyway. the prefixes don't matter in
this case. All i want to see is:

Face Sheet 29444 inside stuff stuff
Face Sheet 29444 outside stuff stuff

That way its not as cluttered. Is it possible to do it like this.
Click to expand...

I'm sorry. I have no clue what you mean by "stufff" and "stuff", nor
do I have any clue what the "last three digits" means - last three
digits of what?

A clearer example of my question might be: if you have
Face sheet 29444 inside xyza prst
Face Sheet 29444 ouside b31q wqjc
Face Sheet 31444 inside stt1 yghc
Face Sheet 31444 outside ttuv vxwy
Click to expand...

What do you want to see in the last two columns?

Note that you can create a calculated field in your query:

Right([fieldname], 3)

to extract the rightmost three digits of the part number. That doesn't
help with the other two fields though!

John W. Vinson[MVP]
 
Back
Top