distinct and yet duplicates

[patti]

Why am i getting duplicates in this query?

SELECT DISTINCT DailyData.IntakeID, DailyData.HGB, DailyData.DDDate
FROM qryMinHgb1 INNER JOIN DailyData ON (qryMinHgb1.MinOfHGB =
DailyData.HGB) AND (qryMinHgb1.IntakeID = DailyData.IntakeID);


The DDDate field is formatted in the table as short date.

thanks for the help.

 

[Jeff Boyce]

Patti

Not much info to go on...

Is there a chance your [DDDate] field is actually storing Date/Time values?

Is there a chance the join is causing duplicates?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[patti]

Hi Jeff-

As far as i can tell, the date is stored as short date.

As for the join, there are duplicate dates but that's why i went with the
distinct statement. So, i am confused on using the distinct statement here?

Patti

Not much info to go on...

Is there a chance your [DDDate] field is actually storing Date/Time values?

Is there a chance the join is causing duplicates?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Why am i getting duplicates in this query?

SELECT DISTINCT DailyData.IntakeID, DailyData.HGB, DailyData.DDDate
FROM qryMinHgb1 INNER JOIN DailyData ON (qryMinHgb1.MinOfHGB =
DailyData.HGB) AND (qryMinHgb1.IntakeID = DailyData.IntakeID);


The DDDate field is formatted in the table as short date.

thanks for the help.

 

[Rick Brandt]

Hi Jeff-

As far as i can tell, the date is stored as short date.

As for the join, there are duplicate dates but that's why i went with
the distinct statement. So, i am confused on using the distinct
statement here?

Distinct amongst the date values as you have decided to *display them* is
not the same as the underlying values themseleves being distinct.

Dates are never stored in a particular format. That is only for display
purposes. If you use the Format() function instead of the format property
it should work because with the function what you see is what is actually
there. That is not the case with the format property.

Of course with the format function what you get back is no longer a date,
but a string. You could alternatively use the DateValue() function to pull
out just the date (time is set to midnight) and that might also get your
distinct to work as you expect it.

 

[patti]

The field in the table is formatted for short date. How is access really
storing it?

 

[Rick Brandt]

The field in the table is formatted for short date. How is access
really storing it?

No, the "datasheet that is used to view the table" is formatted to display
the date as "short date". Formatting never makes any difference to how data
is stored (regardless of the data type). What is unique about DateTimes is
that they are always using a format of some kind because the raw data that
is stored in not meaningful to the user.

Access DateTimes are stored under the covers as a Double numeric type where
the integer portion of the number is the count of days since December 30,
1899 and the fractional portion of the number is a percentage of the 24 hour
clock for that date. Right now as I type the current DateTime is...

4/1/2008 2:43:26 PM

This would be stored as the number...

39539.6134953704

So you might have lots of DateTime values which appear identical when shown
in short date format, but where the underlying numbers are quite different.

 

[patti]

Thanks for thre explanation. I often to seem to run up against problems w/
dates in both excel and access.

As for my query, i will attempt it with the formattting.