February 8, 2010 at 1:01 pm
Greetings all,
I have 6 Excel forms that I will like to turn to db objects.
Let's call these forms Form1, Form2...Form6.
Each form has several but different values.
For instance, Form1 looks similar to the following:
IncidentType #ofWeekReported YearToDate
Office Fires 1 10
House Fires 2 28
Fire Alarms 0 177
...
...
Total Fire Incidents 3 2 1 5
M Svc Response 6 19
etc
etc
After all have been listed, you get grand total.
This part doesn't need to be on the db, I don't think.
Also, these 6 forms below to 4 different Groups.
Can you please help me suggest how I can design the DB?
I hope I gave enough information.
Thanks very, very much, in advance
February 8, 2010 at 3:19 pm
Let me suggest you turn your thinking around.... to the question ... what data do you want out of the DB ... in the form of reports and /or someone running a simple select statement. Then show what data you want to include in the DB from your Excel books.
And then repost to this or a new Forum...
February 8, 2010 at 7:40 pm
Thank you very much for your response.
I thought I did just that.
I attemped to show *how* the excel file looks like.
I would like the db to have similar layout.
The users can select/add/edit/update info.
If the db can mimick the excel file, that's precisely what we want.
Again, data on excel file looks like this:
IncidentType #ofWeekReported YearToDate
Office Fires 1 10
House Fires 2 28
Fire Alarms 0 177
......
Total Fire Incidents 3 2 1 5
M Svc Response 6 19
etc
etc
The important thing there for me is to find a way to record sub groups and there sub totals.
For instance, the main groups here are IncidentType,#ofWeekReported,YearToDate
Then sub groups: office fires, house fires, fire Alarms and their sub total.
Then another subgroup begins, until all subgroups are record and then a grand total.
If I am still not giving you or any kind hearts willing to assist what you need, please let me know.
Once again, thanks a lot for your assistance.
February 9, 2010 at 1:38 pm
See below for a guess. I have used an artificial key which some may disagree with.
All calculations, incidents of a particular type or subtype, incidents per week, incidents per year, etc. do not need to be in the db. Calculate this data when needed.
CREATE TABLE [dbo].[Incidents](
[IncidentID] [int] IDENTITY(1,1) NOT NULL,
[IncidentDate] [datetime] NOT NULL,
[IncidentType] [nchar](20) NOT NULL,
[IncidentSubType] [nchar](20) NULL
) ON [PRIMARY]
This assumes the db will hold every incident, that is, the primary data about each incident. If you db holds summary info then that is a different method.
Do the users enter every incident or do they enter, for example, the number of house fires every week?
February 9, 2010 at 1:48 pm
I'll ramble a bit here so feel free to ignore if this doesn't apply to your situation.
If you are modeling after paper or excel forms then you are essentially saying the process is perfect and I just need to migrate this to a database. I would closely examine your process, this is your opportunity to change it, once it becomes realized in your database model, changes will be difficult.
If you store each and every incident you will be able to perform more robust analysis than if you are storing summary data.
If you are storing the number of office fires per week you will never be able to answer a question like, on what day of the week do the most office fires happen? or do house fires spike on holidays? etc...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply