Table Structure with Date Fields

Question

Post reply

Table Structure with Date Fields

jwellington

Ten Centuries

Points: 1062
More actions
July 29, 2009 at 1:24 am

#224048

Hi,
Currently in the organisation I work in, people are used to spreadsheets. So when it comes to filling out dates/milestones for projects, they are used to viewing and updating the information in spreadsheet format, with each new milestone/date field in a separate column.
This is quite inefficient for the database. It would be more effective to have a structure where there are 2 tables.
1st Table: DateID, DateName.
2nd Table: ProjectID, DateID, Date.
This would enable different milestone/date names for different projects. And also increase the number of rows rather than columns.
The only problem here is that I cannot find a way to then represent this in spreadsheet format, so the users can both view and update the information in the way they are used to.
Is there such a way, or is there a way to structure this differently, so as to get the best of both worlds?
I have looked into using the PIVOT command, but would only be able to view the data (not Update it)
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

How do you want to update your data.

One way would be to pivot the data and send the output to a excel file. Users can update the excel file and you can read the data from the excel file , UNPIVOT and store it back to the database.

"Keep Trying"

jwellington Ten Centuries Points: 1062 More actions · Answer 2

Hi,

This looks like a good solution. The only downside is ensuring that users update the cells in excel in the correct format. But I assume that the UNPIVOT would not work if there was a cell in the incorrect format.

Also, users are used to working in excel, and would like to use it to input and manipulate data.

Could you provide more information about how to link this way with excel. I am familiar with how to output information to excel, but not how to then retrieve it from excel. Have also never used PIVOT or UNPIVOT.

Any help on both these issues, or any other suggestions, would be great.

Thanks

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

Hi

For Pivot and Unpivot look up BOL and the web. There are numerous examples. They are slightly complex but only slightly.

For putting the output of your Pivot query to excel take a look at the BCP utility. You may not be able to use the Pivot and BCP both in a single query. If so load a staging table with the data from the pivot query and then use the BCP on the staging table. This will keeps things simple.

Alternatively you can look at creating a SSIS package for this if you need to do this very regulary (dont ask me abt SSIS iam still learning).

For fetching the data back into SQL from excel you can use BCP or BulkInsert or SSIS.

Try and come up with a solution and come back to this forum if you have any queries.

"Keep Trying"

jwellington Ten Centuries Points: 1062 More actions · Answer 4

Thanks Chirag.

Will look into it further and see which method will work best.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

ChiragNS

One Orange Chip

Points: 26137

August 4, 2009 at 4:43 am

#1034161

Keep us updated.

"Keep Trying"