Is this possible? please help (MS access query of sql database)

I work for an organisation that uses a bespoke document imaging system, the
database of which is an MS sql server.
We have MS Access and already use it for some querying of the database.
The database comprises a large number of distinct cases, which are
differentiated by case reference numbers, in one field (table?) of the
database. Each of these cases may have many documents associated with it,
denoted by the reference number, and these documents may be "new", "pending"
or "complete" shown in another data field.
We need to know how many cases have work outstanding on them.
Our problem is that our bespoke software will only count the number of
documents of each status, and not the cases.

Is it possible to design an MS Access query which will count the number of
different reference numbers which have any "new" documents associated, but
wont count each case more than once?

I am reasonably computer-savvy, I just don't know Access or SQL..
If I know it is possible, I don't mind putting in the effort to find out
how. I just don't want to waste time barking up the wrong tree ;-)

Of course any advice about how this would be achieved, such as pointers to
the right parts of the MS Access helpfiles, or to relevant websites would be
greatly appreciated. Some quick code would be even better...
Also, if there is any 3rd-party software which could easily do this, I need
help discovering it...I have looked long and hard, but don't know enough
about what I am looking for.
Yours in hope..
--
anthonyberet
Please reply in the groups, as my Usenet email address is not working at the
moment.anthonyberet wrote:
> I work for an organisation that uses a bespoke document imaging
> system, the database of which is an MS sql server.
> We have MS Access and already use it for some querying of the
> database.
> The database comprises a large number of distinct cases, which are
> differentiated by case reference numbers, in one field (table?) of the
> database. Each of these cases may have many documents associated with
> it, denoted by the reference number, and these documents may be
> "new", "pending" or "complete" shown in another data field.
> We need to know how many cases have work outstanding on them.
> Our problem is that our bespoke software will only count the number of
> documents of each status, and not the cases.
> Is it possible to design an MS Access query which will count the
> number of different reference numbers which have any "new" documents
> associated, but wont count each case more than once?
> I am reasonably computer-savvy, I just don't know Access or SQL..
> If I know it is possible, I don't mind putting in the effort to find
> out how. I just don't want to waste time barking up the wrong tree ;-)
> Of course any advice about how this would be achieved, such as
> pointers to the right parts of the MS Access helpfiles, or to
> relevant websites would be greatly appreciated. Some quick code would
> be even better...
> Also, if there is any 3rd-party software which could easily do this,
> I need help discovering it...I have looked long and hard, but don't
> know enough about what I am looking for.
> Yours in hope..

You'd probably be better off in an MS Access group - try
microsoft.public.access|||Gordon Burgess-Parker wrote:
> anthonyberet wrote:
>> I work for an organisation that uses a bespoke document imaging
>> system, the database of which is an MS sql server.
>> We have MS Access and already use it for some querying of the
>> database.
>> The database comprises a large number of distinct cases, which are
>> differentiated by case reference numbers, in one field (table?) of
>> the database. Each of these cases may have many documents associated
>> with it, denoted by the reference number, and these documents may be
>> "new", "pending" or "complete" shown in another data field.
>> We need to know how many cases have work outstanding on them.
>> Our problem is that our bespoke software will only count the number
>> of documents of each status, and not the cases.
>>
>> Is it possible to design an MS Access query which will count the
>> number of different reference numbers which have any "new" documents
>> associated, but wont count each case more than once?
>>
>> I am reasonably computer-savvy, I just don't know Access or SQL..
>> If I know it is possible, I don't mind putting in the effort to find
>> out how. I just don't want to waste time barking up the wrong tree
>> ;-)
>>
>> Of course any advice about how this would be achieved, such as
>> pointers to the right parts of the MS Access helpfiles, or to
>> relevant websites would be greatly appreciated. Some quick code would
>> be even better...
>> Also, if there is any 3rd-party software which could easily do this,
>> I need help discovering it...I have looked long and hard, but don't
>> know enough about what I am looking for.
>> Yours in hope..
> You'd probably be better off in an MS Access group - try
> microsoft.public.access

And I didn't see ALL those crossposts.... doh!|||On Sat, 18 Oct 2003 01:03:05 +0100, "anthonyberet"
<witfb001@.sneakemail.com> wrote:

