Quering Table

B

Bij12

I have three tables in database.

1. Membership table where all user information is stored.

2. Award Table where MemberID, Award ID and Award Year received ( all the
fields makes composit Primary key, means to say that one member can recive
multiple award in different year period)

3. AwardType Table where AwardID and Award Name

Now I want the report of all the members who regardless of there Award . but
my problem of retriving all Award name as a column.

Like
ID Name Award"A" Award "B" Award"C" and respective year.

My Query looks like this

SELECT Membership.memID, Membership.LastName, Membership.FirstName,
MemberShipAward.AwardId, MemberShipAward.[Award-Rec-Year]
FROM Membership LEFT JOIN MemberShipAward ON Membership.memID =
MemberShipAward.memID;

I get the result as

ID Name Award-Type Award-Year

12 ABC A 1959
12 ABC B 2000
13 DEF
14---------------------

Is it any way I can concatinate the Award Type and get as all Award Type as
seperate column?

Any suggestion is really appreciated.

Thank you
Bij
 
J

Jerry Whittle

Check out crosstab queries in help. Use the query that you have now as the
record source for a crosstab query.
 
B

Bij12

Thank you for the quick response I am going to try.

Bij

Jerry Whittle said:
Check out crosstab queries in help. Use the query that you have now as the
record source for a crosstab query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Bij12 said:
I have three tables in database.

1. Membership table where all user information is stored.

2. Award Table where MemberID, Award ID and Award Year received ( all the
fields makes composit Primary key, means to say that one member can recive
multiple award in different year period)

3. AwardType Table where AwardID and Award Name

Now I want the report of all the members who regardless of there Award . but
my problem of retriving all Award name as a column.

Like
ID Name Award"A" Award "B" Award"C" and respective year.

My Query looks like this

SELECT Membership.memID, Membership.LastName, Membership.FirstName,
MemberShipAward.AwardId, MemberShipAward.[Award-Rec-Year]
FROM Membership LEFT JOIN MemberShipAward ON Membership.memID =
MemberShipAward.memID;

I get the result as

ID Name Award-Type Award-Year

12 ABC A 1959
12 ABC B 2000
13 DEF
14---------------------

Is it any way I can concatinate the Award Type and get as all Award Type as
seperate column?

Any suggestion is really appreciated.

Thank you
Bij
Click to expand...
Click to expand...
 
B

Bij12

Thank you Ken for the quick response. I am going to try this approch & let
you know

bij

KenSheridan via AccessMonster.com said:
The usual approach to this is to use a VBA function to return the string of
concatenated awards per member/year. The following is an adaptation of one I
use for a similar sort of thing:

Public Function GetAwards(varMemID as Variant, varYear as Variant) As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strAwards As String

If Not IsNull(varMemID) Then
strSQL = "SELECT [Award Name] " & _
"FROM MemberShipAward INNER JOIN " & _
"AwardType ON AwardType.AwardID = " & _
"MemberShipAward.AwardID " & _
" WHERE MemberShipAward.AwardID = " & _
varMemID & " AND [Award-Rec-Year] = " & _
varYear & " ORDER BY [Award Name]"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

With rst
Do While Not .EOF
strAwards = strAwards & ", " & _
.Fields("Award Name")
.MoveNext
Loop
.Close
' remove leading comma and space
strAwards = Mid$(strAwards, 3)
End With

GetAwards = strAwards
End If

End Function

Your query would then be:

SELECT DISTINCT Membership.memID,
Membership.LastName, Membership.FirstName,
GetAwards(MemberShipAward.memID,
MemberShipAward.[Award-Rec-Year]) AS AwardList,
MemberShipAward.[Award-Rec-Year]
FROM Membership LEFT JOIN MemberShipAward
ON Membership.memID = MemberShipAward.memID;

However, my recommendation would be to do it with a report and subreport.
The report would list all members and award years with:

SELECT DISTINCT Membership.memID,
Membership.LastName, Membership.FirstName,
MemberShipAward.[Award-Rec-Year]
FROM Membership LEFT JOIN MemberShipAward
ON Membership.memID = MemberShipAward.memID;

and the subreport all awards made with:

SELECT memID, [Award Name], [Award-Rec-Year]
FROM MemberShipAward INNER JOIN
AwardType ON AwardType.AwardID =
MemberShipAward.AwardID;

Link the parent report/subreport on memID;[Award-Rec-Year] and lay out the
subreport in 'across then down' columns. You'll find a demo of this type of
layout at:

http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24271


The demo also includes a single report solution in which the same thing is
done by manipulating the report layout in code in its module at runtime. The
subreport solution is far simpler, however. The code solution is only
included merely to show that it could be done, because the reports were
originally produced in answer to a request by a reader of a magazine column
written by a contact of mine, who asked for such a solution.

Ken Sheridan
Stafford, England
I have three tables in database.

1. Membership table where all user information is stored.

2. Award Table where MemberID, Award ID and Award Year received ( all the
fields makes composit Primary key, means to say that one member can recive
multiple award in different year period)

3. AwardType Table where AwardID and Award Name

Now I want the report of all the members who regardless of there Award . but
my problem of retriving all Award name as a column.

Like
ID Name Award"A" Award "B" Award"C" and respective year.

My Query looks like this

SELECT Membership.memID, Membership.LastName, Membership.FirstName,
MemberShipAward.AwardId, MemberShipAward.[Award-Rec-Year]
FROM Membership LEFT JOIN MemberShipAward ON Membership.memID =
MemberShipAward.memID;

