HELP

[Stevene James]

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

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Stevene James]

thanks you sir, but i am unsure of how to do this.

i apologise for double posting

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Stevene James]

the tariff contain the same products but at different prices

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Stevene James]

thanks for your suggestion however i am still unsure of what you mean
apologies for appearing thick.

thanks you sir, but i am unsure of how to do this.

i apologise for double posting

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Stevene James]

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

thanks for your suggestion however i am still unsure of what you mean
apologies for appearing thick.

thanks you sir, but i am unsure of how to do this.

i apologise for double posting

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Stevene James]

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.

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

thanks for your suggestion however i am still unsure of what you mean
apologies for appearing thick.

thanks you sir, but i am unsure of how to do this.

i apologise for double posting

:

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

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]

OK, in this case I recommend that you set up TWO Tables for your tariff
rates, one like this, similar to your [Tariff 3] Table:

[Tariff] Table:
[TariffID] (primary key)
[UKN]
[FM9]
[Product ID]
[Product Name]

where [TariffID] is a random and unique number used only to identify a
record in this Table (which I call a "primary key" to the Table).

It wasn't obvious if you mean "UKN" to be the name of a field or to be a
value that might appear in the field, so I assume it's a name. Same
with "FM9". If these are sample values, you should probably choose
names that briefly describe what they mean.

[Product ID] takes its value, I assume, from the primary key field of
the record in a separate [Product] Table to which this record applies.
(If this is not true, I would prefer not to include "ID" at the end, as
I use fields with names like that for linking Tables together.)

For the second Table, I would include these fields:

[TariffRate] Table:

[TariffType] (value = 1, 2, or 3)
[Np] (value comes from one of 3p, 5p, or 7p)
[TariffID] (foreign key, value matches record in [Tariff])

You haven't told me what kinds of values I might expect in your [5p]
field; it could be a percentage, or an amount of money. Without
examples, it's hard to give more specific advice. But I assume all 3 of
these have similar contents, just different values, and there's no need
to put them into separate fields. One field, which I called "Np" here,
though you can probably think of a much better name, takes care of all
such values.

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.

So, how can I determine if the customer is on tariff 2? Does it change
from one shipment to the next? Is a customer who's on tariff 2 always
(or for an extended time) on tariff 2? If the customer stays on one
tariff schedule for a long time, I suggest storing that value into the
[Customer] Table, along with the customer's name and address.
(Addresses can change, too.) If you expect this to change from time to
time, I think you'd need a separate Table, in which each record
identifies a customer, a tariff schedule, and the date at which the
schedule became effective.

Incidentally, in the above example,

30/10/05,11:00.59,[AccountNumber],FM9

it appears that [AccountNumber] is a field name, not a sample value.
But "30/10/05" looks like a sample (date) value, not a field name. I
don't know what "11:00.59" and "FM9" are supposed to represent. (If
"11:00.59" means the usual "11 minutes and 59/100 second", it's unclear
what that would have to do with tariff charges.)

What would make things clearer is if you can list both field names (and
descriptions) for a Table, and also display one or two example records
from that Table, in case I don't understand your descriptions.

Also, if you could describe (one example) exactly what calculations go
into determining a tariff charge, that would help.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Larry Linson]

Generally, you will get more readers who are able to help you if you
summarize your question in the subject line, instead of just appealing for
"HELP". After all, most everyone comes here to get or give "help". But some
of the most capable participants are also the busiest and may not have time
to open and read every post, so they depend on the subject to let them know
if it is an area they can address, or are interested in addressing.

For other good suggestions on effective use of newsgroups, see the FAQ at
http://www.mvps.org/access/netiquette.htm.

Larry Linson
Microsoft Access MVP

 

[Stevene James]

sorry for not replying sooner UKN AND FM9 are variables that could be in the
fields

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]

OK, in this case I recommend that you set up TWO Tables for your tariff
rates, one like this, similar to your [Tariff 3] Table:

[Tariff] Table:
[TariffID] (primary key)
[UKN]
[FM9]
[Product ID]
[Product Name]

