Form based on 1:many => 1:1 relationships

[gemoon]

I have a spreadsheet that has several blocks of text. I am building a database to store data from many spreadsheets.

here is the basic layout
spreadsheet b1 has the following some general info and two blocks of text:

a1 b1 c1 d1
a2 b2 c2 d2
a3 b3 c3 d3

w1 x1 y1 z1
w2 x2 y2 z2
w3 x3 y3 z3

i have constructed 3 tables to store the data: one for the b data, one for the abcd data and one for the wxyz data. the relationships are 1:many for b:abcd and 1:1 for abcd:wxyz.

so finally my problem. i setup a form as an imput for the users. the form is based on the b table with a subform based on abcd. this works fine. for every b value the user can copy the block of text and paste into the form. however, im not sure how to setup the form to then let the user copy the wxyz block and paste it whole?

hopefully this question makes sense.
thanks in advance for any advice given.
ed.

 

[PHV]

Perhaps a TabControl ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Darrylles]

Hi,

Can you give an idea of the actual data in each of the 3 tables? Otherwise we are guessing.

Remember that 1:many really means 1:0 OR 1:1 OR 1:Many when it comes to actual data. Your explanation gives very little insight.

Regards,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[gemoon]

Here are some more specifics. Thanks for the help!

The tables contain environmental data based on field sampling.

The three tables have the following data
tblBiota has info on a sample collected. There are three fields BiotaID, Species and another field which relates to another portion of the database. BiotaID is the primary key. It is an autonumber. Species is as it sounds.

In the second table (i called it abcd) there is the BiotaID, RepID and muliple columns representing metal concentrations, a,b,c,d... The RepID represents subsamples Rep1,Rep2,Rep3... for every BiotaID. The combination of BiotaID and RepID is unique. The RepID comes directly from the spreadsheet.

In the third table has the BiotaID, RepID and values w,x,y,z. Each a has a corrosponding w, b has a corrosponding x and so forth. These are the first matrix of metal concentrations transformed. I have related the second and third table by the combination of BiotaID and RepID. This is a 1:1 relationship.

The blocks to be imported into the second and third tables are separate in the spreadsheet.

I think part of the problem I'm having is assigning the BiotaID and RepID to the third table (in group) via a form. Talking just tables: Open the first table then follow the subdatasheet view to the second table. Then copy block a,b,c,d which contains rows with RepIDs 1,2,3... Next paste the whole block into the expaded sudatasheet of table 2. Access transfers over the BiotaID to each record automatically. I would like to then go to the third table and paste the second block with the w,x,y,z values in whole (all reps). This cant be done via the subdatasheets of tables, because from each record in the second table only 1 record opens in the third.

There should be a way to do this with a form.

Does this last part make sense?