Applying Many to Many relationship

[GISnut]

Let me give you a little background before I get into my problem. I am essentially creating a inventory/search engine for as-builts drawings. I have created forms in order to edit the database and forms to search the database. Most of my data is in one table, As-builts. In the As-builts table I have the following fields:

Project Title
Sheet Title
Number in Drawing Set
Total Number in Drawing Set
Drawing Type
Sheet Number
Final Submittal Date
Hyperlink
b1
b2
b3
b4
b4
multi

I also have a drawing type table that is link to the drawing type field.

My problem comes in with my building numbers. There can be multiple building numbers assigned to a project and multiple projects can have the same buildings numbers in it. I tried to figure out the multi-valued field, appearently introduced in Access 2007, but that not only made the database un-normalized but it also did not allow for a multivalued field to be used in a select query(as I have a long select query that gives results based on the user selected criteria). To get around that, I'm ashamed to say, I created another yet another field that just concatenated all of the building field (b1-b5) into one field (multi) with commas dividing the numbers. It worked for what I needed but I knew it was completely incompatiable with normalization rules.

I now need to fix the patch because 1) it's bad practice 2) I need to use the buildings numbers to create a cascading combo box search form.

I am clearly new at this so any help would be greatly appreciated. I know there are several other posting on this but it seems that I have another level of complexity thrown in when it comes to the select query that is used in my search form.

Thanks in advanced for your help!

Kirsten

 

[GISnut]

When re-reading my post I guess I really didn't state a question. My question is I understand that a many to many relationship by way of a junction table would fix my multiple building numbers and multiple projects problem but how would I implement it?

I did a little mock up of a sole projects table and sole building number table with a bldgproject junction table (independent of my as-builts table) and linked them correctly (one-to-many from each table to the junction table), yet I'm not sure how to apply this.

As I said before any help would be appreciated! Thanks!

Kirsten