where [TariffID] is a random and unique number used only to identify a
record in this Table (which I call a "primary key" to the Table).

It wasn't obvious if you mean "UKN" to be the name of a field or to be a
value that might appear in the field, so I assume it's a name. Same
with "FM9". If these are sample values, you should probably choose
names that briefly describe what they mean.

[Product ID] takes its value, I assume, from the primary key field of
the record in a separate [Product] Table to which this record applies.
(If this is not true, I would prefer not to include "ID" at the end, as
I use fields with names like that for linking Tables together.)

For the second Table, I would include these fields:

[TariffRate] Table:

[TariffType] (value = 1, 2, or 3)
[Np] (value comes from one of 3p, 5p, or 7p)
[TariffID] (foreign key, value matches record in [Tariff])

You haven't told me what kinds of values I might expect in your [5p]
field; it could be a percentage, or an amount of money. Without
examples, it's hard to give more specific advice. But I assume all 3 of
these have similar contents, just different values, and there's no need
to put them into separate fields. One field, which I called "Np" here,
though you can probably think of a much better name, takes care of all
such values.

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.

So, how can I determine if the customer is on tariff 2? Does it change
from one shipment to the next? Is a customer who's on tariff 2 always
(or for an extended time) on tariff 2? If the customer stays on one
tariff schedule for a long time, I suggest storing that value into the
[Customer] Table, along with the customer's name and address.
(Addresses can change, too.) If you expect this to change from time to
time, I think you'd need a separate Table, in which each record
identifies a customer, a tariff schedule, and the date at which the
schedule became effective.

Incidentally, in the above example,

30/10/05,11:00.59,[AccountNumber],FM9

it appears that [AccountNumber] is a field name, not a sample value.
But "30/10/05" looks like a sample (date) value, not a field name. I
don't know what "11:00.59" and "FM9" are supposed to represent. (If
"11:00.59" means the usual "11 minutes and 59/100 second", it's unclear
what that would have to do with tariff charges.)

What would make things clearer is if you can list both field names (and
descriptions) for a Table, and also display one or two example records
from that Table, in case I don't understand your descriptions.

Also, if you could describe (one example) exactly what calculations go
into determining a tariff charge, that would help.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

sorry for not replying sooner UKN AND FM9 are
variables that could be in the fields

OK, if they are variable names (and not values), they are good choices
for field names. You probably should keep a list of field names with
more complete descriptions; I usually keep such information in the
Description property for each field in Table Design View. (It also
appears in the status line when a field is selected in Table Datasheet
View.)

It's still not clear what kinds of values are in [5p], etc., and how you
intend to use them.

Since your original question dealt with how to compute the total tariff
for a customer, it will help me to know more details of the computation
(including the sample data that I mentioned in a previous message).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

:

Stevene James wrote:

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]

OK, in this case I recommend that you set up TWO Tables for your tariff
rates, one like this, similar to your [Tariff 3] Table:

[Tariff] Table:
[TariffID] (primary key)
[UKN]
[FM9]
[Product ID]
[Product Name]

where [TariffID] is a random and unique number used only to identify a
record in this Table (which I call a "primary key" to the Table).

It wasn't obvious if you mean "UKN" to be the name of a field or to be a
value that might appear in the field, so I assume it's a name. Same
with "FM9". If these are sample values, you should probably choose
names that briefly describe what they mean.

[Product ID] takes its value, I assume, from the primary key field of
the record in a separate [Product] Table to which this record applies.
(If this is not true, I would prefer not to include "ID" at the end, as
I use fields with names like that for linking Tables together.)

For the second Table, I would include these fields:

[TariffRate] Table:

[TariffType] (value = 1, 2, or 3)
[Np] (value comes from one of 3p, 5p, or 7p)
[TariffID] (foreign key, value matches record in [Tariff])

You haven't told me what kinds of values I might expect in your [5p]
field; it could be a percentage, or an amount of money. Without
examples, it's hard to give more specific advice. But I assume all 3 of
these have similar contents, just different values, and there's no need
to put them into separate fields. One field, which I called "Np" here,
though you can probably think of a much better name, takes care of all
such values.

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.

