Populating a field in one table from another (1 Viewer)

First, you've used hard to reference table names and a 1-1 relationship is pointless so let's just say you have 1 table right now: tblParticipants. ([T_Randomization Data] hasn't come into play yet, so we will disregard that for now.)

When you break out your data as I prescribed into tblConsents that creates a 1 to many relationship with tblPArticipants. Formwise, those relationships are handled via Form/SubForm. tblPArticipants would be the basis for the main form, and tblConsents would be the basis for the sub-form. The main form would show just 1 record of data from tblParticipants, the subform would show all records in tblConsents for that record in Partcipant. It would be a continous form--just record after record vertically, with the last record spot being blank so that you could enter new consent data to that participant.

OK that makes total sense, thank you.

Plog, I don't want to push my luck but you have been so helpful I thought I should just ask about the randomisation date table. This has information on the date that participants were randomised so the study number and the randomisation date. It is in a separate excel sheet that I need to upload and then update sometimes. There are people in it that aren't in the main table. I don't need those people but they are currently in that excel sheet. Should I be including those columns in the main table and somehow trying to upload that information into it. This sounds very prone to error to me and it would be a nightmare to enter it all by hand. But technically it is a one-to-one situation because people only have one randomisation date....

I don't completely understand, but if its 1-1 that means they should be in the same table.

Pippa1,

You may want to check out how to use Excel spreadsheets in Access. This link may give you a better idea of what you can do rather than trying to force your table to match the spreadsheet.

I don't believe this should be a 1-1 relationship. I'm going to guess that at some point you're going to need T3 year data and then the problem with the design will be come apparent. We'll talk about this if that makes sense to you.

To actually implement a 1-1 relationship, one of the tables must be the "parent" table. Ie, the one that ALWAYS has data and in your case, this will be the ParticipantDetails. That table uses an autonumber as the primary key. On the other side of the 1-1, the primary key is defined as long integer. You won't be able to populate an autonumber with an existing value in anything except an append query and since the right "1" would NEVER have a row that didn't match the left 1 then there is no reason to use an autonumber. The second important point is that you NEVER insert the right "1" until you have data to populate the columns. It is important to good table design that you be able to specify that required fields are required and if you must insert the record before you have any data, you wouldn't be able to do that. That means you have to deal with a missing right 1. The way to do that is to always use a left join between the two tables rather than an inner join. That will allow 1-nothing to be returned as well as 1-1 records.

Thanks everyone, I will muddle along for a bit and see if I can make it work. I may be back with more questions. I really appreciate the help.

Hi everyone, just an update, I started again. Have some new problems but I'll start a new thread for them. Thank you for the advice.

Hi Pippa1

Do you currently have any data at all in Table2 ??

No Table 2 was empty to start with. I've changed the design now as advised on here.