>I work for an organisation that uses a bespoke document imaging system, the
>database of which is an MS sql server.
>We have MS Access and already use it for some querying of the database.
>The database comprises a large number of distinct cases, which are
>differentiated by case reference numbers, in one field (table?) of the
>database. Each of these cases may have many documents associated with it,
>denoted by the reference number, and these documents may be "new", "pending"
>or "complete" shown in another data field.
>We need to know how many cases have work outstanding on them.
>Our problem is that our bespoke software will only count the number of
>documents of each status, and not the cases.
>Is it possible to design an MS Access query which will count the number of
>different reference numbers which have any "new" documents associated, but
>wont count each case more than once?
>I am reasonably computer-savvy, I just don't know Access or SQL..
>If I know it is possible, I don't mind putting in the effort to find out
>how. I just don't want to waste time barking up the wrong tree ;-)
>Of course any advice about how this would be achieved, such as pointers to
>the right parts of the MS Access helpfiles, or to relevant websites would be
>greatly appreciated. Some quick code would be even better...
>Also, if there is any 3rd-party software which could easily do this, I need
>help discovering it...I have looked long and hard, but don't know enough
>about what I am looking for.
>Yours in hope..

this is very difficult without any idea of the tables involved, but
lets try;

Table: Cases
(caseNumber integer) *PK

Table: Documents
(docNumber integer,
caseNumber integer, *FK
status varchar(20))

SELECT COUNT(1) FROM Cases
WHERE caseNumber IN
(SELECT caseNumber
FROM Documents WHERE status='New')

Of course if your tables don't look like this then you need another
approach...|||Lyndon Hills wrote:
> On Sat, 18 Oct 2003 01:03:05 +0100, "anthonyberet"
> <witfb001@.sneakemail.com> wrote:
>> I work for an organisation that uses a bespoke document imaging
>> system, the database of which is an MS sql server.
>> We have MS Access and already use it for some querying of the
>> database. The database comprises a large number of distinct cases,
>> which are differentiated by case reference numbers, in one field
>> (table?) of the database. Each of these cases may have many
>> documents associated with it, denoted by the reference number, and
>> these documents may be "new", "pending" or "complete" shown in
>> another data field. We need to know how many cases have work
>> outstanding on them. Our problem is that our bespoke software will
>> only count the number of documents of each status, and not the cases.
>>
>> Is it possible to design an MS Access query which will count the
>> number of different reference numbers which have any "new" documents
>> associated, but wont count each case more than once?
>>
>> I am reasonably computer-savvy, I just don't know Access or SQL..
>> If I know it is possible, I don't mind putting in the effort to find
>> out how. I just don't want to waste time barking up the wrong tree
>> ;-)
>>
>> Of course any advice about how this would be achieved, such as
>> pointers to the right parts of the MS Access helpfiles, or to
>> relevant websites would be greatly appreciated. Some quick code
>> would be even better... Also, if there is any 3rd-party software
>> which could easily do this, I need help discovering it...I have
>> looked long and hard, but don't know enough about what I am looking
>> for. Yours in hope..
> this is very difficult without any idea of the tables involved, but
> lets try;
> Table: Cases
> (caseNumber integer) *PK
> Table: Documents
> (docNumber integer,
> caseNumber integer, *FK
> status varchar(20))
> SELECT COUNT(1) FROM Cases
> WHERE caseNumber IN
> (SELECT caseNumber
> FROM Documents WHERE status='New')
> Of course if your tables don't look like this then you need another
> approach...

I think only 2 tables are relevant in the first instance - "reference" and
"status".
Can you rcommend a site where I can read about the functions of the
intructions you have posted?
In particular, the "SELECT COUNT(1) FROM Cases" bit looks very powerful.
However, is this SQL or is it bespoke code used by MS Access?
Thank you for your help.
--
Put "usenet" in the subject-line if you want to mail me, otherwise it will
bounce.
Do you use filesharing networks? If so, please visit my online poll:
http://vote.sparklit.com/web_poll.spark/780772
anthonyberet|||On Mon, 20 Oct 2003 23:33:41 +0100, "anthonyberet"
<witfb001@.sneakemail.com> wrote:

<snip>
>> SELECT COUNT(1) FROM Cases
>> WHERE caseNumber IN
>> (SELECT caseNumber
>> FROM Documents WHERE status='New')
>>
>I think only 2 tables are relevant in the first instance - "reference" and
>"status".
>Can you rcommend a site where I can read about the functions of the
>intructions you have posted?
>In particular, the "SELECT COUNT(1) FROM Cases" bit looks very powerful.
>However, is this SQL or is it bespoke code used by MS Access?
>Thank you for your help.

