2 puzzles

[Christi Lee]

My first puzzle is a percentage question. How would I build a query. I
have two fields for sure. Lets say the first one says 22% and the second is
64%. (These numbers are always different) sometimes there is a third. What
I need is. If the first one is low and the second one is high, I want it to
automatically calculate the third percentage. My second
puzzle is a relationship question. I have one bill. The first part is our
company info and charges, the second half is one of our vendors info. Same
product and charges. My relationships go like this. CustomerID is
connected to orderID-. Order ID is related to Products, Products ID is
related to ChargesID. I have a junction table that connects Charges, Mileage
Detail, Taxes, since they are different but connected in many to many ways.
I'm not sure where to connect Vendors. Should they be connected to
customers since they need to be on one bill? Thank you for your help

 

[Allen Browne]

Christi, can I encourage you to open the Northwind sample database from
Microsoft, and look at the Relationships diagram to see how they relate
customers, orders, order details, products, and vendors (which they call
Suppliers)? The example there covers everything except for the charges you
talk about.

There are some important things in the Northwind example. Particularly, the
way they set up the Orders and Order Details table, so that one order can
have many line items. Do you need to be able to handle that?

Now, if I understand you, in your case a single order is billed can be
billed to multiple customers, so Customer 9 might get 22% of the order,
customer 10 might get 64%, and customer 99 would have to pay the remainder.
It that how it works? If so, you will not have a CustomerID field in your
Orders table (since an order doesn't have just one customer.)

Instead, you will have a Charge table, with fields like this:
- ChargeID primary key
- OrderID relates to the primary key of your Orders table
- CustomerID relates to the primary key of your Customers table.
- DuePercent Number (size Double, format Percent.)

To interface this, use another subform on your orders form. You will already
have one subform for the order details (one product each row, as in
Northwind.) In this second subform, you will have a combo for selecting the
customer, and a text box for entering the percent. You can design this so
that when you select a customer, it automatically calculates what percent is
left unassigned, and assign it to the DuePercent for you. So, when you enter
the first customer, it assigns 100%. If you change that to 22%, when you
enter the second row, it assigns 78%. If you change that to 64%, when you
select a customer on the 3rd row, it then assigns 14%. In my experience,
that's the best way to have the computer performing the calculations without
making any assumptions about the number of rows you will have in the end.

So, the code sums the *other* rows of the charges in the After Update event
procedure of the CustomerID combo, and assigns the balance like this:

Private Sub CustomerID_AfterUpdate
Dim strWhere As String
strWhere = "([OrderID] = " & Nz(Me.Parent!OrderID, 0) & _
") AND ([ChargeID] <> " & Nz(Me.ChargeID, 0) & ")"
Me.DuePercent = 1 - Nz(DSum("DuePercent", "Charge", strWhere),0)
End Sub

 

[Christi Lee]

Thank you Allen. That is exactly what I needed!
--
Christi Lee

Christi, can I encourage you to open the Northwind sample database from
Microsoft, and look at the Relationships diagram to see how they relate
customers, orders, order details, products, and vendors (which they call
Suppliers)? The example there covers everything except for the charges you
talk about.

There are some important things in the Northwind example. Particularly, the
way they set up the Orders and Order Details table, so that one order can
have many line items. Do you need to be able to handle that?

Now, if I understand you, in your case a single order is billed can be
billed to multiple customers, so Customer 9 might get 22% of the order,
customer 10 might get 64%, and customer 99 would have to pay the remainder.
It that how it works? If so, you will not have a CustomerID field in your
Orders table (since an order doesn't have just one customer.)

Instead, you will have a Charge table, with fields like this:
- ChargeID primary key
- OrderID relates to the primary key of your Orders table
- CustomerID relates to the primary key of your Customers table.
- DuePercent Number (size Double, format Percent.)

To interface this, use another subform on your orders form. You will already
have one subform for the order details (one product each row, as in
Northwind.) In this second subform, you will have a combo for selecting the
customer, and a text box for entering the percent. You can design this so
that when you select a customer, it automatically calculates what percent is
left unassigned, and assign it to the DuePercent for you. So, when you enter
the first customer, it assigns 100%. If you change that to 22%, when you
enter the second row, it assigns 78%. If you change that to 64%, when you
select a customer on the 3rd row, it then assigns 14%. In my experience,
that's the best way to have the computer performing the calculations without
making any assumptions about the number of rows you will have in the end.

So, the code sums the *other* rows of the charges in the After Update event
procedure of the CustomerID combo, and assigns the balance like this:

Private Sub CustomerID_AfterUpdate
Dim strWhere As String
strWhere = "([OrderID] = " & Nz(Me.Parent!OrderID, 0) & _
") AND ([ChargeID] <> " & Nz(Me.ChargeID, 0) & ")"
Me.DuePercent = 1 - Nz(DSum("DuePercent", "Charge", strWhere),0)
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

My first puzzle is a percentage question. How would I build a query. I
have two fields for sure. Lets say the first one says 22% and the second
is
64%. (These numbers are always different) sometimes there is a third.
What
I need is. If the first one is low and the second one is high, I want it
to
automatically calculate the third percentage. My second
puzzle is a relationship question. I have one bill. The first part is our
company info and charges, the second half is one of our vendors info.
Same
product and charges. My relationships go like this. CustomerID is
connected to orderID-. Order ID is related to Products, Products ID is
related to ChargesID. I have a junction table that connects Charges,
Mileage
Detail, Taxes, since they are different but connected in many to many
ways.
I'm not sure where to connect Vendors. Should they be connected to
customers since they need to be on one bill? Thank you for your help