Bring together 2 queries

[Guest]

I have a query 'Combo' with fields [Account Name],[Policy Number],[Remarks],[Date Remarks Entered],[Auto Number], and[Assigned Consultant] This query is based on table called 'Remarks'

I would like to bring above query together with another query "Service Calls QA" with fields [Account Name],[Policy Number],[Assigned Consultant],[Date of Survey],[Date Written Report Sent],[Comments] and [Location Servicing Division] which is based on table called 'Service Calls'.

Policy Number is the child field linking the 2 tables.

How would I bring together these 2 queries so I could eventually use the results in a report to show [Comments] and [Remarks] sorted by a date. Comments are kept by the [Date Written Report Sent] and Remarks are dated by [Date Remarks Entered.

Basically need the records from both to be in one query don't I.

 

[Michel Walsh]

Hi,


Bring the two tables in the query designer. Drag and drop the PolicyNumber
of one table over the same field, of the other table. Drag the desired
fields, from the desired tables, in the column grid. Use that query as
source for your report. That's should be all you have to do.



Hoping it may help,
Vanderghast, Access MVP

I have a query 'Combo' with fields [Account Name],[Policy

Number],[Remarks],[Date Remarks Entered],[Auto Number], and[Assigned
Consultant] This query is based on table called 'Remarks'

I would like to bring above query together with another query "Service

Calls QA" with fields [Account Name],[Policy Number],[Assigned
Consultant],[Date of Survey],[Date Written Report Sent],[Comments] and
[Location Servicing Division] which is based on table called 'Service
Calls'.

Policy Number is the child field linking the 2 tables.

How would I bring together these 2 queries so I could eventually use the

results in a report to show [Comments] and [Remarks] sorted by a date.
Comments are kept by the [Date Written Report Sent] and Remarks are dated by
[Date Remarks Entered.

 

[Michel Walsh]

Hi,


I would try:

SELECT [Service Calls].[Policy Number], Remarks.[Policy Number],
Remarks.[Account Name], Remarks.Remarks, Remarks.[Date Remarks Entered],
Remarks.[Assigned Consultant], [Service Calls].[Assigned Consultant],
[Service Calls].[Policy Number], [Service Calls].[Date Written Report Sent],
[Service Calls].[Date of Survey], [Service Calls].Comments, [Service
Calls].[Location Servicing Division]
FROM Remarks INNER JOIN [Service Calls] ON Remarks.[Policy Number]
=[Service Calls].[Policy Number]



and if that still shows duplicated rows:

SELECT DISTINCT [Service Calls].[Policy Number], Remarks.[Policy Number],
Remarks.[Account Name], Remarks.Remarks, Remarks.[Date Remarks Entered],
Remarks.[Assigned Consultant], [Service Calls].[Assigned Consultant],
[Service Calls].[Policy Number], [Service Calls].[Date Written Report Sent],
[Service Calls].[Date of Survey], [Service Calls].Comments, [Service
Calls].[Location Servicing Division]
FROM Remarks INNER JOIN [Service Calls] ON Remarks.[Policy Number]
=[Service Calls].[Policy Number]


Note that with the inner join you can only have duplicated rows if Policy
Number is itself duplicated in one of the table... You can try to eliminate
the duplication right at that source.



Hoping it may help,
Vanderghast, Access MVP

I did take out the join property and got all to show but now I end up with

a duplicate issue again and the entries show up multiple times based on my
table and forms relationships.

What is the best way to exclude the duplicates in this case?

Here the current SQL

SELECT DISTINCTROW [Service Calls].[Policy Number], Remarks.[Policy

Number], Remarks.[Account Name], Remarks.Remarks, Remarks.[Date Remarks
Entered], Remarks.[Assigned Consultant], [Service Calls].[Assigned
Consultant], [Service Calls].[Policy Number], [Service Calls].[Date Written
Report Sent], [Service Calls].[Date of Survey], [Service Calls].Comments,
[Service Calls].[Location Servicing Division]

FROM Remarks, [Service Calls];
--
Brian

Hi,


Bring the two tables in the query designer. Drag and drop the PolicyNumber
of one table over the same field, of the other table. Drag the desired
fields, from the desired tables, in the column grid. Use that query as
source for your report. That's should be all you have to do.



Hoping it may help,
Vanderghast, Access MVP

I have a query 'Combo' with fields [Account Name],[Policy

Number],[Remarks],[Date Remarks Entered],[Auto Number], and[Assigned
Consultant] This query is based on table called 'Remarks'

I would like to bring above query together with another query "Service

Calls QA" with fields [Account Name],[Policy Number],[Assigned
Consultant],[Date of Survey],[Date Written Report Sent],[Comments] and
[Location Servicing Division] which is based on table called 'Service
Calls'.

Policy Number is the child field linking the 2 tables.

How would I bring together these 2 queries so I could eventually use

