Summarizing Database Content in VB app
Summarizing Database Content in VB app
Summarizing Database Content in VB app
Summarizing Database Content in VB app
Summarizing Database Content in VB app
Summarizing Database Content in VB app Summarizing Database Content in VB app Summarizing Database Content in VB app Summarizing Database Content in VB app Summarizing Database Content in VB app Summarizing Database Content in VB app Summarizing Database Content in VB app Summarizing Database Content in VB app
Summarizing Database Content in VB app Summarizing Database Content in VB app
Summarizing Database Content in VB app
Go Back  Xtreme Visual Basic Talk > > > Summarizing Database Content in VB app


Reply
 
Thread Tools Display Modes
  #1  
Old 05-09-2013, 11:13 AM
themba themba is offline
Newcomer
 
Join Date: May 2013
Posts: 1
Default Summarizing Database Content in VB app


Hi All

I am Student and very new in development.
I created a access database with 2 tables, one that holds employees details and another that holds students details, all linked to my vb.net app.

I want to create another table and name it summary, i want this table to hold a summary of the other tables. For example - i would like a code that count how many student details were saved on this date, how many employees are above the age 30... Then store the results in the summary table.... Would really appreciate your help
Reply With Quote
  #2  
Old 05-10-2013, 02:56 AM
DrPunk's Avatar
DrPunkSummarizing Database Content in VB app DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

I'm not really sure why you'd want to do this.

The kind of data you're talking about is queried from the table when you want to know it instead of being written to a table (that ultimately gets queried when you want to know it).

Take the summary of how many employees are above the age of 30. Every time you add an employee you have to check their age, if they are over 30 then you have to increment a field in the summary table. Add to that that people are a different age every day. There might be no employees over 30 when they were added, but that was last week and this week 10 of them might have had birthdays and are 31 now, but the summary table says there are 0 over 30. Are you going to run updates on the summary table every day to keep the information correct?

When alternatively, if someone wants to know how many employees are over 30 you could just run the query...
Code:
SELECT COUNT(*) FROM Employess WHERE DATEDIFF(year, Birthdate, GETDATE()) > 30
.. and it'll tell you there and then. Based on today, not when they were added.
__________________
There are no computers in heaven!
Reply With Quote
  #3  
Old 06-04-2013, 01:25 PM
loquin's Avatar
loquinSummarizing Database Content in VB app loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default

Well... Data warehouses can use this approach for their storage. Since the data is updated in a batch mode from various OLTP (and other) data sources, and once stored doesn't change much, they trade small size and updateability for uber-fast data retrieval.
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown
Reply With Quote
  #4  
Old 06-04-2013, 01:55 PM
Gruff's Avatar
GruffSummarizing Database Content in VB app Gruff is offline
Bald Mountain Survivor

Retired Moderator
* Expert *
 
Join Date: Aug 2003
Location: Oregon, USA - deceased
Posts: 6,440
Default

Hey Lou! Great to see you show here again. Hope your knees are sound.

This is an interesting topic.
Since themba is a beginner I suspect he just doesn't know that good database practices say not to combine fields into new tables. I would hate for him to think it is normal to do so.

That said I have used Views to combine data on rare occasions. Of course that was done on the server side manually.
__________________
Burn the land and boil the sea
You can't take the sky from me


~T
Reply With Quote
  #5  
Old 06-04-2013, 03:36 PM
loquin's Avatar
loquinSummarizing Database Content in VB app loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,400
Default

Very true, Tom.

themba: For MOST cases, you do not want to duplicate data in your database (and aggregate data, or other forms of derived data is a form of duplication,) unless there is a very compelling reason for doing so. I would recommend that you search 'data normalization' on the web. The data normalization tutorial here is a good starting point.

Remember, every time you would update ANY of the 'source' data used in a summary table, the summary table would then be out of synch. Unless you updated this table after every change to the source table(s) as a part of a transaction, you wouldn't know, just from examining the tables (without running the aggregate query, that is) WHICH table is incorrect. An end user certainly wouldn't know.
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown
Reply With Quote
  #6  
Old 06-04-2013, 04:36 PM
Gruff's Avatar
GruffSummarizing Database Content in VB app Gruff is offline
Bald Mountain Survivor

Retired Moderator
* Expert *
 
Join Date: Aug 2003
Location: Oregon, USA - deceased
Posts: 6,440
Default

Themba,

To sum up: If you want to keep track a users age what you want to store is their date of birth. This way at any time now or in the future you can get their age by a simple query that takes todays date and subtracts their date of birth.

In a similar manner you would get a users full name by joining their first and last name fields rather than making a third field for their fuilname..

SELECT FirstName + ' ' + LastName FROM tblUSERS WHERE MemberShipRank = 'Gold'
__________________
Burn the land and boil the sea
You can't take the sky from me


~T
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Summarizing Database Content in VB app
Summarizing Database Content in VB app
Summarizing Database Content in VB app Summarizing Database Content in VB app
Summarizing Database Content in VB app
Summarizing Database Content in VB app
Summarizing Database Content in VB app Summarizing Database Content in VB app Summarizing Database Content in VB app Summarizing Database Content in VB app Summarizing Database Content in VB app Summarizing Database Content in VB app Summarizing Database Content in VB app
Summarizing Database Content in VB app
Summarizing Database Content in VB app
 
Summarizing Database Content in VB app
Summarizing Database Content in VB app
 
-->