Solved

Query based on two tables

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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. …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

821 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