Solved

Query based on two tables

Posted on 2007-03-20
4
184 Views
Last Modified: 2010-03-20
Hi experts,

I'm new to SQL so please help me. I need to write a query based on 2 tables. First table is "tblRecords" it contains the following fields:

UserID (number) , WeekID (number) , Approved (yes/no)

The second table is called "tblWeeks" and it contains:

WeekID (total of 4 weekIDs starting from 1-4), Week (description of the job in  that week)

What I need to do is to create a query that would display a recordset based on users, putting each user in one row and have 4 approved fields  for each week based on the weekID so it should look something like this:

   User    |  Approved for Week1 |  Approved for Week2 |  Approved for Week3 |  Approved for Week4 |
_____________________________________________________________________________________
  user1                   1                                     1                                  0                                        0
  user2                   1                                     0                                  1                                        0


and so on....

please help me out on this thanks!
0
Comment
Question by:techman36
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 18757345
What DBMS?

I'll answer like it's MS SQL Server ... may need to adjust a little if you're using something else.

Select R1.UserID as [user],
[Approved for Week1] = sum(Case when r1.Approved) Then 1 else 0 End,
[Approved for Week2] = sum(Case when r2.Approved) Then 1 else 0 End,
[Approved for Week3] = sum(Case when r3.Approved) Then 1 else 0 End,
[Approved for Week4] = sum(Case when r4.Approved) Then 1 else 0 End

From   tblRecords R1 Inner Join
  tblRecords R2 On R1.UserID = R2.UserID And R1.WeekID = 1 and R2.WeekID = 2 Inner Join
  tblRecords R3 On R2.UserID = R3.UserID And R3.WeekID = 3 Inner Join
  tblRecords R4 On R3.UserID = R4.UserID And R4.WeekID = 4

Group by R1.user
0
 

Author Comment

by:techman36
ID: 18757570
Can you please adjust it for access? I'm new at this.
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 100 total points
ID: 18758038
Should have guessed it was Access based on the data types.

Just tested this w/ Access 2003.


Select R1.UserID as [user],
  sum(abs(cint(R1.Approved))) as [Approved for Week1],
  sum(abs(cint(R2.Approved))) as [Approved for Week2],
  sum(abs(cint(R3.Approved))) as [Approved for Week3],
  sum(abs(cint(R4.Approved))) as [Approved for Week4]

From   (((tblRecords AS  R1 Inner Join
  tblRecords R2 On R1.UserID = R2.UserID)  Inner Join
  tblRecords R3 On R2.UserID = R3.UserID)  Inner Join
  tblRecords R4 On R3.UserID = R4.UserID)
Where  R1.WeekID = 1 and R2.WeekID = 2 And R3.WeekID = 3 And R4.WeekID = 4
Group by R1.userID
0
 

Author Comment

by:techman36
ID: 18763022
Thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question