Let's say I have a table (calldwnl) with 3 fields:

acctnum, calldate, callsts



An acctnum can have multiple entries; it can also have the same callsts

on more than one entry.  I wrote a query to create a table that would

contain ONE entry per acctnum for any account that had ever had a

callsts of "ADA", and I wanted to know the last (most current) date an

account had had an entry of that status.  The table I was querying is a

linked table from a non-ACCESS proprietary database.



This is the code:



SELECT ACCTNUM, MAX(CALLDATE) AS MAXOFCALLDATE

INTO ADASTSDATES

FROM CALLDWNL

WHERE CALLSTS = "ADA"

GROUP BY ACCTNUM



The results of this query produced more than one record for some account

numbers. Shouldn't that have been impossible since the acctnum field was

the only field I was grouping on?  Also, there were definitely some

records for acctnums with the status I was selecting that were not in

the created table at all - over 200 out of 800 in fact.  I have relied

on the data from queries like this many times, but these results are

blatantly incorrect.  I ran the code more than once and on different

PCs.  Each time the number of records returned was slightly different

but since I am working with a live table, I can't fault this too much -

but each time produced similar errors.



What gives here?  I hate to lose faith in such a simple, straightforward

method.  Am I doing something wrong?

Diane,



Your query looks just fine. 



Possible reason for more than one record:

You could actually have multiple records for an acctnum. If an acctnum

has more than one  record for callsts "ADA" and the same calldate, then

your query will display those records.  In other words, if acctnum 101

has 5 records with a calldate of 03/10/2000 and callsts of "ADA", then

your query will display 5 records for acctnum 101. 



The only reason I can think of that some records are not being found by

the query is that the criteria callsts="ADA" is not being met.  Please

check the table and make sure that there are no other characters before

or after "ADA".  If there is a space before the first A in ADA (" ADA"),

then this record will not be found.  Also if there are any other

characters after the first "ADA" ("ADA1", "ADAB", "ADABADA"), this

record will not be found either.  You might want to change the WHERE

portion of the query to WHERE callsts Like "*ADA*"



I honestly cannot think of a reason why you would have multiple records

for some acctnums.  What type of database is the linked table in?  Is

the calldate a text field or a date/time field.  When you looked at the

data in the ADASTSDATES table, did you notice anything unusual about

those multiple records?  Have you tried performing a select query on the

ADASTSDATES table to see if those duplicate records show up? 

 

SELECT acctnum, Max(.calldate) AS MaxOfcalldate

FROM ADASTSDATES 

GROUP BY acctnum



Let us know what you come up with and I hope this will help.



John Ruff - The Eternal Optimist :-) 





-----Original Message-----

From: Diane Roberts [mailto:Diane.Roberts@c...]

Sent: Friday, September 21, 2001 12:08 PM

To: Access

Subject: [access] Group By concerns





Let's say I have a table (calldwnl) with 3 fields:

acctnum, calldate, callsts



An acctnum can have multiple entries; it can also have the same callsts

on more than one entry.  I wrote a query to create a table that would

contain ONE entry per acctnum for any account that had ever had a

callsts of "ADA", and I wanted to know the last (most current) date an

account had had an entry of that status.  The table I was querying is a

linked table from a non-ACCESS proprietary database.



This is the code:



SELECT ACCTNUM, MAX(CALLDATE) AS MAXOFCALLDATE

INTO ADASTSDATES

FROM CALLDWNL

WHERE CALLSTS = "ADA"

GROUP BY ACCTNUM



The results of this query produced more than one record for some account

numbers. Shouldn't that have been impossible since the acctnum field was

the only field I was grouping on?  Also, there were definitely some

records for acctnums with the status I was selecting that were not in

the created table at all - over 200 out of 800 in fact.  I have relied

on the data from queries like this many times, but these results are

blatantly incorrect.  I ran the code more than once and on different

PCs.  Each time the number of records returned was slightly different

but since I am working with a live table, I can't fault this too much -

but each time produced similar errors.



