Not sure what type of relationship to use

[randolphoralph]

I have created a database that is used for auditing different types of work that employees do.



I currently have 4 Tables:
FilingAudit (which contain fields such as employee name, date of audit, etc. that will be used on each audit and also audit criteria for audits done on filing)
CallAudit
ClaimAudit
OtherAudit

I am trying to create a form that uses a Tab Control with 4 tabs to be able to quickly switch between the type of Audit.

Here is what I am trying to attempt.

FORM
Employee Name (from FilingAudit table)
Date of Audit (from FilingAudit table)

Tab 1-FilingAudit
Fields from FilingAudit table

Tab 2-CallAudit
Fields from CallAudit table

Tab 3-ClaimAudit
Fields from ClaimAudit table

Tab 4-OtherAudit
Fields from OtherAudit table
__________________________________________________ __________
On the main table (FilingAudit) I have a primary key set on the AuditID field which is a AutoNumber.

On the other tables I have the AuditID field (not set to primary key) which is set as a lookup to the main table AuditID field.

Currently, I have the relationships as One to One from the main table to the other tables.

When I open the form and enter data on the various tabs it will enter the information, but then when I close out the form and reopen the form I start with only the first record and no data on the form.

I have no idea what to do.

[randolphoralph]

I have uploaded a sample of the database. If you go into the Form and enter a record or a couple of records and then close the form and reopen the Form you will see that the records do not show up. This is the problem I am having.

[kimmer]

The Record source is a query with the default inner join. In this join, only AuditIDs that are in all 4 tables will be included in the query results, so if you don't check at least one box on each tab, the record gets excluded from display.

I don't see any reason to have this split into multiple tables anyway. Why not just make one table instead of these one-to-ones? You can still put controls on the different tabs on your form.

[randolphoralph]

kimmer-

This is just a sample database. The main database has over 100 fields on each table and I can not combine them into one table due to the 255 field limitation.

How could I change the record source so the record does not get excluded from display if the AuditID is not in all for tables?

[randolphoralph]

Or maybe pull the records from the 4 tables when the AuditID field is filled in on the main table (FilingAudit)?

The problem is that when the users of the database are using the Form they are not always filling in each tab.

[kimmer]

Change each join from an INNER JOIN to a LEFT JOIN. To do this, you can right-click on the Record Source for the form, choose Zoom and change each instance of INNER to LEFT. (In your sample, there are three.)

[randolphoralph]

Thanks kimmer you rock!

[thecsmith70]

Thanks kimmer you rock!

Hi - I was looking around and saw a similiar issue randolphoralph had and did what kimmer suggested.

If I go to Database Tools/Relationships (in randolphoralph's test mdb), I see one to one relationships between all four tables. Do I need to edit the join type to #2 on each tables from FilingAudit to the three other tables?

[ssanfu]

Your table/relationships are wrong.

The main database has over 100 fields on each table

This is an indication of non normalized structure.

Very, very ,very seldom are there tables with a 1-to-1 relationship.
The primary key (PK) from "FilingAudit" should be linked to a foreign key (FK) field in the other 3 tables, NOT the PK field.

Having a PK field named "AuditID" in every table is confusing at best. Better names would be "CallID_PK", "OtherID_PK" and "ClaimID_PK" (with or without the "_PK" sufix).

Object names should be letters and numbers. No spaces. No punctuation. No special characters (underscore is the exception).

You really should step back and look at normalizing your dB...(IMO)

I have modified your dB, but it is in A2010 format. If you can open A2010 format dB, let me know and I will post it.

[thecsmith70]

I have A2007. Can you post the modified dB so I can see what you did? Thank you very much.

[ssanfu]

I didn't remove all spaces from field names, but you get the idea.

I think you should be able to open the dB in A2007

[thecsmith70]

I didn't remove all spaces from field names, but you get the idea.

I think you should be able to open the dB in A2007

Thank you. That helped me. This is a great forum.

[thecsmith70]

How come Steve had a query1 built in the dB while Kimmer didn't.

[ssanfu]

randolphoralph (and probably Kimmer) had an SQL statement as the record source.

I typically use saved queries as the record source for forms/reports because it is easier to find and edit, if necessary.

If I have a query that is the record source for a form and returns the correct records, if it is a saved query, I can use that same query as the report record source. If I have to edit the query, it is edited for both objects instead of have to edit the form and the report record source separately.