Query Is Not Pulling All The Data...Any Suggestions?

[Chuck Fobian via AccessMonster.com]

I apologize beforehand at the grotesqueness of this message.

Hello,

I am fairly new to access and have had to adapt with the changing market.
I started to implement many access procedures to help make my life a little
easier. There is one query that is frustrating me to no end. When I
checked, everything seemed correct but it wasn't pulling all the data that
it was supposed to. Here is what I've done.

Tables:

tbl_nhi_import

stat|day|daypart|hhaa|hhaud|f2554aa|f2554aud
AE|3/14/2005|MTWTFSS2:00PM-2:15PM|0.348|381|0.131|81

tbl_spot_rotation_import

cstation|date|time|daypart|nbuyrate
AE|3/17/2005|1:00:00 AM|1:00:00 AM|0

tbl_statconversion

NHI_Station|SpotRotn_Station
(AEN)|AE

tbl_spot_conversion

stat|dow|date|time|daypart|cost
AE|05|3/17/2005|1:00:00 AM|1:00:00 AM|0

SELECT tbl_StatConversion.SpotRotn_Station AS stat, Format(Weekday([date],1)
,"00") AS dow, tbl_SpotRotn_Import.date, tbl_SpotRotn_Import.time,
tbl_SpotRotn_Import.daypart, Val([nbuyrate]) AS cost INTO Spot_Conversion
FROM tbl_StatConversion INNER JOIN tbl_SpotRotn_Import ON
tbl_StatConversion.SpotRotn_Station = tbl_SpotRotn_Import.cstation;

nhi_conversion

stat|dow|day|dpt|hhaa|hhaud|f2554aa|f2554aud
AE|02|3/14/2005|11:30 PM|0.582|638|0.388|240

SELECT tbl_StatConversion.SpotRotn_Station AS stat, Format(Weekday([day],1)
,"00") AS dow, tbl_NHI_Import.day, TimeValue(Mid([Daypart],8,(InStr(
[Daypart],"-")-8))) AS dpt, tbl_NHI_Import.hhaa, tbl_NHI_Import.hhaud,
tbl_NHI_Import.f2554aa, tbl_NHI_Import.f2554aud INTO NHI_Conversion
FROM tbl_StatConversion INNER JOIN tbl_NHI_Import ON
tbl_StatConversion.SpotRotn_Station = tbl_NHI_Import.stat;


tbl_finish

stat|dowdate|time|dpt|cost|hhaa|hhaud|f2554aa|f2554aud
AE|5|3/17/2005|9:00:00 PM|9:00:00 PM|3825|1.241|1360|0.822|509

SELECT tbl_SpotRotn_Import.cstation, tbl_SpotRotn_Import.date,
tbl_SpotRotn_Import.time, tbl_SpotRotn_Import.daypart,
tbl_SpotRotn_Import.nbuyrate, NHI_Conversion.hhaa, NHI_Conversion.hhaud,
NHI_Conversion.f2554aa, NHI_Conversion.f2554aud
FROM tbl_SpotRotn_Import LEFT JOIN NHI_Conversion ON
(tbl_SpotRotn_Import.cstation = NHI_Conversion.stat) AND
(tbl_SpotRotn_Import.date = NHI_Conversion.day) AND
(tbl_SpotRotn_Import.daypart = NHI_Conversion.dpt);

For some reason it's not pulling all the times form tbl_nhi_import. For
example, if my time was for 10:20am out of tbl_spot_rotation_import(which
is rounded down to 10:15am from my format)then it should match to 10:15am
from nhi_conversion. It pulls about 60% of the times. If you need any
more explanation I'll try to be more descriptive. I really appreciate any
help to this ongoing saga.

Thanks,

Chuck

 

[John Vinson]

For some reason it's not pulling all the times form tbl_nhi_import. For
example, if my time was for 10:20am out of tbl_spot_rotation_import(which
is rounded down to 10:15am from my format)then it should match to 10:15am
from nhi_conversion. It pulls about 60% of the times. If you need any
more explanation I'll try to be more descriptive. I really appreciate any
help to this ongoing saga.

A Date/Time value is stored internally as a Double Float number, a
count of days and fractions of a day (times) since midnight, December
30, 1899. The double float is actually accurate to a few microseconds,
but *displays* only with one-second accuracy.

It's possible that the rounding is causing roundoff error, and that
the time 10:15am in nhi_conversion is actually a few microseconds off
the rounded time.

Try comparing Format([tbl_spot_rotation_import, "hh:nn") with
Format([timefield], "hh:nn")

to compare to one-minute accuracy, truncating seconds and finer.

John W. Vinson[MVP]

 

[Chuck Fobian via AccessMonster.com]

John,

I thank you so much for the quick response. I tried your suggestion but I
could not get it to work. All the seconds are equal to 0 so I don't see
how it could be a rounding issue in that respect but then again I'm not
well versed in access or it's functions.

You probably already know this and I apologize if I seem repetative but
I rounded the time in tbl_SpotRotn_Import to 15 second increments. The
original format of the field was as text in military time so I formated it
as like so, time: CDate(Format(Val([milltime]),"00\:00")) which gives me
normal time. Then I made a new field, dpt: CDate(Int([time]/(1/96))*(1/96))
to round it down to the nearest 15 minute increment. I know that it must
be something with the time as to why it's not matching everything because
of some sort of rounding issue but I haven't narrowed it down yet.

Since I already had this MTWTFSS1:45AM-2:00AM and I only wanted 1:45AM I
did this,dpt: TimeValue(Mid([Daypart],8,(InStr([Daypart],"-")-8))). So if
I have 1:45AM in both tables NHI_Conversion and tbl_SpotRotn_Import then
evertying should match. Is there a better function than INT function for
rounding time?

Thanks,