1st sorry about leangth...couldn't really cut anymore.
I want the output to be
Organization 320000
Fund 100004
Program 777777
Account1 7234.55
Account2 -347.99
Account3 823.55
Program 888888
Account1 8745.99
Account2 -9878.33
.....on and on.
<b>What I can get to work is</b>
Organization 320000
Fund 100004
Program 777777
Account1 7234.55
Organization 320000
Fund 100004
Program 777777
Account2 -347.99
.........on and on.
The code below looks right but the Organization, Fund, Program numbers
never change when they should. I've been looking at this for two
days...help!
<table border="0">
<%
sSQL3 = "SELECT Distinct Organization" & _
" FROM " & TheYear & _
" WHERE MonthPD = '" & ThePD & "'"
set rs3 = Connect.Execute(sSQL3)
%>
<tr>
<td> <font color="#0000FF">Organization </font></td>
<td colspan="10"><%=rs3("Organization")%></td>
</tr>
<% Do until rs3.eof %>
<%
sSQL2 = "SELECT Distinct Fund" & _
" FROM " & TheYear & _
" WHERE MonthPD = '" & ThePD & "'"
set rs2 = Connect.Execute(sSQL2)
%>
<tr>
<td> <font color="#0000FF">Fund </font></td>
<td colspan="10"><%=rs2("Fund")%></td>
</tr>
<% Do until rs2.eof %>
<%
sSQL4 = "SELECT Distinct Program, Fund, Organization" & _
" FROM " & TheYear & _
" WHERE MonthPD = '" & ThePD & "'" & _
" ORDER BY Organization, Fund, Program"
set rs4 = Connect.Execute(sSQL4)
TheProg = rs4("Program")
%>
<tr>
<td> <font color="#0000FF">Program</font> </td>
<td colspan="10"><%=TheProg%></td>
</tr>
<% Do until rs4.eof %>
<tr>
<td>Account</div></td>
<td><%=TheAct%> </td>
<td><%=TotalAmt%></td>
</tr>
<%
TheFund = rs4("Fund")
TheOrg = rs4("Organization")
TheProg = rs4("Program")
SQL = "SELECT *" & _
"FROM " & TheYear & _
"WHERE MonthPD = '" & ThePD & "'" & _
" and Organization = '" & TheOrg & "'" & _
" and Fund = '" & TheFund & "'" & _
" and Program = '" & TheProg & "'" & _
" ORDER BY Account"
set rs = Connect.Execute(sSQL)
TotalAmt=0
Do until rs.eof
TheAct = rs("Account")
TotalAmt = TotalAmt + rs("PdAmount")
Response.Flush
rs.MoveNext
Loop
Response.Flush
rs4.MoveNext
Loop
Response.Flush
rs2.MoveNext
Loop
Response.Flush
rs3.MoveNext
Loop %>
</table> 14  1516 
You can do this with a single recordset (see http://www.aspfaq.com/2241 ).
I'll leave the HTML pretty-ifying up to you.
<%
sql = "SELECT Organization, Fund, Program, Account, PdAmount" & _
"FROM " & TheYear & _
" ORDER BY Organization, Fund, Program, Account"
cOrg = "": cFund = "": cProg = "": cAcc = ""
set rs = conn.execute(sql)
do while not rs.eof
nOrg = rs(0): nFund = rs(1): nProg = rs(2)
nAcc = rs(3): nPd = rs(4)
if nOrg <> cOrg then ' new org
response.write "<p>Organization:" & nOrg
cOrg = nOrg
end if
if nFund <> cFund then ' new fund
response.write "<br>Fund:" & nFund
cFund = nFund
end if
if nProg <> cProg then
response.write "<br>Program:" & nProg
cProg = nProg
end if
response.write "<br>" & nAcc & ":" & nPd
rs.movenext
loop
%>
Are your tables really named by year? This is horrible design, you really
should have ONE table and have a column for year. You're really messing up
the whole idea of relational design and entity modeling...
-- http://www.aspfaq.com/
(Reverse address to reply.)
"Abby Lee" <ab*******@hotmail.com> wrote in message
news:80**************************@posting.google.c om... 1st sorry about leangth...couldn't really cut anymore.
I want the output to be
Organization 320000
Fund 100004
Program 777777
Account1 7234.55
Account2 -347.99
Account3 823.55
Program 888888
Account1 8745.99
Account2 -9878.33
....on and on.
<b>What I can get to work is</b>
Organization 320000
Fund 100004
Program 777777
Account1 7234.55
Organization 320000
Fund 100004
Program 777777
Account2 -347.99
........on and on.
The code below looks right but the Organization, Fund, Program numbers
never change when they should. I've been looking at this for two
days...help!
<table border="0">
<%
sSQL3 = "SELECT Distinct Organization" & _
" FROM " & TheYear & _
" WHERE MonthPD = '" & ThePD & "'"
set rs3 = Connect.Execute(sSQL3)
%>
<tr>
<td> <font color="#0000FF">Organization </font></td>
<td colspan="10"><%=rs3("Organization")%></td>
</tr>
<% Do until rs3.eof %>
<%
sSQL2 = "SELECT Distinct Fund" & _
" FROM " & TheYear & _
" WHERE MonthPD = '" & ThePD & "'"
set rs2 = Connect.Execute(sSQL2)
%>
<tr>
<td> <font color="#0000FF">Fund </font></td>
<td colspan="10"><%=rs2("Fund")%></td>
</tr>
<% Do until rs2.eof %>
<%
sSQL4 = "SELECT Distinct Program, Fund, Organization" & _
" FROM " & TheYear & _
" WHERE MonthPD = '" & ThePD & "'" & _
" ORDER BY Organization, Fund, Program"
set rs4 = Connect.Execute(sSQL4)
TheProg = rs4("Program")
%>
<tr>
<td> <font color="#0000FF">Program</font> </td>
<td colspan="10"><%=TheProg%></td>
</tr>
<% Do until rs4.eof %>
<tr>
<td>Account</div></td>
<td><%=TheAct%> </td>
<td><%=TotalAmt%></td>
</tr>
<%
TheFund = rs4("Fund")
TheOrg = rs4("Organization")
TheProg = rs4("Program")
SQL = "SELECT *" & _
"FROM " & TheYear & _
"WHERE MonthPD = '" & ThePD & "'" & _
" and Organization = '" & TheOrg & "'" & _
" and Fund = '" & TheFund & "'" & _
" and Program = '" & TheProg & "'" & _
" ORDER BY Account"
set rs = Connect.Execute(sSQL)
TotalAmt=0
Do until rs.eof
TheAct = rs("Account")
TotalAmt = TotalAmt + rs("PdAmount")
Response.Flush
rs.MoveNext
Loop
Response.Flush
rs4.MoveNext
Loop
Response.Flush
rs2.MoveNext
Loop
Response.Flush
rs3.MoveNext
Loop %>
</table>
Well, there's a little bit of logic to fit here, e.g. if you have two orgs
with the same program or two programs with the same account, etc. You can
solve that by setting children to "" whenever you hit a new tier, e.g.
<%
sql = "SELECT Organization, Fund, Program, Account, PdAmount" & _
"FROM " & TheYear & _
" ORDER BY Organization, Fund, Program, Account"
cOrg = "": cFund = "": cProg = "": cAcc = ""
set rs = conn.execute(sql)
do while not rs.eof
nOrg = rs(0): nFund = rs(1): nProg = rs(2)
nAcc = rs(3): nPd = rs(4)
if nOrg <> cOrg then ' new org
response.write "<p>Organization:" & nOrg
cOrg = nOrg: cFund="": cProg=""
end if
if nFund <> cFund then ' new fund
response.write "<br>Fund:" & nFund
cFund = nFund: cProg=""
end if
if nProg <> cProg then
response.write "<br>Program:" & nProg
cProg = nProg
end if
response.write "<br>" & nAcc & ":" & nPd
rs.movenext
loop
%>
-- http://www.aspfaq.com/
(Reverse address to reply.)
Aaron,
You are the sweetest thing...I could just kiss you.
Abby
"Aaron [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message news:<u0**************@TK2MSFTNGP10.phx.gbl>... Well, there's a little bit of logic to fit here, e.g. if you have two orgs
with the same program or two programs with the same account, etc. You can
solve that by setting children to "" whenever you hit a new tier, e.g.
<%
sql = "SELECT Organization, Fund, Program, Account, PdAmount" & _
"FROM " & TheYear & _
" ORDER BY Organization, Fund, Program, Account"
cOrg = "": cFund = "": cProg = "": cAcc = ""
set rs = conn.execute(sql)
do while not rs.eof
nOrg = rs(0): nFund = rs(1): nProg = rs(2)
nAcc = rs(3): nPd = rs(4)
if nOrg <> cOrg then ' new org
response.write "<p>Organization:" & nOrg
cOrg = nOrg: cFund="": cProg=""
end if
if nFund <> cFund then ' new fund
response.write "<br>Fund:" & nFund
cFund = nFund: cProg=""
end if
if nProg <> cProg then
response.write "<br>Program:" & nProg
cProg = nProg
end if
response.write "<br>" & nAcc & ":" & nPd
rs.movenext
loop
%>
I get an Object Required error when I reach the line:
set rs = conn.execute(sql)
Do I needt to make a change to my script that connects to my db?
Set connect = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateOBject("ADODB.Recordset")
connect.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDataSource
connect.Open
> I get an Object Required error when I reach the line: set rs = conn.execute(sql)
Do I needt to make a change to my script that connects to my db?
Set connect = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateOBject("ADODB.Recordset")
connect.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDataSource
connect.Open
Well, do you want to use the name conn, or the name connect? Pick one.
set conn = CreateObject("ADODB.Connection")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDataSource
conn.open connStr
....
set rs = conn.execute(sql)
or
set connect = CreateObject("ADODB.Connection")
connectStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDataSource
connect.open connStr
....
set rs = connect.execute(sql)
I think you'll find that the most common convention is to use the word conn
(or oConn) as opposed to connect, but you could call it baker or airplane or
bobizumi if you wanted to... just stick to one name.
-- http://www.aspfaq.com/
(Reverse address to reply.)
I used this code...which worked...but gave me strange output.
Instead of a list of Accounts with totals I got pages of this...
Organization:Organization
Fund:Fund
Program:Program
Account:MonthPD
Account:MonthPD
Account:MonthPD
Account:MonthPD
Account:MonthPD
<%
sql = "SELECT 'Organization','Fund','Program','Account','MonthPD '" & _
"FROM AllExpenses2004 " & _
" WHERE MonthPD = '0604'" & _
" ORDER BY 'Organization', 'Fund', 'Program', 'Account'"
cOrg = "": cFund = "": cProg = "": cAcc = ""
set rs = connect.execute(sql)
do while not rs.eof
nOrg = rs(0): nFund = rs(1): nProg = rs(2)
nAcc = rs(3): nPd = rs(4)
if nOrg <> cOrg then ' new org
response.write "<p>Organization:" & nOrg
cOrg = nOrg: cFund="": cProg=""
end if
if nFund <> cFund then ' new fund
response.write "<br>Fund:" & nFund
cFund = nFund: cProg=""
end if
if nProg <> cProg then
response.write "<br>Program:" & nProg
cProg = nProg
end if
response.write "<br>" & nAcc & ":" & nPd
rs.movenext
loop
%>
Remove the ticks from around your column names.
Bob Lehmann
"Abby Lee" <ab*******@hotmail.com> wrote in message
news:80**************************@posting.google.c om... I used this code...which worked...but gave me strange output.
Instead of a list of Accounts with totals I got pages of this...
Organization:Organization
Fund:Fund
Program:Program
Account:MonthPD
Account:MonthPD
Account:MonthPD
Account:MonthPD
Account:MonthPD
<%
sql = "SELECT 'Organization','Fund','Program','Account','MonthPD '" & _
"FROM AllExpenses2004 " & _
" WHERE MonthPD = '0604'" & _
" ORDER BY 'Organization', 'Fund', 'Program', 'Account'"
cOrg = "": cFund = "": cProg = "": cAcc = ""
set rs = connect.execute(sql)
do while not rs.eof
nOrg = rs(0): nFund = rs(1): nProg = rs(2)
nAcc = rs(3): nPd = rs(4)
if nOrg <> cOrg then ' new org
response.write "<p>Organization:" & nOrg
cOrg = nOrg: cFund="": cProg=""
end if
if nFund <> cFund then ' new fund
response.write "<br>Fund:" & nFund
cFund = nFund: cProg=""
end if
if nProg <> cProg then
response.write "<br>Program:" & nProg
cProg = nProg
end if
response.write "<br>" & nAcc & ":" & nPd
rs.movenext
loop
%>
Why did you change SELECT Organization, ... to SELECT 'Organization', ...
???
-- http://www.aspfaq.com/
(Reverse address to reply.)
"Abby Lee" <ab*******@hotmail.com> wrote in message
news:80**************************@posting.google.c om... I used this code...which worked...but gave me strange output.
Instead of a list of Accounts with totals I got pages of this...
Organization:Organization
Fund:Fund
Program:Program
Account:MonthPD
Account:MonthPD
Account:MonthPD
Account:MonthPD
Account:MonthPD
<%
sql = "SELECT 'Organization','Fund','Program','Account','MonthPD '" & _
"FROM AllExpenses2004 " & _
" WHERE MonthPD = '0604'" & _
" ORDER BY 'Organization', 'Fund', 'Program', 'Account'"
cOrg = "": cFund = "": cProg = "": cAcc = ""
set rs = connect.execute(sql)
do while not rs.eof
nOrg = rs(0): nFund = rs(1): nProg = rs(2)
nAcc = rs(3): nPd = rs(4)
if nOrg <> cOrg then ' new org
response.write "<p>Organization:" & nOrg
cOrg = nOrg: cFund="": cProg=""
end if
if nFund <> cFund then ' new fund
response.write "<br>Fund:" & nFund
cFund = nFund: cProg=""
end if
if nProg <> cProg then
response.write "<br>Program:" & nProg
cProg = nProg
end if
response.write "<br>" & nAcc & ":" & nPd
rs.movenext
loop
%>
When I use SELECT Organization,
I get the error message:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'OrganizationFROM
AllExpenses2004 WHERE MonthPD = '0604' ORDER BY 'Organization''.
But the SELECT 'Organization', has its own problems.
I finally did this:
SELECT *
and changed this...to fit my db:
nOrg = rs(3): nFund = rs(2): nProg = rs(5)
nAcc = rs(4): nPd = rs(19)
I hate having to grabb all the extra information but this works.
Thanks.
"Aaron [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message news:<O9*************@TK2MSFTNGP11.phx.gbl>... Why did you change SELECT Organization, ... to SELECT 'Organization', ...
???
--
http://www.aspfaq.com/
(Reverse address to reply.)
When I use SELECT Organization,
I get the error message:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'OrganizationFROM
AllExpenses2004 WHERE MonthPD = '0604' ORDER BY 'Organization''.
But the SELECT 'Organization', has its own problems.
I finally did this:
SELECT *
and changed this...to fit my db:
nOrg = rs(3): nFund = rs(2): nProg = rs(5)
nAcc = rs(4): nPd = rs(19)
I hate having to grabb all the extra information but this works.
Thanks.
"Aaron [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message news:<O9*************@TK2MSFTNGP11.phx.gbl>... Why did you change SELECT Organization, ... to SELECT 'Organization', ...
???
--
http://www.aspfaq.com/
(Reverse address to reply.)
You need a space in between Organization and FROM.
Bob Lehmann
"Abby Lee" <ab*******@hotmail.com> wrote in message
news:80**************************@posting.google.c om... When I use SELECT Organization,
I get the error message:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'OrganizationFROM
AllExpenses2004 WHERE MonthPD = '0604' ORDER BY 'Organization''.
But the SELECT 'Organization', has its own problems.
I finally did this:
SELECT *
and changed this...to fit my db:
nOrg = rs(3): nFund = rs(2): nProg = rs(5)
nAcc = rs(4): nPd = rs(19)
I hate having to grabb all the extra information but this works.
Thanks.
"Aaron [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message
news:<O9*************@TK2MSFTNGP11.phx.gbl>... Why did you change SELECT Organization, ... to SELECT 'Organization',
....
???
--
http://www.aspfaq.com/
(Reverse address to reply.)
You need a space in between Organization and FROM.
Bob Lehmann
"Abby Lee" <ab*******@hotmail.com> wrote in message
news:80**************************@posting.google.c om... When I use SELECT Organization,
I get the error message:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'OrganizationFROM
AllExpenses2004 WHERE MonthPD = '0604' ORDER BY 'Organization''.
But the SELECT 'Organization', has its own problems.
I finally did this:
SELECT *
and changed this...to fit my db:
nOrg = rs(3): nFund = rs(2): nProg = rs(5)
nAcc = rs(4): nPd = rs(19)
I hate having to grabb all the extra information but this works.
Thanks.
"Aaron [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message
news:<O9*************@TK2MSFTNGP11.phx.gbl>... Why did you change SELECT Organization, ... to SELECT 'Organization',
....
???
--
http://www.aspfaq.com/
(Reverse address to reply.)
Yep, if you response.write(sql) -- pretty common and trivial debugging
technique -- you'll see that you're missing a space between the column and
the keyword FROM.
-- http://www.aspfaq.com/
(Reverse address to reply.)
"Abby Lee" <ab*******@hotmail.com> wrote in message
news:80**************************@posting.google.c om... When I use SELECT Organization,
I get the error message:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'OrganizationFROM
AllExpenses2004 WHERE MonthPD = '0604' ORDER BY 'Organization''.
But the SELECT 'Organization', has its own problems.
I finally did this:
SELECT *
and changed this...to fit my db:
nOrg = rs(3): nFund = rs(2): nProg = rs(5)
nAcc = rs(4): nPd = rs(19)
I hate having to grabb all the extra information but this works.
Thanks.
"Aaron [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message
news:<O9*************@TK2MSFTNGP11.phx.gbl>... Why did you change SELECT Organization, ... to SELECT 'Organization',
....
???
--
http://www.aspfaq.com/
(Reverse address to reply.)
Yep, if you response.write(sql) -- pretty common and trivial debugging
technique -- you'll see that you're missing a space between the column and
the keyword FROM.
-- http://www.aspfaq.com/
(Reverse address to reply.)
"Abby Lee" <ab*******@hotmail.com> wrote in message
news:80**************************@posting.google.c om... When I use SELECT Organization,
I get the error message:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'OrganizationFROM
AllExpenses2004 WHERE MonthPD = '0604' ORDER BY 'Organization''.
But the SELECT 'Organization', has its own problems.
I finally did this:
SELECT *
and changed this...to fit my db:
nOrg = rs(3): nFund = rs(2): nProg = rs(5)
nAcc = rs(4): nPd = rs(19)
I hate having to grabb all the extra information but this works.
Thanks.
"Aaron [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message
news:<O9*************@TK2MSFTNGP11.phx.gbl>... Why did you change SELECT Organization, ... to SELECT 'Organization',
....
???
--
http://www.aspfaq.com/
(Reverse address to reply.)
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Marco Alting |
last post by:
Hi,
I'm still confused about my queries, I want to do something is ASP that is
easily done in Access. I'll post the Access queries below as a reference.
The main idea is that the queries depend...
|
by: Mike N. |
last post by:
Hello:
I am new to T-SQL programing, and relativly new to SQL statements in
general, although I have a good understanding of database theory. I'm a
little confused as to the fundamental...
|
by: Roger Green |
last post by:
I have inherited a complex database that has many dozens of queries that derive data from a people
table.
I now need to be able to run these queries (from within a significant number of forms)...
|
by: Dom Boyce |
last post by:
Hi
First up, I am using MS Access 2002.
I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my...
|
by: Barn Yard |
last post by:
good morning,
im still kind of new to VBA but I have learned some interesting things
so far. Right now I am normalizing my survey database so I'm having
to run an append query for each...
|
by: j.mandala |
last post by:
I have two versions of a database front end and want to be able to use
docmd.copy (or some other method) to move a bunch of queries.
I was able to use the '.tag' property to of forms and reports...
|
by: Martin Lacoste |
last post by:
There's likely not a simple answer for this, I know, but, I thought
I'd try anyways...
Background..
I've been racking my brain with some queries that I thought were
straightforward, but have...
|
by: Steve Elliott |
last post by:
I have a query set up to gather together data between two specified dates.
Shown in the query column as:
Between #24/09/2004# And #01/10/2004#
Is it possible to enter several different date...
|
by: MHenry |
last post by:
Hi,
I know virtually nothing about creating Macros in Access.
I would appreciate some help in creating a Macro or Macros that
automatically run(s) 14 Queries (three Make Table Queries, and 11...
|
by: Jim Crate |
last post by:
<<finishing message this time, after accidentally sending before>>
I have a couple queries in a PL/PGSQL function which execute very slowly (around
one minute each) which execute in .5 second...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
