Intersect fully fledged queries (1 Viewer)

[exaccess]

Hello experts. I have a table called TelephoneAAA. I should run a query to find duplicate records based on a Field called ADI and a second query to do the same based on a field called SOYADI. I have used the Access query creation wizard To create both queries. Individually they work, but I want to get an intersection of the two queries that never works. Here is the code:

SELECT TelephoneAAA.ADI, TelephoneAAA.SOYADI, TelephoneAAA.TEL, TelephoneAAA.ADRES, TelephoneAAA.DateOfUpdate
FROM TelephoneAAA
WHERE (((TelephoneAAA.ADI) In (SELECT [ADI] FROM [TelephoneAAA] As Tmp GROUP BY [ADI] HAVING Count(*)>1  And [ADI] = [TelephoneAAA].[ADI])))
ORDER BY TelephoneAAA.ADI, TelephoneAAA.SOYADI
INTERSECT 
SELECT TelephoneAAA.ADI, TelephoneAAA.SOYADI,  TelephoneAAA.TEL, TelephoneAAA.ADRES, TelephoneAAA.DateOfUpdate
FROM TelephoneAAA
WHERE (((TelephoneAAA.SOYADI) In (SELECT [SOYADI] FROM [TelephoneAAA] As Tmp1 GROUP BY [SOYADI] HAVING Count(*)>1 And [SOYADI] = [TelephoneAAA].[SOYADI])))
ORDER BY TelephoneAAA.ADI, TelephoneAAA.SOYADI;

Please help

 

[plog]

INTERSECT isn't part of Access, so you need to create some sub-queries to achieve the same thing. Here's what I would do:

sub1: a query that finds all ADI values that appear more than once in your table.

sub2: a query that finds all SOYADI values that appear more than once in your table.

Main: bring in TelephoneAAA and the 2 subs above. INNER JOIN both tables to TelephoneAAA and bring down all the fields from TelephoneAAA that you want to show, run the query and there's your data.

 

[exaccess]

INTERSECT isn't part of Access, so you need to create some sub-queries to achieve the same thing. Here's what I would do:

sub1: a query that finds all ADI values that appear more than once in your table.

sub2: a query that finds all SOYADI values that appear more than once in your table.

Main: bring in TelephoneAAA and the 2 subs above. INNER JOIN both tables to TelephoneAAA and bring down all the fields from TelephoneAAA that you want to show, run the query and there's your data.

Sorry but for the first time I am going to do this. I wrote this code:

SELECT TelephoneAAA.ADI, TelephoneAAA.SOYADI, TelephoneAAA.TEL, TelephoneAAA.ADRES, TelephoneAAA.DateOfUpdate
FROM ([TelephoneAAA]
INNER JOIN [QUERIES]![DupsByAdi]
ON TelephoneAAA.ADI = [QUERIES]![DupsByAdi].[ADI] )
And
SELECT TelephoneAAA.ADI, TelephoneAAA.SOYADI, TelephoneAAA.TEL, TelephoneAAA.ADRES, TelephoneAAA.DateOfUpdate
FROM ([TelephoneAAA]
INNER JOIN [QUERIES]![DupsBySoyadi]
ON TelephoneAAA.SOYADI = [QUERIES]![DupsBySoyadi].SOYADI])

It says error in FROM phrase.

 

[JHB]

What is [QUERIES], a form or ???

[QUERIES]![DupsByAdi].[ADI]

 

[exaccess]

What is [QUERIES], a form or ???

Well here it is referring to a query which is my subquery.

 

[JHB]

What happen if you let the [QUERIES]out?

SELECT TelephoneAAA.ADI, TelephoneAAA.SOYADI, TelephoneAAA.TEL, TelephoneAAA.ADRES, TelephoneAAA.DateOfUpdate FROM ([TelephoneAAA] INNER JOIN [DupsByAdi] ON TelephoneAAA.ADI = [DupsByAdi].[ADI] )

Then I see you're missing a [.

SELECT TelephoneAAA.ADI, TelephoneAAA.SOYADI, TelephoneAAA.TEL, TelephoneAAA.ADRES, TelephoneAAA.DateOfUpdate FROM ([TelephoneAAA] INNER JOIN [QUERIES]![DupsBySoyadi] ON TelephoneAAA.SOYADI = [QUERIES]![DupsBySoyadi].[SOYADI])

 

[exaccess]

What happen if you let the [QUERIES]out?

Then I see you're missing a [.

I have done what you said, but what do you put between two queries to intersect them. Here is the code and it rejects the operator And.

(SELECT TelephoneAAA.ADI, TelephoneAAA.SOYADI, TelephoneAAA.TEL, TelephoneAAA.ADRES, TelephoneAAA.DateOfUpdate
FROM ([TelephoneAAA]
INNER JOIN [DupsByAdi]
ON TelephoneAAA.ADI = [DupsByAdi].[ADI])) And
(SELECT TelephoneAAA.ADI, TelephoneAAA.SOYADI, TelephoneAAA.TEL, TelephoneAAA.ADRES, TelephoneAAA.DateOfUpdate
FROM ([TelephoneAAA]
INNER JOIN [DupsBySoyadi]
ON TelephoneAAA.SOYADI = [DupsByAdi].[SOYADI]));

 

[plog]

Why are there 2 SELECT statements in that thing? There's also a misplaced parenthesis in there. Why are you trying to get this done in all one shot? Are you using Design view or trying to write SQL by hand?

I'd advise you use the Design View and build your sub-queries as I instructed, then construct your main query as I instructed.

 