What gives here?  I hate to lose faith in such a simple, straightforward

method.  Am I doing something wrong?

Diane,



Please disregard the first paragraph of my last message.  I forgot to

delete it before I sent it.  I'm reposting my correct response to your

questions.



Your query looks just fine. 



The only reason I can think of that some records are not being found by

the query is that the criteria callsts="ADA" is not being met.  Please

check the table and make sure that there are no other characters before

or after "ADA".  If there is a space before the first A in ADA (" ADA"),

then this record will not be found.  Also if there are any other

characters after the first "ADA" ("ADA1", "ADAB", "ADABADA"), this

record will not be found either.  You might want to change the WHERE

portion of the query to WHERE callsts Like "*ADA*"



I honestly cannot think of a reason why you would have multiple records

for some acctnums.  What type of database is the linked table in?  Is

the calldate a text field or a date/time field.  When you looked at the

data in the ADASTSDATES table, did you notice anything unusual about

those multiple records?  Have you tried performing a select query on the

ADASTSDATES table to see if those duplicate records show up? 

 

SELECT acctnum, Max(.calldate) AS MaxOfcalldate

FROM ADASTSDATES 

GROUP BY acctnum



Let us know what you come up with and I hope this will help.



John Ruff - The Eternal Optimist :-) 





-----Original Message-----

From: Diane Roberts [mailto:Diane.Roberts@c...]

Sent: Friday, September 21, 2001 12:08 PM

To: Access

Subject: [access] Group By concerns





Let's say I have a table (calldwnl) with 3 fields:

acctnum, calldate, callsts



An acctnum can have multiple entries; it can also have the same callsts

on more than one entry.  I wrote a query to create a table that would

contain ONE entry per acctnum for any account that had ever had a

callsts of "ADA", and I wanted to know the last (most current) date an

account had had an entry of that status.  The table I was querying is a

linked table from a non-ACCESS proprietary database.



This is the code:



SELECT ACCTNUM, MAX(CALLDATE) AS MAXOFCALLDATE

INTO ADASTSDATES

FROM CALLDWNL

WHERE CALLSTS = "ADA"

GROUP BY ACCTNUM



The results of this query produced more than one record for some account

numbers. Shouldn't that have been impossible since the acctnum field was

the only field I was grouping on?  Also, there were definitely some

records for acctnums with the status I was selecting that were not in

the created table at all - over 200 out of 800 in fact.  I have relied

on the data from queries like this many times, but these results are

blatantly incorrect.  I ran the code more than once and on different

PCs.  Each time the number of records returned was slightly different

but since I am working with a live table, I can't fault this too much -

but each time produced similar errors.



What gives here?  I hate to lose faith in such a simple, straightforward

method.  Am I doing something wrong?

>Diane, try this:



SELECT ACCTNUM, Max[CALLDATE] AS MAXOFCALLDATE

INTO ADASTSDATES FROM CALLDWNL 

WHERE CALLSTS = "ADA" GROUP BY ACCTNUM, CALLDATE;



need to also group by CALLDATE.

Carol ;-)



 Let's say I have a table (calldwnl) with 3 fields:

> acctnum, calldate, callsts

> 

> An acctnum can have multiple entries; it can also have the same callsts

> on more than one entry.  I wrote a query to create a table that would

> contain ONE entry per acctnum for any account that had ever had a

> callsts of "ADA", and I wanted to know the last (most current) date an

> account had had an entry of that status.  The table I was querying is a

> linked table from a non-ACCESS proprietary database.

> This is the code:

> SELECT ACCTNUM, MAX(CALLDATE) AS MAXOFCALLDATE

> INTO ADASTSDATES > FROM CALLDWNL

> WHERE CALLSTS = "ADA"  GROUP BY ACCTNUM

> 

> The results of this query produced more than one record for some account

> numbers. Shouldn't that have been impossible since the acctnum field was

> the only field I was grouping on?  Also, there were some records for 

acctnums with the status I was selecting that were not in the created 

table at all - over 200 out of 800 in fact.  I have relied on the data 

