cant order by * 1

[bodmin]

I am trying to use the following SQL string to open a recordset which I will then use to output to a file. When I attempt to open the recordset the query gives me an error message informing me that I can not group by fields selected with an '*'. Though as you can see the * has not been used for the selection of any of the fields so I am unsure why this error message is appearing. Any ideas???

strSql = "select * from Cheque, Debits, Attributes where Cheque.ProducedDate >=#" & FromDate & "# and Cheque.ProducedDate <=#" & ToDate & "# and Cheque.Cancelled = False and Cheque.Transferred = False GROUP BY Cheque.Cheque_No, Debits.Debit_ID, Attributes.Debit_ID ORDER BY Cheque.Cheque_No;"

Cheers
Andi

 

[r937]

Though as you can see the * has not been used for the selection of any of the fields...

yes it has, it's right here:

strSql = "select * <=====

you need to select only the columns in your GROUP BY

are you sure you perhaps don't really want to say ORDER BY instead of GROUP BY?

rudy

http://r937.com/

SQL Consulting

 

[bodmin]

I need to group all data regarding each cheque together and then have the output ordered by the cheque number. But I also need to group the analysis attributes to the debits that they have been applied to. When I try to select all of the fields that I need seperately it gives me the error message that the field is not used in an aggregate function. How would I build the query to get around this problem cheers
Andi

 

[bboffin]

I think that the GROUP BY is unnecessary. Simply using an ORDER BY clause for the same fields that you are attempting to GROUP BY (and removing the existing ORDER BY clause)would give you a recordset that was sorted to the sequence you require.

 

[bodmin]

right I have removed the group by clause and now the file outputs without any errors.
But it is outputting the wrong number of records, its now doing a copy of all cheque information for every debit record in the table. The attribute information is the correct record but is just repeated the same as the cheque info. Only the debits are a complete listing of all debits in the system and not just the ones that are related ot that cheque.

Any ideas??
Cheers
Andi

 

[PHV]

You have no join between Cheque, Debits, Attributes.
Take a look at cartesian product.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bodmin]

Cheers PHV I hadnt noticed that I had left out the joins, but now when I put them in there I have the following sql string.

"select Cheque.Cheque_No, Cheque.RemittanceAdvice, Cheque.ProducedDate, Cheque.Amount, Cheque.Payee, Cheque.CreditAccount, Cheque.CreditDescription, Debits.DebitAccount, Debits.Amount, Attributes.Analysis_Code, Attributes.Analysis_Attribute, Attributes.Account from FROM ((Cheque LEFT JOIN Debits ON Cheque.Cheque_No=Debits.Cheque_No) LEFT JOIN Attributes ON Debits.Debit_ID=Attributes.Debit_ID) where Cheque.ProducedDate >=#" & FromDate & "# and Cheque.ProducedDate <=#" & ToDate & "# and Cheque.Cancelled = False and Cheque.Transferred = False ORDER BY Cheque.Cheque_No, Debits.Debit_ID;"

It is now giving me a syntax error in the from clause and I cant quite identify where, any help??
Cheers loads
Andi

 

[PHV]

You may try this:
FROM Cheque LEFT JOIN (Debits LEFT JOIN Attributes ON Debits.Debit_ID=Attributes.Debit_ID) ON Cheque.Cheque_No=Debits.Cheque_No

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bodmin]

Cheers PHV that has fixed it, thats sorted my prob and I can go home for the weekend :->

Have a star
Andi