So, how can I determine if the customer is on tariff 2? Does it change
from one shipment to the next? Is a customer who's on tariff 2 always
(or for an extended time) on tariff 2? If the customer stays on one
tariff schedule for a long time, I suggest storing that value into the
[Customer] Table, along with the customer's name and address.
(Addresses can change, too.) If you expect this to change from time to
time, I think you'd need a separate Table, in which each record
identifies a customer, a tariff schedule, and the date at which the
schedule became effective.

Incidentally, in the above example,

30/10/05,11:00.59,[AccountNumber],FM9

it appears that [AccountNumber] is a field name, not a sample value.
But "30/10/05" looks like a sample (date) value, not a field name. I
don't know what "11:00.59" and "FM9" are supposed to represent. (If
"11:00.59" means the usual "11 minutes and 59/100 second", it's unclear
what that would have to do with tariff charges.)

What would make things clearer is if you can list both field names (and
descriptions) for a Table, and also display one or two example records
from that Table, in case I don't understand your descriptions.

Also, if you could describe (one example) exactly what calculations go
into determining a tariff charge, that would help.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Stevene James]

apologies for the confusion UKN and FM9 are values that could appear in the
fields.

[5p] is also a value [field name being price] so in that field there will be
values to 4 decimal places i.e 0.1843

as this is quite complecated if you would like me to discuss it over the
telephone please feel free to email me at stephen dot james at icomtel dot com

i am so very very greatful for your time so far thank you.

sorry for not replying sooner UKN AND FM9 are variables that could be in the
fields

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]

OK, in this case I recommend that you set up TWO Tables for your tariff
rates, one like this, similar to your [Tariff 3] Table:

[Tariff] Table:
[TariffID] (primary key)
[UKN]
[FM9]
[Product ID]
[Product Name]

where [TariffID] is a random and unique number used only to identify a
record in this Table (which I call a "primary key" to the Table).

It wasn't obvious if you mean "UKN" to be the name of a field or to be a
value that might appear in the field, so I assume it's a name. Same
with "FM9". If these are sample values, you should probably choose
names that briefly describe what they mean.

[Product ID] takes its value, I assume, from the primary key field of
the record in a separate [Product] Table to which this record applies.
(If this is not true, I would prefer not to include "ID" at the end, as
I use fields with names like that for linking Tables together.)

For the second Table, I would include these fields:

[TariffRate] Table:

[TariffType] (value = 1, 2, or 3)
[Np] (value comes from one of 3p, 5p, or 7p)
[TariffID] (foreign key, value matches record in [Tariff])

You haven't told me what kinds of values I might expect in your [5p]
field; it could be a percentage, or an amount of money. Without
examples, it's hard to give more specific advice. But I assume all 3 of
these have similar contents, just different values, and there's no need
to put them into separate fields. One field, which I called "Np" here,
though you can probably think of a much better name, takes care of all
such values.

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.

So, how can I determine if the customer is on tariff 2? Does it change
from one shipment to the next? Is a customer who's on tariff 2 always
(or for an extended time) on tariff 2? If the customer stays on one
tariff schedule for a long time, I suggest storing that value into the
[Customer] Table, along with the customer's name and address.
(Addresses can change, too.) If you expect this to change from time to
time, I think you'd need a separate Table, in which each record
identifies a customer, a tariff schedule, and the date at which the
schedule became effective.

Incidentally, in the above example,

30/10/05,11:00.59,[AccountNumber],FM9

it appears that [AccountNumber] is a field name, not a sample value.
But "30/10/05" looks like a sample (date) value, not a field name. I
don't know what "11:00.59" and "FM9" are supposed to represent. (If
"11:00.59" means the usual "11 minutes and 59/100 second", it's unclear
what that would have to do with tariff charges.)

What would make things clearer is if you can list both field names (and
descriptions) for a Table, and also display one or two example records
from that Table, in case I don't understand your descriptions.

Also, if you could describe (one example) exactly what calculations go
into determining a tariff charge, that would help.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

apologies for the confusion UKN and FM9 are values that could appear in the
fields.

