In query how to write group into the same minute? Export to text file?

  • Thread starter Thread starter Rolf
  • Start date Start date
R

Rolf

Hi
How can I in a Query write the criteria so that it groups the data from
witin every minute? For example many trades that are made in the same minute
compressed into that minute (and the analysis will be done in other fields,
like: first, high, low, last, sum).

And how can I export the result from a query into a testfile?

Regards
Rolf
 
Hi
How can I in a Query write the criteria so that it groups the data from
witin every minute? For example many trades that are made in the same minute
compressed into that minute (and the analysis will be done in other fields,
like: first, high, low, last, sum).

And how can I export the result from a query into a testfile?

Regards
Rolf
Click to expand...

Try using a calculated field:

TheMinute: Format(datetimefield, "YYYYMMDDHHNN")

Use this as the Group By field in a totals query. How you'll handle minutes
that have multiple records is up to you - you'll probably want to use
max(High), min(Low) but First and Last will be more difficult; the Access
Totals query First and Last choices will return the first and last records *in
disk storage order*, which will not necessarily correspond to the trade at
11:31:01 or 11:31:59.

Note also that date/time fields are limited to one second accuracy - if you
are getting scores of trades a minute you may need finer granularity, for
which Access isn't going to help you!
 
Thank you!! Rolf
John W. Vinson said:
Try using a calculated field:

TheMinute: Format(datetimefield, "YYYYMMDDHHNN")

Use this as the Group By field in a totals query. How you'll handle
minutes
that have multiple records is up to you - you'll probably want to use
max(High), min(Low) but First and Last will be more difficult; the Access
Totals query First and Last choices will return the first and last records
*in
disk storage order*, which will not necessarily correspond to the trade at
11:31:01 or 11:31:59.

Note also that date/time fields are limited to one second accuracy - if
you
are getting scores of trades a minute you may need finer granularity, for
which Access isn't going to help you!
Click to expand...
 
Now I have read your suggestion. Thank you for trying!! Rolf (PS. Response
at earlier post)
 
Hi John, I am not good at Access. I get errors when I try to apply your
suggestion. Could you specify where I shall put your advice?
Thanks Rolf
 
Hi John, I am not good at Access. I get errors when I try to apply your
suggestion. Could you specify where I shall put your advice?
Thanks Rolf
Click to expand...

Create a Query based on your table.

In a vacant Field cell put the expression above (substituting the actual name
of your table field for datetimefield).

Make the query into a Totals query by clicking the Greek Sigma icon (like a
sideways M).

Leave the default GroupBy on this new TheMinute field.

Use whatever Totals function is appropriate on the other fields - not knowing
the nature of your data or your desired result, I cannot suggest just what
those might be. First() will pick an arbitrary record; Min will pick the
lowest price during that minute, Max the highest, Avg the average.
 
Thanks for the suggestion!! I get an error when I paste your statement:
TheMinute: Format([Time];"YYYYMMDDHHNN").
It is something with identifier or paranthesis. I have a swedish Access but
I have configured the pc into Region English(USA). I have made another table
now with the Time as a text-field in stead of Date/Time. I have tested
several combinations. But either I get the above menthiond error or no
result at all.
What am I doing wrong?
Rolf

John W. Vinson said:
Hi John, I am not good at Access. I get errors when I try to apply your
suggestion. Could you specify where I shall put your advice?
Thanks Rolf
John W. Vinson said:
Hi
How can I in a Query write the criteria so that it groups the data from
witin every minute? For example many trades that are made in the same
minute
compressed into that minute (and the analysis will be done in other
fields,
like: first, high, low, last, sum).

And how can I export the result from a query into a testfile?

Regards
Rolf

Try using a calculated field:

TheMinute: Format([Time];"YYYYMMDDHHNN")
Click to expand...
Click to expand...

Create a Query based on your table.

In a vacant Field cell put the expression above (substituting the actual
name
of your table field for datetimefield).

Make the query into a Totals query by clicking the Greek Sigma icon (like
a
sideways M).

Leave the default GroupBy on this new TheMinute field.

Use whatever Totals function is appropriate on the other fields - not
knowing
the nature of your data or your desired result, I cannot suggest just what
those might be. First() will pick an arbitrary record; Min will pick the
lowest price during that minute, Max the highest, Avg the average.
Click to expand...
 
Since you have set the region to English USA you might be running into a data
separator problem. Try replacing the ; with a ,.
Format([Time],"YYYYMMDDHHNN")
Oh, and Time is a reserved word. It is a VBA function that returns the
current system time. If you can change the name to ActionTime or some other
descriptive of what the Time is.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks for the suggestion!! I get an error when I paste your statement:
TheMinute: Format([Time];"YYYYMMDDHHNN").
It is something with identifier or paranthesis. I have a swedish Access
but I have configured the pc into Region English(USA). I have made
another table now with the Time as a text-field in stead of Date/Time.
I have tested several combinations. But either I get the above menthiond
error or no result at all.
What am I doing wrong?
Rolf

John W. Vinson said:
Hi John, I am not good at Access. I get errors when I try to apply your
suggestion. Could you specify where I shall put your advice?
Thanks Rolf
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> skrev i
meddelandet

Hi
How can I in a Query write the criteria so that it groups the data
from
witin every minute? For example many trades that are made in the same
minute
compressed into that minute (and the analysis will be done in other
fields,
like: first, high, low, last, sum).

And how can I export the result from a query into a testfile?

Regards
Rolf

Try using a calculated field:

TheMinute: Format([Time];"YYYYMMDDHHNN")
Click to expand...

Create a Query based on your table.

In a vacant Field cell put the expression above (substituting the
actual name
of your table field for datetimefield).

Make the query into a Totals query by clicking the Greek Sigma icon
(like a
sideways M).

Leave the default GroupBy on this new TheMinute field.

Use whatever Totals function is appropriate on the other fields - not
knowing
the nature of your data or your desired result, I cannot suggest just
what
those might be. First() will pick an arbitrary record; Min will pick the
lowest price during that minute, Max the highest, Avg the average.
Click to expand...
Click to expand...
 
Back
Top