| Author |
Topic |
|
tomislavg
Yak Posting Veteran
51 Posts |
 Posted - 2010-09-01 : 03:24:04
|
| Hi,I need your assistance.I have two tables. One is Employer and other one is their employee.I need to run the seven sp's which will show me each time different employee (I have seven employees).Example: First sp will show me Employers and his first employee, the second one will show me same Employer and his second employee, the third one will show me the same employer and his third employee and like this until the seven employees. I know it is a mess but I cannot do it different because of my fron end application.I know the syntax would go something like this:Select field1, field2, field3from Employerleft join EmployeeonEmployeer.id = Employee.idThanks for your help |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-01 : 05:06:52
|
| This sounds like a very bad plan.What happens when you hire another employee? are you going to make a stored proc for every employee????it would be much, much better to return a set of data (with an employee rank if you like) to the front end app.If you absolutely need to have a stored proc to return ONE employer / employee pair You could have it accept a @employee_Rank variable and display that employee (based on whatever criteria you use for the ranking).Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
tomislavg
Yak Posting Veteran
51 Posts |
Posted - 2010-09-01 : 08:57:17
|
Thanks Charlie,I know it is a bad plan but I am stuck with it.The company can not hire nore then 7, so I am safe on that side.I just need to display Enployee one, two, three etc up to seven for the same Employer. I would use TOP (1,2...7)in TSQL but it is random. So again it is not giving me what I need.quote:
Originally posted by Transact Charlie
This sounds like a very bad plan.What happens when you hire another employee? are you going to make a stored proc for every employee????it would be much, much better to return a set of data (with an employee rank if you like) to the front end app.If you absolutely need to have a stored proc to return ONE employer / employee pair You could have it accept a @employee_Rank variable and display that employee (based on whatever criteria you use for the ranking).Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
|
|
|
|
CSears
Starting Member
39 Posts |
Posted - 2010-09-01 : 09:46:03
|
| I would use the SELECT TOP command. Just remember that you need to order them in order for it not to be random as you call it.SELECT TOP 1 * FROM YourTable ORDER BY SomeOrderingColumn |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-01 : 09:49:51
|
| How do you need the results to look.Can you give us a sample dataset (should be easy -- doesn't sound like much data!) and the required output.You are using SQL SERVEr 2005?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
tomislavg
Yak Posting Veteran
51 Posts |
Posted - 2010-09-02 : 04:13:47
|
ok the problem is that I need to get the data horizontal in the front end application, which is Access. I hate that but I have to work with it also I cannot do anything about the front end application.So in order to present the Report in Access I need to figure out how to get the data I need. Another problem is that the data also have pictures, otherwise I would use datasheet view in Access. I know that continuous forms cannot go horizontal.So I am trying to find another way thru SQL to arrange the data, then to connect seven sp or views into one report and ….that’s it! Yeah right… not simple, I know…And I am using SQL 2005.quote:
Originally posted by Transact Charlie
How do you need the results to look.Can you give us a sample dataset (should be easy -- doesn't sound like much data!) and the required output.You are using SQL SERVEr 2005?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
|
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2010-09-02 : 04:31:35
|
| if it's just a matter of getting data horizontally, Then perhaps you could use PIVOT operator in sql 2005.Formatting etc, I think you should handle on report side. |
|
|
|
tomislavg
Yak Posting Veteran
51 Posts |
Posted - 2010-09-02 : 06:48:34
|
| ok let me try and see if it will work...thanks....It does not work, I need data like this...with photo in the employee table...Employeer 1 | Employee1 | Employee2 | Employee3 | etc....Employeer 2 | Employee1 | Employee2 | Employee3 | etc.... |
|
|
|
tomislavg
Yak Posting Veteran
51 Posts |
Posted - 2010-09-05 : 12:32:45
|
| OK, I know that many people as soon as they saw that I was using MS Access as front end didn't even wanted to bother to check the post but truth is that many people are stuck with this kind of db setup and do not have time or resources to upgrade their application to any other platform. As you know, development for the beginner takes months even years.I wanted to post the solution that I was trying to find myself on the net and was not able to.- Horizontal Forms in MS Access? I created 7 views in SQL and combined them together with the stored procedure showing the results in MS Access report and that is it. The key field that enabled me to that is that "Employee 1" had a field specific only to "Employee 1" table and "Employee 2" also had a specific field only related to "Employee 2" same for other Employees and in that way I managed to separate them in seven tables.This way I managed to get around the problems in creating it with and in MS Access application. I know it is not the perfect solution but...... it works.Thanks a lot for your assistance all,I am still having problems with displaying pictures in SQL SSRS when importing the photos with MS Access but I will find the solution. One step at the time....Cheers |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-06 : 15:27:01
|
| Hi Tomislavg,Sorry that I didn't post back -- I'm on holiday so am not able to catch up with emails / posts.My gut feeling was that you should have been able to create a view / procedure that did what you wanted fairly easily.What we needed from you (and what you failed to provide) was this:1) the structure of the tables involved. (right click on table - go to script table as.....)2) sample data for those tables.3) required output (this is the bit that you did do)Either using multiple joins, or pivot, or some other operator we can then show you a simple way to get the output required.I'm sorry that you feel a little let down by the forum. In my experience that's rather rare but you do need to provide the info we request if you want any proper help.Best of luck -- if you do post the details I"ll check by the next time I'm online.Regards,Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
tomislavg
Yak Posting Veteran
51 Posts |
Posted - 2010-09-06 : 17:01:35
|
Hi Charlie, the forum is great, I could spend days searching what I need or can do with SQL and not getting bored.It helped me a lot, but you know I am also struggling with the MS Access/SQL but as far as the SQL, I love what it can do.As for my query above, you were right, the solution was quite simple but I started as it was more complicated. I did what you wrote. Created few views and combined them with the stored procedure and got the results.Ok, now I have another problem, let me try to find the solution first and if not... I'll post it.Enjoy the holiday but without internet ... yes, I know it is not that easy... :-)Cheers,quote:
Originally posted by Transact Charlie
Hi Tomislavg,Sorry that I didn't post back -- I'm on holiday so am not able to catch up with emails / posts.My gut feeling was that you should have been able to create a view / procedure that did what you wanted fairly easily.What we needed from you (and what you failed to provide) was this:1) the structure of the tables involved. (right click on table - go to script table as.....)2) sample data for those tables.3) required output (this is the bit that you did do)Either using multiple joins, or pivot, or some other operator we can then show you a simple way to get the output required.I'm sorry that you feel a little let down by the forum. In my experience that's rather rare but you do need to provide the info we request if you want any proper help.Best of luck -- if you do post the details I"ll check by the next time I'm online.Regards,Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
|
|
|
|
|
Stored procedure