[RESOLVED] Help with a SQL query or piece of code [ADO and Access 2000]

[Neverbirth]

Well, I'll try to explain this... I have a database, I have a table for bills, and inside it there are several fields, one for its creation date, and other with the total.

I'd like to add charts displaying several types of data, among them the total of every bill each month... which is the best way to do this?

[1337ScriptKiddie]

I would recomend a access report for printing reasons, but if you would just like to have a total spat out and the database is in a net savy location you could even use an asp page. If you need help with the vbscript or vb routine you are making to do this just post it and I can decipher it to the best of my ability.

Garrett

[Neverbirth]

Due to several reasons the database is local, and as I say in the subject I use Access 2000 for it.

About the VB routine... I haven't writed anything definitive yet, since I'm quite busy with a lot of things. Just wrote all the functions I need to later display and manage the charts, but I need to work on the one used to get the data from the database... and my SQL knowledge is limited to simple queries, so all I can think up to now is something like doing 12 recordsets with something like:

"SELECT SUM(bill_total) as Total FROM tbl_bills WHERE bill_date = #" & format(displayDate, "yyyy/mm") & "#"

And I'd say this isn't a very good choice... that's why I ask. If it's possible to get the total of each month of a year without having to do 12 queries...

[1337ScriptKiddie]

No you dont have to make 12 different queries to get the records for each month. There is what is called a wildcard character in SQL statements.
so your SQL statement to pull all bills from December would look like:

SELECT * FROM bills WHERE 12/**/** IN dates

But again thats just the sequal statement. Since your using a local database that cant be accessed from the web it looks like a report would be the best bet.
I dont quite have the time to write a report for you but I do have an ADO connection snippit I can give you to get started

Dim Cnxn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCnxn As String
    Dim strSQL As String
    
    ' Open connection
    strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Database Path here"
    Set Cnxn = New ADODB.Connection
    Cnxn.Open strCnxn
    
    ' Open employee table
    Set rst = New ADODB.Recordset
    strSQL = "SQL string here"
    rst.Open strSQL, Cnxn, adOpenKeyset, adLockOptimistic, adCmdTable

This will create a recordset for you to start working with. If you creat a form instead of a report you can make it interactive so you wont have to create 12 different reports to view the 12 different month totals!

[Neverbirth]

I dont quite have the time to write a report for you but I do have an ADO connection snippit I can give you to get started

Dim Cnxn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCnxn As String
    Dim strSQL As String
    
    ' Open connection
    strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Database Path here"
    Set Cnxn = New ADODB.Connection
    Cnxn.Open strCnxn
    
    ' Open employee table
    Set rst = New ADODB.Recordset
    strSQL = "SQL string here"
    rst.Open strSQL, Cnxn, adOpenKeyset, adLockOptimistic, adCmdTable

This will create a recordset for you to start working with. If you creat a form instead of a report you can make it interactive so you wont have to create 12 different reports to view the 12 different month totals!

I already know how ADO works, but thanks anyway .

No you dont have to make 12 different queries to get the records for each month. There is what is called a wildcard character in SQL statements.
so your SQL statement to pull all bills from December would look like:

SELECT * FROM bills WHERE 12/**/** IN dates

But again thats just the sequal statement. Since your using a local database that cant be accessed from the web it looks like a report would be the best bet.

It seems this sort of query syntax isn't supported by Access 2000.

[1337ScriptKiddie]

I already know how ADO works, but thanks anyway .



It seems this sort of query syntax isn't supported by Access 2000.

Neverbirth:
Sorry, upon examining my trusty ADO reference I relized I did make some syntax mystakes. Here is my new line for you to try:

SELECT * FROM (your table) WHERE (your field name) = #(month number)*#;

This book that I am holding is refers to Access and was written in 1999 so I am pretty sure it will be compatable with your db. The previous problem was just an issue of syntax. When you use this SQL statement be sure you fill in the table and field name properly. There is a bit of room for error with that statement because I dont know if your date fields are defined as the date type or just a number. If it is a number you dont need the # marks. If it is a date you do. Basically the wildcard symbol should help you here its just a matter of tighting the syntax so that your database is happy.

Good Luck,
Garrett

P.S. For those of you reading this who use SQL databases use % instead of *.

[Neverbirth]

SELECT * FROM (your table) WHERE (your field name) = #(month number)*#;

Strange, it gives me problems as well. Also, the field format is set to date format as you guess. However, playing around a bit with Acces I've found out that it lets me do things like this:

"SELECT SUM(bill_total) AS December FROM tbl_bills WHERE Month([bill_date])=12 AND Year([bill_date])=2006"

I also know something this works:

"SELECT SUM(bill_total) as December FROM tbl_bills WHERE bill_date LIKE '%" & format(monthValue, "mm/yyyy") & "%'"

But I was trying to avoid it since it forces me to format the date the same way it's formatted in the db when viewing it as it is.

Anyway... sorry for my ignorance, but how do I sum each bill_total for each month now?

[salvelinus]

I don't know what you're doing with the date, but I think you want to GROUP BY the month field, assuming you have one in the table.

[Neverbirth]

I don't know what you're doing with the date, but I think you want to GROUP BY the month field, assuming you have one in the table.

Lol, you are fully right. Thank you. Also, since I already have a date field I don't need to add a month field.

[salvelinus]

Um, okay. I guess I misunderstood your date field. I'd assume that would group them by date, rather than month. But if you can get the month/year and group by that, cool.

[Neverbirth]

Um, okay. I guess I misunderstood your date field. I'd assume that would group them by date, rather than month. But if you can get the month/year and group by that, cool.

Yes, you are right, grouping by the date field groups the results by date, but I can use "GROUP BY Month(bill_date)".