OK, then your field names should briefly suggest what KINDS of values
these are, rather than to serve as examples.

[5p] is also a value [field name being price] so in that field there will be
values to 4 decimal places i.e 0.1843

So it's a number. Of what? Euros? Is it a percentage? How is it to
be used? I assume the end result is some amount of money to be paid to
a taxing authority, but the process of calculating that is still
unclear. I suggest that you describe a representative example of the
calculation, utilizing the field names you've mentioned as appropriate
in your description. If table lookups (from a paper chart) are needed,
please include an example of the names of the fields that those tables
include, as well; you may have to duplicate them in your database if you
want it to perform the calculations accurately.

as this is quite complecated if you would like me to discuss it over the
telephone please feel free to email me at stephen dot james at icomtel dot com

i am so very very greatful for your time so far thank you.

I prefer Usenet to email (or telephone) for a couple of reasons, though
I understand that writing all this stuff out can be tedious for you.
(But you'll probably have to write down a lot of this, anyway, in
constructing your database!)

- I may give you incomplete (or even erroneous, though I hope not)
advice, and other people on the NG will be able to add comments
- Messages on the NG form a record of what was discussed, helping to
reduce confusion
- It's cheaper than telephoning, and asynchronous (we don't both have
to be awake at the same time)
- I may not get back to you right away, and maybe someone else will
be able to answer before I do

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Stevene James]

no the end projet is going to be a telephone billing engine so i receive
customer call data and need to calculate it accordingly.

ok so lets say this is the customer screen.

[NAME] [SURNAME] [PHONE NUMBER] [TARIFF] [Balance]
JOHN DOE 01234 567890 BUSINESS £0.00

then you have the tariff (in this instance business)

[COUNTRY] [CITY] [DECRIPTION] [PENCE PER MINUTE]
UK FM9 UK NATIONAL 0.0148


then you have the CDRecord that i receive that goes into one big table
called the same.

[COUNTRY] [CITY] [ORIGINATING TELE NO] [DATE] [TIME] [DURATION] [DESTINA'N]
UK FM9 01234 567890 271005 0928 34
09876 541321


obviously there are going to be many more recrods in each table but these
where just to give an example. ok so what i need to happen is for the
[BALANCE] field to calculate the balance buy multipling the [DURATION] by the
[PENCE PER MINUTE] . This information can be reached by matching the [CITY]
code on both records, that is how we determin how much to charge for the
call. If that isnt complecated enough we also have to bear in mind that if
the [TARIFF] record on customer record said Corporate then i would need it to
look up the values from that said tariff, however the layout and the methods
would be the same, just the price would be differenent.

Hope this helps

apologies for the confusion UKN and FM9 are values that could appear in the
fields.

[5p] is also a value [field name being price] so in that field there will be
values to 4 decimal places i.e 0.1843

as this is quite complecated if you would like me to discuss it over the
telephone please feel free to email me at stephen dot james at icomtel dot com

i am so very very greatful for your time so far thank you.

sorry for not replying sooner UKN AND FM9 are variables that could be in the
fields

Stevene James wrote:

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]

OK, in this case I recommend that you set up TWO Tables for your tariff
rates, one like this, similar to your [Tariff 3] Table:

[Tariff] Table:
[TariffID] (primary key)
[UKN]
[FM9]
[Product ID]
[Product Name]

where [TariffID] is a random and unique number used only to identify a
record in this Table (which I call a "primary key" to the Table).

It wasn't obvious if you mean "UKN" to be the name of a field or to be a
value that might appear in the field, so I assume it's a name. Same
with "FM9". If these are sample values, you should probably choose
names that briefly describe what they mean.

[Product ID] takes its value, I assume, from the primary key field of
the record in a separate [Product] Table to which this record applies.
(If this is not true, I would prefer not to include "ID" at the end, as
I use fields with names like that for linking Tables together.)

For the second Table, I would include these fields:

[TariffRate] Table:

[TariffType] (value = 1, 2, or 3)
[Np] (value comes from one of 3p, 5p, or 7p)
[TariffID] (foreign key, value matches record in [Tariff])

