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

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.

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.

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.

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.

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.

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.

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.

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.

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.