Joined two tables, unable to filter/lookup on second table? (1 Viewer)

[RonW7341]

:banghead: I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing. I can find nothing to help me on this. Please help if possible.

 

[plog]

Can you post your SQL? Also, can you post the SQL of a search that returns nothing, but should be returning data?

 

[RonW7341]

Plog: Thanks for your attempt to help me. Unfortunately I'm such a casual user that I'm not even sure I'm capable of answering your request for information. About the best I can do is a sort of reiteration of my original post. We have a listing of auto dealerships in a large flat file Access sheet. It was originally in an Excel format, imported into Access. We are contacting these dealerships and now realize the need for a small contact table. The new table includes today's date, details of the phone call, a followup date on which we are notified by the database to call the dealership back, and lastly a rating of the quality of the prospective dealership. In the dealership table, we've used the filter ability to look up pretty much anything we need to find with great success. After adding the call/contact table and going into Relationships we joined the two tables together. We created a form which shows the dealership information at the top and the bottom of the form shows the results of the phone call/contact. The issue we are facing is now, when we go to the followup field on the small contact table and attempt to filter on a followup date as an example, my supposition was that when the filter occured on the follow up date, the system would bring up all the dealerships we should contact on that date. Unfortunately we get absolutely nothing. When a join occurs is there two way information flow? I'm just too new at this to find the answer to this one. I appreciate your help. Thanks again. ron

 

[Pat Hartman]

To post the SQL, open the querydef and switch to SQL view. Copy the string and paste it here.

If you are using a left join and you want criteria applied to the many-side table, you will probably have to create a second query to apply the criteria and then join to that rather than directly to the table. So to do this, just create a query of the many-side table and add the selection criteria. Save the query. Then open the original query and replace the many-side table with the new query you created. Tables and queries can be used interchangeably in queries.

Applying criteria to the many-side table is only a problem if you are using a left join. If you are using an inner join, there should not be a problem.

 

[RonW7341]

Pat: Thanks for your attempt to help me. As I said before I'm such a casual user that I'm not even sure I'm capable of answering your request for information. If you don't mind my asking, how do I go about opening the querydef? Thanks again, Ron

 

[Pat Hartman]

Here's some pictures that might help.
The first shows the right-click menu for queries.
The second shows what happens after you press Design View
The third shows what happens after you select SQL View.

 

[RonW7341]

Pat: Thanks a lot. I got your info and got to the SQL area your info explained and then realized that what I have is not running from a query at all but is two tables linked in the Relationship area. Therefore when I attempt to get the SQL info you asked about, It doesnt even resemble the commands shown in the examples I could find of a SQL box/window. Should I actually be running this from a query or is it proper to have the two tables linked as I do through relationships?

Just to reiterate what I'm dealing with, we have a main table (AIM Prospect Listing Table) which holds dealerships throughout the country. That is very helpful in making contact with the dealerships. It would be very beneficial to have the ability to log a phone call as it is made, together with the details of the call, follow up date and a rating of the call for the future contact. I've created the phone contact table (Phone Contact Table) and linked it to the dealership table through relationships and then created a form (Prospect Listing Table Form) to hold the information on both tables.

When in the form, the top portion of the form showing the dealership information, we can do a filter/search as normal. However when we drop down to the bottom and attempt to search for something like a follow up date on the expectation that it will do a backward search and bring up the appropriate dealerships which need calling that day, our search finds nothing.

 

[RonW7341]

Pat: After I finished the above post, I realized I should show you a query joining the two tables I mentioned above. When I went to the SQL area this is what that shows.

