By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

To filter some records in a query

P: 2
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)
Sep 29 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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
Sep 29 '08 #2

P: 2
Hellow, Stewart!

Look at this:

___________Table 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
Oct 2 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.