from queries like this many times, but these results are incorrect. Am I 

doing something wrong?

FWIW, your query looks fine to me too.  Is this a multi-user db?  Is there

any chance that more than one person is dumping records in this table at the

same time?



If none of this clarifies the problem, is there any easy way for you to make

the .mdb file (or one enough like it to repro the problem) available to us?

(But please do not try to attach it to a message to this list--I'm pretty

sure that's verboten.)



-Roy



Roy Pardee

Programmer/Analyst







-----Original Message-----

From: John Ruff [mailto:papparuff@c...]

Sent: Friday, September 21, 2001 3:07 PM

To: Access

Subject: [access] RE: Group By concerns





Diane,



Please disregard the first paragraph of my last message.  I forgot to

delete it before I sent it.  I'm reposting my correct response to your

questions.



Your query looks just fine. 



The only reason I can think of that some records are not being found by

the query is that the criteria callsts="ADA" is not being met.  Please

check the table and make sure that there are no other characters before

or after "ADA".  If there is a space before the first A in ADA (" ADA"),

then this record will not be found.  Also if there are any other

characters after the first "ADA" ("ADA1", "ADAB", "ADABADA"), this

record will not be found either.  You might want to change the WHERE

portion of the query to WHERE callsts Like "*ADA*"



I honestly cannot think of a reason why you would have multiple records

for some acctnums.  What type of database is the linked table in?  Is

the calldate a text field or a date/time field.  When you looked at the

data in the ADASTSDATES table, did you notice anything unusual about

those multiple records?  Have you tried performing a select query on the

ADASTSDATES table to see if those duplicate records show up? 

 

SELECT acctnum, Max(.calldate) AS MaxOfcalldate

FROM ADASTSDATES 

GROUP BY acctnum



Let us know what you come up with and I hope this will help.



John Ruff - The Eternal Optimist :-) 





-----Original Message-----

From: Diane Roberts [mailto:Diane.Roberts@c...]

Sent: Friday, September 21, 2001 12:08 PM

To: Access

Subject: [access] Group By concerns





Let's say I have a table (calldwnl) with 3 fields:

acctnum, calldate, callsts



An acctnum can have multiple entries; it can also have the same callsts

on more than one entry.  I wrote a query to create a table that would

contain ONE entry per acctnum for any account that had ever had a

callsts of "ADA", and I wanted to know the last (most current) date an

account had had an entry of that status.  The table I was querying is a

linked table from a non-ACCESS proprietary database.



This is the code:



SELECT ACCTNUM, MAX(CALLDATE) AS MAXOFCALLDATE

INTO ADASTSDATES

FROM CALLDWNL

WHERE CALLSTS = "ADA"

GROUP BY ACCTNUM



The results of this query produced more than one record for some account

numbers. Shouldn't that have been impossible since the acctnum field was

the only field I was grouping on?  Also, there were definitely some

records for acctnums with the status I was selecting that were not in

the created table at all - over 200 out of 800 in fact.  I have relied

on the data from queries like this many times, but these results are

blatantly incorrect.  I ran the code more than once and on different

PCs.  Each time the number of records returned was slightly different

but since I am working with a live table, I can't fault this too much -

but each time produced similar errors.



What gives here?  I hate to lose faith in such a simple, straightforward

method.  Am I doing something wrong?

I don't want to GROUP BY CALLDATE as well as account number.  I only

want one record returned per account number with the latest call date.

It's entirely possible that the application file that I am linking to

could be updated many times while I am trying to process.  However, the

specific examples I researched of multiple result entries per account

had calldates several months in the past.  Any call record created today

would have todays date.  I can't think how I could reproduce a file for

you to research as I think that the problem centers around it being a

linked file (also the actual file is quite large and I am not sure how

much this affects it either althought it shouldn't)  Let me think about

it.  Thanks for your help

Ah, I somehow missed the fact that your source was not an access table.  I

wonder if it's not an ODBC driver problem (assuming you're using ODBC)?



Just as a test, if you use an intermediate temporary access table, do you

