Re: [MS_AccessPros] Relationships

 

Khalid-

I have no clue why you put an SQL statement in the middle of your table descriptions.  Are you showing the Row Source of a combo box on Product?

YOU have to decide whether PricePerWeight and PricePerPc apply to the ProductForClaim table or ClientsClaim.  If those values are standard for the product, then they should be in ProductForClaim.  If they vary depending on the Client or some other factor, then they should be in ClientsClaim.  If there are "standard" values for each Product that might be overridden for a claim, then they should be in both tables, and you should copy the column values in the AfterUpdate event of the Product combo box into the CLientsClaim record.  You should give the fields a different name in the two tables so that it's clear that the values in ProductForClaim are the ones that are "standard" for that product, but the ones in ClientClaim are the values that apply to that particular claim.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Feb 6, 2014, at 10:14 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

Hi All,

Here i am with a basic and essential thing and i admit that i do lack in this matter, even i have made many applications and they are working.

I am up to now not clear with the concept of "Relationships". I give example of my two tables and then i would request the help to make me clear all about Relationships and design of tables:

ProductforClaim

Product ( Key, Text-50, No Duplicates)

PricePerWeight (Number, Double, Standard, Decimal Places 3)

PricePerPc (Number, Double, Standard, Decimal Places 2)

ClientsClaim

ClientCIN (Integer)

ClientName (Text 30, Duplicates OK)

ConsignmentNo (Text 9, Duplicates OK)

CartonNo (Long Integer, Decimal Places 0)

Product (Text 50, Duplicates OK, Row Source: SELECT ProductForClaim.Product, ProductForClaim.PricePerWeight, ProductForClaim.PricePerPc FROM ProductForClaim ORDER BY ProductForClaim.Product; )

PricePerWeight (Number, Double, Standard, Decimal Places 3) [Should this field be in this table?]

PricePerPc (Number, Double, Standard, Decimal Places 2) [Should this field be in this table?]

ProductQtyClaimed (Long Integer, Decimal Places 0)

ClaimPerPc (Number, Double, Standard, Decimal Places 2)

ClaimPerKg (Number, Double, Standard, Decimal Places 2)

My Relationship is One-to-Many on "Product" from ProductforClaim on ClientsClaim

Join Type is: Only include rows where the joined fields from both tables are equal.

Enforce Referential Integrity is Checked

Cascade Update Related Fields is Checked

IS THIS CORRECT?

When on a form "Find/Edit Products For Client Claim" i edit any "Product" it reflects in tbl ClientsClaim, but on the same form if i change "PricePerPc" it does not reflects/change in tbl ClientsClaim

Need help please.

Khalid

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (33)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___