SELECT [AIM Prospect Listing Table].IDDealership AS [AIM Prospect Listing Table_ID Dealership], [AIM Prospect Listing Table].SRAssignedTo AS [AIM Prospect Listing Table_SR Assigned To], [AIM Prospect Listing Table].Brand, [AIM Prospect Listing Table].BACCode, [AIM Prospect Listing Table].BuickDC, [AIM Prospect Listing Table].ChevroletDC, [AIM Prospect Listing Table].CadillacDC, [AIM Prospect Listing Table].GMCDC, [AIM Prospect Listing Table].Dealership, [AIM Prospect Listing Table].Address, [AIM Prospect Listing Table].City, [AIM Prospect Listing Table].State, [AIM Prospect Listing Table].Zip, [AIM Prospect Listing Table].Phone, [AIM Prospect Listing Table].Fax, [AIM Prospect Listing Table].ContactName, [AIM Prospect Listing Table].Title, [AIM Prospect Listing Table].Email, [AIM Prospect Listing Table].FloorSource, [AIM Prospect Listing Table].Latitude, [AIM Prospect Listing Table].Longitude, [AIM Prospect Listing Table].SqFootage, [AIM Prospect Listing Table].TotalEmployees, [AIM Prospect Listing Table].TotalSales, [AIM Prospect Listing Table].Notes, [AIM Prospect Listing Table].Website, [Phone Contact Table].IDContactCall, [Phone Contact Table].IDDealership AS [Phone Contact Table_ID Dealership], [Phone Contact Table].SRAssignedTo AS [Phone Contact Table_SR Assigned To], [Phone Contact Table].ContactDate, [Phone Contact Table].DetailsofCall, [Phone Contact Table].CurrentStatusofDealership, [Phone Contact Table].FollowupContactDate
FROM [AIM Prospect Listing Table] INNER JOIN [Phone Contact Table] ON [AIM Prospect Listing Table].[IDDealership] = [Phone Contact Table].[IDDealership];

 

[Pat Hartman]

When you use an inner join, you will only get the records that have matching values in both tables. Visualize a Venn Diagram with the two overlapping circles. An Inner Join is the middle section where the circles overlap. If you want records from the prospect table even if they have no phone contacts, change "Inner" to "Left"

 

[RonW7341]

Pat: From your perspective would it be better to have these two tables joined by a link in Relationships" or joined with a query. I surmised when I began this that joining them with a link in relationships what the appropriate action. However now that you've asked me to provide information which I now recognize is grown out of a query, I'm not sure what I should do next. I went to the area above and changed the Inner Join to a Left Join, but naturally it made no difference because the query shown above is not really involved in the mix anyway. I truly appreciate your efforts to help me and appologize for my ignorance, but I just do not have a clue what to do next. Is there a place I could send you a copy of the file to look at? Thanks, Ron

 

[Pat Hartman]

Relationships and joins are different animals. Relationships are defined in the relationship window and are used to enforce referential integrity. Joins can be created on any two fields as long as the data types are the same. So you could join the student table to the pet table on firstname = petname. It makes no sense but you can still do it. Normally, the joins you create in queries will mirror exactly the relationships defined in the relationship window but they don't have to.

The query you posted doesn't have any criteria. How exactly are you "searching"?

 

[RonW7341]

Pat: Just to reiterate for clarification, we have a main table (AIM Prospect Listing Table) which holds dealerships throughout the country. That is very helpful in making contact with the dealerships. It would be very beneficial to have the ability to log a phone call as it is made, together with the details of the call, follow up date and a rating of the call for the future contact. I've created the phone contact table (Phone Contact Table) and linked it to the dealership table through relationships and then created a form (Prospect Listing Table Form) to hold the information on both tables.

When in the form, the top portion of the form showing the dealership information, we can do a filter/search as normal. However when we drop down to the bottom and attempt to search for something like a follow up date on the expectation that it will do a backward search and bring up the appropriate dealerships which need calling that day, our search finds nothing. In other words, in searching in either table there is no problem locating anything we need. However when linked together and a search is done on the follow up phone call as an example, nothing is found at all. Hope this helps. Ron

 

[Pat Hartman]

As you start each search, make sure that the previous filter is removed because they are cumulative. I generally add a clear filter button to forms where I use filtering. If you have a mainform/subform you can't filter on the subform except in the context of the mainform. Ie. a filter on the subform will NOT bring up new mainform records. You always stay on the same record.

 

[RonW7341]

Pat: No such luck. I tried ever combination I could think of to try to get it to look up the mainform data from filtering on the subform. It finds nothing. Do you think there is another way to go about doing this? Could I possibly send you a copy of the file to see if you might see a way to go about having the follow up date in the subform locate the appropriate dealership in the mainform?

 

[MSAccessRookie]

Is is possible that at least some of your issues are due to Table Level Lookups? In that case, what you see is not necessarily what you get. For instance, your Table could show a Name or a telephone number that is taken from another Table, and you would need to search for the Foreign Key instead.

 

[Pat Hartman]

I tried ever combination I could think of to try to get it to look up the mainform data from filtering on the subform. It finds nothing.

Try reading my post again. I said you CANNOT use the subform to filter the main form this way.

If you want to filter by the subform data, create a new form with the main form bound to the "child" data. Then the subform will show all the "parent" records that are found.