Multiples sub tables from a main table help (1 Viewer)

[wackywoo105]

I have a database with around 3k entries. I want to create more tables that are linked to records in the main table, but only for some of the 3k main records, not all of them. I also want one of these tables to be capable of multiple records that are linked to a single record in the main table.

Currently I have created the first separate table (which only requires a single record per main table record). For this table I use the unique ID in the main table in the 2nd field of the sub table. The sub table then also has a unique id for each record. Occasionally, if my database glitches, it adds the data from a main table record to the 1st record in the sub table, rather than a new record or the correct one. Due to this I keep the 1st record as a dummy record.

I'm guessing I haven't gone about this in the correct way. Can anyone help with what I am suppose to do, or point me to guide?

 

[Pat Hartman]

Start by showing us your tables in the relationship diagram. Hopefully every table will have a PK and the tables wii have relationships defined.

Normally programs can't just create data out of nothing so I would expect your application to have forms to enter the top table data and then two subforms one for each of the two child tables if they are siblings. If this is a hierarchy, the design becomes a little more complex. In that case you have a single record main form with table 1 data. Then you have a single record subform with table 2 data. On this subform, you have the third subform with table 3 data. It can be single record or multiple records.

Once we see the schema, we'll have a better idea of how to create the forms.

If you can post the whole database, that could speed things up.

 

[XPS35]

Why create a separate table at all if there is only going to be one record per record in the main table? Then you might as well add fields to the main table.

 

[mike60smart]

I have a database with around 3k entries. I want to create more tables that are linked to records in the main table, but only for some of the 3k main records, not all of them. I also want one of these tables to be capable of multiple records that are linked to a single record in the main table.

Currently I have created the first separate table (which only requires a single record per main table record). For this table I use the unique ID in the main table in the 2nd field of the sub table. The sub table then also has a unique id for each record. Occasionally, if my database glitches, it adds the data from a main table record to the 1st record in the sub table, rather than a new record or the correct one. Due to this I keep the 1st record as a dummy record.

I'm guessing I haven't gone about this in the correct way. Can anyone help with what I am suppose to do, or point me to guide?

Can you upload a screenshot of your table relationships?

 

[wackywoo105]

Thanks. I will try and upload some more info.