OK, Here a tough one, well for me it's really tough!!!

[sebs]

My situation is with database. One table have informations
including 4 id's and my other table have the name,lastname, etc.., i want to make a query that will go get the four name that match with the 4 id's.So when i'll work with
my recorset, i wont have to make 4 queries each time i
want the name.

Dunno if i'm clear but i really need to know this!!!!!

Thanks a lot for your help!!!!!!

[Lafor]

Make it a bit easy for us

List the fields in the table
and (if possible) a sample entry in each

Thanks

[sebs]

first table:
tblTime:
-timeId
-timeBookPlayer1
-timeBookPlayer2
-timeBookPlayer3
-timeBookPlayer4

second table:
tblPlayer:
-playerId
-playerName
-playerLastName

In tblTime, timeBookPlayer are player id, so i want to
make a query on tblTime that will return the 4 name on
tblPlayer.

So i'll be able to do something like :
rs!name1 , rs!name2 , rs!name3 , rs!name4 instead of
doiing a 4 query each time i want to get the name of the
player. Cause right now there are like 100 of element in
tblTime ,so 100 times 4 = WAY TOO LONG!!!!



[Edited by sebs on 11-14-2000 at 08:05 AM]

[sebs]

Does anybody know this one or where i could get information
for how to do this!!!

[paulw]

This will do it -

SELECT tblTime.timeID, tp1.playerName, tp2.playerName, tp3.playerName, tp4.playerName
FROM (((tblTime INNER JOIN tblPlayer AS tp1 ON tblTime.timeBookPlayer1 = tp1.playerID) INNER JOIN tblPlayer AS tp2 ON tblTime.timeBookPlayer2 = tp2.playerID) INNER JOIN tblPlayer AS tp3 ON tblTime.timeBookPlayer3 = tp3.playerID) INNER JOIN tblPlayer AS tp4 ON tblTime.timeBookPlayer4 = tp4.playerID;

BUT youre tables are not normalised, so this will only work if all fields on both sides are filled, and you cannot use an outer join to do this, you would have to use subqueries - you would be better re-designing your tables to include a junction table

i.e.
Junction
timeID
playerID

this would have one line per booking, with player id and respective time id. Then your SQL comes out as

SELECT tblTime.timeID, tblPlayer.playerName
FROM (tblTime INNER JOIN Junction ON tblTime.timeID = Junction.timeID) INNER JOIN tblPlayer ON Junction.playerID = tblPlayer.playerID;

A bit easier, no?

Cheers,

Paul

P.S.

Populate Junction automatically and implicitly...

[sebs]

Thanks a lot the first one works great but, i would need
you second idea. Can you explain a bit more on your junction
table and the relationship with the other 2 tables.

Thanx a lot, seb!!

[paulw]

I knew you were going to say that. The SQL only works if all four fields are filled in, in your tblTime table. It is better to simply define a time slot in that table (no player names) and in the player table define just player names.

The junction table, contains a record for each time slot booked by a player.

Now:
i.e Time Slot
12:00, 1, 2, 3, 4
14:00, 1, 3
16:00, 2, 4, 1

Players
1, Joe
2, Jack
3, Jeff
4, John

Using the SQL I showed first, only the 12:00 slot would be shown. That may be a problem.

Now consider the alternative,

Proposed:
Time Slot
12:00
14:00
16:00

Players
1, Joe
2, Jack
3, Jeff
4, John

Junction
12:00, 1
12:00, 2
12:00, 3
12:00, 4
14:00, 1
14:00, 3
16:00, 2
16:00, 4
16:00, 1

This set-up can use the second SQL statement. Try it.

Check out the help topics in Access on Relationships - you are trying to express a many-many relationship. It is better to split this into a many-one-many relationship.

There are probably some tutorials available here on VB-World. A good primer would help, but you could maybe search the Web for information on "Third Normal Form".

Hope that is a bit clearer.

From the Access help file:

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key consists of two fields - the foreign keys from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table. For example, the Orders table and the Products table have a many-to-many relationship that's defined by creating two one-to-many relationships to the Order Details table.

Paul.

[sebs]

Thanks a lot , but i have another for ya:
In the same query i want something from another table:

tblMemberType:
-memberTypeId
-memberTypeColor

tblTime:
-timeId
-timeBookPlayer1
-timeBookPlayer2
-timeBookPlayer3
-timeBookPlayer4

tblPlayer:
-playerId
-playerName
-playerLastName
-playerMemberTypeId

I want the memberTypeColor, is it possible.

Sorry for all those question, but you seem pretty good
with SQL!!!

[paulw]

Do the redesign first and then just JOIN the tblMemberType table to the tblPlayer table on the memberTypeID field.

i.e.

SELECT tblPlayer.playerName, tblTime.timeID, tblMember.memberTypeColor
FROM tblMember INNER JOIN (tblPlayer INNER JOIN (tblTime INNER JOIN Junction ON tblTime.timeID = Junction.timeID) ON tblPlayer.playerID = Junction.playerID) ON tblMember.memberTypeID = tblPlayer.memberTypeID;

Note this is v. similar to the previous example. It is almost impossible without the re-design. I am not just being awkward about the re-design bit, but if you do not do this step first, the sort of things you want to do become very difficult indeed, and you are using three simple tables...

Cheers,

Paul.

P.S. To learn a bit of SQL easily, use the Access query designer and then view the queries SQL source. It's a greate primer.

[sebs]

Thanks a lot PaulW,
you don't know how much time you save me, as in speed.

Now it's goiing almost 10 times faster!!!!

[paulw]

That is what I like to hear.

Cheers,

P.

Thanks Paul, for your calm approach on newby-questions etc.
Your kind answering and tutoring is what we enjoy...
look for and learn from alot!!.

[Lafor]

As much as injustice done to ONE is done to ALL
Help provided to ONE (at whatever level) is provided to ALL

Thanks Paul for helping...