CODEWITHSUNDEEP
excelms-accessrowsmissing-data
Wilskt
Wilskt

Reputation: 347

Rows missing from linked Access query

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];

Upvotes: 0

Views: 2335

Answers (1)

HansUp
HansUp

Reputation: 97131

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%"

Upvotes: 1

Related Questions