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

  • Thread starter Thread starter Mercadogs
  • Start date Start date
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.
 
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?
 
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]
)));

----------------------------------------------------------------------
 
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...
 
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...
 
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...
 
Back
Top