Query Based on Another Query

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

I have a query that returns my patent records. I used that query,
qryMASTERSVH2, and created a new query using another table, Assignment
Information, qryMASTERSVH3. The primary key for both tables is PatentID. The
join is all records from qryMASTERSVH2 and those from Assignment Information
where the fields are equal. The problem is that the first query shows the
appropriate number of patents, but the second query shows the same patent 10
times. There don't appear to be any duplicate fields, so I am not sure why
there are so many duplicate records.


qryMASTERSVH2

SELECT vwPatentMastersReport.DocketNumber,
vwPatentMastersReport.ApplicationNumber,
vwPatentMastersReport.ApplicationDate,
vwPatentMastersReport.ConfirmationNumber, vwPatentMastersReport.AgentName,
vwPatentMastersReport.AgentRefNo, vwPatentMastersReport.AssociateName,
vwPatentMastersReport.CountryName, vwPatentMastersReport.FilingNumber,
vwPatentMastersReport.NextDueDate, vwPatentMastersReport.PatentNumber,
vwPatentMastersReport.PublicationDate,
vwPatentMastersReport.PublicationNumber, vwPatentMastersReport.Title,
vwPatentInventorsReport.InventorName, vwPatentInventorsReport.PatentMasterID,
vwPatentMastersReport.[Text#10] AS [Client Ref],
vwPatentMastersReport.ClassificationNumber, vwPatentMastersReport.GrantDate,
vwPatentMastersReport.StatusDescription
FROM vwPatentMastersReport LEFT JOIN vwPatentInventorsReport ON
vwPatentMastersReport.PatentMasterID = vwPatentInventorsReport.PatentMasterID;



qryMASTERSVH3

SELECT qryMASTERSVH2.*, vwPatentData1Report.Date,
vwPatentData1Report.[Text#1] AS Reel, vwPatentData1Report.[Text#2] AS Frame
FROM qryMASTERSVH2 LEFT JOIN vwPatentData1Report ON
qryMASTERSVH2.PatentMasterID = vwPatentData1Report.PatentMasterID;
 
There will be a record return for each matching record in
vwPatentData1Report. Run the following:

SELECT PatentMasterID, Count(PatentMasterID)
FROM vwPatentMastersReport
ORDER BY 1 ;

You second query will return at least the number of records above if the
PatentMasterID matches qryMASTERSVH2.

Jerry Whittle
 
--
S
Where and how do I run this code?

Jerry Whittle said:
There will be a record return for each matching record in
vwPatentData1Report. Run the following:

SELECT PatentMasterID, Count(PatentMasterID)
FROM vwPatentMastersReport
ORDER BY 1 ;

You second query will return at least the number of records above if the
PatentMasterID matches qryMASTERSVH2.

Jerry Whittle

Sharon said:
I have a query that returns my patent records. I used that query,
qryMASTERSVH2, and created a new query using another table, Assignment
Information, qryMASTERSVH3. The primary key for both tables is PatentID. The
join is all records from qryMASTERSVH2 and those from Assignment Information
where the fields are equal. The problem is that the first query shows the
appropriate number of patents, but the second query shows the same patent 10
times. There don't appear to be any duplicate fields, so I am not sure why
there are so many duplicate records.


qryMASTERSVH2

SELECT vwPatentMastersReport.DocketNumber,
vwPatentMastersReport.ApplicationNumber,
vwPatentMastersReport.ApplicationDate,
vwPatentMastersReport.ConfirmationNumber, vwPatentMastersReport.AgentName,
vwPatentMastersReport.AgentRefNo, vwPatentMastersReport.AssociateName,
vwPatentMastersReport.CountryName, vwPatentMastersReport.FilingNumber,
vwPatentMastersReport.NextDueDate, vwPatentMastersReport.PatentNumber,
vwPatentMastersReport.PublicationDate,
vwPatentMastersReport.PublicationNumber, vwPatentMastersReport.Title,
vwPatentInventorsReport.InventorName, vwPatentInventorsReport.PatentMasterID,
vwPatentMastersReport.[Text#10] AS [Client Ref],
vwPatentMastersReport.ClassificationNumber, vwPatentMastersReport.GrantDate,
vwPatentMastersReport.StatusDescription
FROM vwPatentMastersReport LEFT JOIN vwPatentInventorsReport ON
vwPatentMastersReport.PatentMasterID = vwPatentInventorsReport.PatentMasterID;



qryMASTERSVH3

SELECT qryMASTERSVH2.*, vwPatentData1Report.Date,
vwPatentData1Report.[Text#1] AS Reel, vwPatentData1Report.[Text#2] AS Frame
FROM qryMASTERSVH2 LEFT JOIN vwPatentData1Report ON
qryMASTERSVH2.PatentMasterID = vwPatentData1Report.PatentMasterID;
Click to expand...
Click to expand...
 
Hi,

Open up a new query. Don't bother to select any tables. Next go to View, SQL
and paste it in there.

Let me know what happens.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Sharon said:
--
S
Where and how do I run this code?

Jerry Whittle said:
There will be a record return for each matching record in
vwPatentData1Report. Run the following:

SELECT PatentMasterID, Count(PatentMasterID)
FROM vwPatentMastersReport
ORDER BY 1 ;

You second query will return at least the number of records above if the
PatentMasterID matches qryMASTERSVH2.

Jerry Whittle

Sharon said:
I have a query that returns my patent records. I used that query,
qryMASTERSVH2, and created a new query using another table, Assignment
Information, qryMASTERSVH3. The primary key for both tables is PatentID. The
join is all records from qryMASTERSVH2 and those from Assignment Information
where the fields are equal. The problem is that the first query shows the
appropriate number of patents, but the second query shows the same patent 10
times. There don't appear to be any duplicate fields, so I am not sure why
there are so many duplicate records.


qryMASTERSVH2

SELECT vwPatentMastersReport.DocketNumber,
vwPatentMastersReport.ApplicationNumber,
vwPatentMastersReport.ApplicationDate,
vwPatentMastersReport.ConfirmationNumber, vwPatentMastersReport.AgentName,
vwPatentMastersReport.AgentRefNo, vwPatentMastersReport.AssociateName,
vwPatentMastersReport.CountryName, vwPatentMastersReport.FilingNumber,
vwPatentMastersReport.NextDueDate, vwPatentMastersReport.PatentNumber,
vwPatentMastersReport.PublicationDate,
vwPatentMastersReport.PublicationNumber, vwPatentMastersReport.Title,
vwPatentInventorsReport.InventorName, vwPatentInventorsReport.PatentMasterID,
vwPatentMastersReport.[Text#10] AS [Client Ref],
vwPatentMastersReport.ClassificationNumber, vwPatentMastersReport.GrantDate,
vwPatentMastersReport.StatusDescription
FROM vwPatentMastersReport LEFT JOIN vwPatentInventorsReport ON
vwPatentMastersReport.PatentMasterID = vwPatentInventorsReport.PatentMasterID;



qryMASTERSVH3

SELECT qryMASTERSVH2.*, vwPatentData1Report.Date,
vwPatentData1Report.[Text#1] AS Reel, vwPatentData1Report.[Text#2] AS Frame
FROM qryMASTERSVH2 LEFT JOIN vwPatentData1Report ON
qryMASTERSVH2.PatentMasterID = vwPatentData1Report.PatentMasterID;
Click to expand...
Click to expand...
Click to expand...
 
Jerry,

I get the error message "You tried to execute a query that does not include
the specified expression 'PatentMasterID' as part of an aggregate function."
--
S


Jerry Whittle said:
Hi,

Open up a new query. Don't bother to select any tables. Next go to View, SQL
and paste it in there.

Let me know what happens.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Sharon said:
--
S
Where and how do I run this code?

Jerry Whittle said:
There will be a record return for each matching record in
vwPatentData1Report. Run the following:

SELECT PatentMasterID, Count(PatentMasterID)
FROM vwPatentMastersReport
ORDER BY 1 ;

You second query will return at least the number of records above if the
PatentMasterID matches qryMASTERSVH2.

Jerry Whittle

:

I have a query that returns my patent records. I used that query,
qryMASTERSVH2, and created a new query using another table, Assignment
Information, qryMASTERSVH3. The primary key for both tables is PatentID. The
join is all records from qryMASTERSVH2 and those from Assignment Information
where the fields are equal. The problem is that the first query shows the
appropriate number of patents, but the second query shows the same patent 10
times. There don't appear to be any duplicate fields, so I am not sure why
there are so many duplicate records.


qryMASTERSVH2

SELECT vwPatentMastersReport.DocketNumber,
vwPatentMastersReport.ApplicationNumber,
vwPatentMastersReport.ApplicationDate,
vwPatentMastersReport.ConfirmationNumber, vwPatentMastersReport.AgentName,
vwPatentMastersReport.AgentRefNo, vwPatentMastersReport.AssociateName,
vwPatentMastersReport.CountryName, vwPatentMastersReport.FilingNumber,
vwPatentMastersReport.NextDueDate, vwPatentMastersReport.PatentNumber,
vwPatentMastersReport.PublicationDate,
vwPatentMastersReport.PublicationNumber, vwPatentMastersReport.Title,
vwPatentInventorsReport.InventorName, vwPatentInventorsReport.PatentMasterID,
vwPatentMastersReport.[Text#10] AS [Client Ref],
vwPatentMastersReport.ClassificationNumber, vwPatentMastersReport.GrantDate,
vwPatentMastersReport.StatusDescription
FROM vwPatentMastersReport LEFT JOIN vwPatentInventorsReport ON
vwPatentMastersReport.PatentMasterID = vwPatentInventorsReport.PatentMasterID;



qryMASTERSVH3

SELECT qryMASTERSVH2.*, vwPatentData1Report.Date,
vwPatentData1Report.[Text#1] AS Reel, vwPatentData1Report.[Text#2] AS Frame
FROM qryMASTERSVH2 LEFT JOIN vwPatentData1Report ON
qryMASTERSVH2.PatentMasterID = vwPatentData1Report.PatentMasterID;
Click to expand...
Click to expand...
Click to expand...
 
I messed up. Sorry. The query below 'should' work.

SELECT PatentMasterID, Count(PatentMasterID)
FROM vwPatentMastersReport
GROUP BY PatentMasterID
ORDER BY 1 ;

--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder


Sharon said:
Jerry,

I get the error message "You tried to execute a query that does not include
the specified expression 'PatentMasterID' as part of an aggregate function."
--
S


Jerry Whittle said:
Hi,

Open up a new query. Don't bother to select any tables. Next go to View, SQL
and paste it in there.

Let me know what happens.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Sharon said:
--
S
Where and how do I run this code?

:

There will be a record return for each matching record in
vwPatentData1Report. Run the following:

SELECT PatentMasterID, Count(PatentMasterID)
FROM vwPatentMastersReport
ORDER BY 1 ;

You second query will return at least the number of records above if the
PatentMasterID matches qryMASTERSVH2.

Jerry Whittle

:

I have a query that returns my patent records. I used that query,
qryMASTERSVH2, and created a new query using another table, Assignment
Information, qryMASTERSVH3. The primary key for both tables is PatentID. The
join is all records from qryMASTERSVH2 and those from Assignment Information
where the fields are equal. The problem is that the first query shows the
appropriate number of patents, but the second query shows the same patent 10
times. There don't appear to be any duplicate fields, so I am not sure why
there are so many duplicate records.


qryMASTERSVH2

SELECT vwPatentMastersReport.DocketNumber,
vwPatentMastersReport.ApplicationNumber,
vwPatentMastersReport.ApplicationDate,
vwPatentMastersReport.ConfirmationNumber, vwPatentMastersReport.AgentName,
vwPatentMastersReport.AgentRefNo, vwPatentMastersReport.AssociateName,
vwPatentMastersReport.CountryName, vwPatentMastersReport.FilingNumber,
vwPatentMastersReport.NextDueDate, vwPatentMastersReport.PatentNumber,
vwPatentMastersReport.PublicationDate,
vwPatentMastersReport.PublicationNumber, vwPatentMastersReport.Title,
vwPatentInventorsReport.InventorName, vwPatentInventorsReport.PatentMasterID,
vwPatentMastersReport.[Text#10] AS [Client Ref],
vwPatentMastersReport.ClassificationNumber, vwPatentMastersReport.GrantDate,
vwPatentMastersReport.StatusDescription
FROM vwPatentMastersReport LEFT JOIN vwPatentInventorsReport ON
vwPatentMastersReport.PatentMasterID = vwPatentInventorsReport.PatentMasterID;



qryMASTERSVH3

SELECT qryMASTERSVH2.*, vwPatentData1Report.Date,
vwPatentData1Report.[Text#1] AS Reel, vwPatentData1Report.[Text#2] AS Frame
FROM qryMASTERSVH2 LEFT JOIN vwPatentData1Report ON
qryMASTERSVH2.PatentMasterID = vwPatentData1Report.PatentMasterID;
Click to expand...
Click to expand...
Click to expand...
 
Jerry,

There are approximately 885 records returned in the qryMASTERSVH2 and
approximately 1164 in qryMASTERSVH3. However, when I run this query, there
are 401 records and the Expr1001 field for every record is the number 1. I
am not sure that you understand that I am not interested in the actual count,
but WHY it is creating so many duplicate records when i have not duplicated
any fields in the query.

S


Jerry Whittle said:
I messed up. Sorry. The query below 'should' work.

SELECT PatentMasterID, Count(PatentMasterID)
FROM vwPatentMastersReport
GROUP BY PatentMasterID
ORDER BY 1 ;

--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder


Sharon said:
Jerry,

I get the error message "You tried to execute a query that does not include
the specified expression 'PatentMasterID' as part of an aggregate function."
--
S


Jerry Whittle said:
Hi,

Open up a new query. Don't bother to select any tables. Next go to View, SQL
and paste it in there.

Let me know what happens.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

:


--
S
Where and how do I run this code?

:

There will be a record return for each matching record in
vwPatentData1Report. Run the following:

SELECT PatentMasterID, Count(PatentMasterID)
FROM vwPatentMastersReport
ORDER BY 1 ;

You second query will return at least the number of records above if the
PatentMasterID matches qryMASTERSVH2.

Jerry Whittle

:

I have a query that returns my patent records. I used that query,
qryMASTERSVH2, and created a new query using another table, Assignment
Information, qryMASTERSVH3. The primary key for both tables is PatentID. The
join is all records from qryMASTERSVH2 and those from Assignment Information
where the fields are equal. The problem is that the first query shows the
appropriate number of patents, but the second query shows the same patent 10
times. There don't appear to be any duplicate fields, so I am not sure why
there are so many duplicate records.


qryMASTERSVH2

SELECT vwPatentMastersReport.DocketNumber,
vwPatentMastersReport.ApplicationNumber,
vwPatentMastersReport.ApplicationDate,
vwPatentMastersReport.ConfirmationNumber, vwPatentMastersReport.AgentName,
vwPatentMastersReport.AgentRefNo, vwPatentMastersReport.AssociateName,
vwPatentMastersReport.CountryName, vwPatentMastersReport.FilingNumber,
vwPatentMastersReport.NextDueDate, vwPatentMastersReport.PatentNumber,
vwPatentMastersReport.PublicationDate,
vwPatentMastersReport.PublicationNumber, vwPatentMastersReport.Title,
vwPatentInventorsReport.InventorName, vwPatentInventorsReport.PatentMasterID,
vwPatentMastersReport.[Text#10] AS [Client Ref],
vwPatentMastersReport.ClassificationNumber, vwPatentMastersReport.GrantDate,
vwPatentMastersReport.StatusDescription
FROM vwPatentMastersReport LEFT JOIN vwPatentInventorsReport ON
vwPatentMastersReport.PatentMasterID = vwPatentInventorsReport.PatentMasterID;



qryMASTERSVH3

SELECT qryMASTERSVH2.*, vwPatentData1Report.Date,
vwPatentData1Report.[Text#1] AS Reel, vwPatentData1Report.[Text#2] AS Frame
FROM qryMASTERSVH2 LEFT JOIN vwPatentData1Report ON
qryMASTERSVH2.PatentMasterID = vwPatentData1Report.PatentMasterID;
Click to expand...
Click to expand...
Click to expand...
 
Hi,

If any of the records showed more than 1 in the Expr1001 field, then the
problem would be easy to explain. Since they are just 1, it isn't simple. :-(

If you want, you could zip up the database and email it to me at the
following email address so that I could look at it.

(e-mail address removed)
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder



Sharon said:
Jerry,

There are approximately 885 records returned in the qryMASTERSVH2 and
approximately 1164 in qryMASTERSVH3. However, when I run this query, there
are 401 records and the Expr1001 field for every record is the number 1. I
am not sure that you understand that I am not interested in the actual count,
but WHY it is creating so many duplicate records when i have not duplicated
any fields in the query.

S


Jerry Whittle said:
I messed up. Sorry. The query below 'should' work.

SELECT PatentMasterID, Count(PatentMasterID)
FROM vwPatentMastersReport
GROUP BY PatentMasterID
ORDER BY 1 ;

--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder


Sharon said:
Jerry,

I get the error message "You tried to execute a query that does not include
the specified expression 'PatentMasterID' as part of an aggregate function."
--
S


:

Hi,

Open up a new query. Don't bother to select any tables. Next go to View, SQL
and paste it in there.

Let me know what happens.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

:


--
S
Where and how do I run this code?

:

There will be a record return for each matching record in
vwPatentData1Report. Run the following:

SELECT PatentMasterID, Count(PatentMasterID)
FROM vwPatentMastersReport
ORDER BY 1 ;

You second query will return at least the number of records above if the
PatentMasterID matches qryMASTERSVH2.

Jerry Whittle

:

I have a query that returns my patent records. I used that query,
qryMASTERSVH2, and created a new query using another table, Assignment
Information, qryMASTERSVH3. The primary key for both tables is PatentID. The
join is all records from qryMASTERSVH2 and those from Assignment Information
where the fields are equal. The problem is that the first query shows the
appropriate number of patents, but the second query shows the same patent 10
times. There don't appear to be any duplicate fields, so I am not sure why
there are so many duplicate records.


qryMASTERSVH2

SELECT vwPatentMastersReport.DocketNumber,
vwPatentMastersReport.ApplicationNumber,
vwPatentMastersReport.ApplicationDate,
vwPatentMastersReport.ConfirmationNumber, vwPatentMastersReport.AgentName,
vwPatentMastersReport.AgentRefNo, vwPatentMastersReport.AssociateName,
vwPatentMastersReport.CountryName, vwPatentMastersReport.FilingNumber,
vwPatentMastersReport.NextDueDate, vwPatentMastersReport.PatentNumber,
vwPatentMastersReport.PublicationDate,
vwPatentMastersReport.PublicationNumber, vwPatentMastersReport.Title,
vwPatentInventorsReport.InventorName, vwPatentInventorsReport.PatentMasterID,
vwPatentMastersReport.[Text#10] AS [Client Ref],
vwPatentMastersReport.ClassificationNumber, vwPatentMastersReport.GrantDate,
vwPatentMastersReport.StatusDescription
FROM vwPatentMastersReport LEFT JOIN vwPatentInventorsReport ON
vwPatentMastersReport.PatentMasterID = vwPatentInventorsReport.PatentMasterID;



qryMASTERSVH3

SELECT qryMASTERSVH2.*, vwPatentData1Report.Date,
vwPatentData1Report.[Text#1] AS Reel, vwPatentData1Report.[Text#2] AS Frame
FROM qryMASTERSVH2 LEFT JOIN vwPatentData1Report ON
qryMASTERSVH2.PatentMasterID = vwPatentData1Report.PatentMasterID;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top