select unique values (1 Viewer)

[cpampas]

Hello,
I have a table "myTable", with the folowwing data :

dataM	aValue	bValue	cValue
31-dez-23	19,73	15,86	52,56
31-dez-23	19,73	52,56	15,86
31-dez-23	52,56	15,86	19,73
31-dez-23	52,56	19,73	15,86
31-dez-23	26	31,71	80
31-dez-23	26	80	31,71
31-dez-23	31,71	26	80
31-dez-23	31,71	32	74
31-dez-23	31,71	50	56
31-dez-23	31,71	56	50
31-dez-23	31,71	74	32
31-dez-23	31,71	80	26
31-dez-23	32	31,71	74
31-dez-23	32	74	31,71
31-dez-23	50	31,71	56
31-dez-23	50	56	31,71
31-dez-23	56	31,71	50
31-dez-23	56	50	31,71
31-dez-23	74	31,71	32
31-dez-23	74	32	31,71
31-dez-23	80	26	31,71
31-dez-23	80	31,71	26

i want a query that returns only those records that have a unique set of field values . in this case i would like to return:

dataM	aValue	bValue	cValue
31-dez-23	19,73	15,86	52,56
01-jan-24	26	31,71	80
02-jan-24	31,71	32	74
03-jan-24	31,71	50	56

is this done with a query or dao.recordset ?

 

[Mike Krailo]

So what is the primary key in your table? I'm not sure how you define unique set of field values. You are showing records 1,5,8,9 as being a unique set, but how are they unique compared to any of the other records in the table? Please clarify.

 

[June7]

Why do you show jan dates in the output? Was that an error from building data in Excel?

Assuming there is a unique identifier field in table, like an autonumber named ID, consider:

Query1:
SELECT ID, dataM, aValue AS Qty FROM MyTable
UNION ALL SELECT ID, dataM, bValue FROM MyTable
UNION ALL SELECT ID, dataM, cValue FROM MYTable
ORDER BY ID;

Query2:
TRANSFORM First(Query1.Qty) AS FirstOfQty
SELECT Query1.ID, Query1.dataM
FROM Query1
GROUP BY Query1.ID, Query1.dataM
PIVOT DCount("*","Query1","ID=" & [ID] & " AND Qty<" & [Qty]) + 1;

Query3:
SELECT DISTINCT Query2.dataM, Query2.[1], Query2.[2], Query2.[3] FROM Query2;

dataM	1	2	3
31-Dec-23	15.86	19.73	52.56
31-Dec-23	26	31.71	80
31-Dec-23	31.71	32	74
31-Dec-23	31.71	50	56

 

[cpampas]

Mike, you are right i forgot the primary key, wich is a unique number "ID"
June, i used your queries that work great, altough a litle slow, but i can live with that
Many thanks

 

[Galaxiom]

June, i used your queries that work great, altough a litle slow,

I strongly suspect the whole problem is due to inappropriate table structure and your source data should be stored in a table something like the output of June's first query.

 

[ebs17]

i want a query that returns only those records that have a unique set of field values . in this case i would like to return:

How do you produce other dates from 31-Dec-23?

The only rule that can be derived from the result shown is that the first record was selected for the first date.

Every query and every program procedure must follow rules. So you would have to formulate them sufficiently and reliably in advance.
Unique: With the four fields as a combination, each record is already unique in MyTable. So that's not enough.

If @June7's suggestion appeals to you, then your goal was different from what you originally formulated.

This also implies the question of whether you really know what you want to achieve.

 

[June7]

The rule is: Output unique trios of values for each date, regardless of order in fields.

Normalization would make first query unnecessary and if table stored a sequence number for each trio group, the DCount() would not be needed and performance would be much better.

UNION ALL as shown might be faster than a just UNION version:

SELECT ID, dataM, aValue AS Qty, "a" AS Src FROM MyTable
UNION SELECT ID, dataM, bValue, "b" FROM MyTable
UNION SELECT ID, dataM, cValue, "c" FROM MYTable
ORDER BY ID;

 

[ebs17]

Loop takes too much time to reach EOF

Hello, I have query that list all the posible sums of three numbers in a table. the table only has 70 records, but when i runs the query returns about 275000 records 'step 1 (the query that returns the 275000 records) SELECT DISTINCT a.valor + b.valor + c.valor AS sum_of_3_numbers, a.valor AS...

www.access-programmers.co.uk

It would certainly make more sense to access the original existing tables instead of calculating data back and forth here.