Subdatasheets

[rabu5]

The setup:
I have the primany key as name (31 total records), and columns a,b,c and so on. In column c, a memo column, i have a bunch of dates and numbers.

The question:
I would like to create a subdatasheet unique to each of the 31 different names (not a sheet that is the same for all the records), so that i can document the dates in one column and the numbers in another column for original column c (as a result there would be different amounts of records for each subdatasheet).

Does anyone know if this can be done? How can i do it? Help!
If you dont understand what i am trying to say, i'll try again.

 

[rabu5]

Allow me to elaborate a little more...

my subdatasheet has date, number and comments. When ever i change record 1's subdata, the subdata for 2-31 all change as well. How do i keep them from changing, everytime i update one of the subdatasheets.

 

[Pat Hartman]

You're thinking in terms of spreadsheets. Break out of the box. You have a one-to-many relationship. You are currently mushing all the many-side data into a memo field where you are having trouble using it. To solve the problem, create a new table to hold the many-side data. You've used totally cryptic names so I have no idea what your application is about and theefore cannot suggest any meaningful names for the new table.

tbl1:
YourName (primary key)
dtc.

tbl2:
SomeID (autonumber primary key)
YourName (foreign key to tbl1)
YourDate
YourAmt

In the relationship window, add both tables and draw a line connecting the YourName field in both tables. Check the enforce referential integrity box.

For data entry, use a form based on a query of tbl1 with a subform based on a query of tbl2. Make sure the master/child links are properly set.

Once you define the relationship, Access will show tbl2 as a subdatasheet. Each row of tbl1 can have 0-infinity related rows in tbl2.

 

[rabu5]

Pat, thank you. However, when i try to create the relationship you mentioned, i get an error message saying "no unique index found for the referenced field of the primary table". Being as how i am relatively new to Access, i dont understand the message. I referenced name1 to name 2 in the relationships window.

My problem might be in that i don't know how to declare tbl2: YourName a foriegn key.

These quesions might be rather elementary, i dont know, but please understand that your help is greatly appreciated.

 

[cogent1]

In table 1, you should have a unique primary key field. (NO duplicates, and probably set to Autonumber). Let's say this is called CustomerID. (THIS IS THE ONE SIDE)

In table 2, you should have a field called CustomerID, which is a Long Integer. Duplicates are allowed. (THIS IS THE MANY SIDE.) It is called a Foreign key , because it links to data from another table when you create queries, forms and reports.

in Table 1, you might have every thing which is directly relatable and unique to the customer, like name, address, zip, telephone , fax, etc.

In Table 2 , you might have details about orders the customer has placed, Invoice number, despatch date, despatch method and so on.

By joining the two customerID fields together, you create a relationship between the customers and their orders. Although a customer might place MANY orders, you only have to store the customer data ONCE.