Rows missing from linked Access query

Question

Using Excel 2007, I linked to an Access query using the Data>From Access button, and set it to display as a table. All of the rows are present except any with the Type of 'Placement fee'.

E.g. in the example below, the Free Case Fill deductions show up in the Excel sheet, but the Placement fee deduction doesn't. This query exports fine from Access as Excel format, so it seems to be Excel that is ignoring these rows when linking to it.

Any ideas what can cause rows to be ignored when linking to an Access query?

Chain       Account     Distributor Warehouse  StoreID USDate    Type of Deduction  TotalValue 
Bob's Shops Bob's Shops SMITHS      Romeoville KH00463 5/1/2012  Free Case Fill     29.8 
Bob's Shops Bob's Shops SMITHS      Romeoville KH00463 5/1/2012  Placement fee      2.98 
Bob's Shops Bob's Shops JONES       Greenwood  UN20521 6/1/2011  Free Case Fill     38.81

-edit-

The SQL is below - there are about four or five stacked queries until it gets down to table level.

The placement fees are calculated separately and then added into the rest of the deductions straight from the deductions table, so the only thing I can think of is that they are formatted slightly differently and Excel is ignoring them maybe because the values aren't the same numeric type as the other deductions? But when I export the query directly from Access, everything looks like the same type (i.e. all the values are right-aligned as they are treated as numbers).

SELECT Chain, Account, Distributor, Warehouse, StoreID, USDate, [Type of Deduction], SUM([Total Value($)]) AS TotalValue
FROM (SELECT Chain, Account, Distributor, Warehouse, StoreID, USDate, [Type of Deduction], [Total Value($)]
FROM DeductionsStoresGroupedByMonth
UNION ALL SELECT [Chain/Account/Warehouse], [Chain/Account/Warehouse2], Distributor, [Chain/Account/Warehouse3], StoreID, USDate, [Type of Deduction], [Amount($)]
FROM DeductionsByChainNoStoreID)  AS [%$##@_Alias]
GROUP BY Chain, Account, Distributor, Warehouse, StoreID, USDate, [Type of Deduction];

-edit 2-

This query (and three others like it) just shows two empty rows when linked as a table from Excel - it is just one column of string, and one column of integers, so no idea what the problem is with this one:

SELECT Deductions.[Distributor's Reference], Count(Deductions.StoreID) AS NumFreeCaseFills
FROM Deductions
WHERE (((Deductions.[Type of Deduction]) Like "*free case fill*") AND ((Deductions.Details) Not Like "*placement fee*"))
GROUP BY Deductions.[Distributor's Reference];

Added above, doesn't get down to table level though so not sure it helps. — Wilskt, Jan 07 '13 at 12:26
"Excel is ignoring them maybe because the values aren't the same numeric type " -- seems possible. Is Excel ignoring everything in the UNION? — Fionnuala, Jan 07 '13 at 12:30
No, the ignored results are from the DeductionsStoresGroupedByMonth query, and the results that do appear come from both sides of the Union query. Will try importing the sub-level queries to see at what point these rows disappear... — Wilskt, Jan 07 '13 at 12:42

score 1 · Accepted Answer · edited May 23 '17 at 12:18

1

Try ANSI-92 Query mode wild card characters for the Like patterns.

WHERE
        Deductions.[Type of Deduction] Like "%free case fill%"
    AND Deductions.Details Not Like "%placement fee%"

edited May 23 '17 at 12:18

Community

1
1

answered Jan 07 '13 at 14:59

HansUp

95,961
11
77
135

Yep, that's pretty much it - although Access might need to be set to recognise the percentage sign ([switch to ANSI-92](http://answers.microsoft.com/en-us/office/forum/office_2007-access/will-excel-read-access-query-aliasesexpressions/4227c314-1c46-481a-9ed3-8fc1cd8a6b42)). I didn't really want to make that change, so just removed the wildcards as they're not strictly necessary in this case. – Wilskt Jan 07 '13 at 15:19

Rows missing from linked Access query

1 Answers1