Creating A Form To Work With Two "Many To Many" Relationships., Office 2003

[Ken Gardiner]

Hi Everyone,
I am working on a project that seems to need two many to many relationships. I'm having the hardest time figuring out the best way to create a form that will work well for entering data.

The data for this project is received in Excel files that have many rows (about 45) and several columns (about 6). I have posted an image of my relationships screen below.



Does it looks like I'm on the right track? It would also be ideal if I could display all 270 empty fields on the screen for ease of data entry. Thank you so much in advance for your advice.

[ADezii]

This is a difficult to explain, so I am making available an Attachment in the form of an Access Database that should make this concept easier to grasp. It graphically depicts the Relationship between Authors and Books (MANY <==> MANY) and the use of an Intermediate Table to connect the two. Hope this helps.

[Ken Gardiner]

Hi ADezii,
Thank you for the quick response and for the example. My problem is that I don't want the user to have to select column by column or row by row, we receive the data in an excel spreadsheet that is 45X6 and almost every cell usually has an entry. I'd like to design a form that approximates the spreadsheet for data entry. I have a feeling I might need a sub form for rows, and a sub sub for columns, but I haven't been able to find an example similar to what I am dealing with.

[Ken Gardiner]

Just to explain my situation a little more, this is what the excel forms looks like:



There are actually 45 lines per screen, and 6 different tabs. Most forms are completely filled in when we receive them. The older database we are using has every single cell defined as a variable, and my goal is to create a normalized version of that database in Access that still gives the user a nice easy interface to enter all this data. Is my original table design appropriate? What approach should I take for form design (i.e., unbound cells and VBA code, continuous subforms, etc.). Thanks again in advance for any help.

[ADezii]

Are each of the Excel Worksheets supposed to correspond to a Table as depicted in the Relationships in Post#3?

[Ken Gardiner]

I haven't even attempted to add the 6 tabs to the mix yet. I was planning on getting my head around how to design this db, get it working with the first sheet, and then adding them later. Thanks again.

[silveredge52]

ADezii,
Excellent example. I had the same question and your example worked wonderfully. The "Add &Author to Database" button, is SUPER cool.

** Snip **