from excel to access

[Eitas]

I am working on converting an excel work book to access.

This is for tracking when things are due.

So the data is like this.

Users

clients
Monthly services
Quartly services
Annual services


Each user has multiple clients
each client CAN have multiple services
There are multiple types of quarterly and annual services.
Some quarterlies can be done instead of the monthly that month.



The current excel book has a sheet for each service type with the dates running across so monthly would have 12 and quarterly would have 4, ...


All of the dates are calculated off a base date, so each quarterly will land 3 months after they started with us not by calendar year.


Also they want to track complete dates to compare with the due dates.




I initially thought of



client, service type, due, complete




The issue is I have to calculate the due and store it this way. I also have to add fields to calculate what quarter per due date and per calendar year for the quartiles.




Since the excel sheets are across I am also tempted to have a different table for each period type and just have 12 fields for monthly and 4 for quarterly. This would make the import easier and the due fields could be calculated.


I am trying to rack my brain to figure out what would be best practice / easiest.


Any suggestions / help


Thanks.

[alansidman]

Since the excel sheets are across I am also tempted to have a different table for each period type and just have 12 fields for monthly and 4 for quarterly. This would make the import easier and the due fields could be calculated.

Did you attempt my earlier suggestion on a Union Query to get all the dates in the same field? If you have dates across several fields then you will have a non-normalized data base that may eventually cause you some discomfort when you attempt to do queries.

Here is a good white paper on data normalization and database design

http://forums.aspfree.com/microsoft-...es-208217.html

Alan

[Eitas]

I haven't had a chance to test the union. The excel sheet is one user only so getting access to it can be tricky, I found that if I have it open in access no one else can use it so I had to remove the links.

I was just looking at it all and started doubting my method.

So you think having all the schedules in 1 table is better.

I will probably not be able to access the excel doc till next week sometime.

[Eitas]

wow that was quite a read. So I was right in not wanting a calculated field stored.

How can I have a series of dates increasing down the column. I know how to do it across, when trying to get the dates to increment down I had to do an append query and store the calculated dates.

The method for this that I found was to create a count table with just 1 column of number from 1-12 then do a query with the table and have no relationship.

[alansidman]

In most cases you do not want to store calculations. You do the calculations in either a form or query. The raw data is always there in your tables.

I did not understand

How can I have a series of dates increasing down the column.

You can retrieve the data in a query and sort the field ascending if I understand which I am not sure I do.

Alan

[Eitas]

The table I am looking at making is based off a constant date

So start date = 12 Jan 2011

The table will have each month like below caculated off the date but stored in each record, with quarterly it would be 12-Apr and such.

type due
complete

Monthly 12-Feb-11


Monthly 12-Mar-11


Monthly 12-Apr-11


Monthly 12-May-11


Monthly 12-Jun-11


Monthly 12-Jul-11


Monthly 12-Aug-11


Monthly 12-Sep-11


Monthly 12-Oct-11


Monthly 12-Nov-11


Monthly 12-Dec-11