I am getting incorrect responses to complicated Count() Query...

[Gary B]

MASTER Table: CHARGES
Fields: ChargesKey, ChargesDate

Data: Row1 = 1, 01/15/2004
Row2 = 2, 03/20/2004
Row3 = 3, 04/25/2004


DETAIL Table: SERVICES
Fields: ServicesKey, ChargesKey, ProvidersKey

Data: Row1 = 1, 1, 2
Row2 = 2, 1, 5
Row3 = 3, 1, 3

Row4 = 4, 2, 5
Row5 = 5, 2, 5

Row6 = 6, 3, 1
Row7 = 7, 3, 4
Row8 = 8, 3, 2
Row9 = 9, 3, 5

These Tables are Related on ChargesKey

I need to return the Count of CHARGES(ChargesKey) for each ProvidersKey.

In the above example, the result should be 1 for the following condition

WHERE (ChargesDate BETWEEN #03/20/2004# And #03/20/2004#) and (ProvidersKey
= 5)

Thank you so much!

 

[Michel Walsh]

Hi,



SELECT COUNT(*)
FROM Charges
WHERE ChargesKey IN(
SELECT ServicesKey
FROM Services
WHERE (ChargesDate BETWEEN #03/20/2004# And #03/20/2004#)
AND (ProvidersKey = 5) )



Note that you asked: "for each ProvidersKey" and further along, specify
ONE provider (ProvidersKey = 5). I assume the latter one is the one you
really want.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


I cut and paste your WHERE clause too fast... the following would work
better:



SELECT COUNT(*)
FROM Charges AS a
WHERE (a.ChargesDate BETWEEN #03/20/2004# And #03/20/2004#)
AND EXISTS(
SELECT *
FROM Services As b
WHERE (b.ProvidersKey = 5)
AND (a.ChargesKey=b.ChargesKey) )




Vanderghast, Access MVP

 

[Gary B]

Michel you are awesome!

The final stage.

MASTER Table: CHARGES
Fields: ChargesKey, ChargesDate

Data: Row1 = 1, 01/15/2004
Row2 = 2, 03/20/2004
Row3 = 3, 04/25/2004


DETAIL Table: SERVICES
Fields: ServicesKey, ChargesKey, ProvidersKey

Data: Row1 = 1, 1, 2
Row2 = 2, 1, 5
Row3 = 3, 1, 3

Row4 = 4, 2, 5
Row5 = 5, 2, 5

Row6 = 6, 3, 1
Row7 = 7, 3, 4
Row8 = 8, 3, 2
Row9 = 9, 3, 5

These above Tables are Related on ChargesKey

LOOKUP Table: PROVIDERS
Fields: ProvidersKey, Name

Data: Row1 = 1, "BOB"
Row2 = 2, "MARY"
Row3 = 3, "SAM"
Row4 = 2, "JIM"
Row5 = 3, "ANN"


I need to return the Count of CHARGES(ChargesKey) for each ProvidersKey.

I also need to return the Providers Name

In the above example, the result should be 1 for ProvidersKey 5 (ANN) using
the following condition

WHERE (ChargesDate BETWEEN #03/20/2004# And #03/20/2004#)

Thank you so much!

 

[Michel Walsh]

Hi,


Why not SAM too (and why ANN at all, since ProviderKey =3, for Ann, not
5 ) ? Assuming Ann is 5, not 3:



SELECT COUNT(*), c.Name
FROM
( ( SELECT DISTINCT ChargesKey
FROM Charges
WHERE ChargesDate BETWEEN #03/20/2004#
And #03/20/2004#
) As a INNER JOIN
( SELECT DISTINCT ChargesKey, ProvidersKey
FROM Services WHERE ProviderKey=5
) As b
ON a.chargesKey=b.chargesKey
) INNER JOIN Providers As c
ON c.ProvidersKey=b.ProvidersKey



could do the job (untested).





Hoping it may hlep,
Vanderghast, Access MVP

 

[Gary B]

I actullay want a resultset like this, if i were to pass a wider date range
and NO ProvidersKey condition:

WHERE (ChargesDate BETWEEN #01/01/2004# And #12/31/2004#)

Fields: ProviderName, ChargesCount

"BOB", 1
"MARY", 2
"SAM", 1
"JIM", 2
"ANN", 3

=======================================================================

MASTER Table: CHARGES
Fields: ChargesKey, ChargesDate

Data: Row1 = 1, 01/15/2004
Row2 = 2, 03/20/2004
Row3 = 3, 04/25/2004


DETAIL Table: SERVICES
Fields: ServicesKey, ChargesKey, ProvidersKey

Data: Row1 = 1, 1, 2
Row2 = 2, 1, 5
Row3 = 3, 1, 3

Row4 = 4, 2, 5
Row5 = 5, 2, 5

Row6 = 6, 3, 1
Row7 = 7, 3, 4
Row8 = 8, 3, 2
Row9 = 9, 3, 5

Above Tables are Related on ChargesKey

LOOKUP Table: PROVIDERS
Fields: ProvidersKey, Name

Data: Row1 = 1, "BOB"
Row2 = 2, "MARY"
Row3 = 3, "SAM"
Row4 = 2, "JIM"
Row5 = 3, "ANN"

 

[Michel Walsh]

Hi,




Since I am not sure if you wish the total count or the number of
different chargeskey, I supply both, with a crosstab:

TRANSFORM Count(*) AS c
SELECT Providers.Name ,
Count(c) AS DistinctCount,
Count(*) AS TotalCount
FROM (Services INNER JOIN Charges ON Services.ChargesKey =
Charges.ChargesKey)
INNER JOIN Providers ON Services.ProvidersKey =
Providers.ProvidersKey
WHERE (((Charges.ChargesDate) Between #1/1/2004# And #12/31/2004#))
GROUP BY Providers.Name
PIVOT Services.ChargesKey In (Null)




and the result is:



Query1
Name DistinctCount TotalCount <>
ann 2 3

bob 1 1

jim 2 2

mary 2 2

sam 1 1



Note that for Ann, there are 3 records in services ( rows 4, 5 and 9) with
chargeskey= 2, 2, 3. There are 3 chargeKeys (the third column of the
result), but only 2 different values (2 and 3), as reported by the second
column of the result, DistinctCount. Forget about the fourth column, <>.

My Providers table is different than yours:

Providers
ProvidersKey Name
1 bob
2 mary
3 sam
4 jim
5 ann

If you want to add a condition to the ProviderKeys, in that case, you can
add " AND ProvidersKey=5 " in the WHERE clause, as example (but as said
previously, other formulation may be preferable).


Similar, if you do not want the distinct count, you can use the simpler
formulation:

SELECT Providers.Name ,
Count(*) AS TotalCount
FROM (Services INNER JOIN Charges ON Services.ChargesKey =
Charges.ChargesKey)
INNER JOIN Providers ON Services.ProvidersKey =
Providers.ProvidersKey
WHERE (((Charges.ChargesDate) Between #1/1/2004# And #12/31/2004#))
GROUP BY Providers.Name



Hoping it may help,
Vanderghast, Access MVP

 

[Gary B]

Sir (Michel),

I am astonished with your SQL skills!

You should know that the CrossTab query also worked,
but this one is better for me as I plan on someday migrating
to MSDE or SQL Server.

I did need the Distinct Count().

Could you please tell me what is going on here though,
I never saw anything like this. You are using subqueries
as table names in a FROM clause?

Thank you again!

 

[Michel Walsh]

Hi,

It is just a matter to apply the WHERE clause before making the join,
making the result of the join smaller (so faster). It would be nice to do
the WHERE clause after the join, as usual. You can also use a saved query
(or a view) instead of the sub-query in the FROM clause:

SELECT ...
FROM savedQuery ....

or


SELECT ...
FROM (text_of_the_saved_query_here) As alias ...


The second syntax call the sub-query a "virtual table", since the save
query, or the SELECT statement, acts "like" a table "built" for our "case".



Hoping it may help,
Vanderghast, Access MVP

 

[Gary B]

Last point...

Is this form of SQL usage ANSI compliant?

Will it work in MS-SQL Server

Thanks again!

 

[Michel Walsh]

Hi,


Yes, should work too with MS SQL Server, except that the 'syntax' for a
constant date is different.

Vanderghast, Access MVP

 

[Gary B]

I do not use ACCESS directly, but rather an application through JET.

Regarding MSDE:

Do you consider this a logical upgrade path to ACCESS/JET?

Is MSDE single file based like ACCESS/JET?

What is meant by "Embedded Database"?

I appreciate your support!

Gary

 

[Michel Walsh]

Hi,

I consider MSDE to be use to deploy a solution based on MS SQL Server where
there will be no better version on the target system. MSDE is based on MS
SQL Server, not on Jet. For development, without Access, I would rather
suggest you use MS SQL Server Developer version, rather than MSDE, because
you would then get all the development tools that may be very useful, and
that are not shipped with MSDE.

For a single use, over a desktop, I would consider JET a better choice than
MS SQL Server (any version), for its simplicity, and speed. Sure, outside
Access, you loose a very good advantage: the ability to use VBA functions
(even user defined ones) inside the queries.

Among the major advantages of MS SQL Server based solution over Jet are
about "stability" in a multi-users environment (much less possibilities of
some data corruption, but at a price of being a little bit slower due to
extra overhead). It can also be used where the clients are interested in a
very small subset of data (but also, that means clients are aware of the
limited subset BEFORE asking for the data), or where the connection is not
reliable.



Hoping it may help,
Vanderghast, Access MVP

 

[Gary B]

Michel,

I would LOVE to keep ACCESS/JET, but we have hundreds of clients and all too
often we get database corruptions resulting in data-loss.

I love the fact that deploying JET is free as would MSDE be free.

I love the fact that JET is single file based, this makes it very portable
for clients to take their file/work home.

Our clients usually have 1 to 3 stations, in rare cases, a 10-12 workstation
setup, I believe JET can survive up to 20 stations?

Do you know of high-speed ways of safeguarding the ACCESS/JET file?

Gary

 

[Michel Walsh]

Hi,


Past 10 connections, you need a real OS-Server (NT4 Server, W2000
Server, or W2003 Server) for the back-end, to avoid the max number of
connection limit of 10 with a share folder, on a non-server OS. With an
increased number of users MODIFYING the data (reading it is far less a
problem), you increase the possibility that one user starts a modification,
get interrupted, and go to lunch or move to other tasks, leaving some
records "locked". MS SQL Server can automatically time-out pending
transaction, Jet cannot, by itself.

Access 2003 now has a Tools | Database Utilities | Backup Database...
menu-command available... but I never tried it and I suspect all the
objects, and tables, should be "inert" (readings are probably ok, updating
probably an absolute no-way) when the backup occurs (by opposition, you can
backup a database on MS SQL Server 2000 even when it is running at "full
spin" ). You may ask in ThirdParty, or maybe even in Replication newsgroups
(Replication can be seen as some kind of backup) associated with Access +
Jet.



Hoping it may help,
Vanderghast, Access MVP