Query with multiple table sources not pulling results?? (1 Viewer)

[George10988]

Hello everyone, I am trying to create a database for juvenile processing in a local police station. I am having trouble trying to make a query that pulls together all the info from 5 tables that I have and using that query as the source for a main dataEntry form. The guy working on this database before me had put all the info in one table and used that table as a source for the entry form and it worked great; however, with my multiple table approach the query just pulls the field names but none of the info contained in the field. I also get an error in the data entry form when trying to search using the access provided search box on the bottom of the form saying "You can't use find and replace now."

I suspect the root of this is my table relationships?

My database is too big to attatch here and I can not post a link since I am a new member. If you pm me I can provide a link to megaupload where I have my file. Any help would be greatly appreciated. Thanks!

​

 

[Galaxiom]

If you have set the DataEntry property of the form to Yes then you will not see any records, only allowing you to enter new ones.

When posting a database normally all the records are deleted and a few dummy records put in their place. Remove images from the forms, Compact and Repair the database and zip it. Generally this gets it down to a reasonable size.

Meanwhile get your post count up to ten by explaining more about the table structure such as the records held in the five tables and their relationships.

 

[George10988]

Hello, thanks for the quick response. This is my table structure.

Table 1: Basic Bio (Case ID(Primary Key), Last Name, First Name, Middle Initial, Nick Name, Age, Birthdate, Sex, Gang Affiliation, Place of Birth.)

