New Query, All Records in one Query, which does not exist in anoth

M

Mercadogs

Hello knowledged people:

I'm trying to create a query that compares two queries.
The two existing queries return the same fields, of course, with different
records in each query.

With the new query, I want to pull all the records of Query1 which do not
exist in Query2.

What is the simplest way of doing this?

Needing help,
Gabriel M.
 
J

Jerry Whittle

Is there a field, or a small number of fields, in both queries that uniquely
define a record or do you need to compare the entire record returned in the
queries?

Also there's a good chance that you may want to just do the comparison
before the two queries. In other words, do another query right off the table
that does what you want.

What's the SQL statement for the two queries?
 
M

Mercadogs

The SQLs for Query1 and 2 are below.
I've also included the SQL for a the query I was trying to accomplish.

The Field called "Tech" uniquely defines each record.

NOTE: Both Query1 and Query2 retrieve their info from the same table,
"Combination".



--Gabriel M.

----------------------------------------------

‘Query1--For Finding Duplicates in Table called “Combination†’
SELECT FL.[Tech]
FROM FL INNER JOIN [Combination] ON FL.FLID = [Combination].FLID
WHERE ((([Combination].Mat) In (SELECT [Mat] FROM [Combination] As Tmp GROUP
BY [Mat],[COP] HAVING Count(*)=3 And [COP] = [Combination].[COP])))
ORDER BY [Combination].Mat, [Combination].[COP];

-----------------------------------------------------------------------------------------------

‘Query2--For Finding Duplicates in table called “AisNull†‘
SELECT FL.[Tech]
FROM ANull INNER JOIN FL ON AisNull.FLID = FL.FLID
WHERE (((AisNull.Mat) In (SELECT [Mat] FROM [AisNull] As Tmp GROUP BY
[Mat],[COP] HAVING Count(*)=3 And [COF] = [AisNull].[COP])))
ORDER BY AisNull.Mat, AisNull.[COP];

------------------------------------------------------------------------------
‘AttemptQuery--Query for combining including all Records in Query1 not
existing in Query2'
SELECT [Combination].Mat, [Combination].quant, [Combination].un,
[Combination].A [Combination].[COP], [Combination].type, [Combination].desc,
FL.[Tech]
FROM (FL INNER JOIN [Combination] ON FL.FLID = [Combination].[Tech])
WHERE ((([Combination].[Tech]) In (SELECT [Tech]
FROM [Duplicates for Combination]
)
And ([Combination].[Tech]) Not In (SELECT [Tech]
FROM [Duplicates for AisNull]
)));

----------------------------------------------------------------------
 
M

Mercadogs

I made a mistake in previous message. Query2 gets its information from
another Query.

Mercadogs said:
The SQLs for Query1 and 2 are below.
I've also included the SQL for a the query I was trying to accomplish.

The Field called "Tech" uniquely defines each record.

NOTE: Both Query1 and Query2 retrieve their info from the same table,
"Combination".



--Gabriel M.

----------------------------------------------

‘Query1--For Finding Duplicates in Table called “Combination†’
SELECT FL.[Tech]
FROM FL INNER JOIN [Combination] ON FL.FLID = [Combination].FLID
WHERE ((([Combination].Mat) In (SELECT [Mat] FROM [Combination] As Tmp GROUP
BY [Mat],[COP] HAVING Count(*)=3 And [COP] = [Combination].[COP])))
ORDER BY [Combination].Mat, [Combination].[COP];

-----------------------------------------------------------------------------------------------

‘Query2--For Finding Duplicates in table called “AisNull†‘
SELECT FL.[Tech]
FROM ANull INNER JOIN FL ON AisNull.FLID = FL.FLID
WHERE (((AisNull.Mat) In (SELECT [Mat] FROM [AisNull] As Tmp GROUP BY
[Mat],[COP] HAVING Count(*)=3 And [COF] = [AisNull].[COP])))
ORDER BY AisNull.Mat, AisNull.[COP];

------------------------------------------------------------------------------
‘AttemptQuery--Query for combining including all Records in Query1 not
existing in Query2'
SELECT [Combination].Mat, [Combination].quant, [Combination].un,
[Combination].A [Combination].[COP], [Combination].type, [Combination].desc,
FL.[Tech]
FROM (FL INNER JOIN [Combination] ON FL.FLID = [Combination].[Tech])
WHERE ((([Combination].[Tech]) In (SELECT [Tech]
FROM [Duplicates for Combination]
)
And ([Combination].[Tech]) Not In (SELECT [Tech]
FROM [Duplicates for AisNull]
)));

----------------------------------------------------------------------
Jerry Whittle said:
Is there a field, or a small number of fields, in both queries that uniquely
define a record or do you need to compare the entire record returned in the
queries?

Also there's a good chance that you may want to just do the comparison
before the two queries. In other words, do another query right off the table
that does what you want.

What's the SQL statement for the two queries?
Click to expand...
Click to expand...
 
M

Mercadogs

My Bad. Query2 gets its information from a query(not a table) called “AisNullâ€.

Mercadogs said:
The SQLs for Query1 and 2 are below.
I've also included the SQL for a the query I was trying to accomplish.

The Field called "Tech" uniquely defines each record.