I would google for sql tutorials. www.sqlcourse.com looks basic. Also
there should be some of this at least in the access help files. I
guess you do need to know what your looking for though. Above, all the
words in capitals are sql keywords, and they should be in the help
files. The IN is a subselect which could be replaced with EXISTS and a
slightly different syntax.

SELECT COUNT() FROM, just counts the number of rows that meet the
conditions. It just returns one number, not the actual rows of data.
There are similar options like MAX, MIN AVERAGE which apply to number
columns. A quick word of warning if you plan to use them, be careful
of the case where the number column is null. Average in particular may
give wrong results.

SQL has relatively few keywords, although each of the big
manufacturers have added their own.sql

Is this possible?

I am looking into some possible ideas to get rid of a lot of paperwork for a
volunteer organisation I work for, and just want to know if this is possible
to do with MS Access?

Basically, at the end of every month, each volunteer must hand in their
expense claims along with their monthly performance (hours worked etc...)
This is all currently done on paper.

Now, what would be ideal is for all of this to be done on a network-based
Database. So basically, each individual has restricted access to the
database and can enter their expenses and performance details and then this
can be authorised by a line manage (not too sure how you can electronically
sign off expenses). Then add the end of a month, the boss can simply print
out everybody's claims.

Is this possible to create in MS Access? It will be network-based and there
will need to be different levels of access (ie. restricted access for
individuals, semi-restricted access for line managers and complete access
for boss)"Neil Greenough" <neilgreenough@.btopenworld.com> wrote in message
news:ckj3au$72m$1@.hercules.btinternet.com...
>I am looking into some possible ideas to get rid of a lot of paperwork for
>a
> volunteer organisation I work for, and just want to know if this is
> possible
> to do with MS Access?
> Basically, at the end of every month, each volunteer must hand in their
> expense claims along with their monthly performance (hours worked etc...)
> This is all currently done on paper.
> Now, what would be ideal is for all of this to be done on a network-based
> Database. So basically, each individual has restricted access to the
> database and can enter their expenses and performance details and then
> this
> can be authorised by a line manage (not too sure how you can
> electronically
> sign off expenses). Then add the end of a month, the boss can simply print
> out everybody's claims.
> Is this possible to create in MS Access? It will be network-based and
> there
> will need to be different levels of access (ie. restricted access for
> individuals, semi-restricted access for line managers and complete access
> for boss)

This is an MSSQL group, so you will probably get a better response in an
Access group. Having said that, if security is a concern, you might want to
consider using Access as a front-end interface only, with all the data in
MSDE (a free, 'stripped down' version of MSSQL for small applications). This
would give better security, scalability and data integrity, but it would
also add complexity, so it depends on what skills are available in your
organization, and how important those features are to you. A good Access
implementation can be more useful than a poor MSSQL one.

http://msdn.microsoft.com/library/d...c_msdeintro.asp

Simon|||Apologies Simon.

I just thought that the subject of SQL servers kinda overlapped that of MS
Access.

Thanks a lot for the advice.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:416d175a_1@.news.bluewin.ch...
> "Neil Greenough" <neilgreenough@.btopenworld.com> wrote in message
> news:ckj3au$72m$1@.hercules.btinternet.com...
> >I am looking into some possible ideas to get rid of a lot of paperwork
for
> >a
> > volunteer organisation I work for, and just want to know if this is
> > possible
> > to do with MS Access?
> > Basically, at the end of every month, each volunteer must hand in their
> > expense claims along with their monthly performance (hours worked
etc...)
> > This is all currently done on paper.
> > Now, what would be ideal is for all of this to be done on a
network-based
> > Database. So basically, each individual has restricted access to the
> > database and can enter their expenses and performance details and then
> > this
> > can be authorised by a line manage (not too sure how you can
> > electronically
> > sign off expenses). Then add the end of a month, the boss can simply
print
> > out everybody's claims.
> > Is this possible to create in MS Access? It will be network-based and
> > there
> > will need to be different levels of access (ie. restricted access for
> > individuals, semi-restricted access for line managers and complete
access
> > for boss)
> This is an MSSQL group, so you will probably get a better response in an
> Access group. Having said that, if security is a concern, you might want
to
> consider using Access as a front-end interface only, with all the data in
> MSDE (a free, 'stripped down' version of MSSQL for small applications).
This
> would give better security, scalability and data integrity, but it would
> also add complexity, so it depends on what skills are available in your
> organization, and how important those features are to you. A good Access
> implementation can be more useful than a poor MSSQL one.
>
http://msdn.microsoft.com/library/d...-us/dnacc2k2/ht
ml/odc_msdeintro.asp
> Simon|||"Neil Greenough" <neilgreenough@.btopenworld.com> wrote in message
news:ckj8vn$elq$1@.hercules.btinternet.com...
> Apologies Simon.
> I just thought that the subject of SQL servers kinda overlapped that of MS
> Access.
> Thanks a lot for the advice.

