Include Two Slave Fields for one Master Field

samueltmcf · February 23, 2020, 4:08am

I have a fairly simple database (SQL) that is used to track cattle inventory. Each cow is added to a table called Stock. The main form then shows all the information from the Stock table. The 3 fields that are important for my question are ID#, and the Dam and Sire fields. On that main form is a subform that shows the offspring of each cow. Currently it works fine for showing all the offspring for all females because the the master field “ID” is linked to slave field “Dam”. This correctly displays all calves that have the ID number in the Dam field.

Is there a way to make it so the Sire field can also be linked to the same ID field. This way offspring will correctly show under records for Male cattle as well.

I tried listing both options: “Dam”;“Sire” for “ID”;“ID” but this made it so that no results showed for neither males nor females.

Any suggestions are appreciated.

Thanks

Ratslinger · February 23, 2020, 4:32am

Hello,

Can think of many methods you may have set this up as. Not certain if you have a single table or multiple tables. In those records are the ‘Dam’ and ‘Sire’ id’s relating back to others in the Stock table.

With that it is a form with two sub forms. The first sub form is as originally stated - ‘Dam’ linked to ID. The second sub form is identical to the first except ‘Sire’ is linked back to the ID.

So there are two sub forms - one for “Dam” and one for ‘Sire’.

samueltmcf · February 23, 2020, 4:48am

Thank you for your answer. The Dam and Sire IDs do relate back to others in the Stock Table. There is just one table.
By having two subforms is there a way to hide the results to only show the Sire subform on Bull records? I would rather not have two subforms displayed when most of the time only one will have information.

Ratslinger · February 23, 2020, 4:59pm

The sub forms are not related to each other. simply eliminate the one not wanted. So have two different Base forms. One showing the ‘Sire’ relation and another with the ‘Dam’ information. This is all your choice and as stated in the answer can see many ways one may want to do this.