[exaccess]

Why are there 2 SELECT statements in that thing?

INTERSECT isn't part of Access, so you need to create some sub-queries to achieve the same thing. Here's what I would do:

sub1: a query that finds all ADI values that appear more than once in your table.

sub2: a query that finds all SOYADI values that appear more than once in your table.

Main: bring in TelephoneAAA and the 2 subs above. INNER JOIN both tables to TelephoneAAA and bring down all the fields from TelephoneAAA that you want to show, run the query and there's your data.

That is what you said. First SELECT joins the first query and the table and second SELECT joins the second query and the table.

There's also a misplaced parenthesis in there. Why are you trying to get this done in all one shot? Are you using Design view or trying to write SQL by hand?

Now it is in three steps. If you think that more steps are needed I would be pleased to hear.

I'd advise you use the Design View and build your sub-queries as I instructed, then construct your main query as I instructed.

I am using the design wizard and view. That is how I constructed the two sub-queries. What else should I do?

 

[plog]

You should build sub1, save it. Build sub2, save it. Then you create a 3rd query object, in design view you bring in your table and sub1 and sub2, join them appropriately and bring down your fields from your table.

The final query will have 1 SELECT clause, 1 FROM clause and 2 INNER JOIN clauses.

 

[exaccess]

You should build sub1, save it. Build sub2, save it. Then you create a 3rd query object, in design view you bring in your table and sub1 and sub2, join them appropriately and bring down your fields from your table.

The final query will have 1 SELECT clause, 1 FROM clause and 2 INNER JOIN clauses.

I have done exactly that. The subs work, but the final one does not. Perhaps it is something very obvious but I can't see it. The error message is "Syntax error"

[CODE]SELECT  TelephoneAAA.ADI, TelephoneAAA.SOYADI, TelephoneAAA.TEL, TelephoneAAA.ADRES, TelephoneAAA.DateOfUpdate
FROM TelephoneAAA INNER JOIN DupsByAdi ON TelephoneAAA.ADI = DupsByAdi.ADI , 
INNER JOIN DupsBysoyadi ON TelephoneAAA.SOYADI =  DupsBySoyadi.SOYADI 
ORDER BY TelephoneAAA.ADI;

[/CODE]

 

[MarkK]

There is also an "IN" operator in Jet SQL, so you can do stuff like . . .

SELECT * FROM Table1 WHERE Field1 IN ( SELECT Field1 FROM Table2 )

. . . which might yield the intersection you are talking about.

 

[plog]

FROM TelephoneAAA INNER JOIN DupsByAdi ON TelephoneAAA.ADI = DupsByAdi.ADI ,

That comma shouldn't exist.

If that doesn't fix it, can you upload your database?

 

[exaccess]

FROM TelephoneAAA INNER JOIN DupsByAdi ON TelephoneAAA.ADI = DupsByAdi.ADI ,

That comma shouldn't exist.

If that doesn't fix it, can you upload your database?

Neither comma deletion nor putting "AND" do not work. I may try to upload the db, but due to data privacy it will take time. In the meantime I will see if I can find another solution.

 

[plog]

It could be one of the sub-queries causing the issue as well. Do they run fine by themselves?


You can clear out all the data and upload blank tables and the queries and that would be enough to let me see the issue

 

[exaccess]

It could be one of the sub-queries causing the issue as well. Do they run fine by themselves?


You can clear out all the data and upload blank tables and the queries and that would be enough to let me see the issue

Here is the latest version of code that does not work. I have deleted the comma.

SELECT TelephoneAAA.ADI, TelephoneAAA.SOYADI, TelephoneAAA.TEL, TelephoneAAA.ADRES, TelephoneAAA.DateOfUpdate
FROM TelephoneAAA INNER JOIN DupsByAdi ON TelephoneAAA.ADI = DupsByAdi.ADI 
 INNER JOIN DupsBySoyAdi ON TelephoneAAA.ADI = DupsBySoyAdi.ADI
ORDER BY TelephoneAAA.ADI;

Yes the two subs work fine. THe database is coming in a few moments.

 

[exaccess]

Sorry plog I can not upload the databases (Test and data) due to an error I am getting and it tells me there is a security issue and to contact the webmaster.I have no time to deal with the uploading issue. I"ll have to look for another solution. Thanks for all your efforts

 

[exaccess]

Further to this subject I found the following solution. It works:

SELECT TelephoneAAA.ADI, TelephoneAAA.SOYADI, TelephoneAAA.TEL, TelephoneAAA.ADRES, TelephoneAAA.DateOfUpdate
FROM (( TelephoneAAA INNER JOIN DupsByAdi ON TelephoneAAA.ADI = DupsByAdi.ADI )
 INNER JOIN DupsBySoyAdi ON TelephoneAAA.ADI = DupsBySoyAdi.ADI)
ORDER BY TelephoneAAA.ADI;

Pay attention to the commas in the FROM phrase. Queries with multiple joins work like this.

 

[exaccess]

I found the solution. Her is code that does exactly the job.

SELECT TelephoneAAA.ADI, TelephoneAAA.SOYADI, TelephoneAAA.TEL, TelephoneAAA.ADRES, TelephoneAAA.DateOfUpdate
FROM( (TelephoneAAA INNER JOIN DupsByAdi ON TelephoneAAA.ADI = DupsByAdi.ADI)
 INNER JOIN DupsBySoyadi ON TelephoneAAA.SOYADI = DupsBySoyAdi.SOYADI)
ORDER BY TelephoneAAA.ADI;

Attention to the ( that follow FROM.