Distinct SQL for 2 fields

[Erick]

How can I query this table to return distinct records for fields 1 and also distinct record for field 2.

eg.
Field1--------Field2
1-------------A
1-------------B
2-------------A
2-------------B

The query will return:
Field1--------Field2
1-------------A
2-------------B

 

[SydneyGeek]

Add both fields to the grid; they should be the only fields in the query.
Click the Sigma icon to make a Totals row; leave both fields as Group By

Denis

 

[Erick]

Thanks for your response Denis

If I do this, the I get the following:
Field1------Field2
1-----------a
1-----------b
2-----------a
2-----------b

I need to return:

Field1------Field2
1-----------a
2-----------b
so that each record in each field is unique.

Just to confirm, I did what you suggested, here is the sql code:

SELECT Table1.Field1, Table1.Field2
FROM Table1
GROUP BY Table1.Field1, Table1.Field2;

 

[dk]

Hi

What is the rule/logic that says that 1 maps to A and 2 maps to B? In order to get a query to do this there needs to be some logic that can be applied....

It might help if you explain what those field1,field2 represent in your real life database and what you are trying to accomplish.

DK

 

[Erick]

Hi

What is the rule/logic that says that 1 maps to A and 2 maps to B? In order to get a query to do this there needs to be some logic that can be applied....
DK

The rule is that the query result of Field1 returns just the distinct records from record source Field1 and query result of Field2 returns just the distinct record from record source Field2

Hi
It might help if you explain what those field1,field2 represent in your real life database and what you are trying to accomplish.
DK

Ultimately, I am creating an auto-mapping function to designate 1 record item from 1 table to another table. So say for example, in 1 record set I have VLV1, VLV2, VLV3 and in another record set, I have ST0009V121, ST0009V122, ST0009V123. I am creating a auto-mapping function that will allocate VLV1 to ST0009V121, VLV2 to ST0009V122, VLV3 to ST0009V123. Just for general info, these are equipment numbers and the VLV# are pre-generated generic tags and the ST0009# are specific equip# and I need to allocate a specific equipment to each generic tag...can you now see why I didn't try and elaborate earlier...even I get lost with my own explanation

 

[SydneyGeek]

So, does the table automatically create all the possible combinations, or is this the result of an earlier query?
It's going to be difficult to line them up from a cartesian join if the rule for doing so can't easily be turned into an expression.

Denis

 

[Erick]

So, does the table automatically create all the possible combinations, or is this the result of an earlier query?
It's going to be difficult to line them up from a cartesian join if the rule for doing so can't easily be turned into an expression.

Denis

The sample table I gave in my original post was a result of cartesion join. But because this creates all possible combinations and hence create duplicated data in each field, I was hoping I can then reduce this cartesian join result by somehow calling for separate distinct values for each field...but this is obviously proving to be difficult.

Another way I thought of doing this is to query each set of data separately and add a quasi row counter field for each set of queries then map the 2 sets of data using this quasi row number field. I can generate the quasi row numbers for each query (in less than 10secs) but for some reason when i map the 2 queries together MS Access hangs. I trialled it out with only about 100 records and let the query run overnight and my pc was still in freeze mode in the morning.

 

[SydneyGeek]

Try turning each query (with the index) into a make-table query. Run the make-table and join those. Should be much quicker than joining two subquery fields (if that's how you created the counters).

Denis

 

[Erick]

Try turning each query (with the index) into a make-table query. Run the make-table and join those. Should be much quicker than joining two subquery fields (if that's how you created the counters).

Denis

Thanks Denis, I am actually thinking about going down this path with vba code to drop the queries into a table and then query the resulting 2 tables. At first glance, I just thought I could accomplish this task in 1 simple SQL code...I gues sometimes life is just not that easy.....

 

[SydneyGeek]

Definitely. I have found over time that some queries are best converted to tables before analysing them further, either because of the time taken or because the indexing gets scrambled.

Denis