Many to Many tables (1 Viewer)

[OakCraft]

Hi guys, I am having a bit of trouble designing a table layout for a complex form.
What I have is softwood order sheet with rows down the left for types of timber, ie studwork, bracing, rafters, etc. Across the top I have headings for timber size (47 x 100 or 47 x 150, etc) timber length (2400, 3000, 4200) and total number required.
I would like to have this sheet appear in a client details form, so I need unique data for each client.
Any ideas on the easiest way to accomplish this? I have been toying with importing an excel sheet, but would rather use tables.

As an aside, I have products table in place already, and this includes various timbers of correct size to link to the form. What I need though, is the sheet to list all of the columns, even I have no need for the timber. Clear as mud eh!

Thanks guys

 

[The_Doc_Man]

When you are asking for something that looks like a spreadsheet, smells like a spreadsheet, and acts like a spreadsheet,... you should MAKE it a spreadsheet. I understand and applaud your desire to go "tablular" and even think it might be the right idea. Long-term, tables are probably easier to manage and control, logically speaking.

Having said that, the design of that table - given you are still thinking "spreadsheet" - is going to be crucial. If the proposed database function requires certain types of human interaction with that spreadsheet, you could well be looking at a really ugly interface.

In general, if you have a true spreadsheet, the mapping of that sheet to a table involves making three fields (minimum). The first two fields are in either order depending on preference. They hold data corresponding to the values for the spreadsheet's column-header row and for its row-title column. The third value is then the value in the spreadsheet cell found at the intersection of the column and row keys.

Sadly, that is a generality that often doesn't quite fit. (Been there too many times.) Nonetheless, it is possible to map forwards like I said. The other side of the coin might well be the question, "how do you get that table back into spreadsheet format?"

If you wanted a REPORT, look into CrossTab queries. If you wanted something more interactive, I'm not sure what is best for you because then we start to bog down in the details. You would have to decide what you wanted to do with it and describe that before any of us can give you much more direction.

 

[OakCraft]

Thanks TheDocMan, I have revised the plans slightly, but still need guidance. Down the left I will have Studwork, Rafters, Bracing, across the top I wil have ProductID and Quantity. It all needs to link back to ClientID in another table. I am playing with relationships at the moment, but can't quite picture it.

Thanks, Jason.

 

[OakCraft]

I have been playing around today, and have managed to get a scheme up together. The problem now is that I can only have one instance of a ProductID per SoftwoodID. What I need to do is be able to have many of the same ProductID in the one table, if you know what I mean!
Do I need another junction table, and then make the softwood details to product link a many to many relationship, or is there another way around it?