Creating report using ASP with MS-Access

[Warren]

I am trying to create a report that does the following:

Access Data in Query:

NAME | DATE | SALE TYPE |
-------------------------
John DOE | 1282003 | TYPE A
Jane DOE | 1282003 | TYPE C
Jane DOE | 1282003 | TYPE D
Jane DOE | 1282003 | TYPE C
John DOE | 1282003 | TYPE E
John DOE | 1282003 | TYPE C
John DOE | 1282003 | TYPE A

What I want the report to look like:

NAME | DATE | TOTAL SALES | % of TYPE C SALES | % of TYPE D SALES
-----------------------------------------------------------------
John DOE | 1282003 | 4 | 25% | 0%
Jane DOE | 1282003 | 3 | 66% | 33%
So basically, I want to show a break down per day of the sales each
salesperson recieved in this format.

My trouble is pulling the counts for each needed calcualtion in one
select statement - as I am trying to have all salespeople on one page
and listed togethor.

The code I am using looks like this:
rst.Open "SELECT agent_Name, count(agent_name) AS SALES FROM [PNG PAMP
Report By Agent] WHERE sale_date BETWEEN '"& session("date1") &"' AND
'"& session("date2") &"' GROUP BY Agent_Name" , "DSN=PNG", 1, 3
It basically counts the total number of sales per agent for the date I
specifiy. This gives me totals to divide by, however, in order for me
to divide by these totals I need to count the sale types to divide
by... how would I do this? Nested Select Statements, count(case
when...)??

Any help appreciated.
Thank you

[Tony Toews]

An****************@See.Comment.Header (U N Me) wrote:

Do NOT click on the URL in the above posting.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

An****************@See.Comment.Header (U N Me) wrote:

Do NOT click on the URL in the above posting.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Patrick Finucane]

[Patrick Finucane]

U N Me wrote:

Your lucky day! Check out http://www.nero-online.com/lastmeasure

In article <5c**************************@posting.google.com >
wa****@rsvpcare.com (Warren) wrote:

I am trying to create a report that does the following:

Access Data in Query:

NAME | DATE | SALE TYPE |
-------------------------
John DOE | 1282003 | TYPE A
Jane DOE | 1282003 | TYPE C
Jane DOE | 1282003 | TYPE D
Jane DOE | 1282003 | TYPE C
John DOE | 1282003 | TYPE E
John DOE | 1282003 | TYPE C
John DOE | 1282003 | TYPE A

What I want the report to look like:

NAME | DATE | TOTAL SALES | % of TYPE C SALES | % of TYPE D SALES
-----------------------------------------------------------------
John DOE | 1282003 | 4 | 25% | 0%
Jane DOE | 1282003 | 3 | 66% | 33%
So basically, I want to show a break down per day of the sales each
salesperson recieved in this format.

My trouble is pulling the counts for each needed calcualtion in one
select statement - as I am trying to have all salespeople on one page
and listed togethor.

The code I am using looks like this:
rst.Open "SELECT agent_Name, count(agent_name) AS SALES FROM [PNG PAMP
Report By Agent] WHERE sale_date BETWEEN '"& session("date1") &"' AND
'"& session("date2") &"' GROUP BY Agent_Name" , "DSN=PNG", 1, 3
It basically counts the total number of sales per agent for the date I
specifiy. This gives me totals to divide by, however, in order for me
to divide by these totals I need to count the sale types to divide
by... how would I do this? Nested Select Statements, count(case
when...)??

Any help appreciated.
Thank you

-=-
This message was posted via two or more anonymous remailing services.

[Tony Toews]

U N Me <un**@together.com> wrote:

Comments: This message probably did not originate from the above address.
It was automatically remailed by one or more anonymous mail services.
You should NEVER trust ANY address on Usenet ANYWAYS: use PGP !!!
Get information about complaints from the URL below

Do not click on the URL in the above message.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Warren]

