Separate table for dates?

[soonerfan]

I am designing a database to enter daily/monthly performance numbers for employees and department totals. However, I don't want to have to enter the month and/or day for every category for each employee entered. In looking through these forums, nobody recommends a separate date table. But it seems time-consuming to have to enter the reporting month for every sales category for every employee. To wit:

TblEmp
EmplID
EmplName
HireDate
TerminationDate

TblCategories
CatID
CatName
CatType

TblDate
MonthYr
DayMonth

TblMonthlyPerformance
AutoID
EmplName
CatName
MonthYr
Amount

TblDailyPerformance
AutoID
EmplName
CatName
DayMonth
Amount


If I don't have a table for dates, then for every category for every employee, I would have to enter the date...right or wrong? Or am I thinking flat.

 

[neileg]

So create a data entry form that has an unbound date text box, and set your record date field to look at this when created. When you want to move to a new date, change the date in this box.

 

[soonerfan]

Can it be that simple? I will try it when I get back to my database at home. If it is that simple, don't think of me as stupid, only ignorant.

Thanks!!

 

[reclusivemonkey]

There is a calendar control as well you might want to look into using if you want a quicker way of entering dates. Well, I gues "quicker" is too subjective a word... I think most people here would of entered the date in the keyboard by the time most people got to their mouse ;-) Guess it all depends who you are designing the DB for!

 

[Pat Hartman]

Why do you have separate tables for daily and monthly performance? Monthly data should just summarize the daily data. To record it separately simply leaves it open to mistakes.

 

[soonerfan]

The daily data may or may not equal the monthly data, and is not necessary to match exactly during the month. It may not be input on an every day basis, or may have to be input for previous months, or may not be input at all on a daily basis, but the month end will be.

It still seems to me that a table just for the months and/or days of the month would be easier. Since I am so new to Access, I am struggling, especially since I have little time to mess around with it. I have a huge Excel file (10mb template, over 30 worksheets to track five employees and one department) but there are so many formulas and worksheets that interconnect, whenever I need to add new data headings, or other change, it takes a long time and too many chances for error in a connected worksheet. THat's why I got Access and am trying to learn to think relational instead of flat. But there's the rub, I am a flatliner, and can't get past the design and relationship aspect. Interestingly, I haven't found any sample databases that are designed to track performance/sales, or at least in my narrow mindset. In sales, monitoring and performance tracking is important, inventory is another program and in this case, irrelevant--but all the samples I look at for ideas seem to focus on inventory and sales of such. I am dealing with sales performance of individuals and department--for example, how many orders written, sales, amount of product sold by category (not individual orders/sales, but in total by category), gross, etc. Don't care about individual orders, only total by employee/dept over a time period.

Enough of that. Still plugging away. Would still have the Access in the shrink wrap if it wasn't for these forums. Only wish I could contribute rather than pick brains.

Have a great weekend!

 

[neileg]

The hardest job you have is forgetting about Excel. If you design your application along Excel lines it just wont work. Good luck!

 

[Pat Hartman]

Having only month and day is insufficient. You need a real date associated with your daily entries otherwise, you'll be scrambling in December to figure out what you're going to do in January. With Excel, you'd probably just build a new set of spreadsheets. Thate is NOT how relational applications work. If you store the complete date, there is no reason to do anything different next January. You would just keep entering data in the existing tables. The advantage of this is that it becomes very easy to do not only month to month, but year to year analysis of your sales data. Plus, as long as you have recorded the data at a sufficient level of detail, you can make reports by department, sales person, etc. If you have access to the actual orders, there is no need to reenter the data at all!!! You can create your stats directly from the customer orders.

You've said that an anoying thing about Excel is having to continually add new columns and change your calculations. That NEVER happens in a properly designed database. There is NO reason to add new dolumns monthly or annually or when you get new departments or new sales people. You simply have more rows of data. NO application design changes are required.

I'll say it in no uncertain terms -
1. get rid of the month table. It is a duplication of effort and a potential source of error.
2. enter the complete date in all records.

If you want to eliminate typing for the times you are doing a lot of data entry, learn how to use the shortcuts that copy data from the previous record. Cntl-quote will copy the value for the field that currently has focus, from the previous record. So, you'll need to enter the date in the first record and then use cntl-quote to fill it in subsequent records.

 

[soonerfan]

I think I am catching on a bit. Entering month end data not by month and year, but by any day of the month (probably last day) will still enable me to pull monthly or yearly data. Therefore, all I need is an "order date", or in my terms, sales date, in the performance table.

One other question. If my performance data is in different number formats (currency, whole numbers, etc.), would a categories table be helpful--or would I format them in the reports?

THanks for all your help.

 

[Pat Hartman]

No, no, no, no!!!! Monthly data is ALWAYS calculated. You add all the daily data together.

 

[soonerfan]

Yes, I know, but daily data may not be entered daily, but weekly, every other day, etc. So for example, if a user enters data daily, then it could be a total of daily data. But if, however, a user only enters it one time, they would enter it using the last day of the month and I could calculate the monthly total by adding all of the daily totals, whether 30 entries, 4 entries, or just one entrie. Right?

 

[Pat Hartman]

That is correct. However, if your data is not entered consistantly, you will not be able to do various types of analysis such as which days of the week are the busiest? or which weeks of the month are the busiest?

 

[soonerfan]

You are absolutely right. And, that would be a nice thing to do, one I haven't considered. However, the people inputting this are not consistent, and I can't force them to. The important feature is the monthly tracking, and to a lesser extent, daily tracking in regards to forecast, etc.

One other question. If my performance data is in different number formats (currency, whole numbers, etc.), would a categories table be helpful--or would I simply format them in the reports?

 

[Pat Hartman]

Use currency as the data type since it will shelter you from the floating point errors caused by the single and double precision data types. You can format the data as you need to in the report or form. Use conditional formatting.

 

[soonerfan]

Thanks, will do that. I won't even ask what single/double precision data types means...I will find that out as I go.

'preciate all your help.