how to separate repeating fields

[saralee]

Hi everyone,

I need to separate six repeating fields into individual fields in a database that others created. I wonder if it is possible to create some scripts to do it in the current database instead of exporting/importing to some external files. Please advice. Thank you so much!

[comment]

Are you sure you want six fields, rather than six related records? If so, you could define six calculation fields =

GetRepetition ( YourRepeatingField ; n )

(with n being the repetition. going from 1 to 6). After checking the results change the fields' type to Text or Number or whatever is appropriate.

[saralee]

Thank you!!

Yes I want to have six individual fields for reporting purposes.

Sorry but I couldn't find the function GetRepetition you mentioned..

Thanks!

[comment]

I want to have six individual fields for reporting purposes.

Well, you haven't told us what this is about - but I'd suggest you review the entire process because something doesn't sound right here.

[saralee]

Ok.. sorry missed the big picture-

This is a database recording thousands of babies' information researched on by a research centre. For each baby, we have the family (mom, dad, siblings) information along with the lab visit information. The previous tech support who designed the database defined several repeating fields to contain lab name and research study name, because we run several studies on each baby. I assume that was just a quick way for him to define as repeating fields, and now we need to seperate these fields to be able to report on each of the lab visit information.

Does it sound right that I need to separate these repeating fields?

Thanks a lot! :

[comment]

Yes, you should - but not into separate fields, but into separate records in a related table. Only then you will have full reporting capability.

Splitting repeating fields into records is relatively easy: first, make sure you have a unique BabyID (e.g. auto-entered serial number) in the Babies table. Then import the BabyID and the repeating fields into a new table in the same file. When asked about the repeating fields, select 'Splitting into separate records'. Finally, define a relationship between the two tables, matching on BabyID.

[saralee]

Thanks! haha.. i just found out that the database only has one table by its own which stores all the information. There seems no relations at all.. so there is not a baby table with baby ID obviously.. can I still split the fields into records in this case?

Thanks a lot :

[comment]

If the one table that you have has one record per baby, then that is the Babies table. You need to create and populate the BabyID, then import the records as outlined above into a NEW table (this will create a new table with the imported fields, and import the data at the same time).

[saralee]

Thank you : I thought about "tweating" the database and building a relational structure with multiple tables- babies, parents, research info. The only thing is that the Babies table would have relationship with itself (siblings case) I'm not quite sure how to implement that.

Do you think it is doable for me to export the current data in this big table into separate tables in the new design? I never did it before! That way I can just separate the repeating fields during the export.

Please advice. Thank you again!

[comment]

It does sound like a "project" - but with some planning ahead and keeping backups(!), I don't see why it couldn't be done, and fairly quickly.

As for parents and siblings: how many parents does a baby have? In case that sounds like a stupid question, it's really about one record for both parents vs. separate records for each. The answer about siblings depends on that, since with separate records for each parent there can be half-siblings.

---

"Tweating" ??

[saralee]

I get your point about the parent/sibling issue. I think it's better for me to put both parents as one record, as we don't actually care much about if they are half siblings or not.

The way I'm doing it now is to create a new database with all the relational tables and fields (names to be consistent with the old ones), and then export the data in the old database to the new one (one table after another). Does it sound right?

Thank you! really appreciate it :

--

by tweating i meant revising it a bit^_^

[comment]

If you put both parents in the same record, then the table is actually Families (or Households, or whatever is PC these days...).

Then you need to solve the issue of removing duplicate families, while retaining the relationship to ALL their children.

As for siblings, it can be a simple self-join relationship:

Babies::FamilyID = Babies 2::FamilyID

AND

Babies::BabyID ≠ Babies 2::BabyID

[saralee]

Thank you! I will think about it tomorrow and will let you know if I have any more questions.

Many thanks for your advice today :