Please describe exactly what you have set up and then exactly how it is failing. What you describe sounds feasible, but we don't know the details that are keeping it from working for you. "id count will be one number higher", for example doesn't really mean anything to me is this the serial number? a record count? the number of the parent company or the child?
If CompanyID is a serial number field, you should have this relationship to link (possibly) many child companies to one parent company:
Companies::ParentID = ParentCompany::CompanyID
where ParentCompany is an additional table occurrence of Companies.
Sorry to be unclear -- I've tried a few different solutions and run into different problems, but what what you've described is what I believe I'm now doing (although it isn't working yet).
I have a table "Company" with three fields:
- a serial number field "Company ID"
- another serial number field "Child Company ID."
- Company Name (text field)
I have an additional table occurrence of that table called "Child Company."
I am linking "Company ID" in the company table to "Child Company ID" in the other occurrence of the "Company" table, and allowing the creation of new records in the "Child Company" table.
On my layout I've created a portal to "Child Company" through which I'm trying to define these relationships.
In this example the "id count" isn't off -- please disregard, but instead duplicate records are being created (of the children) when I establish the relationship between child and parent. If I don't allow the creation of new records I'm unable to select the names in my portal.
I hope that's clearer, and thanks for your help.
Not what I am describing here.
There's no need for the second serial number field. There should be just one in use here. CompanyID is an auto-entered serial number. ParentID is just a number field.
The attempt to add/edit records in your portal is what is creating duplicate child records. You can use a portal to Companies on the ParentCompany layout to display the child records, but you can't use it to add child records as those records are not listed in this portal. (Nor are they in your portal.)
To link a record to a given parent, you enter/select the company ID for that parent in the ParentID field. You can define a two column value list that lists all the company ID values from Companies in column 1 and all the company names from Companies in column 2. The user then selects a company's name, but the value list enters the number.
Thank you! That works perfectly.