Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Access 2007: Queries not Pulling Table Info


(!)

mstransient's Avatar
mstransient mstransient is offline
Computer Specs
Member with 6 posts.
THREAD STARTER
 
Join Date: May 2012
Location: Seoul, South Korea
Experience: Intermediate
22-May-2012, 05:50 AM #1
Access 2007: Queries not Pulling Table Info
Over the past several months I have been working on an Access database for my company in order to make our operations a bit more efficient. The database was coming along quite well, then my motherboard on my computer needed to be replaced. This may or may not have anything to do with the problem, but when I returned to working on the database my queries would not pull from the tables as they were before. So here is what I am experiencing:

In my Access 2007 Database, I can input information using the forms. This information then appears in the tables where it should. However, the queries are not pulling the information from the tables. Even information that I input directly in the tables (without the forms) do not appear. Because my queries are not pulling the table information, my reports also are not functioning properly to show the data present.

I have been trying to uncover the bug with this for some time and cannot figure out why they are not working. If I create a new query, these queries will work, but I have already put much time and effort in the current queries, forms, and attached report so do not want to have to redo all this work.

If you have any advice for me I would like to receive it. Your support is greatly appreciated.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,526 posts.
 
Join Date: Mar 2005
Location: UK
22-May-2012, 06:07 AM #2
mstransient, welcome to the forum.
I don't think a motherboard failure would have anything to do with this.
Have you Compacted & Repaired the database?
When you say the queries are not showing records, are they only newly added records?
Have you tried opening a new blank database and Importing all the Objects in to the new database?
__________________
OBP
I do not give up easily
mstransient's Avatar
mstransient mstransient is offline
Computer Specs
Member with 6 posts.
THREAD STARTER
 
Join Date: May 2012
Location: Seoul, South Korea
Experience: Intermediate
23-May-2012, 09:20 AM #3
Angry attempted your suggestions
OBP
Thank you for your suggestions, but I have tried to compact and repair the database, but that did not work. I then created a new blank database and imported the objects from the old database to that, but still they are not showing up in any of my queries. I had dummy information before which was showing up but I have since deleted all the dummy info and started over. With all newly added records the queries are not pulling the information. My relationships look fine to me, and I do not see that there are any filters on the queries that would limit the information that they showed.

I am attaching the repaired, newly created database with the objects imported from the old file. Please let me know if you see my troubles.

Thanks again for your support!
Attached Files
File Type: rar ICDatabase-Revised.rar (166.7 KB, 37 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,526 posts.
 
Join Date: Mar 2005
Location: UK
23-May-2012, 09:54 AM #4
I can't get the RAR to unzip, can you post it as a ZIP file?

PS, don't worry i have worked around it.

Last edited by OBP; 23-May-2012 at 10:01 AM..
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,526 posts.
 
Join Date: Mar 2005
Location: UK
23-May-2012, 10:13 AM #5
OK, I opened the first query 3p... and it shows no record due to the Relationship join of 1 to 1 being used as the query join type.
There are no records in the subtable CompanyContacts, hence no records displayed for the CompaniesMetT. Normally a subtable like Contacts would not be a 1 to 1 but a 1 to Many, with the many on the contacts side.
Also in the table relationships you are joining the CompaniesMetT Key field to the CompanyContacts key field which forces the 1 to 1 relationship. The same applies to the MOInfoT table.
mstransient's Avatar
mstransient mstransient is offline
Computer Specs
Member with 6 posts.
THREAD STARTER
 
Join Date: May 2012
Location: Seoul, South Korea
Experience: Intermediate
23-May-2012, 10:25 AM #6
The reason I have the join type of 1 to 1 is for CompaniesMetT and CompanyContactsT is that they should refer to the same ID. Each company meeting has one ID and the list of contacts met during that specific meeting needs to be associated with that meeting and only that meeting, hence the reason CompanyContactsT ID = CompanyContactsT CompaniesID. I wanted the CompaniesMetT to generate the ID and then have the contacts entered use this ID generated by the other table.

What do you advise?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,526 posts.
 
Join Date: Mar 2005
Location: UK
23-May-2012, 10:31 AM #7
There is little point to a table with a 1 to1 relationship, the fields might just as well be in the Main table.
The normal method to set up company meetings would be to have a separate table with the key field value from the Company and the key field value from the Contacts, which is known as a linking table or Many to Many table. But the fields in this new table are not themselves Key fields, they are just Number Type Long.
mstransient's Avatar
mstransient mstransient is offline
Computer Specs
Member with 6 posts.
THREAD STARTER
 
Join Date: May 2012
Location: Seoul, South Korea
Experience: Intermediate
23-May-2012, 07:34 PM #8
I have created the junction table as you advised, but it seems that I am still having the same trouble. My queries are not working. I have attached the new file with the junction tables for you to view. Is there anything else I can do? Any suggestions are much appreciated.

thank you!
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,526 posts.
 
Join Date: Mar 2005
Location: UK
24-May-2012, 06:31 AM #9
No new database attached.
mstransient's Avatar
mstransient mstransient is offline
Computer Specs
Member with 6 posts.
THREAD STARTER
 
Join Date: May 2012
Location: Seoul, South Korea
Experience: Intermediate
25-May-2012, 12:48 AM #10
Database with Dummy Information
This file shows how the information used to work before my motherboard crashed.
Attached Files
File Type: zip ICProjectDatabase_5-25.zip (262.1 KB, 17 views)
mstransient's Avatar
mstransient mstransient is offline
Computer Specs
Member with 6 posts.
THREAD STARTER
 
Join Date: May 2012
Location: Seoul, South Korea
Experience: Intermediate
25-May-2012, 12:51 AM #11
This file shows the new database file with the junction tables as you suggested. As you can see it is still not pulling in the information newly added to the table.
Thanks for your support thus far. I really appreciate your advice and insight.
Attached Files
File Type: zip ICDatabase-Revised.zip (380.6 KB, 15 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,526 posts.
 
Join Date: Mar 2005
Location: UK
25-May-2012, 06:33 AM #12
OK, in your old database I just added a new record to the Companies 3P set and it does not show in the query because there is no Contact for that new record.
I reset the Query Join to
All record from the CompaniesMetT and only those matching from the CompanyContacts.
The record now shows as does the record for Livingston International that you had entered.
So that solves the problem with the original database, you must have had Contacts for all the Companies entered.

That still does not solve the design issue of having a 1 to 1 relationship, I notice that the Contacts table has more than one set of Contact data fields, this would be overcome using the Many to Many linking table, but you would of course still need to set the Join between the query Tables correctly.
So I have looked at your new database table design, which is now much better and corrected the design of the 3P query,
you hadn't added the Linking table to the query.

So you no longer need extra Contacts field sets in the contacts table, but you will need a Subform based on the Linking table to enter and display the data if you want to use the linking table as it should be used.
Attached Files
File Type: zip ICDatabase-Revised.zip (391.2 KB, 16 views)
File Type: zip ICProjectDatabase_5-25.zip (251.5 KB, 18 views)
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
access 07, queries, tables

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