You haven't told me what kinds of values I might expect in your [5p]
field; it could be a percentage, or an amount of money. Without
examples, it's hard to give more specific advice. But I assume all 3 of
these have similar contents, just different values, and there's no need
to put them into separate fields. One field, which I called "Np" here,
though you can probably think of a much better name, takes care of all
such values.


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.

So, how can I determine if the customer is on tariff 2? Does it change
from one shipment to the next? Is a customer who's on tariff 2 always
(or for an extended time) on tariff 2? If the customer stays on one
tariff schedule for a long time, I suggest storing that value into the
[Customer] Table, along with the customer's name and address.
(Addresses can change, too.) If you expect this to change from time to
time, I think you'd need a separate Table, in which each record
identifies a customer, a tariff schedule, and the date at which the
schedule became effective.

Incidentally, in the above example,

30/10/05,11:00.59,[AccountNumber],FM9

it appears that [AccountNumber] is a field name, not a sample value.
But "30/10/05" looks like a sample (date) value, not a field name. I
don't know what "11:00.59" and "FM9" are supposed to represent. (If
"11:00.59" means the usual "11 minutes and 59/100 second", it's unclear
what that would have to do with tariff charges.)

What would make things clearer is if you can list both field names (and
descriptions) for a Table, and also display one or two example records
from that Table, in case I don't understand your descriptions.

Also, if you could describe (one example) exactly what calculations go
into determining a tariff charge, that would help.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

no the end projet is going to be a telephone billing engine so i receive
customer call data and need to calculate it accordingly.

ok so lets say this is the customer screen.

[NAME] [SURNAME] [PHONE NUMBER] [TARIFF] [Balance]
JOHN DOE 01234 567890 BUSINESS £0.00

then you have the tariff (in this instance business)

[COUNTRY] [CITY] [DECRIPTION] [PENCE PER MINUTE]
UK FM9 UK NATIONAL 0.0148


then you have the CDRecord that i receive that goes into one big table
called the same.

[COUNTRY] [CITY] [ORIG. TELE NO] [DATE] [TIME] [DURATION] [DESTINA'N]
UK FM9 01234 567890 271005 0928 34 09876 541321


obviously there are going to be many more recrods in each table but these
where just to give an example. ok so what i need to happen is for the
[BALANCE] field to calculate the balance buy multipling the [DURATION] by the
[PENCE PER MINUTE] . This information can be reached by matching the [CITY]
code on both records, that is how we determin how much to charge for the
call. If that isnt complecated enough we also have to bear in mind that if
the [TARIFF] record on customer record said Corporate then i would need it to
look up the values from that said tariff, however the layout and the methods
would be the same, just the price would be differenent.

Hope this helps

Yes, it does. It looks as if you just multiply [PENCE PER MINUTE] by
[DURATION], where [PENCE PER MINUTE] is a function of [TARIFF]. From
what you say, there may be only 3 values of [TARIFF]. Or there may be
other factors involved -- you said you "need it to look up the values
from that said tariff". Does that mean that more than one value could
apply, and if so, how?

If there are only 3 [TARIFF] values, then all you need there is a Table
with 3 records, each of which specifies a [TARIFF] field (text data
type, = "BUSINESS", etc.) and a [PENCE PER MINUTE] or [PRICE] field
(currency data type, = 0.0148 or similar value). Does this seem accurate?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

:

apologies for the confusion UKN and FM9 are values that could appear in the
fields.

[5p] is also a value [field name being price] so in that field there will be
values to 4 decimal places i.e 0.1843

as this is quite complecated if you would like me to discuss it over the
telephone please feel free to email me at stephen dot james at icomtel dot com

i am so very very greatful for your time so far thank you.

:

sorry for not replying sooner UKN AND FM9 are variables that could be in the
fields

:


Stevene James wrote:


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]

OK, in this case I recommend that you set up TWO Tables for your tariff
rates, one like this, similar to your [Tariff 3] Table:

[Tariff] Table:
[TariffID] (primary key)
[UKN]
[FM9]
[Product ID]
[Product Name]

