Access Query - vb

[opus_two]

I have several pieces of equipment that log real time data to a common Access database. Each equipment logs as many as 6 times per minute. I would like to create a query that captures the equipment ID, the rate, and only one sample per minute and ignore the remaining samples that exist in that same minute. I use the queried data in excel and I don’t need the accuracy to the second, just to the minute.

I suspect that a VB query needs to be created however my programming skills are not up to the task.

The fields in the dbm are

ARCHIVETIME – Date/Time - SORT Accending
ED – Number - Long Integer
RATE– Number – Double

I attached a snippet of the database if anyone wants to help

Thanks

Russ

 

[HiTechCoach]

Welcome to AWF!

What I would try first is to format the date time to only the minute in a qusry then append to a table where there is an index on the date/time field set to no dups. This will allow the first record to be added for each minute. Access will automatically not append the dups.

Hope this helps ...

 

[pbaldy]

Presuming you still want to capture all the data, you could adapt this technique to only get one record per minute (group by a field that formats to the minute):

http://www.baldyweb.com/LastValue.htm

 

[opus_two]

Welcome to AWF!

What I would try first is to format the date time to only the minute in a qusry then append to a table where there is an index on the date/time field set to no dups. This will allow the first record to be added for each minute. Access will automatically not append the dups.

Hope this helps ...

Couldnt get this query to work

Russ

 

[johnconor]

I have a query in an Access db that prompts the user for info. (e.g. a primary key representing a company) before compiling the relevant table of results. This works fine in Access but I want to use this query from within VB.Net. But how do I, in code, pass a parameter to the Access query?

 

[johnconor]

Access Query

I have a query in an Access db that prompts the user for info. (e.g. a primary key representing a company) before compiling the relevant table of results. This works fine in Access but I want to use this query from within VB.Net. But how do I, in code, pass a parameter to the Access query?

 

[HiTechCoach]

Couldnt get this query to work

Russ

The query is very simple:

INSERT INTO tmpRADData ( ED, ARCHIVETIME, RATE )
SELECT RADSDATA.ED, Format([ARCHIVETIME],"dd/mm/yyyy hh:nn") AS ND, RADSDATA.RATE
FROM RADSDATA
ORDER BY Format([ARCHIVETIME],"dd/mm/yyyy hh:nn");

They key is ion the temp table you are appending into must has an index set with No Dups.

See the attached:

 

[raskew]

Hi -

Your query obviously works, but I've got to ask a question.

Format([ARCHIVETIME],"dd/mm/yyyy hh:nn") AS ND returns a string, e.g.

This, from the debug window, returns a Type Mismatch:

x = format(now(), "mm/dd/yyyy")
? cdbl(x)

So the question is, how can you take a date/time which has been formatted, thus returning a string, and shove it back into a date/time field?

Like I say, it works, but seems to defy everything I thought I had known about date/time versus text fields.

Bob

 

[HiTechCoach]

My example works because Access can handle the data type conversion automatically. The reason it can convert the data from a string to a date/time data type is because the text string generated by the format() returns a properly formated date/time string that Access understands. This allows Acess to handle the data type conversion as the data is placed into the fields that is a date/time data type.

To handle the data type conversion yourself try using CDate() which is design to handle a date/time string.

Example:

? CDate(format(now(), "mm/dd/yyyy hh:nn"))
6/10/2009 11:23:00 PM

so the query would look like:

INSERT INTO tmpRADData ( ED, ARCHIVETIME, RATE )
SELECT RADSDATA.ED, CDate(Format([ARCHIVETIME],"dd/mm/yyyy hh:nn")) AS ND, RADSDATA.RATE
FROM RADSDATA
ORDER BY CDate(Format([ARCHIVETIME],"dd/mm/yyyy hh:nn"));

 

[opus_two]

That worked
thanks

Russ