Query to generate only unique values for multiple fields

[hcisteve]

I want to construct a query that will generate unique values for multiple columns -- so that the row relationship among values will no longer be maintained. Currently each column has multiple values for the same column. I have set the row properties to be unique but this only gives me unique records. The only time I can get a unique values for a column is when my query only contains one column. Is there a way to do this for multiple columns in Access?

Steve

 

[dhookom]

Have you tried viewing totals?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hcisteve]

Do you mean using Reports? I don't have much experience with Reports, could you provide a little more information?

Steve Graves

 

[hcisteve]

I tried using a totals query using "Group By" but I had the same effect. I can get unique values for one column, but I can't get unique values for more than one column at at time.

Steve

 

[dhookom]

Steve,
It is clear that I don't understand what you need. Please provide enough sample records and desired display so that your question becomes clear to us.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lynchg]

I think I understand what you are trying to do, but I don't know why you would want to do it and I can't see what use the information would be within one data set. But, judgements aside, try something like this:

Select Distinct [Field A] as FieldA, '' as FieldB, '' as FieldC, '' as FieldD From yourTable
Union
Select Distinct '', [Field B], '', '' From yourTable
Union
Select Distinct '', '', [Field C], '' From yourTable
Union
Select Distinct '', '', '', [Field D] From yourTable

The separate "lists" end up getting stacked on top of one another but you do get your sets of unique values within each field.

HTH

 

[hcisteve]

Sorry about not being more specific. Let me provide an example:

Region Site
US OR
US AZ
US FM
Asia PG
Asia KM
Asia KM
Europe SW
US AZ
Europe MO
Asia PG
Asia PG

What I want from multiple columns -- several more than what has been provided in this example are the folowing (one instance of each) I was hoping to put it in a second table. But I am open to other ideas.

Region Site
US AZ
Asia OR
Europe PG
MO
SW
KM
FM

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lynchg]

Can I ask you what purpose it serves to have data in the same record in a table where the data in one column does not correspond to the data in the next column of the same record?

Ex: Oregon has nothing to do with Asia, the fact that those 2 pieces of data happen to end up in the same record is totally random and seems to be of no use.

You can probably eventually come up with some scheme to populate a table in the manner you present, but it looks like a lot of work with little result.

 

[hcisteve]

What I am doing is creating a table of values that I will read into list boxea and that can be used to generate new queries against the main table.

Steve

 

[hcisteve]

What I have had to do before is to copy the data into Excel and use the Advanced filtering function in Excel to take each column and create a new column with unique values. I was hoping to find a faster solution using Access.

Steve

 

[lynchg]

Are they going to make choices that become part of the Where clause of your query?

 

[hcisteve]

Yes

 

[lynchg]

Would it make sense to you to have a few combo boxes, each with a list for one thing, like Region, Site, etc?

Have them pick region first, that value can feed your query but also determine what values appear in your Site combo box, only the US sites would appear if your region choice was US.

Then, after the user makes all of their choices, the values in those combo boxes can serve as the criteria in your where clause. This can be done either by sending parameters to the query or by creating your SQL on the fly in your code.