Need help ASAP Report

[college_fellow]

What I am looking to do is create a report that shows matching


Customer # car make vin #


What I have 2 spread sheets that I have uploaded onto access. What I want
to do is match the customer # with the Care make and the vin. If I have all
three field that match put it on a match report. If the care make and the
vin # match different report. Then if if they do not find a care make and
vin # match put on another report. I am lost can someone help me?

 

[John Spencer]

Match all three query
-- Add both tables to the query
-- Set the join by
---- Dragging from Customer to Customer, Vin to Vin, and Make To Make
-- Run the query or use it as a source for a report.

Match Make and Vin, but not customer
-- Add both tables to the query
-- Set the join by
----dragging Vin to Vin and Make To Make
-- Under the Customer field for the first table add the criteria
---- <> [Table2Name].[CustomerFieldName]
-- Run the query or use it as a source for a report.

No match on Make and Vin (two queries required one for each table's unique
values)
-- Add both tables to the query
-- Set the join by
----dragging Vin to Vin and Make To Make
----double click on each join line and select All in table one and match in
table two
-- Under the Vin for the Second table add the criteria
---- Is Null
-- Run the query or use it as a source for a report.

-- Add both tables to the query
-- Set the join by
----dragging Vin to Vin and Make To Make
----double click on each join line and select All in table TWOand match in
table ONE
-- Under the Vin for the table ONE add the criteria
---- Is Null
-- Run the query or use it as a source for a report.

 

[college_fellow via AccessMonster.com]

Match all three query
-- Add both tables to the query
-- Set the join by
---- Dragging from Customer to Customer, Vin to Vin, and Make To Make
-- Run the query or use it as a source for a report.

Match Make and Vin, but not customer
-- Add both tables to the query
-- Set the join by
----dragging Vin to Vin and Make To Make
-- Under the Customer field for the first table add the criteria
---- <> [Table2Name].[CustomerFieldName]
-- Run the query or use it as a source for a report.

No match on Make and Vin (two queries required one for each table's unique
values)
-- Add both tables to the query
-- Set the join by
----dragging Vin to Vin and Make To Make
----double click on each join line and select All in table one and match in
table two
-- Under the Vin for the Second table add the criteria
---- Is Null
-- Run the query or use it as a source for a report.

-- Add both tables to the query
-- Set the join by
----dragging Vin to Vin and Make To Make
----double click on each join line and select All in table TWOand match in
table ONE
-- Under the Vin for the table ONE add the criteria
---- Is Null
-- Run the query or use it as a source for a report.

What I am looking to do is create a report that shows matching

[quoted text clipped - 8 lines]

vin # match different report. Then if if they do not find a care make and
vin # match put on another report. I am lost can someone help me?

I ahve having some problems with the last query is states to make two querys.
But how do I connect these back so I can get one report?

Thanks

 

[John Spencer]

-- Make Two reports,
or
--Make a report with two subreports
or
--Union the two queries together and make one report


I would probably do the latter. The SQL for the UNION query would look
something like the following. This type of query has to be built in the SQL
view and cannot be constructed in the design (grid) view.

SELECT "In A" as Location, TableA.Vin, TableA.Make
FROM TableA LEFT JOIN TableB
ON TableA.Vin = TableB.VIN
And TableA.Make =TableB.Make
WHERE TableB.Vin is Null
UNION ALL
SELECT "In B" as Location, TableB.Vin, TableB.Make
FROM TableB LEFT JOIN TableA
ON TableB.Vin = TableA.VIN
And TableB.Make =TableA.Make
WHERE TableA.Vin is Null

Match all three query
-- Add both tables to the query
-- Set the join by
---- Dragging from Customer to Customer, Vin to Vin, and Make To Make
-- Run the query or use it as a source for a report.

Match Make and Vin, but not customer
-- Add both tables to the query
-- Set the join by
----dragging Vin to Vin and Make To Make
-- Under the Customer field for the first table add the criteria
---- <> [Table2Name].[CustomerFieldName]
-- Run the query or use it as a source for a report.

No match on Make and Vin (two queries required one for each table's unique
values)
-- Add both tables to the query
-- Set the join by
----dragging Vin to Vin and Make To Make
----double click on each join line and select All in table one and match
in
table two
-- Under the Vin for the Second table add the criteria
---- Is Null
-- Run the query or use it as a source for a report.

-- Add both tables to the query
-- Set the join by
----dragging Vin to Vin and Make To Make
----double click on each join line and select All in table TWOand match in
table ONE
-- Under the Vin for the table ONE add the criteria
---- Is Null
-- Run the query or use it as a source for a report.

What I am looking to do is create a report that shows matching

[quoted text clipped - 8 lines]

vin # match different report. Then if if they do not find a care make
and
vin # match put on another report. I am lost can someone help me?

I ahve having some problems with the last query is states to make two
querys.
But how do I connect these back so I can get one report?

Thanks

 

[college_fellow via AccessMonster.com]

-- Make Two reports,
or
--Make a report with two subreports
or
--Union the two queries together and make one report

I would probably do the latter. The SQL for the UNION query would look
something like the following. This type of query has to be built in the SQL
view and cannot be constructed in the design (grid) view.

SELECT "In A" as Location, TableA.Vin, TableA.Make
FROM TableA LEFT JOIN TableB
ON TableA.Vin = TableB.VIN
And TableA.Make =TableB.Make
WHERE TableB.Vin is Null
UNION ALL
SELECT "In B" as Location, TableB.Vin, TableB.Make
FROM TableB LEFT JOIN TableA
ON TableB.Vin = TableA.VIN
And TableB.Make =TableA.Make
WHERE TableA.Vin is Null
[quoted text clipped - 43 lines]

Thanks

Ok now I have a new problem I ahve duplicate data in my table and I want to
filter out all the duplicates before I run the query. How do I do this?

I have 956,000 rows.


Thanks

 

[John Spencer]

Use the DISTINCT operator in the queries as
SELECT DISTINCT ...

And if this is homework then stop asking questions in this forum and apply
your brain to studying the text and attending class.

If this is not homework, then I apologize in advance for my suspicion.

-- Make Two reports,
or
--Make a report with two subreports
or
--Union the two queries together and make one report

I would probably do the latter. The SQL for the UNION query would look
something like the following. This type of query has to be built in the
SQL
view and cannot be constructed in the design (grid) view.

SELECT "In A" as Location, TableA.Vin, TableA.Make
FROM TableA LEFT JOIN TableB
ON TableA.Vin = TableB.VIN
And TableA.Make =TableB.Make
WHERE TableB.Vin is Null
UNION ALL
SELECT "In B" as Location, TableB.Vin, TableB.Make
FROM TableB LEFT JOIN TableA
ON TableB.Vin = TableA.VIN
And TableB.Make =TableA.Make
WHERE TableA.Vin is Null

Match all three query
-- Add both tables to the query

[quoted text clipped - 43 lines]

Thanks

Ok now I have a new problem I ahve duplicate data in my table and I want
to
filter out all the duplicates before I run the query. How do I do this?

I have 956,000 rows.


Thanks