Acess compare two columns

[Guest]

I have two columns (formatted as text because of alphanumeric data). I want
to find how many rows match between the two columns. I know there are quite a
few that match. So i query in access using the find matched and it comes up
empty. Can someone help?

 

[Guest]

Show us the SQL of the query that doesn't work. Open the query in design
view. Next go to View, SQL View and copy and past it here. Also provide the
names of the columns that you are attempting to compare.

Also is is possible that there are extra spaces either in the beginning,
middle, or end of the data in one of the fields?

 

[John W. Vinson]

I have two columns (formatted as text because of alphanumeric data). I want
to find how many rows match between the two columns. I know there are quite a
few that match. So i query in access using the find matched and it comes up
empty. Can someone help?

What kind of "match" do you mean? Are these two columns (fields is actually
the preferable term) in the same table? By "match" do you mean you want to
find records where the fields have the same value within an individual record,
or do you want to find cases where FieldA in one record matches FieldB in a
different record?

Details please!

John W. Vinson [MVP]

 

[Guest]

Thanks Jerry,
Here is the SQL for "Find Duplicates" query found in the Wizard:
SELECT First([Nov 2006].[Aug 2007]) AS [Aug 2007 Field], First([Nov
2006].[Nov 2006]) AS [Nov 2006 Field], Count([Nov 2006].[Aug 2007]) AS
NumberOfDups
FROM [Nov 2006]
GROUP BY [Nov 2006].[Aug 2007], [Nov 2006].[Nov 2006]
HAVING (((Count([Nov 2006].[Aug 2007]))>1) AND ((Count([Nov 2006].[Nov
2006]))>1));

 

[Guest]

Very strange.I just responded to jerry but my post was wiped out. Here is my
response.
Basically I want to find cases where FieldA in one record matches FieldB in a
different record.
Here is the SQL from the find duplicates sequel wizard:
SELECT First([Nov 2006].[Aug 2007]) AS [Aug 2007 Field], First([Nov
2006].[Nov 2006]) AS [Nov 2006 Field], Count([Nov 2006].[Aug 2007]) AS
NumberOfDups
FROM [Nov 2006]
GROUP BY [Nov 2006].[Aug 2007], [Nov 2006].[Nov 2006]
HAVING (((Count([Nov 2006].[Aug 2007]))>1) AND ((Count([Nov 2006].[Nov
2006]))>1));

Sorry for asking newbie questions but I am really stuck.
Thanks

 

[John W. Vinson]

Basically I want to find cases where FieldA in one record matches FieldB in a
different record.

Well, it sounds like you need a "Self Join" query. Try

SELECT First([Nov 2006].[Aug 2007]) AS [Aug 2007 Field], First([Nov
2006].[Nov 2006]) AS [Nov 2006 Field], Count([Nov 2006].[Aug 2007]) AS
NumberOfDups
FROM [Nov 2006]
GROUP BY [Nov 2006].[Aug 2007], [Nov 2006].[Nov 2006]
HAVING (((Count([Nov 2006].[Aug 2007]))>1) AND ((Count([Nov 2006].[Nov
2006]))>1));

SELECT [Nov 2006].*, [Aug 2007].*
FROM [Nov 2006] INNER JOIN [Aug 2007]
ON [Nov 2006].[Nov 2006 Field] = [Aug 2007].[Aug 2007 Field];

Note that having data - dates - in tablenames and fieldnames is "committing
spreadsheet" and is not a good design for a relational database!! Is [Nov
2006] a Table, or a Query selecting only those records from some other table?
What are the other fields in [Nov 2006]?

John W. Vinson [MVP]

 

[Pieter Wijnen]

First of all you have a bad table design in my opinion as it is
denormalized.
you should have one row per month instead off one field per month (excel
based?)
that aside, the Find Dups wizard is created to find duplicates in *one* (or
a set of ) field(s)
you can however use it on your data if you run it on a union Query
ie

SELECT [Now 2006] FROM [Nov 2006]
UNION ALL
SELECT [Aug 2007] FROM [Nov 2006]

HTH

Pieter

Thanks Jerry,
Here is the SQL for "Find Duplicates" query found in the Wizard:
SELECT First([Nov 2006].[Aug 2007]) AS [Aug 2007 Field], First([Nov
2006].[Nov 2006]) AS [Nov 2006 Field], Count([Nov 2006].[Aug 2007]) AS
NumberOfDups
FROM [Nov 2006]
GROUP BY [Nov 2006].[Aug 2007], [Nov 2006].[Nov 2006]
HAVING (((Count([Nov 2006].[Aug 2007]))>1) AND ((Count([Nov 2006].[Nov
2006]))>1));

---------
Nov 2006 is 3918 rows
Aug 2007 is 3269 rows and the rest are empty (upto row #3918)

Show us the SQL of the query that doesn't work. Open the query in design
view. Next go to View, SQL View and copy and past it here. Also provide
the
names of the columns that you are attempting to compare.

Also is is possible that there are extra spaces either in the beginning,
middle, or end of the data in one of the fields?