Query not returning all records from a linked Excel table

[SpartaGal]

I've created a query that uses a linked table from Excel.
The join is on a Number format double fixed field.
Examples of the values are 1.00, 2.00, 3.00, 3.01,etc.
The query is:

SELECT [TableA].[Req_Id], [TableB].Test_Case,
[TableB].Reference FROM [TableA] INNER JOIN [TableB] ON
[TableA].[Req_Id] = [TableB].Reference;

TableA is the linked table.

For some reason when running the query there are three
records missing (out of 300) and the Req_Id values that
are missing are 3.01, 251.01 and 409.03. Most values for
Req_Id are like 1.00, 2.00, 3.00,...,400.00. The strange
thing is when I open up the Excel spreadsheet (TableA) the
query in Access works fine. When I close TableA, the
query misses the 3 above records.

Please help. Thanks.

 

[Guest]

I would be very reluctant to use Double or Single as the
linking Fields since Double / Single are not *exact*
representations of the values (whole numbers are).

What you see as exact match 3.01 may be stored as
3.009999999 in [Reg_ID] and 3.010000000012 in [Reference];
they don't match and therefore not selected by your
Query. This seems to apply in your case since all the
matched samples are whole numbers and unmatched samples
are fractional numbers.

Use whole numbers (AutoNumber/Long, Integer) if you can.
Otherwise, try Decimal data type (only available in later
Access versions???).

HTH
Van T. Dinh
MVP (Access)