Table 2: Home Bio(Case ID(Primary Key), Mother, Mother's Adress, Mother's Telephone Number, Father, Father's Address, Father's Telephone Number, Guardian, # of brothers <17, # of sisters < 17, Juvenile's phone number, Juvenile's address.)

Table 3: Profile (Case ID(Primary Key) Height, Weight, Hair Color, Second Hair Color, Eye Color, Second Eye Color, Photo, Race)

Table 4: R/O[this is the time the officers check on the juvenile in custody]
(Case ID(Primary Key), Memo, Time-1, R/O-1, Time-2, R/O-2....... all the way to R/O-10 and Time-10)

Table 5: Signatures [This table holds the signature file from the digital signature pad](Case ID(Primary Key), Signature file)

Table 6: Offense(Case ID(Primary Key), Offense, Arresting OFficer, Place of Arrest, Date Detained, Time Detained, Petition Date, Date Released, Time Released, Release OFficer, Released To, Relation To Juvenile.)

My relationships are as follows:

Table 1[Basic Bio] (case ID) to Table 2[Home Bio](Case ID)

Table 1[Basic Bio (Case ID) to table 3[Profile](Case ID)

Table 1[Basic Bio (Case ID) to table 4[R/O](Case ID)

Table 1[Basic Bio (Case ID) to table 5[Signatures](Case ID)

Table 1[Basic Bio (Case ID) to table 6[Offense](Case ID)

They are all One to One
My Query pulls everything from every table except for the case ID. The only case ID it pulls is from Table 1[Basic Bio]

Hope this helps.

 

[Brianwarnock]

If you had put each table in a separate post you would now have enough posts to post your link.

If the system gets in the way circumvent it.

Brian

 

[dpw204]

... trouble trying to make a query that pulls together all the info from 5 tables that I have and using that query as the source for a main dataEntry form. The guy working on this database before me had put all the info in one table and used that table as a source for the entry form and it worked great; however, with my multiple table approach the query just pulls the field names but none of the info contained in the field....
I suspect the root of this is my table relationships?

Two thoughts:
1. Debugging
2. Join Types
=============
1. Debugging: Iterate toward a solution
Some debugging suggestions:

a. run the query straight manually - does it return results? Expected results?
If so the problem would probably be with the form.

b. If problems persist: How about copying the offending query, and removing All the tables except the primary table.

- run the (copied) query, note the record count. - it *should* have everything you want.

Iteratively add the other tables back into the query. Run the query after *Each* table is added back. I suspect at some point the record count won't match. If/when that happens refer to the notes below on Join Types

2. Join Types
- Could it be that one (or more) of the tables is failing to find a valid link? In design mode on the query right-click on join-properties and try different join types.

Regards

 

[George10988]

Two thoughts:
1. Debugging
2. Join Types
Regards

I have troubleshooted the query problem I am having. I got it to work only if it gathers information from the primary table when all my relationships have been deleted. If I establish a relationship then the query goes back to pulling just the labels of the colums.

 

[George10988]

If you had put each table in a separate post you would now have enough posts to post your link.

If the system gets in the way circumvent it.

Brian

Damn I wish I would have thought of that!

 

[George10988]

Damn I wish I would have thought of that!

Once I get the 10 posts I will post my database link on here.

 

[George10988]

If you had put each table in a separate post you would now have enough posts to post your link.

If the system gets in the way circumvent it.

Brian

As you can see Brian I have taken your advice. hahahahahah!

 

[George10988]

Ok guys sorry for that rant I just had... I think I drank too much of my protein shake. Here is the link to my database. If anyone is willing to help out a fellow programmer in training it would be friggen amazing. Thanks

http://www.megaupload.com/?d=ECTES0Y0

 

[dpw204]

I have troubleshooted the query problem I am having. I got it to work only if it gathers information from the primary table when all my relationships have been deleted. If I establish a relationship then the query goes back to pulling just the labels of the colums.

Did you try adding just ONE table? and test with the three available join types?

 

[George10988]

Did you try adding just ONE table? and test with the three available join types?

No I have not, I am not to positive on how join types work.

 

[George10988]

I figured it out guys. I went and added one table at a time like dpw said and I also added one relationship at a time in my table relationships window. In addition I also added one table at a time to my query to see which table was causing the problem. All tables worked fine untill I got to my table "R/O" and my other table "Signatures" I then checked my join properties and moved it from "1: Only include rows where the joined fields from both tables are equal." to "2: Include ALL records from 'Basic Bio' and only those records from 'Signatures' where the joined fields are equal."

My question is: What the heck does that mean? From what I understand it is saying is that it will never pull out a signature attatchment or an R/O if it doesn't have a matching field in the "Basic Bio" table. That would be a problem for me because if the juvenile has information in these tables, I want them to be pulled in the query. I do not want to have to make a signature field in my "basic bio" table just so my query can pull results and have matching fields.

 

[dpw204]

Ok guys sorry for that rant I just had... I think I drank too much of my protein shake. Here is the link to my database. If anyone is willing to help out a fellow programmer in training it would be friggen amazing. Thanks

Sorry, I can't access your file within access. It downloaded fine. Problem: I'm using an ancient version of access (2002). It can only handle .MDB files, not .ACCDB

 

[dpw204]

I figured it out guys....

My question is: What the heck does that mean? From what I understand it is saying is that it will never pull out a signature attatchment or an R/O if it doesn't have a matching field in the "Basic Bio" table. That would be a problem for me because if the juvenile has information in these tables, I want them to be pulled in the query. I do not want to have to make a signature field in my "basic bio" table just so my query can pull results and have matching fields.

Good to hear you're headed the right direction! I believe you're on the path to solving the problem.

Join types - Hmm. I was thinking of trying to do a quick write-up to try to help describe the different joins. Frankly though, it can be a bit of a thorny concept to grasp. So, rather than re-invent the wheel (plus I do have other work to do!

) Google to the rescue: Search criteria: 'understanding access query join types'
Recommended reading result: http://www.about-access-databases.com/access-relationships.html

Another result, with Venn diagrams: http://www.tavislovell.com/post/Understanding-Join-Types.aspx

Hope this helps!

 

[George10988]

I checked out the site with Venn Diagrams and I think I understand now. I am having another problem now. I'm sorry for the many questions but I keep getting a msgbox that says "The Microsoft Access Database Engine cannot find a record in the table 'Home Bio' with key matching field(s) 'Case ID'.

I am assuming that since the relationship between my main table "Basic Bio" and "Home Bio" is a one to one relationship on the "Case ID" field, access is telling me I can not add a record through my data entry form since the "Case ID" box on that form relates to the case ID field on my main table and not the rest. How would I go about so that when the data entry person adds a case ID, it applies it to all 5 tables?

 

[George10988]

I checked out the site with Venn Diagrams and I think I understand now. I am having another problem now. I'm sorry for the many questions but I keep getting a msgbox that says "The Microsoft Access Database Engine cannot find a record in the table 'Home Bio' with key matching field(s) 'Case ID'.

I am assuming that since the relationship between my main table "Basic Bio" and "Home Bio" is a one to one relationship on the "Case ID" field, access is telling me I can not add a record through my data entry form since the "Case ID" box on that form relates to the case ID field on my main table and not the rest. How would I go about so that when the data entry person adds a case ID, it applies it to all 5 tables?

ahhh just figured it out. It was my joins again..

 

[George10988]

Actually nevermind, now I am getting an error saying the primary key can not be null. Sorry for the over posts. I know what the problem is I just have no Idea how to fix it. On my data entry form the "Case ID" box pertains to my main table and when the data entry person puts a number in it they are only assigning a case id number to table one but the tables are joined by case ID and each table has that field therefor the rest of the case id fields in the other tables are blank. I dont want to add 5 boxes to the data entry form for each case id field on each table. Would there be an easier way around this?

 

[dpw204]

ahhh just figured it out. It was my joins again..

All Right! You're on track. Good Luck.

 

[Galaxiom]

George:

Making separate tables for the Bio and Profile information still left the database with a flat rather than relational structure.

Table four is not structured correctly. The checks should be individual records with a time field not separate fields for each check.

You need to read up about normalization and relational structures before you continue your project.