Data mismatches between tables not being identified

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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 spreadsheet, 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
 
Problem solved. I changed the criteria from <>[StartDate] to Not
Like[StartDate].

Seemed to solve the problem. Thanks for your time on this Jerry.

Regards
Nuno

Nuno Bento said:
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 spreadsheet, 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 said:
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
Click to expand...
Click to expand...
 
Back
Top