Create calendar table

[Dachande11]

Hello,

I am trying to find out if there is a way for me to create a table based on a specific month. For example I might select January 2005, I would then want to create a table with all the days of that month included, so that I could then add specific data to each day.

Is there a way that I can simply query the system calendar to do this sort of thing. I have spent quite a lot of time searching through this forum but have had no luck.

Thanks for any help

Mark

 

[Mile-O]

I am trying to find out if there is a way for me to create a table based on a specific month. For example I might select January 2005, I would then want to create a table with all the days of that month included, so that I could then add specific data to each day.

Sounds more like you want to use a spreadsheet and don't know what a relational database is for.

 

[Dachande11]

Thanks for your constructive help there. Maybe I am just confusing myself but the idea is that I do a daily sales tracker and to start things off I need to have all the working days in a month. Now I could manually do this every month but I am looking to automate this report.

So I have my working days, I can then add an average daily budget figure (calculated from a monthly figure/working days), a cumulative forecast figure and some other figures. This table is then joined to a sales table for current month etc to create a source for a chart.

So if you think I am going about this the wrong way please feel free to add some constructive feedback or if you feel like having a dig at my lack of knowledge please feel free to continue. I just thought that this forum was here to help people develop their knowledge, obviously some people just like to make other people feel stupid.

 

[Mile-O]

So if you think I am going about this the wrong way please feel free to add some constructive feedback or if you feel like having a dig at my lack of knowledge please feel free to continue.

I wasn't "having a dig" - simply commenting upon your post. Maybe it would make you think why this method wouldn't be good for a relational database.

Anyway, each database table should model an object/entity. Tables based on a specific month is the bad approach because the number of tables will grow and grow, the database will become unweildy, and you have no quick way of summarising data over greater periods of time rather than just one month.

Store everything you need in one table and use a query to select the time periods you want.

With respect to pre-filling data for each month into the table I still don't see the need. The database should get its information when you have information to put into it - any half-baked data entered could and probably will, through off any summaries you try to do.

If you need the number of working days for some expression then use a VBA function to determine it for you.

 

[The_Doc_Man]

The problem with Access is that it doesn't have an "automatic" calender. There is such a thing as a calendar control that would allow you to select a date from a calendar-like object.

As you are beginning to discover, your problem is that Access is what is called a "sparse" system. That is, if you don't store it, it doesn't exist. So to have a calendar such as you suggest, you have to figure out how to build one so that you can use it constructively.

The only thing I find unusual is that a forum search didn't turn up anything. Do you perhaps mean anything you can tie back to your intent? 'cause I have personally answered calendar questions before. Anyway, here is the straight stuff.

SJ's comment, while perhaps a bit curt, was not totally inaccurate. By building such a table as you described, you are thinking in spreadsheet terms. You are thinking of empty slots that you can then fill in to define other things in a record. Excel would certainly do this, though it might not support other things - like, more useful and more extensive reporting abilities.

But Access doesn't need a calendar table and neither do you. Search this forum for articles on how to use a Calendar control. This is all the "table" you need. Basically, don't think of a date table and links to it. Think instead of the things you would have linked each being tagged with a date.

Like, if you wanted to make a sales projection, you would have a SALESPROJ table (or similar name) in which the records include a date field. Put a calendar control on it to allow you to select the date on which that projection is valid.

If you wanted to make an annotation about a date, you would have an ANNOTATION table (or similiar name) in which the records include a date field. Then make your note and date it.

If you wanted to make charts based on budget info, you would have a BUDGETINFO table (or similar name) in which the records include a date field plus whatever else you needed to use.

Sorting by dates is an easy trick for Access. So on those fields where you want to correlate things that are on the same date, you have a start - the common date as a candidate key for both tables.

If you MUST have separate days, learn how to use VBA to generate records in a recordset so you can do a FOR loop to populate your tables and eliminate entries for non-work days.

 

[Dachande]

OK, sorry I took your comment the wrong way. I think I am beginning to see your points. The reason I wanted to generate a table and not just use sales information was for instances (for whatever reason) when a working day does not have any sales in it. If this happened and I then tried to build in things like cumulative daily budget, the end budget figure would be wrong as the number of days would be wrong.

I do have the suggested tables for budget and forecast but as I mentioned before they are at a month level and not a daily level. The daily level is calculated by the number of working days within the period and then should be assigned against each working day so that a cumulative picture can be built up.

I think I know what I need to do now.

Thanks for highlighting the problems in my thought process.

Oh and Doc man I have read your posts with regards to calendar options and they will come in useful with other front ends I use. Thanks

Mark

 

[The_Doc_Man]

You are welcome.

Just for absolute, simplest possible clarification: You should not think about having a date which you tag with estimates, sales, etc. Instead, you should think about having estimates, sales, etc. which you tag with dates.

A calendar is a "fixed position" thing. An Excel spreadsheet is a "fixed position" thing. An Access database or its tables are NOT "fixed position" things. In other words, both SJ and I, each in our own way, were trying to tell you to break with the spreadsheet mindset.

But I'll add one other comment for you for a specific problem you mentioned. There is no reason in the world why you could not make a sales record with ZERO sales (to keep your entry count straight) and just date-tag it appropriately. For your sake, I hope that does not turn out to be a common event (a day with no sales.)