Populating a field in one table from another

[plog]

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.

 

[Pippa1]

OK that makes total sense, thank you.

 

[Pippa1]

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....

 

[plog]

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

 

[Mark_]

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.

 

[Pippa1]

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.

 

[Pippa1]

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.

 

[mike60smart]

Hi Pippa1

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

 

[Pippa1]

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