I get the result as

ID Name Award-Type Award-Year

12 ABC A 1959
12 ABC B 2000
13 DEF
14---------------------

Is it any way I can concatinate the Award Type and get as all Award Type as
seperate column?

Any suggestion is really appreciated.

Thank you
Bij
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
B

Bij12

Thank you Jerry,

I use the crosstab query, that solved my problem.

Thanks you saved my day.

bij

Jerry Whittle said:
Check out crosstab queries in help. Use the query that you have now as the
record source for a crosstab query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Bij12 said:
I have three tables in database.

1. Membership table where all user information is stored.

2. Award Table where MemberID, Award ID and Award Year received ( all the
fields makes composit Primary key, means to say that one member can recive
multiple award in different year period)

3. AwardType Table where AwardID and Award Name

Now I want the report of all the members who regardless of there Award . but
my problem of retriving all Award name as a column.

Like
ID Name Award"A" Award "B" Award"C" and respective year.

My Query looks like this

SELECT Membership.memID, Membership.LastName, Membership.FirstName,
MemberShipAward.AwardId, MemberShipAward.[Award-Rec-Year]
FROM Membership LEFT JOIN MemberShipAward ON Membership.memID =
MemberShipAward.memID;

I get the result as

ID Name Award-Type Award-Year

12 ABC A 1959
12 ABC B 2000
13 DEF
14---------------------

Is it any way I can concatinate the Award Type and get as all Award Type as
seperate column?

Any suggestion is really appreciated.

Thank you
Bij
Click to expand...
Click to expand...
 
B

Bij12

Ken I used the crosstab Query that solved my problem.

Thank you

Bij12 said:
Thank you Ken for the quick response. I am going to try this approch & let
you know

bij

KenSheridan via AccessMonster.com said:
The usual approach to this is to use a VBA function to return the string of
concatenated awards per member/year. The following is an adaptation of one I
use for a similar sort of thing:

Public Function GetAwards(varMemID as Variant, varYear as Variant) As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strAwards As String

If Not IsNull(varMemID) Then
strSQL = "SELECT [Award Name] " & _
"FROM MemberShipAward INNER JOIN " & _
"AwardType ON AwardType.AwardID = " & _
"MemberShipAward.AwardID " & _
" WHERE MemberShipAward.AwardID = " & _
varMemID & " AND [Award-Rec-Year] = " & _
varYear & " ORDER BY [Award Name]"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

With rst
Do While Not .EOF
strAwards = strAwards & ", " & _
.Fields("Award Name")
.MoveNext
Loop
.Close
' remove leading comma and space
strAwards = Mid$(strAwards, 3)
End With

GetAwards = strAwards
End If

End Function

Your query would then be:

SELECT DISTINCT Membership.memID,
Membership.LastName, Membership.FirstName,
GetAwards(MemberShipAward.memID,
MemberShipAward.[Award-Rec-Year]) AS AwardList,
MemberShipAward.[Award-Rec-Year]
FROM Membership LEFT JOIN MemberShipAward
ON Membership.memID = MemberShipAward.memID;

However, my recommendation would be to do it with a report and subreport.
The report would list all members and award years with:

SELECT DISTINCT Membership.memID,
Membership.LastName, Membership.FirstName,
MemberShipAward.[Award-Rec-Year]
FROM Membership LEFT JOIN MemberShipAward
ON Membership.memID = MemberShipAward.memID;

and the subreport all awards made with:

SELECT memID, [Award Name], [Award-Rec-Year]
FROM MemberShipAward INNER JOIN
AwardType ON AwardType.AwardID =
MemberShipAward.AwardID;

Link the parent report/subreport on memID;[Award-Rec-Year] and lay out the
subreport in 'across then down' columns. You'll find a demo of this type of
layout at:

http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24271


The demo also includes a single report solution in which the same thing is
done by manipulating the report layout in code in its module at runtime. The
subreport solution is far simpler, however. The code solution is only
included merely to show that it could be done, because the reports were
originally produced in answer to a request by a reader of a magazine column
written by a contact of mine, who asked for such a solution.

Ken Sheridan
Stafford, England
I have three tables in database.

1. Membership table where all user information is stored.

2. Award Table where MemberID, Award ID and Award Year received ( all the
fields makes composit Primary key, means to say that one member can recive
multiple award in different year period)

3. AwardType Table where AwardID and Award Name

Now I want the report of all the members who regardless of there Award . but
my problem of retriving all Award name as a column.

Like
ID Name Award"A" Award "B" Award"C" and respective year.

My Query looks like this

SELECT Membership.memID, Membership.LastName, Membership.FirstName,
MemberShipAward.AwardId, MemberShipAward.[Award-Rec-Year]
FROM Membership LEFT JOIN MemberShipAward ON Membership.memID =
MemberShipAward.memID;

I get the result as

ID Name Award-Type Award-Year

12 ABC A 1959
12 ABC B 2000
13 DEF
14---------------------

Is it any way I can concatinate the Award Type and get as all Award Type as
seperate column?

Any suggestion is really appreciated.

Thank you
Bij
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Design help for community group database 9
Please help with upgrade info 3
third table relationship and query on form 3
Matching Data from 1 column across many columns 1
Showing in an HTML table continuing data 1
Table Design: common attributes in multiple entities 7
Renewing member database design question. 7
having problem with table 1

Top