new records in datatable don't show in query

[Ecologist]

I have 2 particular data tables that I update annually, as more (weather) data become available. I have added records from 2007, 2008, 2009 and 2010. I can query these tables to display the 2007-2009 records, but the 2010 records do not show up. How do I "update" the query so that the output table includes the latest addition of new data?

I put "update" in quotes because I have tried to "update" as Access uses the term, and I have tried to "append" as well, but neither option is truly what I am aiming to do -- I do not need to update another data table, nor do I need to append another table. I simply need the query to finish the job by including the 2010 records that are clearly in the data table.

A few other tidbits: the formatting of the 2010 records appears to be identical to the 2007-2009 records that were originally in the data table. I use Access 2007.

Seems a simple task, but apparently I am missing something...sorry to bother y'all with something that is probably so elementary to most users.

Thanks in advance for your help -- Ecologist

 

[pbaldy]

What is the SQL of the query? What does the field contain that identifies a 2010 record?

 

[Ecologist]

HI Paul,

Thanks for your reply. I'm a greenhorn, so apologies if I am not using the same terms you do.

Unsure what you mean "what does the field contain that identifies a 2010 record" -- do you mean DATE? I have columns formatted for dates and am relating the date fields between three tables to connect the survey number, occurring on a given date, to a precipitation reading on the same date, and to connect the survey start time to the hourly temperature reading for the same survey on the same date.

SQL:

SELECT [Day Survey Effort and Conditions].Survey_No, [Day Survey Effort and Conditions].Date, [Day Survey Effort and Conditions].[St Time], Weather_RAWS.RAWS_Time, Weather_RAWS.TMPC AS CURRENTTMPC, Weather_SNOTEL.PREC
FROM Weather_SNOTEL RIGHT JOIN (Weather_RAWS RIGHT JOIN [Day Survey Effort and Conditions] ON Weather_RAWS.RAWS_Date = [Day Survey Effort and Conditions].Date) ON Weather_SNOTEL.SNOTEL_Date = [Day Survey Effort and Conditions].Date
WHERE (((Weather_RAWS.RAWS_Time)>DateAdd("n",-30,[St Time]) And (Weather_RAWS.RAWS_Time)<DateAdd("n",31,[St Time]))) OR (((Weather_RAWS.RAWS_Time) Is Null));

Where statement is intended to link an hourly temperature reading (from the RAWS table) to the nearest hour that a survey began. The SNOTEL table links a daily cumulative precipitation value to the date of the survey.

The query functions as it should to find correct values for 2007 -09 surveys but doesn't pick up the rest of the column with the records associated with 2010. Perhaps the way in which I added the 2010 records to the data table (copy/paste from an excel file, if i recall correctly)? I may try exporting the data table with all 4 years and importing back into Access to see if that would do the trick.

I had a very helpful field tech set up this query originally. Being somewhat new to Access, I am essentially "just getting by" via trial and error, and realize that my hacking may require more time to fix than it may be to learn correctly the first time! Live and learn.

Thanks for your help in advance...Ecologist.

 

[pbaldy]

My question regarding the 2010 records was an attempt to find out what's different. If you can query other years but not 2010, something is different. Your query appears to rely on a value in

[Day Survey Effort and Conditions].[St Time]

Does that contain the appropriate data? Can you post the db, or a representative sample?

 

[Ecologist]

Resolved!

The column for times for 2010 hourly temperature readings included extra junk ("x/x/1900") that was not visible until the column was widened significantly, as if there were a few tab spaces between the junk date on the left side of the cell and the hh:mm:ss seen on the right side of the cell. With help from an Access user/guru that I managed to lure to my screen with the promise of happy hour beers, we were able to find the issue and fix it so queries are running as they should now. Nonetheless, I really appreciate your responses and attempts to help.

Cheers, Ecologist

 

[pbaldy]

If you had mentioned beer, I'd have come over!

Thanks for posting the solution. It seemed like it had to be something in the data.

 

[Ecologist]

Thanks, Paul. Its amazing what can be accomplished by bartering beer! Cheers!