the
results in a report to show [Comments] and [Remarks] sorted by a date.
Comments are kept by the [Date Written Report Sent] and Remarks are dated by
[Date Remarks Entered.

Basically need the records from both to be in one query don't I.

 

[Guest]

If I want to restrict rows that show up when bringing the tables together how does one do that.

I am still having trouble getting just the rows I want.

I have 3 sample Remarks in that table and I can get just the three remarks in a query. I have created 2 sample comments but since that table has a 1-many relationship and I cannot get down to just the 2 records. I am having trouble getting that table with a query to show records with just entries for the 2 records.

How best to do that with just one table in the query?

 

[Michel Walsh]

Hi,


You have a table with all the possible policy numbers? If so, bring that
table, in addition to the other two.

From the table with all the policies number, make a join "type two" against
each of the other tables. Do not make any other join between those tables.

Drag the data from the two initial tables, in the grid.


Hoping it may help,
Vanderghast, Access MVP

Michel

Tried both with this join property 1 and get no records.

First SQL with Property 2 I get Remarks data only and Property 3 I get Service Calls Info only.

Second SQL with Property 2 I get Remarks only data but with Property 3 I

get the closet but can not see any Remark data but number of records appears
close to what we should see.

--
Brian

Hi,


I would try:

SELECT [Service Calls].[Policy Number], Remarks.[Policy Number],
Remarks.[Account Name], Remarks.Remarks, Remarks.[Date Remarks Entered],
Remarks.[Assigned Consultant], [Service Calls].[Assigned Consultant],
[Service Calls].[Policy Number], [Service Calls].[Date Written Report Sent],
[Service Calls].[Date of Survey], [Service Calls].Comments, [Service
Calls].[Location Servicing Division]
FROM Remarks INNER JOIN [Service Calls] ON Remarks.[Policy Number]
=[Service Calls].[Policy Number]



and if that still shows duplicated rows:

SELECT DISTINCT [Service Calls].[Policy Number], Remarks.[Policy Number],
Remarks.[Account Name], Remarks.Remarks, Remarks.[Date Remarks Entered],
Remarks.[Assigned Consultant], [Service Calls].[Assigned Consultant],
[Service Calls].[Policy Number], [Service Calls].[Date Written Report Sent],
[Service Calls].[Date of Survey], [Service Calls].Comments, [Service
Calls].[Location Servicing Division]
FROM Remarks INNER JOIN [Service Calls] ON Remarks.[Policy Number]
=[Service Calls].[Policy Number]


Note that with the inner join you can only have duplicated rows if Policy
Number is itself duplicated in one of the table... You can try to eliminate
the duplication right at that source.



Hoping it may help,
Vanderghast, Access MVP

I did take out the join property and got all to show but now I end up

with
a duplicate issue again and the entries show up multiple times based on my
table and forms relationships.

What is the best way to exclude the duplicates in this case?

Here the current SQL

SELECT DISTINCTROW [Service Calls].[Policy Number], Remarks.[Policy

Number], Remarks.[Account Name], Remarks.Remarks, Remarks.[Date Remarks
Entered], Remarks.[Assigned Consultant], [Service Calls].[Assigned
Consultant], [Service Calls].[Policy Number], [Service Calls].[Date Written
Report Sent], [Service Calls].[Date of Survey], [Service Calls].Comments,
[Service Calls].[Location Servicing Division]

FROM Remarks, [Service Calls];
--
Brian


:

Hi,


Bring the two tables in the query designer. Drag and drop the PolicyNumber
of one table over the same field, of the other table. Drag the desired
fields, from the desired tables, in the column grid. Use that query as
source for your report. That's should be all you have to do.



Hoping it may help,
Vanderghast, Access MVP


I have a query 'Combo' with fields [Account Name],[Policy
Number],[Remarks],[Date Remarks Entered],[Auto Number], and[Assigned
Consultant] This query is based on table called 'Remarks'

I would like to bring above query together with another query "Service
Calls QA" with fields [Account Name],[Policy Number],[Assigned
Consultant],[Date of Survey],[Date Written Report Sent],[Comments] and
[Location Servicing Division] which is based on table called 'Service
Calls'.

Policy Number is the child field linking the 2 tables.

How would I bring together these 2 queries so I could eventually

use
the

results in a report to show [Comments] and [Remarks] sorted by a date.
Comments are kept by the [Date Written Report Sent] and Remarks are dated by
[Date Remarks Entered.

Basically need the records from both to be in one query don't I.

 

[Guest]

Issue still not resolved.

See earlier posts for info.
--
Brian

If I want to restrict rows that show up when bringing the tables together how does one do that.

I am still having trouble getting just the rows I want.

I have 3 sample Remarks in that table and I can get just the three remarks in a query. I have created 2 sample comments but since that table has a 1-many relationship and I cannot get down to just the 2 records. I am having trouble getting that table with a query to show records with just entries for the 2 records.

How best to do that with just one table in the query?
--
Brian

I have a query 'Combo' with fields [Account Name],[Policy Number],[Remarks],[Date Remarks Entered],[Auto Number], and[Assigned Consultant] This query is based on table called 'Remarks'

I would like to bring above query together with another query "Service Calls QA" with fields [Account Name],[Policy Number],[Assigned Consultant],[Date of Survey],[Date Written Report Sent],[Comments] and [Location Servicing Division] which is based on table called 'Service Calls'.

Policy Number is the child field linking the 2 tables.

How would I bring together these 2 queries so I could eventually use the results in a report to show [Comments] and [Remarks] sorted by a date. Comments are kept by the [Date Written Report Sent] and Remarks are dated by [Date Remarks Entered.

Basically need the records from both to be in one query don't I.