Table not linking to query

[MSpoon]

I am pretty new to creating relationships between tables. I have a main form with subforms embedded. I tried running queries from the main form and they run just fine. I also run from the tables linked to the subforms just fine. however, when I try to run a query for a report that takes data from the main form and joins with data from the tables linked to the subforms all the data cells are blank. I have a linking table which is also blank. All the forms are linked by the same program ID number. Any ideas on how to resolve this? Thanks

 

[Dudley]

Hi MSpoon,
I think we need more details in order to help you. Can you post a zipped copy of your database so we can see what's going on?

 

[MSpoon]

Linking tables

Thanks Dudley. I will do so shortly.

 

[themurph2000]

I am pretty new to creating relationships between tables. I have a main form with subforms embedded. I tried running queries from the main form and they run just fine. I also run from the tables linked to the subforms just fine. however, when I try to run a query for a report that takes data from the main form and joins with data from the tables linked to the subforms all the data cells are blank. I have a linking table which is also blank. All the forms are linked by the same program ID number. Any ideas on how to resolve this? Thanks

Yeah, sending the zipped file will definitely help. If I were to take a guess, though, there is an issue with the one-to-one or one-to-many relationship between your main form and your subform.

 

[MSpoon]

Tables not linking to query

I am attaching the zip file of the database as you suggested. Please note that this is a new database that I am creating. Therefore there is only a few records in there. I have been testing tblAgency and subtblcounselingprogram to see if I could join these two tables in to one query. So far, I have not had any success although I am able to run the tables separately and pull the data quite fine. Yes, I do believe there is a problem with the linking table which is tblAgencyProgrammes.
Thanks for any help you can provide

 

[Dudley]

Hi MSpoon,

Well, you have quite an extensive list of tables. Just looking at your query, the reason it returns no records is the absence of records in tblAgencyPrograms. I assume this table will hold the ids of the Programs overseen by the Agencies. But you have none of these relationships entered. I keep expecting a tblPrograms, but I don't see one.

Looking at your relationships, it looks like tblAgencyPrograms is the foundation of your database - is this where you will list all the Programs? I think your query will work when you enter some data in this table - just make sure the sample data you enter represents programs your sample counselstaff records work for (same programid). Then your query will return some records.

The way you have the query set up, it will only return records if there are records with the same programid in each table. You could experiment with left and right joins, to, say, return all records in tblAgency and only records in the other query tables if they have matching records.

I hope these nuggets get you on your way.

 

[MSpoon]

Dudley
Thanks for your insight. Yes, the agencyprogrammes table is the foundation. You advise entering data into this table. Could you explain this a bit more. I assumed that the data would filter from the agency form when the data is entered. Obviously, this is not the case? Should I be doing something else at the data entry point?
Thanks

 

[themurph2000]

Dudley
Thanks for your insight. Yes, the agencyprogrammes table is the foundation. You advise entering data into this table. Could you explain this a bit more. I assumed that the data would filter from the agency form when the data is entered. Obviously, this is not the case? Should I be doing something else at the data entry point?
Thanks

I noticed something as well. In your Table tblAgencyProgrammes, you have the ID field as the Primary Key, which is an AutoNumber format. I would think the ProgramID field would be better for this, as you are using that as your link to all the other tables and subtables. In other tables, ProgramID is the Primary key. To create working one-to-one or one-to-many relationships, the field you use as the "one" on the parent table (AgencyProgrammes) should have ProgramID as its primary field so you can enforce referential integrity. In other words, you won't be able to enter a record in a subtable if the ProgramID doesn't appear in the primary table.

Unfortunately, relationships don't filter the data down from the primary table to the subtables. Their primary purpose is to make sure data from several sources (such as when you use several tables in a query or report) match up properly.

 

[MSpoon]

Table not linking

the Murph2000
Thanks for your insight. I have addressed the primary key issue. I don't know what is going on with my database. If I run queries with the wizard joining tables with the main table Agency, the Agency cells are empty but the other tables contain data. If I run the main table Agency by itself it pulls up the data. I am not able to do 1 to many joins in the relationships so i use the join type. However, I can run queries in the design view and the data comes out just fine. Any ideas why this might be happening?

 

[themurph2000]

the Murph2000
Thanks for your insight. I have addressed the primary key issue. I don't know what is going on with my database. If I run queries with the wizard joining tables with the main table Agency, the Agency cells are empty but the other tables contain data. If I run the main table Agency by itself it pulls up the data. I am not able to do 1 to many joins in the relationships so i use the join type. However, I can run queries in the design view and the data comes out just fine. Any ideas why this might be happening?

Repost your database with the joins and fixes you have done. If you're not able to do a 1 to 1 or 1 to many relationship, are you doing left and right joins? Also, I've never used any wizard to create the queries: I always used the QBE grid straight out. The only time I use the wizards is to create basic forms and organize reports, but that's just me.

Also, here's some online material I did some years ago, but it should still apply.

http://www.uic.edu/depts/accc/seminars/access2000-intermed/tables-relationships.html

 

[MSpoon]

Table not linking

the Murph2000
Thanks for the reference site. It was very helpful. Also, I don't feel so bad now that I know the QBE site is ok to use in lieu of the wizard. I will post the database again when I have a few minutes.
Thanks again.

 

[MSpoon]

Table not linking

theMurph2000
The database is attached. Any insights on overall integrity would be welcome.
Thanks.

 

[themurph2000]

theMurph2000
The database is attached. Any insights on overall integrity would be welcome.
Thanks.

Sorry it took me so long to reply, but work's been hell here lately.

Without a lot of data in the table, I'm not sure if there's a lot I can tell. However, if you're doing left/right joins, wouldn't you want the joins to be reversed from where they are now? If tblAgencyProgrammes is the main table and you wish to display its record, wouldn't you want the subtables only displaying the records that match your main table's current record? If so, the arrows in your relationships are reversed.

But don't quote me on that.

 

[MSpoon]

The Murph2000
Thanks for following up. I actually referenced the information you sent me and figured it out. Thanks again.

 

[themurph2000]

The Murph2000
Thanks for following up. I actually referenced the information you sent me and figured it out. Thanks again.

Cool. Glad it worked.