get the same results?  That is, something like:



SELECT * INTO tblTEMP FROM CALLDWNL ;



And then do your GROUP BY on tblTEMP instead of CALLDWNL.  Any difference?



One other stray thought--and I don't think this is really relevant to your

issue.  Access always includes a value for the time of day in its date/time

fields, even if you never display it or enter anything into it.  This can

sometimes cause confusion if you try to GROUP BY a date field that has a

format like 'mm/dd/yyyy', but includes entries that have the same date, but

different times.  So if you have one record with say, 9/25/2001 10:30 AM and

another with 9/25/2001 12:30 AM, your GROUP BY query may appear to be

returning two different rows with the same date of 9/25/2001 on it.  The

solution is of course to either GROUP BY a formatted version of the field so

that the time portion is excluded, or to change the format on the field, so

that you can see the time of day & see that they are indeed distinct values.



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

(xxx) xxx-xxxx



-----Original Message-----

From: Diane Roberts [mailto:Diane.Roberts@c...]

Sent: Tuesday, September 25, 2001 9:42 AM

To: Access

Subject: [access] RE: Group By concerns





It's entirely possible that the application file that I am linking to

could be updated many times while I am trying to process.  However, the

specific examples I researched of multiple result entries per account

had calldates several months in the past.  Any call record created today

would have todays date.  I can't think how I could reproduce a file for

you to research as I think that the problem centers around it being a

linked file (also the actual file is quite large and I am not sure how

much this affects it either althought it shouldn't)  Let me think about

it.  Thanks for your help

Yes, I was able to create a local table and run the query successfully.

I did send a message about this at some point, but perhaps I did not

address it to ALL.  My problem definitely seems to center around the

ODBC linking.  The database I am processing is written in a language

called Cadol and  uses a Cview ODBC driver - not very prevalent I

imagine.  We are using the latest driver but that doesn't mean it

doesn't have bugs.

also - (I always think of something as soon as I "send").  I am not and

have never been grouping on the date field.  I am just grouping on the

account number field, and selecting the maximum date  in order to get

the most current occasion the account was statused AAD.  Anyway, as I

say, it works the way it should on a local table.



--

CONFIDENTIALITY NOTICE



This message and any included attachments are from CashRetriever

Systems, Inc. (CSI), and are intended only for the parties identified

above.  The information contained in this message is confidential and

the unauthorized forwarding, printing, copying, distributing, or using

such information is strictly prohibited and may be unlawful.  If you

have received this transmission in error, please immediately notify the

sender by return email or by telephone at (205) 733-9925.

Yikes, I've never heard of those things...



It sounds to me like your options are working with the driver vendor or

using a temporary staging table as a workaround for the existing bad

driver--can that be made to work?



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

(xxx) xxx-xxxx



-----Original Message-----

From: Diane Roberts [mailto:Diane.Roberts@c...]

Sent: Tuesday, September 25, 2001 12:27 PM

To: Access

Subject: [access] RE: Group By concerns





Yes, I was able to create a local table and run the query successfully.

I did send a message about this at some point, but perhaps I did not

address it to ALL.  My problem definitely seems to center around the

ODBC linking.  The database I am processing is written in a language

called Cadol and  uses a Cview ODBC driver - not very prevalent I

imagine.  We are using the latest driver but that doesn't mean it

doesn't have bugs.

Looks like it's going to have to and I'll have to be extra careful in

checking my results.  Thanks

Understood--I didn't think that was the issue, but you never know when

someone is simplifying a problem for the list.



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

(xxx) xxx-xxxx



-----Original Message-----

From: Diane Roberts [mailto:Diane.Roberts@c...]

Sent: Tuesday, September 25, 2001 12:29 PM

To: Access

Subject: [access] RE: Group By concerns





also - (I always think of something as soon as I "send").  I am not and

have never been grouping on the date field.  I am just grouping on the

account number field, and selecting the maximum date  in order to get

the most current occasion the account was statused AAD.  Anyway, as I

say, it works the way it should on a local table.