Excel Data Query from SQL

[krissz]

I am trying to generate several spreadsheets, sourcing the data from an SQL database. I am new to SQL. The only tool I have for accessing the database is MS Query.
I have managed a lot by the copy & modify process, but now am stuck. I have the following code which allows me to select values from a specified Ac for a specified period.

SELECT Table1.Date, Table1.Ac, Table1.Ref, Table1.Text, Table1.Value
FROM Main.dbo.Table1 Table1
WHERE (Table1.Ac=?) AND (Table1.Date>=? And Table1.Date<=?)
ORDER BY Table1.Date

What I now want to do is:
* Delete the Table1.Ac criterium so that I get all records between selected dates
* Group by the Table1.Ac field, sorted ascending
* In a new column, Display the sum of all the values for each Table1.Ac
This would be very similar to a Summary TB in accounts parlance

As soon as I start modifying the code, I get the message: "Parameters are not allowed in queries that can't be displayed graphically".

I would appreciate any help on the SQL code, and on any better tools that I can integrate into Excel.

Thank you

 

[jkpieterse]

I would suggest to create an Access database and link the SQL table to that. After that, you can easily use the MSACCESS query designer to create things like what you are asking for.

 

[krissz]

Jan
Thank you, but the company has a policy to standardise all database application onto MS SQL.
All existing ACCESS databases are migrating onto SQL, also Btrieve & probably others.
Hence my need to learn SQL on the fly

 

[jkpieterse]

I understand that. Still, if you have MSAccess, you can just use it to create your SQL, I'm not suggesting to use it instead of SQL Server

 

[krissz]

Since I have never used Access in my life, I would much prefer to keep to just one learning curve.
My experience in databases relates to Btrieve & Sage 50

 

[jkpieterse]

Fair enough Have a look at this article, it may help you further:
SQL GROUP BY Statement