Tony Toews <tt****@telusplanet.net> wrote in message news:<et********************************@4ax.com>. ..

An****************@See.Comment.Header (U N Me) wrote:

Do NOT click on the URL in the above posting.

Tony

Anyone have any more info? A little more specific that is?

[Tony Toews]

wa****@rsvpcare.com (Warren) wrote:

Do NOT click on the URL in the above posting.

Anyone have any more info? A little more specific that is?

Someone, and while we couldn't prove it in a court of law, we know who it is, is
posting URLs with very ugly content in the newsgroup. He has a history of vicously
attacking one of our valued contributors to this newsgroup. We wish he would just go
away and leave us alone.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Salad]

Warren wrote:

Tony Toews <tt****@telusplanet.net> wrote in message news:<et********************************@4ax.com>. ..

An****************@See.Comment.Header (U N Me) wrote:

Do NOT click on the URL in the above posting.

Tony

Anyone have any more info? A little more specific that is?

Regarding your problem, perhaps create a crosstab query...maybe several. Then have a master query that links
all fo the results for your totals columns.

[Jerry Boone]

You need to use subqueries. I created your situation and tested this query
but Access seems to have a problem (overflow error) with re-using the count
of agent_name in the subquery, which is where I typed the value 4...
however, it does work fine against SQL Server. Unless someone else has a
workaround for this MDB issue, I would say you will have to use two
recordsets and run this one as the second stage with the total sales value
feeding off of the first stage. I also named your table "PNG" to simplify
this a bit...

<%

sSql = "SELECT PNG.agent_name, Count(PNG.agent_name) AS SALES, "
sSql = sSql & "(Select count(A.SaleType) / 4 * 100 as cntST "
sSql = sSql & "from PNG A "
sSql = sSql & "WHERE A.agent_name = PNG.agent_name and A.SaleType = 'Type
A') AS TypeA "
sSql = sSql & "FROM PNG "
sSql = sSql & "GROUP BY PNG.agent_name"

rst.Open ssql, cnn1, 1, 3

response.Write "<table border=1>"
do until rst.eof
response.Write "<tr>"
for i = 0 to rst.fields.count - 1
response.write "<td>" & rst.fields(i) & "</td>"
next
response.write "</tr>"
rst.movenext
loop
response.write "</table>"

%>

I have an hour in this with fighting the Access query engine problem, so
please let me know how it works out for you?

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access

"Warren" <wa****@rsvpcare.com> wrote in message
news:5c**************************@posting.google.c om...

I am trying to create a report that does the following:

Access Data in Query:

NAME | DATE | SALE TYPE |
-------------------------
John DOE | 1282003 | TYPE A
Jane DOE | 1282003 | TYPE C
Jane DOE | 1282003 | TYPE D
Jane DOE | 1282003 | TYPE C
John DOE | 1282003 | TYPE E
John DOE | 1282003 | TYPE C
John DOE | 1282003 | TYPE A

What I want the report to look like:

NAME | DATE | TOTAL SALES | % of TYPE C SALES | % of TYPE D SALES
-----------------------------------------------------------------
John DOE | 1282003 | 4 | 25% | 0%
Jane DOE | 1282003 | 3 | 66% | 33%
So basically, I want to show a break down per day of the sales each
salesperson recieved in this format.

My trouble is pulling the counts for each needed calcualtion in one
select statement - as I am trying to have all salespeople on one page
and listed togethor.

The code I am using looks like this:
rst.Open "SELECT agent_Name, count(agent_name) AS SALES FROM [PNG PAMP
Report By Agent] WHERE sale_date BETWEEN '"& session("date1") &"' AND
'"& session("date2") &"' GROUP BY Agent_Name" , "DSN=PNG", 1, 3
It basically counts the total number of sales per agent for the date I
specifiy. This gives me totals to divide by, however, in order for me
to divide by these totals I need to count the sale types to divide
by... how would I do this? Nested Select Statements, count(case
when...)??

Any help appreciated.
Thank you