table structure help

[bjackson]

Pat
Have i done the wrong thing by not following your advice exactly ?

Regards
Bjackson

 

[Pat Hartman]

Sorry, I haven't had time to look at your db and I'm off to our monthly Access users group meeting and I'll be gone for the rest of the day. I'll try to look at it if I get home early enough tonight or tomorrow.

Please understand that when you get design suggestions from myself and others, they are "first thoughts" rather than cast in concrete. If I were doing this for myself, I would spend days or at least hours, depending on complexity, to design the complete application. I don't know all your business rules. I don't have any of the user documentation to refer to. I am basically flying blind and giving you a best guess based on long experience.

 

[bjackson]

Thanks Pat

I Guess its like "How Long is a piece of string?".What takes you hrs blindfolded takes me weeks.Even though i get it down on paper i still struggle to understand how to connect it.My parents were right,i should have finished school.
Anyhow i will wait for further suggestions.I have no trouble doing it with excell spreadsheets but can see the enormous benifits of using a properly constructed db,so if it takes me a while to get it right,i dont mind.


Regards
bjackson

 

[Pat Hartman]

I put the db together and of course it's too big to upload. PM me with your email and I'll send it.

I added primary keys to all the tables that did not have them.
I added a record to one of the tables so that I could enforce RI. It has ??? in it except for the primary key.
I conntectd all the tables as best I could based on foreign key names and data values.
I don't think the structure is quite right yet but it is getting close.

 

[bjackson]

Thanks for the help Pat
The Table LaminateSizes Now has three keys.How Did you do that?
The query still returns 3 values, so what i will try next is to set up a purchase order form with all the relevant criteria's and see what happens when i enter an actual purchase order.

Again thank you for the help.

Regards
Bjackson

 

[Pat Hartman]

The Table LaminateSizes Now has three keys.How Did you do that?

Jet supports multi-field primary keys and indexes with up to 10 columns. To create a multi-field primary key use cntl-click to select each column. When all the necessary columns are highlighted, click the key icon. To make a multi-column index, you need to open the indexes dialog. Add a name for the index and select the first column. Then move down to the next row and leave the name field blank but select the next key column and so on until all are selected. Ascending and descending are specified on the first index row.

The query still returns 3 values,

- I worked out why 3 rows are returned but I don't have the information necessary to solve the problem. Remember that I told you that I felt that something wasn't quite right with the table structures. I don't really have the hours necessary to spend analyzing this problem so I'll tell you where the problem is and hopefully you have sufficient domain knowledge to resolve it.

TblLaminateCategoryPrices has three rows that have a lowqty of 1 and highqty of 10 so all three are selected. Either this table needs to change or you nees some other piece of information to narrow down the criteria so that only one row is returned from this table. To get to this point, I opened each table in the query separately and applied the criteria. The other tables could be wittled down to a single row but this table could not.

 

[bjackson]

Thanks Again

I will Get There !!

Regards
Bjackson