 | |
#1
 
| |||
| |||
|
RELATIONSHIPS -
11-05-2004
, 11:13 PM
#2
| |||
| |||
|
|
I have a Database where I enter Orders. The tables are as such. Sales Sales Details Discount Types Sales ID Sales ID Discount ID Discount ID Products (One) (Many) (One) The Relationships are in Parenthess. Whenever I go to enter a product I can't because its looking for a Discount. If I remove the relationship between the Sales Details and the Discount Types I can enter all of the Products I want. Do I have to have the Discount Types connected in a relationship? Is it bad if I break the relationship? Any help is appreciated. Thanks. DS |
#3
| |||
| |||
|
|
1. Open the Sales Details table in design view. 2. Select the DiscountID field. 3. In the lower pane, remove the zero from the Default Value. When you try to add a record and don't choose a discount, the field's Default Value is assigning a zero, which does not match any entry in the Discount Types table, and so the relationship is quite correctly warning you that things have gone bad. You *really* need the relationship, with Referential Integrity. Once you remove the default value, it will save quite happily without any discount entered. Another trap you will strike later, though: When you create a query using all 3 tables, Access will only give you the sales that have a discount. To solve this, you need an outer join. Details in: The Query Lost My Records! at: http://members.iinet.net.au/~allenbrowne/casu-02.html Mr. Browne, |
One other question. I have discounts that could apply to both Checks
and Items on checks. Do I have to make a seperate discount table for
the check discounts and one for the item discounts or can I use the same
table for both and in the relationship table I would have the same table
but twice. Access would name the copy of the first table Discounts 1.
Is that OK? Thank you once again.
Sincerely,
DS
#4
| |||
| |||
|
|
Allen Browne wrote: 1. Open the Sales Details table in design view. 2. Select the DiscountID field. 3. In the lower pane, remove the zero from the Default Value. When you try to add a record and don't choose a discount, the field's Default Value is assigning a zero, which does not match any entry in the Discount Types table, and so the relationship is quite correctly warning you that things have gone bad. You *really* need the relationship, with Referential Integrity. Once you remove the default value, it will save quite happily without any discount entered. Another trap you will strike later, though: When you create a query using all 3 tables, Access will only give you the sales that have a discount. To solve this, you need an outer join. Details in: The Query Lost My Records! at: http://members.iinet.net.au/~allenbrowne/casu-02.html Mr. Browne, Your the best! Removing the "0" from the default worked Like a Gem! One other question. I have discounts that could apply to both Checks and Items on checks. Do I have to make a seperate discount table for the check discounts and one for the item discounts or can I use the same table for both and in the relationship table I would have the same table but twice. Access would name the copy of the first table Discounts 1. Is that OK? Thank you once again. Sincerely, DS |
#5
| |||
| |||
|
|
If you need two relationships, then yes, drag the table into the Relationships window again, and let Access alias it with the "_1" suffix. That's the correct way to set up 2 different relationships (as distinct from a 2-field relationship). Thanks Allen. |
Thank you
DS
#6
| |||
| |||
|
|
Allen Browne wrote: If you need two relationships, then yes, drag the table into the Relationships window again, and let Access alias it with the "_1" suffix. That's the correct way to set up 2 different relationships (as distinct from a 2-field relationship). Thanks Allen. But is it OK to use the table once and access it from 2 different tables? Thank you DS |
#7
| |||
| |||
|
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.