where [TariffID] is a random and unique number used only to identify a
record in this Table (which I call a "primary key" to the Table).

It wasn't obvious if you mean "UKN" to be the name of a field or to be a
value that might appear in the field, so I assume it's a name. Same
with "FM9". If these are sample values, you should probably choose
names that briefly describe what they mean.

[Product ID] takes its value, I assume, from the primary key field of
the record in a separate [Product] Table to which this record applies.
(If this is not true, I would prefer not to include "ID" at the end, as
I use fields with names like that for linking Tables together.)

For the second Table, I would include these fields:

[TariffRate] Table:

[TariffType] (value = 1, 2, or 3)
[Np] (value comes from one of 3p, 5p, or 7p)
[TariffID] (foreign key, value matches record in [Tariff])

You haven't told me what kinds of values I might expect in your [5p]
field; it could be a percentage, or an amount of money. Without
examples, it's hard to give more specific advice. But I assume all 3 of
these have similar contents, just different values, and there's no need
to put them into separate fields. One field, which I called "Np" here,
though you can probably think of a much better name, takes care of all
such values.



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.

So, how can I determine if the customer is on tariff 2? Does it change
from one shipment to the next? Is a customer who's on tariff 2 always
(or for an extended time) on tariff 2? If the customer stays on one
tariff schedule for a long time, I suggest storing that value into the
[Customer] Table, along with the customer's name and address.
(Addresses can change, too.) If you expect this to change from time to
time, I think you'd need a separate Table, in which each record
identifies a customer, a tariff schedule, and the date at which the
schedule became effective.

Incidentally, in the above example,

30/10/05,11:00.59,[AccountNumber],FM9

it appears that [AccountNumber] is a field name, not a sample value.
But "30/10/05" looks like a sample (date) value, not a field name. I
don't know what "11:00.59" and "FM9" are supposed to represent. (If
"11:00.59" means the usual "11 minutes and 59/100 second", it's unclear
what that would have to do with tariff charges.)

What would make things clearer is if you can list both field names (and
descriptions) for a Table, and also display one or two example records
from that Table, in case I don't understand your descriptions.

Also, if you could describe (one example) exactly what calculations go
into determining a tariff charge, that would help.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Stevene James]

ok that seems straight forward, how would i implement that calculation though.

so the tariffs cant be in 3 seperate tables , the table would have to look
like this:

COUNTRY] [CITY] [DECRIPTION] [PPM BUSINES] [PPM CORP] [PPM CORP PLUS]

UK FM9 UK NATIONAL 0.0178 0.0163 0.0148

is that right?

so how do i get it to use the figures from the right field for the right
tariff

no the end projet is going to be a telephone billing engine so i receive
customer call data and need to calculate it accordingly.

ok so lets say this is the customer screen.

[NAME] [SURNAME] [PHONE NUMBER] [TARIFF] [Balance]
JOHN DOE 01234 567890 BUSINESS £0.00

then you have the tariff (in this instance business)

[COUNTRY] [CITY] [DECRIPTION] [PENCE PER MINUTE]
UK FM9 UK NATIONAL 0.0148


then you have the CDRecord that i receive that goes into one big table
called the same.

[COUNTRY] [CITY] [ORIGINATING TELE NO] [DATE] [TIME] [DURATION] [DESTINA'N]
UK FM9 01234 567890 271005 0928 34
09876 541321


obviously there are going to be many more recrods in each table but these
where just to give an example. ok so what i need to happen is for the
[BALANCE] field to calculate the balance buy multipling the [DURATION] by the
[PENCE PER MINUTE] . This information can be reached by matching the [CITY]
code on both records, that is how we determin how much to charge for the
call. If that isnt complecated enough we also have to bear in mind that if
the [TARIFF] record on customer record said Corporate then i would need it to
look up the values from that said tariff, however the layout and the methods
would be the same, just the price would be differenent.

Hope this helps

apologies for the confusion UKN and FM9 are values that could appear in the
fields.

[5p] is also a value [field name being price] so in that field there will be
values to 4 decimal places i.e 0.1843

as this is quite complecated if you would like me to discuss it over the
telephone please feel free to email me at stephen dot james at icomtel dot com

i am so very very greatful for your time so far thank you.

sorry for not replying sooner UKN AND FM9 are variables that could be in the
fields

:

Stevene James wrote:

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]