<snip
No need to apologize - they're often used together, and many people do
associate them with each other somewhat, rightly or wrongly. Since this
group is an MSSQL one, you're unlikely to get responses to pure Access
questions; if you are using them together, then people here will generally
prefer solutions which rely on MSSQL functionality, not on Access.

Simon

Is this a sound design for Reporting Services

Hi,
We are looking at deploying Report Services within our organisation
however, we have to support multiple clients, operating systems and
authentication methods, so we are unable to use the in-built Windows
Security or Reporting Services Portal.
To resolve this issue I have designed a WebService that acts as a
facade / proxy to the Reporting Services Web Service and uses a
specifically created Windows Account to authenticate against the Report
Server.
Then the various consumers of our reports (home grown sites plus third
party portals) request the reports from our Web Service which supports
the various authentication methods required (LDAP / Single Sign-on etc)
and manages the various Roles / Permissions for the reports.
The Report Server is then securely tied down to the single Windows
Account that is used by our web service.
Does the above sound like a good solution or has anyone else used a
similar approach?
Thanks
RussPersonally, I am an advocate against building any additional facades on top
of RS. The moment you introduce another "wrapper", you are pretty much
kissing good-bye perhaps 30% of the RS feature set. For example, you can't
use the RS 2005 report viewers, interactive features, etc., plus in many
cases you will find yourself just reinventing the wheel (e.g. to handle
parameters, printing, etc.) Instead, in your case, I would gravitate toward
replacing the default Windows-based authentication with custom
authentication. If your reporting clients can invoke programatically your
web service, they should be able to capture the authentication cookie and
pass it back.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"russ" <russ.1@.excite.com> wrote in message
news:1132222181.477746.69640@.g44g2000cwa.googlegroups.com...
> Hi,
> We are looking at deploying Report Services within our organisation
> however, we have to support multiple clients, operating systems and
> authentication methods, so we are unable to use the in-built Windows
> Security or Reporting Services Portal.
> To resolve this issue I have designed a WebService that acts as a
> facade / proxy to the Reporting Services Web Service and uses a
> specifically created Windows Account to authenticate against the Report
> Server.
> Then the various consumers of our reports (home grown sites plus third
> party portals) request the reports from our Web Service which supports
> the various authentication methods required (LDAP / Single Sign-on etc)
> and manages the various Roles / Permissions for the reports.
> The Report Server is then securely tied down to the single Windows
> Account that is used by our web service.
> Does the above sound like a good solution or has anyone else used a
> similar approach?
> Thanks
> Russ
>|||Hi Teo,
Thanks for the reply. I should have mentioned that we are using 2000
and not 2005 and therefore are unable to use the RS feature set due to
the poor non-IE support.
Cheers
Russ|||Nonetheless, IMO, one should view the RS web service as the ultimate facade
and avoid building additional facades on top of it unless it is absolutely
necessary. There are functionality, security, deployment, and maintenance
tradeoffs once you go for the "facade-on-top-of-facade" approach.
BTW, you can still use URL addressability with non-web clients to get the
full RS 2000 feature set. For example, a COM or .NET application can use the
Web Browser control. That said, given the fact that URL addressability is
deprecated in RS 2005 in favor of SOAP, I would strongly consider moving
forward with RS 2005 and the Report Viewer control if possible.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"russ" <russ.1@.excite.com> wrote in message
news:1132238241.374215.84200@.g14g2000cwa.googlegroups.com...
> Hi Teo,
> Thanks for the reply. I should have mentioned that we are using 2000
> and not 2005 and therefore are unable to use the RS feature set due to
> the poor non-IE support.
> Cheers
> Russ
>|||many thanks for the advice :)
I think a push for 2005 is in ordersql