[mstransient]

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]

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?

[mstransient]

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!

[OBP]

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.

[OBP]

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]

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]

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]

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]

No new database attached.

[mstransient]

This file shows how the information used to work before my motherboard crashed.

[mstransient]

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.

[OBP]

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.