Form and Link Table Problem

[Knight of Nee]

Hiya

The structure of my database can be seen here

but basically I have a products and suppliers table which are separated by a link table (because more than one supplier can stock the same item). For some reason unknown to me Access wont let me create a relationship between the pricelist table and the order_details table. This is turn wont let me create a order getting the price of a product from a certain supplier. Can anyone offer me an explanation or possible solution.

Thanks
Craig

 

[SydneyGeek]

I think the main issue is that your keys don't match. In one table you have Stock_ID and Supplier_ID as a compound key -- in the second table you have Stock_ID and Order_ID. Access won't let you relate these tables.

Denis

 

[Andrew Fergus]

I was looking at this earlier and couldn't quite put my finger on what was wrong but, like Denis, I think the issue is the price list table.

There is a one to many relationship between the order header (one) and the order detail (many) tables - which is fine. You also have a one to many relationship between the stock (one) and price list (many) tables. Given the price list and order details are both on the many side of their respective relationships, Access can't link these two tables using the stock id field (which is what I think you are trying to do). In effect I think that what you are trying to create is a many to many relationship (which doesn't exist naturally in Access without going through an intermediary table - you don't really want to go there, but if you do then read this).

Also, I have found that where the table links form a circle (I think you have 2 circles), then Access can get confused with the relationships. You may find that you want to chnage the relationships in your queries when you are designing your queries to avoid these sorts of issues.

I personally would have linked the supplier table to the order table and linked the order detail table to the product table. I would eliminate the supplier price list given it will be a major hassle to maintain. Alternatively, you could have the supplier price lists table in your database, but not link it in the relationship screen - simply link it when you need it (if you can) in your queries.

Lastly, is tblAuthorisation necessary?

HTH, Andrew.

 

[Knight of Nee]

Hi

Thanks for the replies. I will try a few of the suggestions and see if they work. Andrew, The Authorisation table isn’t completely necessary i will now be using the Order table to store this information

Thanks
Craig

 

[Knight of Nee]

Hi

Andrew you suggest removing the pricelist table, if i do that this would create a many -to -many as many suppliers can supplier the same product. how would i get round this?

Craig

 

[SydneyGeek]

Hi Craig,

A quick comment re the Staff table -- it doesn't need to be related to the Order or Category tables. Taking a punt, these realtionships are probably the result of using the Lookup Wizard to create lookup fields. No need -- it's more efficient to break those links and use combo boxes on the Order and Category forms to pick the Staff IDs.
If you need more detail on this, post back.

Denis

 

[Knight of Nee]

I have stored the staffid in category because only certain members of staff can order from certain categorys so i need to check ther id against whether they can order or not. How could i get round this

Thanks
Craig

 

[SydneyGeek]

Quick rundown:

1. Break the relationship between Staff and Catgories.
2. Go to the Categories form in Design view. Check the StaffID control -- it will be either a text box or a combo. If it's a text box, right-click the control and Change To | Combo Box.
3. Now, right-click the Combo and select Properties. Click the All tab. There are quite a few properties -- concentrate on the following:
Control Source -- should be StaffID (or whatever you called the field)
Row Source Type -- should be Table / Query
Row Source -- may or may not have an expression in it. Select the databox, click the Builder button (...) and you'll see the design grid for the underlying query. Set it as follows:
First field -- StaffID
Second field -- [Surname] & ", " & [Forename]
Close the dialog, save when prompted.
Column Count -- should be 2 for this example
Bound Column -- should be 1. This is the column whose value gets stored.
Column Widths -- Set to 0;3 This hides the forst column and shows the name instead.

Save, go to Datasheet view and try it out.

Denis

 

[Andrew Fergus]

Hi Craig

In answer to this post :

Andrew you suggest removing the pricelist table, if i do that this would create a many -to -many as many suppliers can supplier the same product. how would i get round this?

I suggested removing the price list table because (from experience) I think you will find the table a major hassle to maintain (changing prices, changing suppliers, changing products etc.) and deleting it now will save you the hassle of deleting it later - a hassle due to the price list being embedded in many queries and forms and I believe you will be cursing that table in a month or two. {<- would you agree or disagree with that Denis? I'm curious as to a 2nd opinion} Also, it helped to solve the many-to-many problem.

So, how to get around it? My suggestion of linking the supplier table to the order header table and linking the order detail table to the product detail will result in the user being able to select any product for any supplier. So in one sense it is good because there are no restrictions (so we have got around the problem) but on the other hand, it may cause data integrity problems due to the same lack of restrictions.

An alternative approach would be to de-link the price list (but link the other tables per my suggestion) and limit the products able to be selected for a particular supplier on the basis of the price list, via drop down / combo box on the form (similar to the suggestion from Denis with the staff & categories)

Andrew

 

[SydneyGeek]

Craig, I mostly agree with Andrew on the last post. You CAN set up the Pricelist table for relatively simple maintenence, but it will be tedious if your suppliers regularly change their catalogues.
You'd need a Suppliers form with Pricelist as the subform, so you can see all items from a given supplier. You'll need a Category field in the pricelist table as well (I assume you have that). Probably the best thing to do is to look at the Northwind database if you want to proceed this way. I guess, if your suppliers sned you electronic catalogues, it wouldn't be too hard to change prices with a succession of Update queries. If you have to do it by hand...

If you want to de-link the table as per Andrew's suggestion, it's not difficult to force one combo to only show items based on an "upstream" combo. This post shows how to cascade 3 combos -- you'll get th epattern if you want to go any further http://www.mrexcel.com/board2/viewtopic.php?t=94118&highlight=combo

Denis