Ms-access – Best way to enter data in DB that is spread out date wise

database-designexcelms accessvba

I have an Excel file that, to keep it simple here, has 3 columns: ActivityID, ActivityName and TotalHours.

TotalHours column is basically all the hours allotted to that specific ActivityID and the file can have about ~1500 of these activityID equating to 1500 rows.

Now the thing is that there are additional columns in the file which break down the TotalHours by week dates. These hours are split into different week periods but adding them all up equals TotalHours.

I don't want to end up dynamically creating table columns in my DB as I could end up with 100+ fields. One solution is that I could read the Excel just like it is and do calculations on the fly but this would then require the file to be strictly templatized meaning the data needs to be consistent and be in specified cells for me to read them. This is something that I am trying to avoid.

Is there any better way that you know I can approach this problem?

Thanks

Best Answer

Am I understanding that you pretty much have a work order, and that work order can be worked on for a number of hours on any day, and you want to store how many hours someone worked it each day? So if I work on a project for 2 hours on the 3rd, and 5 hours on the 10th, then it would store 12 hours in the total column?

If that's the case, I would approach it in a more event based method. Have a table for the activity (ActivityID, ActivityName...). Then Have a table for work entries for the activity (ActivityID, PersonWorking, DateWorked, PersonWorked...). The hours will each only be entered into the Work table, and have a view that would sum up each activity and show the days worked along with the total. And as a note, that database systems can usually handle a lot of rows listing 4-5 fields a bit easier than trying to just have 10 years worth of dates in the column headers and just spread everything out.

In my experience it is almost never a good idea to 1)store a computed value based on data in the same record row, and 2)store date data as field names. Unless you have a VERY SPECIFIC set of dates that will apply to all projects/activities, don't use them as field headers.

Related Solutions

Mysql – Best practice design when expecting large data in a table

Yes, the same concept could be used. What you have done is re-implement table partition, but in user space. Most industrial-strength RDBMSs will have this built in. The provided functionality often includes additional abilities, such as efficiently adding and removing partitions at run time without applicaiton changes. By choosing to roll your own you miss out on these additional features. Additionally you complicate some things, such as surrogate ID uniqueness checking, aggregate queries across your whole user community and DRI referencing the "user" table.

Be aware that your sub-tables are very unlikely to be well balanced. There aren't many Mr. Aardvark or Ms. Zymology in the world but a lot of Smiths and Jones.

The reason key lookups are faster on smaller tables is because the indexes have fewer levels, assuming you have B-Tree indexes. Therefore the DBMS has to read fewer pages to get from the index's root node to its leaf & data pages. The index on your 676 sub-tables is likely to be only one or two levels deep and so incur only one or two page reads to read a key's row. In contrast a full B-Tree built on 1M rows may be three or four levels deep and require that many page reads per lookup. Built-in partitioning can give you similar benefits if you define your index as partitioned, too.

This is a good reason to keep your index keys compact, if you have a choice. For example, if your user name is 30 characters long this will take 30 bytes to store and you will get a certain number on a page. If instead you calculate an integer hash (4 bytes) of the user name and index that, there will be 30/4 = 7-ish times more rows per index page and the index will likely have fewer levels. (Of course you will have to account for potential hash collisions.) Similarly limiting the amount of free space you have in the index to allow for inserts will help increase density.

Ms-access – Splitting 1 record on one table to become two records on another table

See if this work.

 CREATE FUNCTION [dbo].[FN_CommaSeparator] (@array VARCHAR(max))

> RETURNS @Table TABLE (value VARCHAR(max))

AS
BEGIN
    DECLARE @separator_position INT
        ,@array_value VARCHAR(max)

    SET @array = @array + ','

    WHILE PATINDEX('%,%', @array) <> 0
    BEGIN
        SELECT @separator_position = PATINDEX('%,%', @array)

        SELECT @array_value = LEFT(@array, @separator_position - 1)

        INSERT @Table
        VALUES (@array_value)

        SELECT @array = STUFF(@array, 1, @separator_position, '')
    END

    RETURN
END

CREATE TABLE #Temp (
    Id VARCHAR(10)
    ,Author VARCHAR(50)
    )

CREATE TABLE #Temp2 (
    Id VARCHAR(10)
    ,Author VARCHAR(50)
    )

INSERT INTO dbo.#Temp (
    Id
    ,Author
    )
VALUES (
    'S1'
    ,'ABC,BCD'
    )
    ,(
    'S2'
    ,'CDE,DEF'
    )
    ,(
    'S3'
    ,'EFG,FGH'
    )
    ,(
    'S4'
    ,'GHI,HIJ'
    )

DECLARE @Id VARCHAR(50)
    ,@Author VARCHAR(50)

DECLARE ABC CURSOR
FOR
SELECT Id
    ,Author
FROM dbo.#Temp t

OPEN ABC

FETCH NEXT
FROM ABC
INTO @Id
    ,@Author

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO dbo.#Temp2 (
        Id
        ,Author
        )
    SELECT @Id
        ,Value
    FROM dbo.FN_CommaSeparator(@Author)

    FETCH NEXT
    FROM ABC
    INTO @Id
        ,@Author
END

DEALLOCATE ABC

SELECT *
FROM dbo.#Temp2 t

DROP TABLE dbo.#Temp

DROP TABLE dbo.#Temp2

Best Answer

Related Solutions

Mysql – Best practice design when expecting large data in a table

Ms-access – Splitting 1 record on one table to become two records on another table

Related Question