Please bear with this question, as I know there is a simple solution, but I haven't jumped into an Access Database for a good year or so and my brain is having trouble figuring something out.

I have two tables (tblMembers and tblCrews). tblMembers list all of the members of an organization and tblCrews has a 4 fields that assigns 4 individual MemberID #'s to a single CrewID in a single row.

Now I'm wanting to display the names for each of the members belonging to a crew in a query but can't figure out how to do so in the query as I can't link the MemberID of the tblMembers table to each of the fields in the tblCrews table.

If this makes sense and anyone has any ideas to help me get past the "Access Developers Block" I'd appreciate it.

The table structure is not ideal. Use three tables. Your query will be easy this way.

Members: MemberID (PK), MemberName, etc
Crews: CrewID (PK), CrewName
CrewMembers: CrewID (FK), MemberID (FK)

CrewMembers is a join table and allows Members to be in multiple teams and Teams to have any number of Members.

Otherwise you will need to join a separate copy of Members to each Crew field.

Alright I now have 3 tables (tblMembers, tblCrews, tblCrewMembers)

tblMembers
----------
MemberID (PK)
FirstName
LastName
PositionID


tblCrews
--------
CrewID (PK)


tblCrewMembers
--------------
CrewMembersID (PK)
CrewID (FK)
MemberID (FK)


As you can see there is only one field in the tblCrews table and acts as just that joining table.
What I'm trying to figure out now is how to display the names of the crew in a single row on a combobox identifying that crew with the last name of all of the members via an expression

I think this would work for you:

http://www.mvps.org/access/modules/mdl0004.htm

Air Force Dad

Hey Paul -

Thanks for the link. How have you been?

I was looking over it and I'm not so sure I have a 1:M relationship now that I've added the 3rd table in there. If I do (trying to knock the rust off), I'm struggling with getting the Public Function to display correctly. I've added it into a module and am able to call it but can't yield any results. I'm using currently:

Expr1: fConcatChild("tblCrewMembers","CrewID","LastName","Text","1")

Hey Russ, I'm doing great; hope you are too! I visited Air Force Daughter in England back in September. She'll be there for another couple of years, unless something changes.

For starters, I think you made the same mistake I made when I first experimented with it. I believe it wants "String", not "Text". Can you post a db with enough sample data in it to test with?

That's great. I take it you got to take in a good number of the sights and sounds while you were visiting?

I'm posting up a copy of the DB with some dummy data now.

I've got several questions, whatever you'd had time to look over:

1) Displaying each member of the crew in a drop down in the format "Member1LastName, Member2LastName, etc.)

2) How to break up the fields in the tblMissions Date fields on the Missions form so that the user can input/edit/view the date and time in two separate textboxes but have the value stored in one field in the table

3) Create a query that will display the number of hours since a crew that was assigned to a mission last had an ETATime. In other words, I need to use the most recent ETATime in the tblMissions table for each crew to the present time which will tell me how long it's been since they've completed a mission.

Those are the stumbling blocks I've ran into so far. Any help would, as always, be appreciated.

I'll get to these as I have time. Because your needs are a little customized (name in related table) I wrote my own function (I'll let you add error handling):

Public Function PaulConcat(CrewID As Long) As String
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()

strSQL = "SELECT tblCrewMembers.CrewID, tblMembers.LastName " _
& "FROM tblCrewMembers INNER JOIN tblMembers ON tblCrewMembers.MemberID = tblMembers.MemberID " _
& "WHERE tblCrewMembers.CrewID = " & CrewID
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Do While Not rs.EOF
PaulConcat = PaulConcat & rs!LastName & "; "
rs.MoveNext
Loop

If PaulConcat <> "" Then
PaulConcat = Left(PaulConcat, Len(PaulConcat) - 2)
End If

Set rs = Nothing
Set db = Nothing
End Function

then try this query:

SELECT tblCrews.CrewID, PaulConcat([crewid]) AS CrewNames
FROM tblCrews

Regarding number two, here's an example where I have two unbound textbox for date and time, and one hidden bound textbox. After update of the unbound textboxes:

If IsDate(Me.txtOutDate) And IsDate(Me.txtOutTime) Then
Me.OutDateTime = Me.txtOutDate + Me.txtOutTime
Else
Me.OutDateTime = Null
End If

In the current event:

If IsDate(Me.OutDateTime) Then
Me.txtOutDate = DateValue(Me.OutDateTime)
Me.txtOutTime = TimeValue(Me.OutDateTime)
End If

For number 3, I don't see a field in tblMissions for CrewID. Is that an oversight, or how would you determine which crew did the mission? If you add that field, a technique like this could be used:

http://www.baldyweb.com/LastValue.htm

I just noticed tblSchedule which addresses my question regarding number 3. Presuming each mission can only have one crew, I'd probably just have a field in the mission table for CrewID. If you keep this table, this query returns the last mission date for each crew:

SELECT tblSchedule.CrewID, Max(tblMissions.ETATime) AS LastETATime
FROM tblSchedule INNER JOIN tblMissions ON tblSchedule.MissionID = tblMissions.MissionID
GROUP BY tblSchedule.CrewID

That combined with the technique in the link above should get whatever related info you want.

I'll get to these as I have time. Because your needs are a little customized (name in related table) I wrote my own function (I'll let you add error handling):

Public Function PaulConcat(CrewID As Long) As String
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()

strSQL = "SELECT tblCrewMembers.CrewID, tblMembers.LastName " _
& "FROM tblCrewMembers INNER JOIN tblMembers ON tblCrewMembers.MemberID = tblMembers.MemberID " _
& "WHERE tblCrewMembers.CrewID = " & CrewID
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Do While Not rs.EOF
PaulConcat = PaulConcat & rs!LastName & "; "
rs.MoveNext
Loop

If PaulConcat <> "" Then
PaulConcat = Left(PaulConcat, Len(PaulConcat) - 2)
End If

Set rs = Nothing
Set db = Nothing
End Function

then try this query:

SELECT tblCrews.CrewID, PaulConcat([crewid]) AS CrewNames
FROM tblCrews

Amazing. Problem solved on that one. Thank you very much.

Regarding number two, here's an example where I have two unbound textbox for date and time, and one hidden bound textbox. After update of the unbound textboxes:

If IsDate(Me.txtOutDate) And IsDate(Me.txtOutTime) Then
Me.OutDateTime = Me.txtOutDate + Me.txtOutTime
Else
Me.OutDateTime = Null
End If

In the current event:

If IsDate(Me.OutDateTime) Then
Me.txtOutDate = DateValue(Me.OutDateTime)
Me.txtOutTime = TimeValue(Me.OutDateTime)
End If

Problem #2 also amazingly solved. The only way I could get the bound textbox to update however was to use:

Me.txtOutDate = Me.txtOutDate& " " & Me.txtOutTime

Otherwise I got a type mismatch. But, I can't complain as you pushed me 99.99% in the right direction so thank you once again.

Glad to help Russ. Post back if number 3 isn't resolved.