To filter some records in a query

[zopita]

Hi! Good morning all the people.

I have two tables:

Table 1 Table 2
---------- -----------
A | a E | e
B | b1 F | f1
B | b2 F | f2
C | c G | g

The query gives me this table:

A a E e
B b1 F f1
B b1 F f2
B b2 F f1
B b2 F f2
C c G g

but I need only the first "B" (b1) with the first "F" (f1) and the second "B" (b2) with the second "F" (f2):

A a E e
B b1 F f1
B b2 F f2
C c G g

Is possible make it in SQL (I dont know VA)? Thanks!

zopita (Spain)

[Stewart Ross]

Hi zopita, and Welcome to Bytes!

You can group rows and get rid of duplicates.

Simplest way of all to achieve this in Access is from the Access Query Editor select View, Totals to turn on grouping. This will group the query by your selected fields and eliminate any duplicates.

If you prefer to use standard SQL you can select unique rows using the DISTINCT qualifier:

SELECT DISTINCT field1, field2 ... fieldN FROM sometable;

However, if you need to select one part of a row from the duplicates and another from a different row you cannot do what you ask selectively without coding - SQL has no concept of record position, and cannot do a 'select this from the current row and that from the next'.

In the grouping version of the query it is possible to use functions such as Min to return the minimum value of a field, and Max to return the maximum. This may do what you need, if your 'b1' represents the minimum vaue and 'f2' the maximum. Your dummy data is so far removed from real-world data that I simply cannot tell whether or not using Min on the B field and Max on the F field will indeed select the rows you need. It helps us if you post data which is more representative of the real thing...

-Stewart

[zopita]

Hellow, Stewart!

Look at this:

___________Tabl e 1____________ Table 2_______
row 1: . a --- 2.5.4 --- b . . . . . . k --- 3.1.2.1 --- l
row 2: . c --- 3.1.2 --- d . . . . . . m --- 6.1.8 --- n
row 3: . e --- 8.1.3 --- f . . . . . . ñ --- 2.5.4 --- o
row 4: . g --- 3.1.2 --- h . . . . . . p --- 3.1.2.2 --- q
row 5: . i --- 4.9.6 --- j . . . . . . . r --- 8.1.3 --- s


To make a query I must to use the 3 numbers. So, the Access repport is like this.

Repp. row 1: . a --- 2.5.4 --- b ----- ñ --- 2.5.4 --- o
Repp. row 2: . c --- 3.1.2 --- d ----- k --- 3.1.2.1 --- l
Repp. row 3: . c --- 3.1.2 --- d ----- p --- 3.1.2.2 --- q
Repp. row 4: . e --- 8.1.3 --- f ----- r --- 8.1.3 --- s
Repp. row 5: . g --- 3.1.2 --- h ----- k --- 3.1.2.1 --- l
Repp. row 6: . g --- 3.1.2 --- h ----- p --- 3.1.2.2 --- q


Well. There are two "duplicates ", Repp. row 3 and Repp. row 5:

Repp. row 2: . c --- 3.1.2 --- d ----- k --- 3.1.2.1 --- l
Repp. row 3: . c --- 3.1.2 --- d ----- p --- 3.1.2.2 --- q
Repp. row 5: . g --- 3.1.2 --- h ----- k --- 3.1.2.1 --- l
Repp. row 6: . g --- 3.1.2 --- h ----- p --- 3.1.2.2 --- q

because I need that the first 3.1.2 from the table 1 (c --- 3.1.2 --- d) goes only with the first 3.1.2 from the table 2 (k --- 3.1.2.1 --- l), and the second 3.1.2 from de table 1 (g --- 3.1.2 --- h) goes only with the second 3.1.2 from the table 2 (p --- 3.1.2.2 --- q).

Stewart, do you think it's posible? I'm making a macro to export the repport to Excel where I can delete the duplicates, but the command SendKeys don't wants to work... :(

Thanks you very much, Stewart.

Gloria