MS Access SQL question

[MikeB]

Hi, I'm not sure which of the 30+ Access forums is the right one to ask
an SQL question in?

If this is not the one, please point me to the right one and accept my
apologies for posting my question here.

If this is the right place, then here is my question.

I have a very large Excel file (11 worksheets) of data. I'd like to get
this onto a single table so that I don't have to repeat each
manipulation 11 times and always run out of space to do things like
subtotaling. So I want to import the table to Access and manipulate the
data there. One of the things I need to do that I can't find a way to
do is to extract a month/year value from a timestamp. Is this possible?
Eg. I have a timestamp (01/04/2007 12:34 AM) and I want to represent
it as 200701 so that I can subtotal all the transactions that occurred
in the same month.

Thanks

 

[Rick Brandt]

Hi, I'm not sure which of the 30+ Access forums is the right one to
ask an SQL question in?

If this is not the one, please point me to the right one and accept my
apologies for posting my question here.

If this is the right place, then here is my question.

I have a very large Excel file (11 worksheets) of data. I'd like to
get this onto a single table so that I don't have to repeat each
manipulation 11 times and always run out of space to do things like
subtotaling. So I want to import the table to Access and manipulate
the data there. One of the things I need to do that I can't find a
way to do is to extract a month/year value from a timestamp. Is this
possible? Eg. I have a timestamp (01/04/2007 12:34 AM) and I want to
represent it as 200701 so that I can subtotal all the transactions
that occurred in the same month.

Thanks

Just store it in your table as a complete timestamp. In queries and reports
it is trivial to then group on that at different intervals (year, month,
quarter, etc.). For example to group the data in a query by month you could
use...

YearAndMonth: Format([FieldName], "yyyymm")

 

[Dirk Goldgar]

Hi, I'm not sure which of the 30+ Access forums is the right one to
ask an SQL question in?

If this is not the one, please point me to the right one and accept my
apologies for posting my question here.

If this is the right place, then here is my question.

I have a very large Excel file (11 worksheets) of data. I'd like to
get this onto a single table so that I don't have to repeat each
manipulation 11 times and always run out of space to do things like
subtotaling. So I want to import the table to Access and manipulate
the data there. One of the things I need to do that I can't find a
way to do is to extract a month/year value from a timestamp. Is this
possible? Eg. I have a timestamp (01/04/2007 12:34 AM) and I want to
represent it as 200701 so that I can subtotal all the transactions
that occurred in the same month.

Assuming this is a one-off job, and not something you're going to do
over and over again, I'd probably link to each of the worksheets as a
linked table, making 11 linked tables, create a native Access table to
hold the data that I want from them, then use append queries -- really
the same query just targeting the each of the 11 linked tables in
turn -- to copy the data into the target table.

Assuming your timestamp field is stored in Access as a date/time field,
you can either use the Year() and Month() functions in a query to get
the year and month from the date/time as separate, numeric values, or
else use the Format() function to return a string in "yyyymm" format.