Help with a Query

[Guest]

I currently have the following Query:

SELECT Year([ClosingDate]) AS [Year], Month([ClosingDate]) AS [Month],
[Source] AS Broker, Count(File_Tracking.FileNumber) AS Files
FROM File_Tracking
WHERE ([ClosingDate]) Is Not Null
GROUP BY Year([ClosingDate]), Month([ClosingDate]), [Source];

The problem that I am having is that some of the companies in my database
(in the source field) have been entered different ways, for example:
Abc Company
Abc Company, Inc.
Therefore when I run my report I get seperate report entries when they
should show up as the same company. I have tried using the like command but
have been unsuccessful so far. Does anyone have suggestions on my problem?
Additionally, I am trying to make One column in my report showing "January,
2005" as the date grouped by, is there an easy way to do this? Any help
would be greatly appreciated.

Thanks

 

[[MVP] S.Clark]

I currently have the following Query:

SELECT Year([ClosingDate]) AS [Year], Month([ClosingDate]) AS [Month],
[Source] AS Broker, Count(File_Tracking.FileNumber) AS Files
FROM File_Tracking
WHERE ([ClosingDate]) Is Not Null
GROUP BY Year([ClosingDate]), Month([ClosingDate]), [Source];

The problem that I am having is that some of the companies in my database
(in the source field) have been entered different ways, for example:
Abc Company
Abc Company, Inc.
Therefore when I run my report I get seperate report entries when they
should show up as the same company. I have tried using the like command
but
have been unsuccessful so far. Does anyone have suggestions on my
problem?
Additionally, I am trying to make One column in my report showing
"January,
2005" as the date grouped by, is there an easy way to do this? Any help
would be greatly appreciated.

Thanks

 

[[MVP] S.Clark]

You could:
1. Create a Source table, and enter all of the Source names in a
standardized method. Use a SourceID as the primary key.
2. To the File_Tracking data, I would add the Source ID, which links to the
SourceID in the Source table.
3. Alter your query to incorporate this structure change.