I am having a problem with the attached database. Everytime I think I have kicked my relationship/joins problem, it seems to pop back up.
The database was created to provide a means to send trade or bank credit references for potential customers. Each customer may have 3 or 4 trade references and also 1 or 2 bank references. When I go into the form in the database to add a bank reference, the city and state are missing for the bank reference. Also, if I go ahead and try to add the bank reference anyway, it seems to work fine. But if I exit the subform and go back in, the bank reference I just added isn't there.
Can anyone tell me what I am doing wrong? I have read and re-read relationships and joins articles and nothing seems to be helping me. If someone could please just point me in the right direction, that would be great. I appreciate more articles to read, but at this point I just need guidance rather than more reading material.
The database has two structural problems, as I see it. First, the reference and code data don't exist in all related tables to make matches. Second, if you want data in related tables to display gracefully, you want to open up the related table in a subform, not by clicking a command button. Subforms are related to main forms by fields, like tables, and they force data from the main form/table into the subform/table.
What you want to do is to manually enter all code and reference information for the data that exists. For all data that doesn't exist - such as all the bank references that don't seem to match to anything - you can keep that data in a separate table, but only keep data in your working tables that relate to other records in related tables.
Second, you want to redesign your db to automatically open related tables in subforms. Don't know how? There's exhaustive material on main/sub forms and their relations in the help file. Sorry, but sometimes reading is essential; there's really no other way to learn in this business.
By the way, your Relationships screen could use some redesign as well. In Access, at least, the "driver" is always on the left, with subs further and further to the right. Ergo, tblCustomer, which drives the entire db, would be on the left, tblCustomerXXXRef tables further right, and the two detail tables further right.