Solved

Query based on two tables

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 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