[Microsoft] [ODBC Text Driver] Too few parameters. Expected:1

I had a complex query that returned the above error intermittently. It turned out that one of the fields was DATE (upper case) as specified in the schema.ini file for the text file. The query referred to Date (proper case). When the query was changed to refer to DATE (upper case) throughout, the intermittent failures stopped.

More than two tables in an outer join query

I've often come across situations where MS Query complains that you can't have more than two tables involved in an outer join query. I'm not sure if this occurs with every ODBC driver but it certainly does with the Microsoft Text Driver (*.txt; *.csv) driver.  I've always worked around this by using multiple queries to get the job done but I recently discovered that it seems to be a MS Query issue rather than a restriction associated with the text driver. I stumbled across this because Excel 2007 and later allow you to edit the SQL associated with your data connections without invoking MS Query.  I had a three table query with inner joins and discovered that one of the inner joins needed to be an outer join.  I made the change to the SQL manually and it worked.  MS Query still complains and will refuse to deal with the query so I often adopt a hybrid approach to dealing with such queries as follows:

• construct the query in MS Query using inner joins only;
• manually change the inner join details in the SQL generated by MS Query to outer joins

A simple example follows using three data files (Data, Data2 and Data3) each with an ID column on which they are joined. The initial SQL generated from MS Query was:

SELECT Data.ID, Data.DName, Data2.D2Name, Data3.D3Name
FROM Data.txt Data, Data2.txt Data2, Data3.txt Data3
WHERE Data.ID = Data2.ID AND Data.ID = Data3.ID

After editing the SQL became:

SELECT Data.ID, Data.DName, Data2.D2Name, Data3.D3Name
FROM (Data.txt Data
left outer join Data2.txt Data2 on Data.ID = Data2.ID)
left outer join Data3.txt Data3 on Data.ID = Data3.ID

The output of this query looks as follows:

ID	DName	D2Name	D3Name
1	Unrestricted		Unrestricted
2	Restricted	Restricted
3	Designated		Designated

The outer join operation is evidenced by the null values returned under D2Name and D3Name.


For Excel 2003 I make the changes to the SQL by opening an Immediate window in the Visual Basic Editor and typing something like [?activecell.querytable.commandtext] followed by [Enter]. This reports the first SQL above on rows two to four of the window. Then I edit the first line to read [activecell.querytable.commandtext = "SELECT Data.ID, Data.DName, Data2.D2Name, Data3.D3Name FROM (Data.txt Data left outer join Data2.txt Data2 on Data.ID = Data2.ID) left outer join Data3.txt Data3 on Data.ID = Data3.ID"] followed by [Enter]. This changes the SQL without the need to use MS Query.  A simple refresh of the data will show the multitable query with the outer joins in place.

I hope you will find this helpful.  It has always worked for me, but I worry that there must be some good reason that MS Query complains about such a query.

Your comments would be very welcome

Running Totals using SQL and the Text File ODBC driver

With a data file containing detailed dated transactions how can you use SQL to record a running total?

Here's the RTData.txt data file:

Item	Date	Amount
A	01/01/2011	12.30
A	15/01/2011	16.00
B	16/01/2011	5.69
A	01/02/2011	65.12
B	01/02/2011	0.58
A	14/02/2011	8.91
A	28/02/2011	1.00
A	15/03/2011	9.00
B	01/04/2011	31.45
B	01/05/2011	738.00
B	21/05/2011	9.11
A	21/05/2011	10.93

Schema.ini looks as follows:

[RTData.txt]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
Col1=Item Text width 255
Col2=Date Date
Col3=Amount Currency

The SQL written to show the running totals is as follows:

SELECT Cur.Item, Cur.Date, Cur.Amount, Sum(Cum.Amount) AS 'Running Total'
FROM RTData.txt Cum, RTData.txt Cur
WHERE Cum.Item = Cur.Item AND Cum.Date <= Cur.Date
GROUP BY Cur.Item, Cur.Date, Cur.Amount
ORDER BY Cur.Item, Cur.Date

The output is as follows:

Item	Date	Amount	Running Total
A	01/01/2011	12.30	12.30
A	15/01/2011	16.00	28.30
A	01/02/2011	65.12	93.42
A	14/02/2011	8.91	102.33
A	28/02/2011	1.00	103.33
A	15/03/2011	9.00	112.33
A	21/05/2011	10.93	123.26
B	16/01/2011	5.69	5.69
B	01/02/2011	0.58	6.27
B	01/04/2011	31.45	37.72
B	01/05/2011	738.00	775.72
B	21/05/2011	9.11	784.83

It adds another layer of complexity if we want to generate monthly totals and also keep a running total.  This SQL will do it:

SELECT Cur.item, Cur.month, sum(Cur.Amount)/count(Cur.Amount) AS Amount, sum(Cum.Amount) AS 'Running Total'
FROM (SELECT RTD.Item, (year(RTD.Date)*100)+month(RTD.Date) AS Month, Sum(RTD.Amount) AS Amount
FROM RTData.txt RTD
Group By RTD.Item, (year(RTD.Date)*100)+month(RTD.Date)) as Cur,
(SELECT RTD.Item, (year(RTD.Date)*100)+month(RTD.Date) AS Month, Sum(RTD.Amount) AS Amount
FROM RTData.txt RTD
Group By RTD.Item, (year(RTD.Date)*100)+month(RTD.Date)) as Cum
WHERE Cum.Item = Cur.Item AND Cum.Month <= Cur.Month
Group By Cur.Item, Cur.Month
ORDER BY Cur.Item, Cur.Month

The output is as follows:

Item	Month	Amount	Running Total
A	201101	28.30	28.30
A	201102	75.03	103.33
A	201103	9.00	112.33
A	201105	10.93	123.26
B	201101	5.69	5.69
B	201102	0.58	6.27
B	201104	31.45	37.72
B	201105	747.11	784.83

The trick here is to realise that the Amount column inflates artificially as we progress and we need to correct for this by dividing by count(Cur.Amount) which returns 1, 2, 3 ... with successive records.

Comments welcome.