Sorting based on dates

[Guest]

I recently have some problems with making a proper query.

I have a few linked excel keeping track of hours worked per day. The excel
sheet are being organized weekly such as 1/1/06. For the whole week, the
date for monday until sunday will still be 1/1/06 (the following week will
be 8/1/06 and son on) instead of 1/1/06, 2/1/06, 3/1/06 and so on.

I am trying to run a query on hours worked per person per month but the
problem now is, I can't sort them out by day and I can only sort them out by
weeks. As you know, some week starts on 31/1/06 and the next day it will be
2/1/06. But the query still return the hours worked on Febuary under
January, which is somethign I do not want.

Can something be done? I tried to reverse engineered something which was
done by the previous person but it doesn't make much sense to me as a
beginner in Access.

Thank you for the help and pardon me for the long question.

 

[kingston via AccessMonster.com]

Somehow, the order of the records needs to be preserved to reconstruct the
actual dates. The easiest thing to do would be to change the spreadsheet by
adding a real date column. If this is not possible and you import the data,
allow Access to create a primary key; this gives each record a sequential
number that you can use to reconstruct the actual dates:
ID WeekDate
...
25 31/1/06
26 31/1/06
27 31/1/06
28 31/1/06
29 31/1/06
30 31/1/06
31 31/1/06
32 7/2/06
33 7/2/06
...
RealDate = [WeekDate] + [ID] - DMin("[ID]","

","[WeekDate]=#" &
[WeekDate] & "#")

If you link the spreadsheet to the database, you can append the data to a
temporary table with an AutoID and use the formula. The main concern is
whether the record order is preserved and I think that it will be. HTH

 

[Guest]

Thanks for the quick reply.

I thought about reorganizing the date as well over excel, it is not an
option to me because of the massive excel files already in place. I'll have
to go over with the second option.

I realised at some point the previous author created 7 queries consisting of
monday -> sunday, with something called DatePart on one of the field.

DatePart("m",[week commencing]+5)

If this helps?

Thanks again !

Somehow, the order of the records needs to be preserved to reconstruct the
actual dates. The easiest thing to do would be to change the spreadsheet by
adding a real date column. If this is not possible and you import the data,
allow Access to create a primary key; this gives each record a sequential
number that you can use to reconstruct the actual dates:
ID WeekDate
...
25 31/1/06
26 31/1/06
27 31/1/06
28 31/1/06
29 31/1/06
30 31/1/06
31 31/1/06
32 7/2/06
33 7/2/06
...
RealDate = [WeekDate] + [ID] - DMin("[ID]","

","[WeekDate]=#" &
[WeekDate] & "#")

If you link the spreadsheet to the database, you can append the data to a
temporary table with an AutoID and use the formula. The main concern is
whether the record order is preserved and I think that it will be. HTH

I recently have some problems with making a proper query.

I have a few linked excel keeping track of hours worked per day. The excel
sheet are being organized weekly such as 1/1/06. For the whole week, the
date for monday until sunday will still be 1/1/06 (the following week will
be 8/1/06 and son on) instead of 1/1/06, 2/1/06, 3/1/06 and so on.

I am trying to run a query on hours worked per person per month but the
problem now is, I can't sort them out by day and I can only sort them out by
weeks. As you know, some week starts on 31/1/06 and the next day it will be
2/1/06. But the query still return the hours worked on Febuary under
January, which is somethign I do not want.

Can something be done? I tried to reverse engineered something which was
done by the previous person but it doesn't make much sense to me as a
beginner in Access.

Thank you for the help and pardon me for the long question.