Solved

Query based on two tables

Posted on 2007-03-20
4
181 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now