If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
2 puzzles
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 -- Christi Lee |
#2
|
|||
|
|||
2 puzzles
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Christi Lee" wrote in message ... 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 -- Christi Lee |
#3
|
|||
|
|||
2 puzzles
Thank you Allen. That is exactly what I needed!
-- Christi Lee "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Christi Lee" wrote in message ... 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 -- Christi Lee |
Thread Tools | |
Display Modes | |
|
|