Building a daily activity log

[Naomin]

I keep track of my daily activities in a kind of moving calendar in Excel. The dates are kept in a linear fashion along the top 2 rows (date/day of the week), and as I move into another week, I add in those dates to the right, very much like MS Project. The various projects I work on are arranged in the rows below. I keep track of my ongoing progress in the grid: short descriptions for activities in the corresponding cell for a particular project, on a particular date. A summary is kept to the far right, with the most recent activity plus the date it was done on. I've built macros that fetch this information. I'd like to recreate this in Access, with the summary on one page and the weeks on another. I've attached a pdf as an example.



Looking forward to some brilliant answers! I'm very happy with my creation but excel can't handle it. The macros corrupt.

[ranman256]

you want a table like:
project, Date, task

enter the info. Later you can, if you want, produce a report that looks like your excel example; days across the top.

[Naomin]

I have too may projects to enter the date on each line. I would like to keep the entry area as the calendar and the report as the summary. I'd like to keep the functionality as close to the excel build as possible.

[mike60smart]

Hi Naomin

Ranman has given you the best layout for Access.

Remember Access is not Excel.

In Excel you enter a new date as a Column Heading, In Access you would enter a Date as a New Record

[Micron]

Possible approaches:
- find out why Excel macros are not stable and fix that. Excel is the better tool for your layout
- considering the mantra "Normalize until it hurts, denormalize until it works" take the denormalize part to heart. Create a project table with fields for each day, but trying to have 2 Saturdays and 2 Sundays would be a deal breaker. However, you will find that extracting data based on days will always be a problem and if you post issues about that here, you will probably get told your schema is all wrong every time.

- key thing to remember is that with Access, storage is one thing, presentation is another. I'm not saying this is a great approach, but I would think you could store the data properly and use a continuous form to pivot and present it. I'm thinking that the presentation would involve a crosstab query to pivot the weekday row data into day columns. However, this assumes that you would be able to group by Day values so that you restrict column results to the 7 days. Again, 2 Sat/Sun is a no-go.

That is all speculation because as noted, Access is not meant to be used as a spreadsheet and it's likely that not many helpers here would be very interested in such a project. Perhaps an Excel forum could help you with the macro corruption issue.