Data Doesn't Display When I Query Two Related Tables

[formula1]

Hi there everyone,

I have been trying to complete an assignment for a computer course and I have run into a real stumbling block in respect to getting data from my two tables to display in a query.

Here's the essentials of my problem:

I am using Access 2007 for this assignment and I have tried to upload my database everyone can look at it, however, the upload process keeps failing for some reason.

1) Doing the queries is not a difficult exercise really, so it's not as if I'm having trouble learning how to formulate a query. I'm using the query wizard. Anyways, when I do a query on ONE of my tables, no problem, the data displays perfectly as per my query.

2) When I do a query with data fields taken from both tables, no data actually displays, only the field headings of those that I chose in my query.

3) I have tried to do queries from multiple tables in Northwind, and they worked perfectly. This leads me to believe that there is a problem with how I have related my two tables, but I don't know exactly.

I'm trying for make a query containing the required data from my two tables so that I can do a mail merge document. I have been working on this problem all day with no progress. Does anyone here have any ideas on this? I have followed all the directions from my Access books and the online help. They are fine of course, but this problem is not mentioned anywhere.

Additional Information:

Ok, I have two tables: Speakers and Sessions.

Speakers Table:

Primary Key:Speaker ID(This is joined to the Speaker ID field in the Sessions table)
Data Type: Autonumber
Field Size: Long Integer
Indexed: Yes(No Duplicates)

Sessions Table:

Primary Key: ID
Data Type: Autonumber
Field Size: Long Integer
Indexed: Yes(No Duplicates)

Now, to relate these two tables I have joined the Speaker ID field in the Speakers table to the Speaker ID field in the Sessions table. I simply made the Speaker ID field in Sessions to facilitate this relationship.

Speaker ID field in Session table

Data Type: Number
Field Size: Long Integer
Indexed: Yes(No Duplicates)

I hope this helps in diagnosing the problem here. I appreciate all of your efforts. I also attached the database in 2002-2003 form so you could have a look at it, even though I'm using the 2007 version.

Formula1

 

[formula1]

Also, when it comes to the "Join Type" that is selected for this relationship and query it says in the "Join Properties" box:

"Only include rows where the joined fields from both tables are equal"

Now I have experimented with the other two join settings and the query output will include the data from one of the tables but not the data from the other table. Both sets of field heading are displayed, but only one table's data values are displayed.

Formula1

 

[WIS]

Hi there everyone,

I also attached the database in 2002-2003 form so you could have a look at it, even though I'm using the 2007 version.
Formula1

Formula1

There is a very simple explanation - there are no SpeakerID's entered in the Session table. They are all blank.

One speaker could have several sessions? If so, change SpeakerID Indexed to Duplicates Allowed.

In general there are a few problems with the tbl's.

1 Instead of ID as a field use xxxxxID.
2.Get rid of the spaces out of fld names. eg SpeakerID instead of Speaker ID.

 

[formula1]

Thanks for the replly WIS.
However, the Speaker ID in the Sessions was just a field that I put there so that I could join the Speaker ID from the Speakers table to the Sessions table. Do I actually have to manually input the Speaker ID values in to that field to make this workable. That seems like a huge duplication of value input, which is considered bad design I thought.
I mean, the Speaker ID field is linked to the Speaker ID field in the Speakers table, therefore, shouldn't those values somehow be accessible in the Speaker ID in the Sessons table?
I have actually tried your suggession about allowing duplicates and it didn't work, but I'll try again.

Thanks

Formula1

 

[WIS]

Thanks for the replly WIS.
However, the Speaker ID in the Sessions was just a field that I put there so that I could join the Speaker ID from the Speakers table to the Sessions table. Do I actually have to manually input the Speaker ID values in to that field to make this workable. That seems like a huge duplication of value input, which is considered bad design I thought.
I mean, the Speaker ID field is linked to the Speaker ID field in the Speakers table, therefore, shouldn't those values somehow be accessible in the Speaker ID in the Sessons table?
I have actually tried your suggession about allowing duplicates and it didn't work, but I'll try again.

Thanks

Formula1

You have to put a SpeakerID in - it joins because the SpeakerID in the records of both tbls have to match. Make sure that you change SpeakerID in Sessions to Indexed Duplicates Allowed, otherwise if a Speaker has 2 sessions you'll only see one.

If you are going to continue with Access, I'd get a very basic book on Access and how it works.

 

[formula1]

