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
|
|||
|
|||
HELP
Ok people i appreciate this might me a bit long winded but ANY help would be
appreciated. i have a table called "Customer_Details" and i have 3 other tables calls "tariff 1", "tariff 2" and "tariff 3". The Customer_Details table has a form with the same name and on that form there is a field called "tariff" which is a drop down box with tariff 1,2 and 3 in it. Also on the Customer_Details form is a Balance field which is linked to the same on the table. finally there is a table called "data_live" this is continually added to on a day to day basis, the fields are : Acc No, Cost, Prod ID. on the tariff tables each product has a product id Basically want i want to do is on the customer form select a tariff, then when the data is entered into data_live the correct tariff is apllied to the product (via product id) then i think i am write in saying that i will be able to create a query that sums up all the prices for that customer (via Acc No) and displays the result in the Balance field. any help at all would be great many thanks |
#2
|
|||
|
|||
HELP
Stevene James wrote:
Ok people i appreciate this might me a bit long winded but ANY help would be appreciated. i have a table called "Customer_Details" and i have 3 other tables calls "tariff 1", "tariff 2" and "tariff 3". I haven't analyzed your structure extensively, but this looks suspicious. Are [tariff 1], [tariff 2], and [tariff 3] so different from each other that you can't combine their data into one Table? You'd probably want to include a [TariffType] field with value of 1, 2, or 3 to distinguish, but maybe the included fields are similar enough to combine the records. The Customer_Details table has a form with the same name and on that form there is a field called "tariff" which is a drop down box with tariff 1,2 and 3 in it. Also on the Customer_Details form is a Balance field which is linked to the same on the table. finally there is a table called "data_live" this is continually added to on a day to day basis, the fields are : Acc No, Cost, Prod ID. on the tariff tables each product has a product id Basically want i want to do is on the customer form select a tariff, then when the data is entered into data_live the correct tariff is apllied to the product (via product id) then i think i am write in saying that i will be able to create a query that sums up all the prices for that customer (via Acc No) and displays the result in the Balance field. any help at all would be great many thanks I'm not sure exactly what difficulty you're having, but the tariff to be applied seems to be a function of both [product id] and the equivalent of the [TariffType] field that I proposed. That should be easy to do in a Query. -- Vincent Johns Please feel free to quote anything I say here. |
#3
|
|||
|
|||
HELP
thanks you sir, but i am unsure of how to do this.
i apologise for double posting "Vincent Johns" wrote: Stevene James wrote: Ok people i appreciate this might me a bit long winded but ANY help would be appreciated. i have a table called "Customer_Details" and i have 3 other tables calls "tariff 1", "tariff 2" and "tariff 3". I haven't analyzed your structure extensively, but this looks suspicious. Are [tariff 1], [tariff 2], and [tariff 3] so different from each other that you can't combine their data into one Table? You'd probably want to include a [TariffType] field with value of 1, 2, or 3 to distinguish, but maybe the included fields are similar enough to combine the records. The Customer_Details table has a form with the same name and on that form there is a field called "tariff" which is a drop down box with tariff 1,2 and 3 in it. Also on the Customer_Details form is a Balance field which is linked to the same on the table. finally there is a table called "data_live" this is continually added to on a day to day basis, the fields are : Acc No, Cost, Prod ID. on the tariff tables each product has a product id Basically want i want to do is on the customer form select a tariff, then when the data is entered into data_live the correct tariff is apllied to the product (via product id) then i think i am write in saying that i will be able to create a query that sums up all the prices for that customer (via Acc No) and displays the result in the Balance field. any help at all would be great many thanks I'm not sure exactly what difficulty you're having, but the tariff to be applied seems to be a function of both [product id] and the equivalent of the [TariffType] field that I proposed. That should be easy to do in a Query. -- Vincent Johns Please feel free to quote anything I say here. |
#4
|
|||
|
|||
HELP
the tariff contain the same products but at different prices
"Vincent Johns" wrote: Stevene James wrote: Ok people i appreciate this might me a bit long winded but ANY help would be appreciated. i have a table called "Customer_Details" and i have 3 other tables calls "tariff 1", "tariff 2" and "tariff 3". I haven't analyzed your structure extensively, but this looks suspicious. Are [tariff 1], [tariff 2], and [tariff 3] so different from each other that you can't combine their data into one Table? You'd probably want to include a [TariffType] field with value of 1, 2, or 3 to distinguish, but maybe the included fields are similar enough to combine the records. The Customer_Details table has a form with the same name and on that form there is a field called "tariff" which is a drop down box with tariff 1,2 and 3 in it. Also on the Customer_Details form is a Balance field which is linked to the same on the table. finally there is a table called "data_live" this is continually added to on a day to day basis, the fields are : Acc No, Cost, Prod ID. on the tariff tables each product has a product id Basically want i want to do is on the customer form select a tariff, then when the data is entered into data_live the correct tariff is apllied to the product (via product id) then i think i am write in saying that i will be able to create a query that sums up all the prices for that customer (via Acc No) and displays the result in the Balance field. any help at all would be great many thanks I'm not sure exactly what difficulty you're having, but the tariff to be applied seems to be a function of both [product id] and the equivalent of the [TariffType] field that I proposed. That should be easy to do in a Query. -- Vincent Johns Please feel free to quote anything I say here. |
#5
|
|||
|
|||
HELP
Stevene James wrote:
the tariff contain the same products but at different prices Then I suggest that you define a Table in which each record specifies a [product id], a [price], and a [TariffType] (1, 2, or 3) specifying which price you want to use. -- Vincent Johns Please feel free to quote anything I say here. |
#6
|
|||
|
|||
HELP
thanks for your suggestion however i am still unsure of what you mean
apologies for appearing thick. "Stevene James" wrote: thanks you sir, but i am unsure of how to do this. i apologise for double posting "Vincent Johns" wrote: Stevene James wrote: Ok people i appreciate this might me a bit long winded but ANY help would be appreciated. i have a table called "Customer_Details" and i have 3 other tables calls "tariff 1", "tariff 2" and "tariff 3". I haven't analyzed your structure extensively, but this looks suspicious. Are [tariff 1], [tariff 2], and [tariff 3] so different from each other that you can't combine their data into one Table? You'd probably want to include a [TariffType] field with value of 1, 2, or 3 to distinguish, but maybe the included fields are similar enough to combine the records. The Customer_Details table has a form with the same name and on that form there is a field called "tariff" which is a drop down box with tariff 1,2 and 3 in it. Also on the Customer_Details form is a Balance field which is linked to the same on the table. finally there is a table called "data_live" this is continually added to on a day to day basis, the fields are : Acc No, Cost, Prod ID. on the tariff tables each product has a product id Basically want i want to do is on the customer form select a tariff, then when the data is entered into data_live the correct tariff is apllied to the product (via product id) then i think i am write in saying that i will be able to create a query that sums up all the prices for that customer (via Acc No) and displays the result in the Balance field. any help at all would be great many thanks I'm not sure exactly what difficulty you're having, but the tariff to be applied seems to be a function of both [product id] and the equivalent of the [TariffType] field that I proposed. That should be easy to do in a Query. -- Vincent Johns Please feel free to quote anything I say here. |
#7
|
|||
|
|||
HELP
Stevene James wrote:
thanks for your suggestion however i am still unsure of what you mean apologies for appearing thick. OK, maybe an example would help. Can you post some example data (maybe 2 or 3 records) from each Table showing what you're trying to do? Please be sure to change anything that might convey sensitive data, such as people's names. -- Vincent Johns Please feel free to quote anything I say here. |
#8
|
|||
|
|||
HELP
thanks for being so patient.
ok so i have 1 table called customer details. it has all the customers details like name, address.etc and a drop down box called Tariff. In the drop down box is tariff 1,2 and 3 then i have 3 tables called tariff 1,2 and 3 each 3 tables has the same data in it apart from the prices i.e Tariff 1 [Product ID] [Product Name] [3p] Tariff 2 [Product ID] [Product Name] [5p] Tariff 3 [Product ID] [Product Name] [7p] ALL THE DATA IS THE SAME IN EACH APPART FROM PRICE i then have a table called CDRecords - Live in this table is all transactions that are sent to be from an external source and these would look something like this: 24/10/05 11:24 [Account Number] [PRODUCT ID] so what i want to happen is on the customer details form for there to be a balance field which drags its data through a query from the CDRecords - Live and the [account number] field. But applies the ammount based on what tariff is selected. I hope that is easier to understand "Stevene James" wrote: thanks for your suggestion however i am still unsure of what you mean apologies for appearing thick. "Stevene James" wrote: thanks you sir, but i am unsure of how to do this. i apologise for double posting "Vincent Johns" wrote: Stevene James wrote: Ok people i appreciate this might me a bit long winded but ANY help would be appreciated. i have a table called "Customer_Details" and i have 3 other tables calls "tariff 1", "tariff 2" and "tariff 3". I haven't analyzed your structure extensively, but this looks suspicious. Are [tariff 1], [tariff 2], and [tariff 3] so different from each other that you can't combine their data into one Table? You'd probably want to include a [TariffType] field with value of 1, 2, or 3 to distinguish, but maybe the included fields are similar enough to combine the records. The Customer_Details table has a form with the same name and on that form there is a field called "tariff" which is a drop down box with tariff 1,2 and 3 in it. Also on the Customer_Details form is a Balance field which is linked to the same on the table. finally there is a table called "data_live" this is continually added to on a day to day basis, the fields are : Acc No, Cost, Prod ID. on the tariff tables each product has a product id Basically want i want to do is on the customer form select a tariff, then when the data is entered into data_live the correct tariff is apllied to the product (via product id) then i think i am write in saying that i will be able to create a query that sums up all the prices for that customer (via Acc No) and displays the result in the Balance field. any help at all would be great many thanks I'm not sure exactly what difficulty you're having, but the tariff to be applied seems to be a function of both [product id] and the equivalent of the [TariffType] field that I proposed. That should be easy to do in a Query. -- Vincent Johns Please feel free to quote anything I say here. |
#9
|
|||
|
|||
HELP
Stevene James wrote:
thanks for being so patient. I still need a little more information, but this is clearer. ok so i have 1 table called customer details. it has all the customers details like name, address.etc and a drop down box called Tariff. In the drop down box is tariff 1,2 and 3 then i have 3 tables called tariff 1,2 and 3 each 3 tables has the same data in it apart from the prices i.e Tariff 1 [Product ID] [Product Name] [3p] Tariff 2 [Product ID] [Product Name] [5p] Do you mean that there's a record in [Tariff 2] which has the same [Product ID] and [Product Name] values as some record in [Tariff 1], and that some record in [Tariff 3] also has these same values? Tariff 3 [Product ID] [Product Name] [7p] ALL THE DATA IS THE SAME IN EACH APPART FROM PRICE i then have a table called CDRecords - Live in this table is all transactions that are sent to be from an external source and these would look something like this: 24/10/05 11:24 [Account Number] [PRODUCT ID] Nothing in this record appears to have anything to do with an amount of money. so what i want to happen is on the customer details form for there to be a balance field which drags its data through a query from the CDRecords - Live and the [account number] field. But applies the ammount based on what tariff is selected. Where does the [amount] come from? How do you "apply" the number in [5p] or [7p] to the [amount]? (You might multiply it, or you might add the square root. You didn't specify.) I hope that is easier to understand It's a little easier to understand. I'm still guessing that you need to have one [Tariff] Table that includes these fields: [TariffID] (primary key) [Product ID] [Product Name] [3p] [5p] [7p] .... and which of [3p], [5p], and [7p] is chosen as the value to be used in calculating the tariff is determined by the customer's tariff type (using the value in [3p] if the tariff type selected is 1, etc.) -- Vincent Johns Please feel free to quote anything I say here. |
#10
|
|||
|
|||
HELP
thanks for your reply.
ok, the tariff table(s) look like this: Tariff 1 UKN,FM9,[PRODUCT NAME] [3p] Tariff 2 UKN,FM9,[PRODUCT NAME] [5p] Tariff 3 UKN,FM9,[PRODUCT NAME] [7p] so then in the CDRecords - Live table there is: 30/10/05,11:00.59,[AccountNumber],FM9 so, the FM9 or sometimes even the UKN is the itentifier, but i if a customer is on tariff 2 then they would pay more for that product than if they were on tariff 1. "Stevene James" wrote: thanks for being so patient. ok so i have 1 table called customer details. it has all the customers details like name, address.etc and a drop down box called Tariff. In the drop down box is tariff 1,2 and 3 then i have 3 tables called tariff 1,2 and 3 each 3 tables has the same data in it apart from the prices i.e Tariff 1 [Product ID] [Product Name] [3p] Tariff 2 [Product ID] [Product Name] [5p] Tariff 3 [Product ID] [Product Name] [7p] ALL THE DATA IS THE SAME IN EACH APPART FROM PRICE i then have a table called CDRecords - Live in this table is all transactions that are sent to be from an external source and these would look something like this: 24/10/05 11:24 [Account Number] [PRODUCT ID] so what i want to happen is on the customer details form for there to be a balance field which drags its data through a query from the CDRecords - Live and the [account number] field. But applies the ammount based on what tariff is selected. I hope that is easier to understand "Stevene James" wrote: thanks for your suggestion however i am still unsure of what you mean apologies for appearing thick. "Stevene James" wrote: thanks you sir, but i am unsure of how to do this. i apologise for double posting "Vincent Johns" wrote: Stevene James wrote: Ok people i appreciate this might me a bit long winded but ANY help would be appreciated. i have a table called "Customer_Details" and i have 3 other tables calls "tariff 1", "tariff 2" and "tariff 3". I haven't analyzed your structure extensively, but this looks suspicious. Are [tariff 1], [tariff 2], and [tariff 3] so different from each other that you can't combine their data into one Table? You'd probably want to include a [TariffType] field with value of 1, 2, or 3 to distinguish, but maybe the included fields are similar enough to combine the records. The Customer_Details table has a form with the same name and on that form there is a field called "tariff" which is a drop down box with tariff 1,2 and 3 in it. Also on the Customer_Details form is a Balance field which is linked to the same on the table. finally there is a table called "data_live" this is continually added to on a day to day basis, the fields are : Acc No, Cost, Prod ID. on the tariff tables each product has a product id Basically want i want to do is on the customer form select a tariff, then when the data is entered into data_live the correct tariff is apllied to the product (via product id) then i think i am write in saying that i will be able to create a query that sums up all the prices for that customer (via Acc No) and displays the result in the Balance field. any help at all would be great many thanks I'm not sure exactly what difficulty you're having, but the tariff to be applied seems to be a function of both [product id] and the equivalent of the [TariffType] field that I proposed. That should be easy to do in a Query. -- Vincent Johns Please feel free to quote anything I say here. |
|
Thread Tools | |
Display Modes | |
|
|