Ok so I have a database that tracks travelers and shows where they are going and what time they are leaving and when they are suppose to be back. This is going to be long because im going to explaine how this database works before it get to my main question.

Now I have two main tables, "Main" table which is set up like this:(this is basically the movement information)

Field Name Data Type
_________________________________________

ID1 AutoNumber (primary key)
Date of Departure Date/Time
Date of Return Date/Time
To Text
Traveler1 Text
(traveler's field names continue to Traveler 10)
Run Info Text
Closed Text


The other Table is called "Tbl Add Traveler" and it is set up like this:

Field Name Data Type
_________________________________________

Traveler ID AutoNumber (primary key)
Traveler Name Text
Traveler Agency Text
Traveler Homecamp Text
Traveler Phone1 Text

Ok now on my main form it displays all of the recordsets in from "Main" Table using a query and on the form when you dblclick a person's name it bring up a form that displays their contact info that is in the table "tbl add traveler".

Now In a different form that is being used to control a query that displays all of the movements out in a date range. then that query is used to populate a report that list's all of the recordsets in that range, now I have been asked to add the phone numbers for the all the travelers in each movement. the report looks like this

(IMG:http://www.sn95haven.com/sn95haven.com/report.jpg)

now I've been asked to have the report display the each of traveler's phone number's that are located in the "Tbl add traveler" next to their names in a different text box. I have no clue how to go about getting this to pull the phone data out of the "Tbl add traveler" that is the correct one for each person in each movement.

oh yeah there are no relationships set up in the database.

You really need to rethink your Main table...I wouldl call it tblJourneys or similar. Delete the Traveller 1 to 10 Fields and create a TravellerId Field instead...it shoudl be a number long integer type. You can then lookup the Travellers from your travellers table to be included in journeys.

Now you can join the two tables on the TravellerId fields which will allow you to creat a Form and subform arrangement to show the desired data.


HTH

Z

Welcome to Utter Access.

As you no doubt realize by now, this table design is not quite correct. You need to address that before attempting to create reports.

Let's start by deciding WHAT you actually need to track.

"Travelers" is one entity. That should be a roster table listing just the travelers. NO other information other than that needed to uniquely identify each traveler belongs in that table.

Before we address that, however, I'm going to suggest that you adopt a more conventional naming standard, which eliminates the spaces in field names. Not, "Traveler Name" , but "TravelerName", and so on.

Also, you need to create all of the fields in a table so that they contain the smallest meaningful piece of data: here I'm talking about the "TravelerName" field. People generally have at least two names, a first name and a last name. It is never a good idea to combine them in a single field in a table. You can combine them, if you want, to display on a form or in a report, but they need to be kept separate in the fields.

In your table, it is not clear with "Traveler Agency" means, but I'm going to assume that you are tracking information about agencies which are coordinating travel? That is NOT an attribute of a person. Important point here. A roster table of people (and these are people even though their role in this database is "traveler") should not contain any attributes that don't help uniquely identify the persons. In some cases, we go ahead and track that information in a roster table anyway, but only under very specific situations. Here that would be the case IF, and only if, each traveller can be associated with one and only one agency. If it is true, you can leave that attribute in this table. However, if the person can travel under two or more agencies, you must move that to a separate table.

The same logic applies to "Homecamp". If a person has one and only one Homecamp, you can leave that field in that table.

I am going to assume that this would be appropriate.

However, you need to consider how "Agency" and "Home Camp" work in your business. Is there a limited number of Agencies from which travellers are assigned to you? Is there a limited number of Home Camps? If so, you need to create tables for them. If not, then it is probably okay to enter those values as text, but that will be an opportunity for inconsistent data to get into your database.

In other words, if you allow your users to enter Agency names as free text each time they add a new record, you'll quickly have a variety of spellings of the same agency, and you'll have no good way to report transactions by agency. Not knowing your business, of course, I can only suggest that this is something you probably do want to avoid.

The same is true, again, for Home Camp.

So, you'll probably want two additional tables for Agency and HomeCamp, and you'll need to revise the Traveler table accordingly.


tblTraveler
=================
TravellerID (Primary Key, autonumber)
FirstName
LastName
AgencyID (Foreign Key from tblAgency, Long Integer)
HomeCampID (Foreign Key from tblHomeCamp, Long Integer)
TravelerPhone

tblAgency
===================
AgencyID (Primary Key, autonumber)
AgencyName

tblHomeCamp
===================
HomeCampID (Primary Key, autonumber)
HomeCampName

Now we can turn to the more difficult problem of resolving the "Main" table, which really needs to be named something more meaningful to the workflow here, probably something like "tblTravel", which I'll use.

It is a transaction type table which identifies two things: the travel to be undertaken and the parties who will be doing the travelling. It requires, therefore a minimum of three pieces of information: Start Date (date of departure for travel), End Date (date of return for travel) and the traveler. In your case, it also looks like there are three additional pieces of information required: destination ("To") , "Run Info", and "Closed" whatever those last two are.

You've included a classic error in designing this table to look a lot like a spreadsheet, with multiple columns for "traveler1", through "traveler10". This error is so common that it has been given its own name, "Repeating Groups".

Instead, you enter ONE record per traveller, not one column.

Because your data is telling me that you consider a "travel transaction" to be an event in which one or more "travelers" go together in a group, I know that you have to have two transaction tables to support that.

First will be the "travel" table itself, which contains the attrbutes of the travel event. The second will be the "TravelDetail" table, which contains the list of travelers in the group and links them to a single travel event. BTW, doing it properly avoids two other problems. I see in your screenshot that you have to display several "empty" fields because there are fewer than 10 members of that travel group. Plus, it will allow you to handle groups in which there are more than 10 members, without having to re-create/re-design the entire set of tables, forms and reports.

So, the Travel table you need will look like this:

tblTravel
===========
TravelID (Primary Key, autonumber)
DateofDeparture
DateofReturn
Destination
RunInfo
Closed

tblTravelTraveler
============
TravelTravelerID (Primary Key, autonumber)
TravelID (Foreign key from tblTravel, Long Integer)
TravelerID (Foreign Key from tblTraveler, Long Integer)

I also see another look up table in there, now that we've sorted out the travel table, Destination. Is it the case that travelers are going to the same destination(s) over and over? I'll bet it is. So, you should resolve that into a table of destinations.

tblDestination
===========
DestinationID (Primary Key, autonumber)
Destination

And that allows you to link the travel to a destination via the primary and foreign keys and avoid typing errors in the destination field.

tblTravel
===========
TravelID (Primary Key, autonumber)
DateofDeparture
DateofReturn
DestinationID (Foreign Key to tblDestination, Long Integer)
RunInfo
Closed

With a proper table structure in place, you can more readily support your requirements for adding and reporting on travel.

Note that the query which returns travelers and their phone numbers, which is what prompted your question, would be something like this.

SELECT TravelerID, FirstName, LastName, TravelerPhone, TravelID
FROM tblTraveler INNER JOIN tblTravelerTravel on tblTraveler.TravelerID = tblTravelerTravel.TravelerID

This query would be the recordsource for a subreport in the main report you now have.

The main report would be based on tblTravel and would have as its recordsource:

SELECT TravelID, DateofDeparture, DateofReturn, Destination, RunInfo, Closed
FROM tblTravel INNER JOIN tblDestination on tblTravel.DestinationID =tblDestination.DestinationID

There is a lot of information there to digest, but the effort you invest in getting the tables properly structured now, before you get too far down the path, will pay off.

HTH

George

With doing that I run in to the problem of I cannot have multiple travelers on one "journey", but it did allow me to have the phone num on the report which is awesome, though.

We described the proper table structure to support multiple travelers on one journey. You'll do it with a main report for the journey itself and a subreport for the travelers on that journey.

That should resolve any such problems for you.

Thanks George, I just went ahead and redesigned the backend of the database, basically I'm starting from scratch; as I was not the orginial developer for the current one; I was only told to modify the current one that is already in place.

Now in the query I recieve an error saying syntax error in JOIN operation in the following SQL statement:

SELECT TravelerID, FirstName, LastName, TravelerPhone, TravelID
FROM tblTraveler INNER JOIN tblTravelerTravel on tblTravel.TravelerID = tblTravelerTravel.TravelerID

and it highlights tblTraveler after the FROM.

I fixed that, so I actually need to make a form that will fill out all of the required things in all of the tables. What do you think is a good way of approaching that?

Hm. I thought I'd replied earlier, but I don't see my post. Sorry.

Anyway, the best approach would be to create a main form for the Travel (Journey) with a subform in it for the travelers.

The main form would be bound to the table called tblTravel and would include a combo box for selecting the destination and other controls for the remaining fields (departure date, return date, "run info" and "closed")

The subform would be bound to the table called tblTravelTraveler. It should be created as either a datasheet or continuous view form and would include a combo box bound to the TravelerID field. The rowsource for this combo would be the tblTraveler table.

George

Here's a demo showing the construction of a main form with a subform and a main report with a subreport.

Thanks a lot George! You have been so helpful.

Glad to hear you are making progress on your project.

Best of luck going forward.

George