Ok WIS, I have manually put values into the Speaker ID field in the Sessions table and my queries work perfectly now. Thanks for the suggestion. However, there must be a simpler way for me to somehow place values automatically from the first table into the second table so I don't have to do it manually. Of course I am just starting out using Access and I don't know a great deal yet.
Perhaps I should have used a different field in the Sessions table to join both tables. Do you have any thoughts on this?
Now that I have this working, what I want to do is to create a mail merge using this query as my data source. That was the whole point of the query in the first place.

Thanks a lot WIS

Formula1

 

[formula1]

Thanks
Ok, what is the best basic book that you know of to get for a beginner WIS?

Formula1

 

[WIS]

Thanks for the replly WIS.
However, the Speaker ID in the Sessions was just a field that I put there so that I could join the Speaker ID from the Speakers table to the Sessions table. Do I actually have to manually input the Speaker ID values in to that field to make this workable. That seems like a huge duplication of value input, which is considered bad design I thought.
I mean, the Speaker ID field is linked to the Speaker ID field in the Speakers table, therefore, shouldn't those values somehow be accessible in the Speaker ID in the Sessons table?
I have actually tried your suggession about allowing duplicates and it didn't work, but I'll try again.

Thanks

Formula1

dbfixed. I had to delete the 1:1 relationship and redo after I made SpeakerID in sessions tbl Indexed Duplicates Allowed.

 

[formula1]

Thanks WIS I appreciate the help here. However, I have a question. In the Sessions table you have put numberic values in the Speaker ID field. That's all well and good, but where exactly did you get these numbers? Did you input them manually? Or are they placed in that field automatically somehow? What I did, was to copy and paste the Speaker ID values from the Speakers table into the Speaker ID field in the Sessions table. Is there an easier way than doing the copy and paste method?
Oh ya, and what is the best basic bood for beginners learning Access?

Thanks

Formula1

 

[WIS]

Thanks WIS I appreciate the help here. However, I have a question. In the Sessions table you have put numberic values in the Speaker ID field. That's all well and good, but where exactly did you get these numbers? Did you input them manually? Or are they placed in that field automatically somehow? What I did, was to copy and paste the Speaker ID values from the Speakers table into the Speaker ID field in the Sessions table. Is there an easier way than doing the copy and paste method?
Oh ya, and what is the best basic bood for beginners learning Access?

Thanks

Formula1

Yes - I just made them up and posted them in manually.

You always add data to tbls via frms. In this case you need a main frm for the Speaker and a subform for the Sessions. They would be linked via speakerID and that would be automatically added to the Sessions tbl via the forms setup.

As far as a good book - look @ one by Carey. I'm sure there would be several others. Post a thread on General about it. I'm sure there would be many others out there that would also like to know.

 

[formula1]

Thanks a lot for your help today WIS, it's been very useful in getting my DB working right. I'll be working night shift tonight so I'll have some time to read up on forms and subforms so I can add the subforms to my DB.

Thanks

Formula1

 

[formula1]

Yes - I just made them up and posted them in manually.

You always add data to tbls via frms. In this case you need a main frm for the Speaker and a subform for the Sessions. They would be linked via speakerID and that would be automatically added to the Sessions tbl via the forms setup.

As far as a good book - look @ one by Carey. I'm sure there would be several others. Post a thread on General about it. I'm sure there would be many others out there that would also like to know.

So what you are saying here WIS is that when I include a subform for my Sessions table its Speaker ID field will automatically be filled in when I input the data to the main Speakers form? And this ocurrs because of the relationship that I have established between the two tables. Have I got this right?

thanks

Formula1

 

[WIS]

So what you are saying here WIS is that when I include a subform for my Sessions table its Speaker ID field will automatically be filled in when I input the data to the main Speakers form? And this ocurrs because of the relationship that I have established between the two tables. Have I got this right?

thanks

Formula1

It works because of the Parent Child link (SpeakerID).

Create a Sessions form (frmSessionsSubform) and put in all the flds you want to see there - making sure that SpeakerID is there. Hide the ctrl with SpeakerID in it (Visible=False from ppties).

Use the wizard on the Toolbox to drag this subform onto the frmSpeakers form and it will automatically create the links - Link Child and Link Master from the ppties.

What happens is that a subform ctrl is placed on the main form and the subform is "housed" within that container (subform ctrl).

1. If you put data into the subform on an existing speaker - autolinked.

2. When you add a new speaker it will automatically create the speakerID in the Sessions form and you then add data there.