Reports and Updates

[dom24]

Hi,

1) Can somebody please tell me if it's possible to generate reports from SQL server and how?

2) Now my system is complete I will be passing the database over to the admin staff. Each week they will update the database with new data but I don't want the old data to be deleted. How can I write the code so that if the new data is the same as the old data then ignore it, otherwise enter a new record? The main changes will be when new staff join the company or staff leave.

3) Is there a way of copying data from one table to another when an update is complete? I want particular columns to be transferred to other tables when they have been updated to save admin staff ahving to pick out the data required and paste it into the other tables.

Any help would be greatly appreciated! Thanks.

 

[hmckillop]

Answers:
1 - There are many third party reporting tools available for producing reports - such as Crystal reports, MS reporting services etc. Ultimately most of these require a database developer to create stored procedures to create the data (or at least the queries to return the data) and then someone to design the report. There is no product which ships with SQL which will give you a "report" as such, but you can use query analyser to query the data and return information - I would however strongly recommend against the latter option unless the person is an administrator and has the priveledge to view the data.

2 - When you say write code to stop duplicates what do you mean? - Does each field have to be exactly the same, just some of them or one in particular to meet the criteria of duplicates?
Its relatively easy to do, but you need to be clear on what defines a duplicate. To prevent duplicates, simply do a lookup prior to any insert (i.e. SELECT COUNT(*) FROM MyTable Where field1='John') if this returns a value > 0 then a match exists and you should perform an update alternatively do an INSERT. But once again ensure you know what criteria performs a match (i keep going on about this but if you have two people who have the same name, you dont want their data to be constantly overwritten by the others, alternatively if someone mistypes a small input at the front end you may end up with a duplicate regardless!)

3- Copying from one table to another is easy,
INSERT INTO TableA(field1, field3, field4)
SELECT MatchField1, MatchField3, MatchField4
FROM MyOtherTable
WHERE UpdatedFlag=1 --or some other indicator for updates, most people generally use a datefield i.e. Version!

Hope this helps


"I'm living so far beyond my income that we may almost be said to be living apart

 

[dom24]

Thanks for that.

With regards to 2) i'll be using the StaffNo as the criteria. What I want is, if the same staffno is being inserted then it ignores this record and carries on to the next one. The thing is, the admin staff will be importing this data from an excel file so how do I get the code to run automatically during the import?

Also, for 3), I want this to run automatically when the staff table is updated. Can I do this?

Thanks.

 

[hmckillop]

Answers/Questions

2: OK, how do you plan to import this data? Triggers might be the way forward for this depending on how you import.
Alternatively you could batch upload to a holding table and then sequentially process it. More info please.

3: You can use a AFTER UPDATE trigger to do this.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[dom24]

2) Err.......not sure. What would you suggest?
I was going to let the admin staff have access to the database and then do an import from there using the wizard.
I'm open to suggestions though as i've never done this before.

3) Not sure what the AFTER UPDATE trigger does but i'lll look into that now. Thanks.

 

[hmckillop]

Letting admin staff near the database sounds like a recipe for disaster

have you ever built a client/server app, do you have access to a programming tool such as VB.
Alternatively you could amend the spreadsheet and add macro code which will allow the admin team to maintain their data view their spreadsheet but store it centrally on the database.

If you plan to allow updates to happen as you say, by allowing the admin staff access to the database wizards (do they have any technically knowledge or experience), you need to be prepared to have a good backup and recovery strategy.


Both of your questions 2 and 3 can be answered by triggers, number 2 should use an INSTEAD OF INSERT trigger and number 3 should use an AFTER Trigger.

Samples shown in books online should provide enough information. If you have any specific coding issues please repost.




"I'm living so far beyond my income that we may almost be said to be living apart