RE: Data mismatches between tables not being identified

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi Jerry,

The 2 tables are NewDataMaster and Initial Tracker 2007-09-26.

The tables are linked by OriBus in NewDataMaster and BusinessUnit in Initial
and also by EmpNum in NewDataMaster and EmpNo in Initial.

Here is the SQL

SELECT NewDataMaster.OriBus, [Initial Tracker 2007-09-26].BusinessUnit,
NewDataMaster.EmpNum, [Initial Tracker 2007-09-26].EmpNo,
NewDataMaster.EmpName, [Initial Tracker 2007-09-26].InitialName,
NewDataMaster.PnEmpStart, [Initial Tracker 2007-09-26].StartDate
FROM NewDataMaster INNER JOIN [Initial Tracker 2007-09-26] ON
(NewDataMaster.OriBus=[Initial Tracker 2007-09-26].BusinessUnit) AND
(NewDataMaster.EmpName=[Initial Tracker 2007-09-26].InitialName)
WHERE (((NewDataMaster.OriBus)="Initial") AND
((NewDataMaster.PnEmpStart)<>[StartDate]));

The problem is that the query throws out too little data - only a handful of
results come out, when there should be about 50 lines.

I thought that the problem might have something to do with the Employee
Number fields - these were mostly numeric fields in the spread***, but were
converted to text in both tables to allow for a few cases that were
alphanumeric. If Access was reading these fields differently, even though
they are the same, they would not appear in the query results. Just a
thought.

Regards
Nuno

"Jerry Whittle" wrote:

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.

Also check out you date fields. If they are truely Date/Time data types,
it's possible that there's a time component being hidden by formatting.
10/10/2007 <> 10/10/2007 11:00:00 AM
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Nuno Bento" wrote:

I have two tables of employee records that I imported in from Excel with
about 1,500 records in each. I wrote a query to extract those records where
the start date did not agree between then tables and the query only returned
2 lines of data, when I know that there must be more than 50 cases where the
dates differ.

Do anyone have any idea why the query produced so few results?

Thanks
Nuno
.

Quantcast