Don Oldenburg
|
| Posted: 08/09/2001, 6:38 AM |
|
NOW -- my problem is to create a screen which gives sum by users to be
viewed by administration. Summing by one employee, only his/her
transactions was fairly easy... any further thoughts on how to implement
something which will do the same thing, but in a broader level -- for all
employees, on one screen/report?
DOn
|
|
|
 |
CodeCharge
|
| Posted: 08/09/2001, 2:44 PM |
|
Please use SQL query instead of table in the Grid.
If you are not familiar with how to write SQL queries, please try MS Access.
It is a great tool for quick, visual query design.
For example i just created an MS Access Query where I selected "bugs" and
"employees" table, then selected couple fields and asked for a Total by
employee_name. MS Access created an SQL query:
SELECT employees.employee_name, Count(bugs.bug_id) AS CountOfbug_id FROM
employees INNER JOIN bugs ON employees.employee_id = bugs.assigned_to GROUP
BY employees.employee_name;
which you can cleanup and place into CodeCharge, Form Properties / SQL
section.
Adam S.
YS/CC
"Don Oldenburg" <don.oldenburg@cacmnet.com> wrote in message
news:9ku3ok$ve4$1@news.codecharge.com...
> NOW -- my problem is to create a screen which gives sum by users to be
> viewed by administration. Summing by one employee, only his/her
> transactions was fairly easy... any further thoughts on how to implement
> something which will do the same thing, but in a broader level -- for all
> employees, on one screen/report?
>
> DOn
>
>
>
|
|
|
|
CodeCharge
|
| Posted: 08/09/2001, 3:48 PM |
|
Hi Don,
(per your email)
The SQL created by MS Access may need to be cleaned up in some cases.
For example INNER JOIN is not supported by all databases and I don't know
which one you are using.
Usually this should work:
SELECT employees.employee_name, Count(bugs.bug_id) AS bugtotal FROM
employees WHERE employees.bug_id=bugs.bug_id GROUP BY
employees.employee_name;
As you noticed, once you use your own SQL statement, the Field Joins will
not work, because CodeCharge is unable to understand your own SQL query and
modify it for this case.
If you need additional fields, they need to be included in the SQL query.
We may not be able to help you with all your SQL needs, so please consider
referging to various SQL tutorial on the net:
http://www.google.com/search?q=sql+tutorial
Possibly it may be also a good idea if you use MS Access to generate some
queries and learn how to modify them for your needs.
Adam
----- Original Message -----
From: "Oldenburg, Don" <Don.Oldenburg@cacmnet.com>
To: "'CodeCharge'" <support@codecharge.com>
Sent: Thursday, August 09, 2001 3:35 PM
Subject: RE: Total by employee on grid showing all employees
> Very cool... Didn't realize I could just create a sql statement of my own
> on the fly like that and expect it to work within CC.
>
> Problem... this is the statement I am trying to use... which WORKS --
>
> SELECT rtatrans.employee_id, sum(rtatrans.amount) as emptotal FROM
rtatrans
> GROUP by rtatrans.employee_id
>
> Two problems... I then try to do a join through CC to join
> rtatrans.employee_id with EmployeeID from cacmEmployee table. It doesn't
> work. So I'm assuming I need to do everything I need to do in that
> window--as it ignores the other CC settings?
>
> I tried doing your INNER JOIN by adding: INNER JOIN cacmemployee on
> rtatrans.employee_id = cacmemployee.employeeid
> but that didn't work.
>
>
>
>
> -----Original Message-----
> From: CodeCharge [mailto:support@codecharge.com]
> Sent: Thursday, August 09, 2001 4:46 PM
> To: Oldenburg, Don
> Subject: Re: Total by employee on grid showing all employees
>
>
> Please use SQL query instead of table in the Grid.
>
> If you are not familiar with how to write SQL queries, please try MS
Access.
> It is a great tool for quick, visual query design.
> For example i just created an MS Access Query where I selected "bugs" and
> "employees" table, then selected couple fields and asked for a Total by
> employee_name. MS Access created an SQL query:
> SELECT employees.employee_name, Count(bugs.bug_id) AS CountOfbug_id
FROM
> employees INNER JOIN bugs ON employees.employee_id = bugs.assigned_to
GROUP
> BY employees.employee_name;
>
> which you can cleanup and place into CodeCharge, Form Properties / SQL
> section.
>
> Adam S.
> YS/CC
>
>
> "Don Oldenburg" <don.oldenburg@cacmnet.com> wrote in message
>news:9ku3ok$ve4$1@news.codecharge.com...
> > NOW -- my problem is to create a screen which gives sum by users to be
> > viewed by administration. Summing by one employee, only his/her
> > transactions was fairly easy... any further thoughts on how to
implement
> > something which will do the same thing, but in a broader level -- for
all
> > employees, on one screen/report?
> >
> > DOn
> >
> >
> >
>
|
|
|
|
Don Oldenburg
|
| Posted: 08/09/2001, 3:52 PM |
|
I'm using Microsoft SQL Server. I've been looking at the tuturials... and
my syntax seems right... How do I know if my SQL server can do this. And
if it can't.. how do I substitute the numbers -- for names? etc?
Don
"CodeCharge" <support@codecharge.com> wrote in message
news:9kv402$167$1@news.codecharge.com...
> Hi Don,
>
> (per your email)
> The SQL created by MS Access may need to be cleaned up in some cases.
> For example INNER JOIN is not supported by all databases and I don't know
> which one you are using.
> Usually this should work:
> SELECT employees.employee_name, Count(bugs.bug_id) AS bugtotal FROM
> employees WHERE employees.bug_id=bugs.bug_id GROUP BY
> employees.employee_name;
>
> As you noticed, once you use your own SQL statement, the Field Joins will
> not work, because CodeCharge is unable to understand your own SQL query
and
> modify it for this case.
> If you need additional fields, they need to be included in the SQL query.
>
> We may not be able to help you with all your SQL needs, so please consider
> referging to various SQL tutorial on the net:
> http://www.google.com/search?q=sql+tutorial
> Possibly it may be also a good idea if you use MS Access to generate some
> queries and learn how to modify them for your needs.
>
> Adam
>
>
>
> ----- Original Message -----
> From: "Oldenburg, Don" <Don.Oldenburg@cacmnet.com>
> To: "'CodeCharge'" <support@codecharge.com>
> Sent: Thursday, August 09, 2001 3:35 PM
> Subject: RE: Total by employee on grid showing all employees
>
> > Very cool... Didn't realize I could just create a sql statement of my
own
> > on the fly like that and expect it to work within CC.
> >
> > Problem... this is the statement I am trying to use... which WORKS --
> >
> > SELECT rtatrans.employee_id, sum(rtatrans.amount) as emptotal FROM
> rtatrans
> > GROUP by rtatrans.employee_id
> >
> > Two problems... I then try to do a join through CC to join
> > rtatrans.employee_id with EmployeeID from cacmEmployee table. It
doesn't
> > work. So I'm assuming I need to do everything I need to do in that
> > window--as it ignores the other CC settings?
> >
> > I tried doing your INNER JOIN by adding: INNER JOIN cacmemployee on
> > rtatrans.employee_id = cacmemployee.employeeid
> > but that didn't work.
> >
> >
> >
> >
> > -----Original Message-----
> > From: CodeCharge [mailto:support@codecharge.com]
> > Sent: Thursday, August 09, 2001 4:46 PM
> > To: Oldenburg, Don
> > Subject: Re: Total by employee on grid showing all employees
> >
> >
> > Please use SQL query instead of table in the Grid.
> >
> > If you are not familiar with how to write SQL queries, please try MS
> Access.
> > It is a great tool for quick, visual query design.
> > For example i just created an MS Access Query where I selected "bugs"
and
> > "employees" table, then selected couple fields and asked for a Total by
> > employee_name. MS Access created an SQL query:
> > SELECT employees.employee_name, Count(bugs.bug_id) AS CountOfbug_id
> FROM
> > employees INNER JOIN bugs ON employees.employee_id = bugs.assigned_to
> GROUP
> > BY employees.employee_name;
> >
> > which you can cleanup and place into CodeCharge, Form Properties / SQL
> > section.
> >
> > Adam S.
> > YS/CC
> >
> >
> > "Don Oldenburg" <don.oldenburg@cacmnet.com> wrote in message
> >news:9ku3ok$ve4$1@news.codecharge.com...
> > > NOW -- my problem is to create a screen which gives sum by users to be
> > > viewed by administration. Summing by one employee, only his/her
> > > transactions was fairly easy... any further thoughts on how to
> implement
> > > something which will do the same thing, but in a broader level -- for
> all
> > > employees, on one screen/report?
> > >
> > > DOn
> > >
> > >
> > >
> >
>
>
|
|
|
|
CodeCharge
|
| Posted: 08/09/2001, 4:06 PM |
|
OK. I just found your previous email mentioning that you use MS SQL 
In such case, I'd like to know how you determine that your SQL doesn't work
If CodeCharge accepts your SQL after you click "OK" in Form Properties, then
it should be OK.
If you like to test the query in MS SQL, you can view Table and click "SQL"
icon in the SQL Enterprise Manager, then enter your query.
"Don Oldenburg" <don.oldenburg@cacmnet.com> wrote in message
news:9kv46t$1h8$1@news.codecharge.com...
> I'm using Microsoft SQL Server. I've been looking at the tuturials...
and
> my syntax seems right... How do I know if my SQL server can do this.
And
> if it can't.. how do I substitute the numbers -- for names? etc?
>
> Don
>
>
> "CodeCharge" <support@codecharge.com> wrote in message
>news:9kv402$167$1@news.codecharge.com...
> > Hi Don,
> >
> > (per your email)
> > The SQL created by MS Access may need to be cleaned up in some cases.
> > For example INNER JOIN is not supported by all databases and I don't
know
> > which one you are using.
> > Usually this should work:
> > SELECT employees.employee_name, Count(bugs.bug_id) AS bugtotal FROM
> > employees WHERE employees.bug_id=bugs.bug_id GROUP BY
> > employees.employee_name;
> >
> > As you noticed, once you use your own SQL statement, the Field Joins
will
> > not work, because CodeCharge is unable to understand your own SQL query
> and
> > modify it for this case.
> > If you need additional fields, they need to be included in the SQL
query.
> >
> > We may not be able to help you with all your SQL needs, so please
consider
> > referging to various SQL tutorial on the net:
> > http://www.google.com/search?q=sql+tutorial
> > Possibly it may be also a good idea if you use MS Access to generate
some
> > queries and learn how to modify them for your needs.
> >
> > Adam
> >
> >
> >
> > ----- Original Message -----
> > From: "Oldenburg, Don" <Don.Oldenburg@cacmnet.com>
> > To: "'CodeCharge'" <support@codecharge.com>
> > Sent: Thursday, August 09, 2001 3:35 PM
> > Subject: RE: Total by employee on grid showing all employees
> >
> > > Very cool... Didn't realize I could just create a sql statement of my
> own
> > > on the fly like that and expect it to work within CC.
> > >
> > > Problem... this is the statement I am trying to use... which WORKS --
> > >
> > > SELECT rtatrans.employee_id, sum(rtatrans.amount) as emptotal FROM
> > rtatrans
> > > GROUP by rtatrans.employee_id
> > >
> > > Two problems... I then try to do a join through CC to join
> > > rtatrans.employee_id with EmployeeID from cacmEmployee table. It
> doesn't
> > > work. So I'm assuming I need to do everything I need to do in that
> > > window--as it ignores the other CC settings?
> > >
> > > I tried doing your INNER JOIN by adding: INNER JOIN cacmemployee on
> > > rtatrans.employee_id = cacmemployee.employeeid
> > > but that didn't work.
> > >
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: CodeCharge [mailto:support@codecharge.com]
> > > Sent: Thursday, August 09, 2001 4:46 PM
> > > To: Oldenburg, Don
> > > Subject: Re: Total by employee on grid showing all employees
> > >
> > >
> > > Please use SQL query instead of table in the Grid.
> > >
> > > If you are not familiar with how to write SQL queries, please try MS
> > Access.
> > > It is a great tool for quick, visual query design.
> > > For example i just created an MS Access Query where I selected "bugs"
> and
> > > "employees" table, then selected couple fields and asked for a Total
by
> > > employee_name. MS Access created an SQL query:
> > > SELECT employees.employee_name, Count(bugs.bug_id) AS
CountOfbug_id
> > FROM
> > > employees INNER JOIN bugs ON employees.employee_id = bugs.assigned_to
> > GROUP
> > > BY employees.employee_name;
> > >
> > > which you can cleanup and place into CodeCharge, Form Properties / SQL
> > > section.
> > >
> > > Adam S.
> > > YS/CC
> > >
> > >
> > > "Don Oldenburg" <don.oldenburg@cacmnet.com> wrote in message
> > >news:9ku3ok$ve4$1@news.codecharge.com...
> > > > NOW -- my problem is to create a screen which gives sum by users to
be
> > > > viewed by administration. Summing by one employee, only his/her
> > > > transactions was fairly easy... any further thoughts on how to
> > implement
> > > > something which will do the same thing, but in a broader level --
for
> > all
> > > > employees, on one screen/report?
> > > >
> > > > DOn
> > > >
> > > >
> > > >
> > >
> >
> >
>
>
|
|
|
|
Don Oldenburg
|
| Posted: 08/10/2001, 6:11 AM |
|
well, you can tell I was here way to long...
I was selecting the userid field, instead of the username field... which
would explain why I got a number, and not a name... SIGH! It was a 14 hour
day yesterday -- please excuse my sometimes frequent moments of dumbness!
DJ
"CodeCharge" <support@codecharge.com> wrote in message
news:9kv514$357$1@news.codecharge.com...
> OK. I just found your previous email mentioning that you use MS SQL
> In such case, I'd like to know how you determine that your SQL doesn't
work
>
> If CodeCharge accepts your SQL after you click "OK" in Form Properties,
then
> it should be OK.
> If you like to test the query in MS SQL, you can view Table and click
"SQL"
> icon in the SQL Enterprise Manager, then enter your query.
>
>
>
> "Don Oldenburg" <don.oldenburg@cacmnet.com> wrote in message
>news:9kv46t$1h8$1@news.codecharge.com...
> > I'm using Microsoft SQL Server. I've been looking at the tuturials...
> and
> > my syntax seems right... How do I know if my SQL server can do this.
> And
> > if it can't.. how do I substitute the numbers -- for names? etc?
> >
> > Don
> >
> >
> > "CodeCharge" <support@codecharge.com> wrote in message
> >news:9kv402$167$1@news.codecharge.com...
> > > Hi Don,
> > >
> > > (per your email)
> > > The SQL created by MS Access may need to be cleaned up in some cases.
> > > For example INNER JOIN is not supported by all databases and I don't
> know
> > > which one you are using.
> > > Usually this should work:
> > > SELECT employees.employee_name, Count(bugs.bug_id) AS bugtotal
FROM
> > > employees WHERE employees.bug_id=bugs.bug_id GROUP BY
> > > employees.employee_name;
> > >
> > > As you noticed, once you use your own SQL statement, the Field Joins
> will
> > > not work, because CodeCharge is unable to understand your own SQL
query
> > and
> > > modify it for this case.
> > > If you need additional fields, they need to be included in the SQL
> query.
> > >
> > > We may not be able to help you with all your SQL needs, so please
> consider
> > > referging to various SQL tutorial on the net:
> > > http://www.google.com/search?q=sql+tutorial
> > > Possibly it may be also a good idea if you use MS Access to generate
> some
> > > queries and learn how to modify them for your needs.
> > >
> > > Adam
> > >
> > >
> > >
> > > ----- Original Message -----
> > > From: "Oldenburg, Don" <Don.Oldenburg@cacmnet.com>
> > > To: "'CodeCharge'" <support@codecharge.com>
> > > Sent: Thursday, August 09, 2001 3:35 PM
> > > Subject: RE: Total by employee on grid showing all employees
> > >
> > > > Very cool... Didn't realize I could just create a sql statement of
my
> > own
> > > > on the fly like that and expect it to work within CC.
> > > >
> > > > Problem... this is the statement I am trying to use... which
WORKS --
> > > >
> > > > SELECT rtatrans.employee_id, sum(rtatrans.amount) as emptotal FROM
> > > rtatrans
> > > > GROUP by rtatrans.employee_id
> > > >
> > > > Two problems... I then try to do a join through CC to join
> > > > rtatrans.employee_id with EmployeeID from cacmEmployee table. It
> > doesn't
> > > > work. So I'm assuming I need to do everything I need to do in that
> > > > window--as it ignores the other CC settings?
> > > >
> > > > I tried doing your INNER JOIN by adding: INNER JOIN cacmemployee on
> > > > rtatrans.employee_id = cacmemployee.employeeid
> > > > but that didn't work.
> > > >
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: CodeCharge [mailto:support@codecharge.com]
> > > > Sent: Thursday, August 09, 2001 4:46 PM
> > > > To: Oldenburg, Don
> > > > Subject: Re: Total by employee on grid showing all employees
> > > >
> > > >
> > > > Please use SQL query instead of table in the Grid.
> > > >
> > > > If you are not familiar with how to write SQL queries, please try MS
> > > Access.
> > > > It is a great tool for quick, visual query design.
> > > > For example i just created an MS Access Query where I selected
"bugs"
> > and
> > > > "employees" table, then selected couple fields and asked for a Total
> by
> > > > employee_name. MS Access created an SQL query:
> > > > SELECT employees.employee_name, Count(bugs.bug_id) AS
> CountOfbug_id
> > > FROM
> > > > employees INNER JOIN bugs ON employees.employee_id =
bugs.assigned_to
> > > GROUP
> > > > BY employees.employee_name;
> > > >
> > > > which you can cleanup and place into CodeCharge, Form Properties /
SQL
> > > > section.
> > > >
> > > > Adam S.
> > > > YS/CC
> > > >
> > > >
> > > > "Don Oldenburg" <don.oldenburg@cacmnet.com> wrote in message
> > > >news:9ku3ok$ve4$1@news.codecharge.com...
> > > > > NOW -- my problem is to create a screen which gives sum by users
to
> be
> > > > > viewed by administration. Summing by one employee, only his/her
> > > > > transactions was fairly easy... any further thoughts on how to
> > > implement
> > > > > something which will do the same thing, but in a broader level --
> for
> > > all
> > > > > employees, on one screen/report?
> > > > >
> > > > > DOn
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> >
> >
>
>
|
|
|
|
|
