calcculte Total selling based on format filed curancey (2 Viewers)

[AHMEDRASHED]

How i mange it please to calcculte Total selling based on format filed curancey (sellamountindivual+sellamountindivualgroup) * (usd or syp or eur )


if filed SellAmount format = AED then SumSF = =Sum([SellAmountIndividual])+Sum([SellAmountGroup])
if filed SellAmount format = USD then SumSF = =Sum([SellAmountIndividual])+Sum([SellAmountGroup])*TextUSD
if filed SellAmount format = EUR then SumSF = =Sum([SellAmountIndividual])+Sum([SellAmountGroup])*TextEUR
if filed SellAmount format = SYP then SumSF = =Sum([SellAmountIndividual])+Sum([SellAmountGroup])*TextSYP

 

[June7]

Need better understanding of data structure. Provide sample data. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[The_Doc_Man]

What you presented to us included mixed language intentions because those statements won't compile. There is also the issue that if we are to accept your variables as being descriptive, you have cases where you want to multiply by something you designated as text, which won't work. You would have to convert text values to a numeric data type. Then, while a set of mutually exclusive "IF" statements (called an IF-ladder) will work, for clarity you MIGHT wish to examine the SELECT CASE statement as a clear and efficient way to select one path from among many choices.

There are enough issues with what you posted that it will be difficult for us to make suggestions. Try describing your requirements in common language first, and build from that.

 

[Gasman]

I would use Case logic.

 

[AHMEDRASHED]

Need better understanding of data structure. Provide sample data. If you want to provide db for analysis, follow instructions at bottom of my post.

Thajk you , I Will share it morning because I don't have access to my computer now

 

[AHMEDRASHED]

What you presented to us included mixed language intentions because those statements won't compile. There is also the issue that if we are to accept your variables as being descriptive, you have cases where you want to multiply by something you designated as text, which won't work. You would have to convert text values to a numeric data type. Then, while a set of mutually exclusive "IF" statements (called an IF-ladder) will work, for clarity you MIGHT wish to examine the SELECT CASE statement as a clear and efficient way to select one path from among many choices.

There are enough issues with what you posted that it will be difficult for us to make suggestions. Try describing your requirements in common language first, and build from that.

Thank you, all filled numeric

Need better understanding of data structure. Provide sample data. If you want to provide db for analysis, follow instructions at bottom of my post.

form name : frmInv

 

[June7]

Why does tblPassengers have records without InvID and some without Passenger?

Problem is conversion rate (if that's what this factor is for?) needs to be included in aggregate calc. Aggregate function must reference fields not controls. This means need to include data for conversion rate in form RecordSource (which might result in a non-editable dataset or other issues because this would mean tblPassengers linking to tblInv). Could use DLookup() in RecordSource to pull rate into form dataset but domain aggregate functions can cause slow performance. Another approach is to use listbox to display summary data from an aggregate query. I have done that. Would need VBA to requery listbox when navigating records or after record entry/edit.

Aggregate calcs on form are not easy. This sort of data manipulation is what reports are for. Forms are supposed to be for data entry and reports for manipulation and output. There are no reports in the posted db. Why not at least one for Invoice?

 

[AHMEDRASHED]

Thanks guys, I just found an easy solution split currency celles (not the professional way but working for me)

 

[AHMEDRASHED]

What is the wrong in this relation ? please

 

[The_Doc_Man]

You showed two error messages.

"Index or primary cannot contain a null value" - because you probably have at least one record in that table that has a null in the primary key (PK) and it is a basic design rule, strictly enforced by Access, that your PK cannot contain a null. When you declare something to be a PK, it has to have a value so that the index can be used to find it. But null isn't a value. You cannot compare anything to a null (including another null!) Depending on whether the data came first or the table design came first, this is either a data problem or a problem with whatever you tried to use to load data to the table.

"You cannot add or change a record because a related record is required in table 'tbluSuppliers'" - because when you declare a relationship that enforces relational integrity (RI), you establish a dependency between a "parent" table and its "child" table. The PK of the parent table is used to tag records in the child table as a way to demonstrate that the records are related. But the most common error is trying to enter data in the child table first. When RI is specified, Access will not allow "orphans" - i.e. a child record with no parent record. In simple terms, you tried to enter a value in tblInv, tblPassengers, or tblPassengersH for which the (foreign key) SupplierID was not in in the supplier table.

You have other issues as well. You appear to have direct AND indirect relationships leading from tblInv and tblInvH through the two passenger tables to the supplier table. This will seriously complicate matters - basically because in queries that include the Inv and Suppliers table, Access won't know whether to go directly or indirectly through the relationships. This ambiguous relational path might be a cause (at some later point) for making a query become unable to be updated.

 

[AHMEDRASHED]

You showed two error messages.

"Index or primary cannot contain a null value" - because you probably have at least one record in that table that has a null in the primary key (PK) and it is a basic design rule, strictly enforced by Access, that your PK cannot contain a null. When you declare something to be a PK, it has to have a value so that the index can be used to find it. But null isn't a value. You cannot compare anything to a null (including another null!) Depending on whether the data came first or the table design came first, this is either a data problem or a problem with whatever you tried to use to load data to the table.

"You cannot add or change a record because a related record is required in table 'tbluSuppliers'" - because when you declare a relationship that enforces relational integrity (RI), you establish a dependency between a "parent" table and its "child" table. The PK of the parent table is used to tag records in the child table as a way to demonstrate that the records are related. But the most common error is trying to enter data in the child table first. When RI is specified, Access will not allow "orphans" - i.e. a child record with no parent record. In simple terms, you tried to enter a value in tblInv, tblPassengers, or tblPassengersH for which the (foreign key) SupplierID was not in in the supplier table.

You have other issues as well. You appear to have direct AND indirect relationships leading from tblInv and tblInvH through the two passenger tables to the supplier table. This will seriously complicate matters - basically because in queries that include the Inv and Suppliers table, Access won't know whether to go directly or indirectly through the relationships. This ambiguous relational path might be a cause (at some later point) for making a query become unable to be updated.

Thank you for your clarifications i appreciate it , i will try to fix it