OK, in this case I recommend that you set up TWO Tables for your tariff
rates, one like this, similar to your [Tariff 3] Table:

[Tariff] Table:
[TariffID] (primary key)
[UKN]
[FM9]
[Product ID]
[Product Name]

where [TariffID] is a random and unique number used only to identify a
record in this Table (which I call a "primary key" to the Table).

It wasn't obvious if you mean "UKN" to be the name of a field or to be a
value that might appear in the field, so I assume it's a name. Same
with "FM9". If these are sample values, you should probably choose
names that briefly describe what they mean.

[Product ID] takes its value, I assume, from the primary key field of
the record in a separate [Product] Table to which this record applies.
(If this is not true, I would prefer not to include "ID" at the end, as
I use fields with names like that for linking Tables together.)

For the second Table, I would include these fields:

[TariffRate] Table:

[TariffType] (value = 1, 2, or 3)
[Np] (value comes from one of 3p, 5p, or 7p)
[TariffID] (foreign key, value matches record in [Tariff])

You haven't told me what kinds of values I might expect in your [5p]
field; it could be a percentage, or an amount of money. Without
examples, it's hard to give more specific advice. But I assume all 3 of
these have similar contents, just different values, and there's no need
to put them into separate fields. One field, which I called "Np" here,
though you can probably think of a much better name, takes care of all
such values.


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.

So, how can I determine if the customer is on tariff 2? Does it change
from one shipment to the next? Is a customer who's on tariff 2 always
(or for an extended time) on tariff 2? If the customer stays on one
tariff schedule for a long time, I suggest storing that value into the
[Customer] Table, along with the customer's name and address.
(Addresses can change, too.) If you expect this to change from time to
time, I think you'd need a separate Table, in which each record
identifies a customer, a tariff schedule, and the date at which the
schedule became effective.

Incidentally, in the above example,

30/10/05,11:00.59,[AccountNumber],FM9

it appears that [AccountNumber] is a field name, not a sample value.
But "30/10/05" looks like a sample (date) value, not a field name. I
don't know what "11:00.59" and "FM9" are supposed to represent. (If
"11:00.59" means the usual "11 minutes and 59/100 second", it's unclear
what that would have to do with tariff charges.)

What would make things clearer is if you can list both field names (and
descriptions) for a Table, and also display one or two example records
from that Table, in case I don't understand your descriptions.

Also, if you could describe (one example) exactly what calculations go
into determining a tariff charge, that would help.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

ok that seems straight forward, how would i implement that calculation though.

so the tariffs cant be in 3 seperate tables , the table would have to look
like this:

COUNTRY] [CITY] [DECRIPTION] [PPM BUSINES] [PPM CORP] [PPM CORP PLUS]

UK FM9 UK NATIONAL 0.0178 0.0163 0.0148

is that right?

You could do it that way, but I'd prefer omitting the last 3 fields and
defining an additional Table, maybe like this:

[Rates] Table:
[CityID] <-- link (foreign key) to the above Table ([City])
[Type] <-- value of "BUSINESS" or "CORP" or "CORP PLUS"
[PPM] <-- currency value, such as 0.0148

I assume that the [PPM] values will depend on the city. If not, omit
the [CityID] field.

The above Table would then look like this:

[City] Table:
[CityID] <-- Primary key, identifying the record
[COUNTRY] <-- such as "UK"
[CITY] <-- such as "FM9"
[DECRIPTION] <-- such as "UK NATIONAL"

so how do i get it to use the figures from the right field for the right
tariff

Find the record in [Rates] that matches the call you're calculating;
i.e., the [CityID] matches and the [Type] matches. Use the value of
[PPM] from that (unique) record.

You can link Tables via the Relationships window, or you can do it in
Query Design View (or both, in which case the Query supersedes).


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.