NOTE: Both Query1 and Query2 retrieve their info from the same table,
"Combination".



--Gabriel M.

----------------------------------------------

‘Query1--For Finding Duplicates in Table called “Combination†’
SELECT FL.[Tech]
FROM FL INNER JOIN [Combination] ON FL.FLID = [Combination].FLID
WHERE ((([Combination].Mat) In (SELECT [Mat] FROM [Combination] As Tmp GROUP
BY [Mat],[COP] HAVING Count(*)=3 And [COP] = [Combination].[COP])))
ORDER BY [Combination].Mat, [Combination].[COP];

-----------------------------------------------------------------------------------------------

‘Query2--For Finding Duplicates in table called “AisNull†‘
SELECT FL.[Tech]
FROM ANull INNER JOIN FL ON AisNull.FLID = FL.FLID
WHERE (((AisNull.Mat) In (SELECT [Mat] FROM [AisNull] As Tmp GROUP BY
[Mat],[COP] HAVING Count(*)=3 And [COF] = [AisNull].[COP])))
ORDER BY AisNull.Mat, AisNull.[COP];

------------------------------------------------------------------------------
‘AttemptQuery--Query for combining including all Records in Query1 not
existing in Query2'
SELECT [Combination].Mat, [Combination].quant, [Combination].un,
[Combination].A [Combination].[COP], [Combination].type, [Combination].desc,
FL.[Tech]
FROM (FL INNER JOIN [Combination] ON FL.FLID = [Combination].[Tech])
WHERE ((([Combination].[Tech]) In (SELECT [Tech]
FROM [Duplicates for Combination]
)
And ([Combination].[Tech]) Not In (SELECT [Tech]
FROM [Duplicates for AisNull]
)));

----------------------------------------------------------------------
Jerry Whittle said:
Is there a field, or a small number of fields, in both queries that uniquely
define a record or do you need to compare the entire record returned in the
queries?

Also there's a good chance that you may want to just do the comparison
before the two queries. In other words, do another query right off the table
that does what you want.

What's the SQL statement for the two queries?
Click to expand...
Click to expand...
 
J

Jerry Whittle

At this point, it's too complicated for me to hazzard a guess. I suggest that
you take a step or two back and see if there is a way to simplify what you
are doing. Maybe trace down the data manually to see where the duplicates get
in.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Mercadogs said:
My Bad. Query2 gets its information from a query(not a table) called “AisNullâ€.

Mercadogs said:
The SQLs for Query1 and 2 are below.
I've also included the SQL for a the query I was trying to accomplish.

The Field called "Tech" uniquely defines each record.

NOTE: Both Query1 and Query2 retrieve their info from the same table,
"Combination".



--Gabriel M.

----------------------------------------------

‘Query1--For Finding Duplicates in Table called “Combination†’
SELECT FL.[Tech]
FROM FL INNER JOIN [Combination] ON FL.FLID = [Combination].FLID
WHERE ((([Combination].Mat) In (SELECT [Mat] FROM [Combination] As Tmp GROUP
BY [Mat],[COP] HAVING Count(*)=3 And [COP] = [Combination].[COP])))
ORDER BY [Combination].Mat, [Combination].[COP];

-----------------------------------------------------------------------------------------------

‘Query2--For Finding Duplicates in table called “AisNull†‘
SELECT FL.[Tech]
FROM ANull INNER JOIN FL ON AisNull.FLID = FL.FLID
WHERE (((AisNull.Mat) In (SELECT [Mat] FROM [AisNull] As Tmp GROUP BY
[Mat],[COP] HAVING Count(*)=3 And [COF] = [AisNull].[COP])))
ORDER BY AisNull.Mat, AisNull.[COP];

------------------------------------------------------------------------------
‘AttemptQuery--Query for combining including all Records in Query1 not
existing in Query2'
SELECT [Combination].Mat, [Combination].quant, [Combination].un,
[Combination].A [Combination].[COP], [Combination].type, [Combination].desc,
FL.[Tech]
FROM (FL INNER JOIN [Combination] ON FL.FLID = [Combination].[Tech])
WHERE ((([Combination].[Tech]) In (SELECT [Tech]
FROM [Duplicates for Combination]
)
And ([Combination].[Tech]) Not In (SELECT [Tech]
FROM [Duplicates for AisNull]
)));

----------------------------------------------------------------------
Jerry Whittle said:
Is there a field, or a small number of fields, in both queries that uniquely
define a record or do you need to compare the entire record returned in the
queries?

Also there's a good chance that you may want to just do the comparison
before the two queries. In other words, do another query right off the table
that does what you want.

What's the SQL statement for the two queries?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hello knowledged people:

I'm trying to create a query that compares two queries.
The two existing queries return the same fields, of course, with different
records in each query.

With the new query, I want to pull all the records of Query1 which do not
exist in Query2.

What is the simplest way of doing this?

Needing help,
Gabriel M.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to pass a parameter to a query base of a query that is the base of a report 0
Variables in Query 1
Extract records from query 1
loop though records in a query? 5
Query 1 without matching records in Query 2 5
Query does not calculate/show all results 2
MS Access query filter problem 1
Crosstab query totals 3

Top