simple query results, yet a difficult way to get to them...

Hi all,
I'm using MS Access 2003.
Bare with me on this description....here's the situation: Imagine insurance,
and working out premiums for different insured properties. The rates for
calculating premiums are dependant on the country in which the client is in.
Therefore, we have a Country table, with its list of rates, a client table
and then the property table. Getting this is great, works fine, easy!
Problem is, now I need to work out a way for special cases - a client within
a country might have their own rate instead of using the "global" rate for
the whole country. And what's worse, a client might have several groups of
rates. So I need a system that will allow the user to be able to choose
which set of rates to use to calculate the premiums for that client. I
created another table called ClientRates, and that's linked to the Client
table, in a many-to-one relationship.
Creating a query that takes rates from one table and multiplies with other
values, and then gives you the premiums is easy, but is there a way to allow
the user to choose which set of rates to use for that specific instance?
Some kind of drop-down list before doing the calculation. I can't imagine
that's possible...but is there any other way?

Here's an image of the tables and their relationships.
http://img106.exs.cx/my.php?loc=img1...ationships.jpg

Thanks a lot for all the help. I hope I made enough sense.
Dejan

re: simple query results, yet a difficult way to get to them...

There are a lot of ways to accomplish this. I will describe one method,
which may of course not be the optimal one for you; and others may disagree
or come with alternatives.

First, I would like to make a note about the relationships you have defined
between the client, client rates and property tables. You base these
relationships on the ClientName field. That is not a good thing to do,
because you may have two clients with exactly the same name. It is therefor
better to add a ClientID field to the tbl_Client table that has the
AutoNumber type. And in the tbl_ClientRates and tbl_Property you add a
corresponding field, e.g. ClientID or ClientRatesClientID and
PropertyClientID. (The names only matter for your own reference, as long
they are Number (Long Integer)).

Then your actual question. You may add two fields to the tbl_Client table:
ClientSpecialRate (Yes/No) and ClientSpecialRateID (Number, Long Integer).
On your form you add a checkbox, e.g. chkClientSpecialRate, and a combo box,
e.g. cboClientSpecialRate. I suppose you will have the checkbox unselected
by default and the combo box disabled (greyed out) by default.

The combo box' control source is your new field ClientSpecialRateID and the
row source is something like 'Select ClientRatesID, ClientRateName From
tbl_ClientRates Where ClientRatesClientID = ClientID'. When you select the
checkbox you can have the combo box enabled.

Then in your premium calculation query you have your formulas looks at the
ClientSpecialRate Yes/No field. If Yes, then use the data from the
corresponding row in tbl_ClientRates. If No, use the country info.

I hope this helps.

HJ

"d.p." <no@spam.never> wrote in message
news:41aa4454$0$1064$db0fefd9@news.zen.co.uk...[color=blue]
> Hi all,
> I'm using MS Access 2003.
> Bare with me on this description....here's the situation: Imagine[/color]
insurance,[color=blue]
> and working out premiums for different insured properties. The rates for
> calculating premiums are dependant on the country in which the client is[/color]
in.[color=blue]
> Therefore, we have a Country table, with its list of rates, a client table
> and then the property table. Getting this is great, works fine, easy!
> Problem is, now I need to work out a way for special cases - a client[/color]
within[color=blue]
> a country might have their own rate instead of using the "global" rate for
> the whole country. And what's worse, a client might have several groups of
> rates. So I need a system that will allow the user to be able to choose
> which set of rates to use to calculate the premiums for that client. I
> created another table called ClientRates, and that's linked to the Client
> table, in a many-to-one relationship.
> Creating a query that takes rates from one table and multiplies with other
> values, and then gives you the premiums is easy, but is there a way to[/color]
allow[color=blue]
> the user to choose which set of rates to use for that specific instance?
> Some kind of drop-down list before doing the calculation. I can't imagine
> that's possible...but is there any other way?
>
> Here's an image of the tables and their relationships.
> http://img106.exs.cx/my.php?loc=img1...ationships.jpg
>
> Thanks a lot for all the help. I hope I made enough sense.
> Dejan
>
>[/color]

re: simple query results, yet a difficult way to get to them...

thanks a lot HJ!
this sounds very possible. I haven't had a chance to mess with it the past 2
days, but I'll definintely give your suggestion a go. I'm not that skillful
at Access, but learning slowly. I hope I can figure this out. I'll give a
shout if i run into things :-)

Oh, about the primary key for the client...I made it ClientName because I
know definitely that it'll be different for every client (it's just the way
this company names it), although I'll consider just giving it an ID field,
just for consistency at least.

You also said this at the end:[color=blue]
> Then in your premium calculation query you have your formulas looks at the
> ClientSpecialRate Yes/No field. If Yes, then use the data from the
> corresponding row in tbl_ClientRates. If No, use the country info.[/color]

Sorry to be a bit less knowledgable now, but how would an SQL statement look
at a check box and then determine what to use? Here's the SQL statement that
was generated by Access, so you can see the current query. If you have a
diff suggestion, please feel free to say:

SELECT
tbl_Property.ClientName,
tbl_Property.PropertyName,
tbl_Country.CountryCurrency,
[tbl_Property.PropertyBuildValue]*[tbl_CountryInfo.CountryBuildRate] AS
PremiumBuildValue,
[tbl_Property.PropertyContValue]*[tbl_CountryInfo.CountryContRate] AS
PremiumContValue,
[tbl_Property.PropertyStockValue]*[tbl_CountryInfo.CountryStockRate] AS
PremiumStockValue,
[tbl_Property.PropertyElecEqValue]*[tbl_CountryInfo.CountryElecEqRate] AS
PremiumEleqEqValue,
[tbl_Property.PropertyEQCover]*[tbl_CountryInfo.CountryEQRate] AS
PremiumEQCover,
[tbl_Property.PropertySGValue]*[tbl_CountryInfo.CountrySGRate] AS
PremiumSGValue,
[tbl_Property.PropertyBICover]*[tbl_CountryInfo.CountryBIRate] AS
PremiumBICover,
[tbl_Property.PropertyRoDCover]*[tbl_CountryInfo.CountryRoDRate] AS
PremiumRoDCover,
tbl_Property.PropertyNotes

FROM
tbl_Country INNER JOIN ((tbl_Client INNER JOIN tbl_CountryInfo ON
tbl_Client.CountryCode = tbl_CountryInfo.CountryCode) INNER JOIN
tbl_Property ON tbl_Client.ClientName = tbl_Property.ClientName) ON
(tbl_Country.CountryCode = tbl_CountryInfo.CountryCode) AND
(tbl_Country.CountryCode = tbl_Client.CountryCode);

Thanks so much for the help! I really appretiate it!

Dejan



"HJ" <hjiscoolno@spamhotmail.com> wrote in message
news:41aaf0f2$1$42417$e4fe514c@news.xs4all.nl...[color=blue]
> There are a lot of ways to accomplish this. I will describe one method,
> which may of course not be the optimal one for you; and others may[/color]
disagree[color=blue]
> or come with alternatives.
>
> First, I would like to make a note about the relationships you have[/color]
defined[color=blue]
> between the client, client rates and property tables. You base these
> relationships on the ClientName field. That is not a good thing to do,
> because you may have two clients with exactly the same name. It is[/color]
therefor[color=blue]
> better to add a ClientID field to the tbl_Client table that has the
> AutoNumber type. And in the tbl_ClientRates and tbl_Property you add a
> corresponding field, e.g. ClientID or ClientRatesClientID and
> PropertyClientID. (The names only matter for your own reference, as long
> they are Number (Long Integer)).
>
> Then your actual question. You may add two fields to the tbl_Client table:
> ClientSpecialRate (Yes/No) and ClientSpecialRateID (Number, Long Integer).
> On your form you add a checkbox, e.g. chkClientSpecialRate, and a combo[/color]
box,[color=blue]
> e.g. cboClientSpecialRate. I suppose you will have the checkbox unselected
> by default and the combo box disabled (greyed out) by default.
>
> The combo box' control source is your new field ClientSpecialRateID and[/color]
the[color=blue]
> row source is something like 'Select ClientRatesID, ClientRateName From
> tbl_ClientRates Where ClientRatesClientID = ClientID'. When you select the
> checkbox you can have the combo box enabled.
>
> Then in your premium calculation query you have your formulas looks at the
> ClientSpecialRate Yes/No field. If Yes, then use the data from the
> corresponding row in tbl_ClientRates. If No, use the country info.
>
> I hope this helps.
>
> HJ
>
> "d.p." <no@spam.never> wrote in message
> news:41aa4454$0$1064$db0fefd9@news.zen.co.uk...[color=green]
> > Hi all,
> > I'm using MS Access 2003.
> > Bare with me on this description....here's the situation: Imagine[/color]
> insurance,[color=green]
> > and working out premiums for different insured properties. The rates for
> > calculating premiums are dependant on the country in which the client is[/color]
> in.[color=green]
> > Therefore, we have a Country table, with its list of rates, a client[/color][/color]
table[color=blue][color=green]
> > and then the property table. Getting this is great, works fine, easy!
> > Problem is, now I need to work out a way for special cases - a client[/color]
> within[color=green]
> > a country might have their own rate instead of using the "global" rate[/color][/color]
for[color=blue][color=green]
> > the whole country. And what's worse, a client might have several groups[/color][/color]
of[color=blue][color=green]
> > rates. So I need a system that will allow the user to be able to choose
> > which set of rates to use to calculate the premiums for that client. I
> > created another table called ClientRates, and that's linked to the[/color][/color]
Client[color=blue][color=green]
> > table, in a many-to-one relationship.
> > Creating a query that takes rates from one table and multiplies with[/color][/color]
other[color=blue][color=green]
> > values, and then gives you the premiums is easy, but is there a way to[/color]
> allow[color=green]
> > the user to choose which set of rates to use for that specific instance?
> > Some kind of drop-down list before doing the calculation. I can't[/color][/color]
imagine[color=blue][color=green]
> > that's possible...but is there any other way?
> >
> > Here's an image of the tables and their relationships.
> > http://img106.exs.cx/my.php?loc=img1...ationships.jpg
> >
> > Thanks a lot for all the help. I hope I made enough sense.
> > Dejan
> >
> >[/color]
>
>[/color]

re: simple query results, yet a difficult way to get to them...

Hi DP,

If I primarily focus at your query, and if I do not use any VBA code, you
can have a look at http://www.xs4all.nl/~hjm/images/dp/QueryForDP.jpg (SQL
statement is printed below; image stays online for only a few days).

You see a few adjustments to the tables and one supplement to the query.
That is the Left Join for the tbl_ClientRates table. I have now only
adjusted the calculation for the PremiumBuildValue field; you can do the
rest.

In the example for the PremiumBuildValue you see that if the
ClientSpecialRate Yes/No field has been selected (=Yes), then the special
build rate is being used. If the ClientSpecialRate field has not been
selected, then the general CountryBuildRate is used.

Beware of the Left Join to the ClientRates table. You do need that type of
relationship, because you may not have special rates for every client in
your database.

HJ

To make it easier for you to rebuild the drawn query, here is the SQL
statement:

SELECT tbl_Client.ClientName, tbl_Property.PropertyName,
tbl_Country.CountryCurrency,
[tbl_Property.PropertyBuildValue]*IIf([ClientSpecialRate],[tbl_ClientRates.C
lientBuildRate],[tbl_Country.CountryBuildRate]) AS PremiumBuildValue,
[tbl_Property.PropertyContValue]*[tbl_Country.CountryContRate] AS
PremiumContValue,
[tbl_Property.PropertyStockValue]*[tbl_Country.CountryStockRate] AS
PremiumStockValue,
[tbl_Property.PropertyElecEqValue]*[tbl_Country.CountryElecEqRate] AS
PremiumEleqEqValue,
[tbl_Property.PropertyEQCover]*[tbl_Country.CountryEQRate] AS
PremiumEQCover, [tbl_Property.PropertySGValue]*[tbl_Country.CountrySGRate]
AS PremiumSGValue,
[tbl_Property.PropertyBICover]*[tbl_Country.CountryBIRate] AS
PremiumBICover, [tbl_Property.PropertyRoDCover]*[tbl_Country.CountryRoDRate]
AS PremiumRoDCover, tbl_Property.PropertyNotes
FROM ((tbl_Country INNER JOIN tbl_Client ON tbl_Country.CountryCode =
tbl_Client.CountryCode) INNER JOIN tbl_Property ON tbl_Client.ClientID =
tbl_Property.PropertyClientID) LEFT JOIN tbl_ClientRates ON
tbl_Client.ClientSpecialRateID = tbl_ClientRates.ClientRatesID;

"d.p." <no@spam.never> wrote in message
news:41ace760$0$1073$db0fefd9@news.zen.co.uk...[color=blue]
> thanks a lot HJ!
> this sounds very possible. I haven't had a chance to mess with it the past[/color]
2[color=blue]
> days, but I'll definintely give your suggestion a go. I'm not that[/color]
skillful[color=blue]
> at Access, but learning slowly. I hope I can figure this out. I'll give a
> shout if i run into things :-)
>
> Oh, about the primary key for the client...I made it ClientName because I
> know definitely that it'll be different for every client (it's just the[/color]
way[color=blue]
> this company names it), although I'll consider just giving it an ID field,
> just for consistency at least.
>
> You also said this at the end:[color=green]
> > Then in your premium calculation query you have your formulas looks at[/color][/color]
the[color=blue][color=green]
> > ClientSpecialRate Yes/No field. If Yes, then use the data from the
> > corresponding row in tbl_ClientRates. If No, use the country info.[/color]
>
> Sorry to be a bit less knowledgable now, but how would an SQL statement[/color]
look[color=blue]
> at a check box and then determine what to use? Here's the SQL statement[/color]
that[color=blue]
> was generated by Access, so you can see the current query. If you have a
> diff suggestion, please feel free to say:
>
> SELECT
> tbl_Property.ClientName,
> tbl_Property.PropertyName,
> tbl_Country.CountryCurrency,
> [tbl_Property.PropertyBuildValue]*[tbl_CountryInfo.CountryBuildRate] AS
> PremiumBuildValue,
> [tbl_Property.PropertyContValue]*[tbl_CountryInfo.CountryContRate] AS
> PremiumContValue,
> [tbl_Property.PropertyStockValue]*[tbl_CountryInfo.CountryStockRate] AS
> PremiumStockValue,
> [tbl_Property.PropertyElecEqValue]*[tbl_CountryInfo.CountryElecEqRate] AS
> PremiumEleqEqValue,
> [tbl_Property.PropertyEQCover]*[tbl_CountryInfo.CountryEQRate] AS
> PremiumEQCover,
> [tbl_Property.PropertySGValue]*[tbl_CountryInfo.CountrySGRate] AS
> PremiumSGValue,
> [tbl_Property.PropertyBICover]*[tbl_CountryInfo.CountryBIRate] AS
> PremiumBICover,
> [tbl_Property.PropertyRoDCover]*[tbl_CountryInfo.CountryRoDRate] AS
> PremiumRoDCover,
> tbl_Property.PropertyNotes
>
> FROM
> tbl_Country INNER JOIN ((tbl_Client INNER JOIN tbl_CountryInfo ON
> tbl_Client.CountryCode = tbl_CountryInfo.CountryCode) INNER JOIN
> tbl_Property ON tbl_Client.ClientName = tbl_Property.ClientName) ON
> (tbl_Country.CountryCode = tbl_CountryInfo.CountryCode) AND
> (tbl_Country.CountryCode = tbl_Client.CountryCode);
>
> Thanks so much for the help! I really appretiate it!
>
> Dejan
>
>
>
> "HJ" <hjiscoolno@spamhotmail.com> wrote in message
> news:41aaf0f2$1$42417$e4fe514c@news.xs4all.nl...[color=green]
> > There are a lot of ways to accomplish this. I will describe one method,
> > which may of course not be the optimal one for you; and others may[/color]
> disagree[color=green]
> > or come with alternatives.
> >
> > First, I would like to make a note about the relationships you have[/color]
> defined[color=green]
> > between the client, client rates and property tables. You base these
> > relationships on the ClientName field. That is not a good thing to do,
> > because you may have two clients with exactly the same name. It is[/color]
> therefor[color=green]
> > better to add a ClientID field to the tbl_Client table that has the
> > AutoNumber type. And in the tbl_ClientRates and tbl_Property you add a
> > corresponding field, e.g. ClientID or ClientRatesClientID and
> > PropertyClientID. (The names only matter for your own reference, as long
> > they are Number (Long Integer)).
> >
> > Then your actual question. You may add two fields to the tbl_Client[/color][/color]
table:[color=blue][color=green]
> > ClientSpecialRate (Yes/No) and ClientSpecialRateID (Number, Long[/color][/color]
Integer).[color=blue][color=green]
> > On your form you add a checkbox, e.g. chkClientSpecialRate, and a combo[/color]
> box,[color=green]
> > e.g. cboClientSpecialRate. I suppose you will have the checkbox[/color][/color]
unselected[color=blue][color=green]
> > by default and the combo box disabled (greyed out) by default.
> >
> > The combo box' control source is your new field ClientSpecialRateID and[/color]
> the[color=green]
> > row source is something like 'Select ClientRatesID, ClientRateName From
> > tbl_ClientRates Where ClientRatesClientID = ClientID'. When you select[/color][/color]
the[color=blue][color=green]
> > checkbox you can have the combo box enabled.
> >
> > Then in your premium calculation query you have your formulas looks at[/color][/color]
the[color=blue][color=green]
> > ClientSpecialRate Yes/No field. If Yes, then use the data from the
> > corresponding row in tbl_ClientRates. If No, use the country info.
> >
> > I hope this helps.
> >
> > HJ
> >
> > "d.p." <no@spam.never> wrote in message
> > news:41aa4454$0$1064$db0fefd9@news.zen.co.uk...[color=darkred]
> > > Hi all,
> > > I'm using MS Access 2003.
> > > Bare with me on this description....here's the situation: Imagine[/color]
> > insurance,[color=darkred]
> > > and working out premiums for different insured properties. The rates[/color][/color][/color]
for[color=blue][color=green][color=darkred]
> > > calculating premiums are dependant on the country in which the client[/color][/color][/color]
is[color=blue][color=green]
> > in.[color=darkred]
> > > Therefore, we have a Country table, with its list of rates, a client[/color][/color]
> table[color=green][color=darkred]
> > > and then the property table. Getting this is great, works fine, easy!
> > > Problem is, now I need to work out a way for special cases - a client[/color]
> > within[color=darkred]
> > > a country might have their own rate instead of using the "global" rate[/color][/color]
> for[color=green][color=darkred]
> > > the whole country. And what's worse, a client might have several[/color][/color][/color]
groups[color=blue]
> of[color=green][color=darkred]
> > > rates. So I need a system that will allow the user to be able to[/color][/color][/color]
choose[color=blue][color=green][color=darkred]
> > > which set of rates to use to calculate the premiums for that client. I
> > > created another table called ClientRates, and that's linked to the[/color][/color]
> Client[color=green][color=darkred]
> > > table, in a many-to-one relationship.
> > > Creating a query that takes rates from one table and multiplies with[/color][/color]
> other[color=green][color=darkred]
> > > values, and then gives you the premiums is easy, but is there a way to[/color]
> > allow[color=darkred]
> > > the user to choose which set of rates to use for that specific[/color][/color][/color]
instance?[color=blue][color=green][color=darkred]
> > > Some kind of drop-down list before doing the calculation. I can't[/color][/color]
> imagine[color=green][color=darkred]
> > > that's possible...but is there any other way?
> > >
> > > Here's an image of the tables and their relationships.
> > > http://img106.exs.cx/my.php?loc=img1...ationships.jpg
> > >
> > > Thanks a lot for all the help. I hope I made enough sense.
> > > Dejan
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: simple query results, yet a difficult way to get to them...

Hi HJ
thanks a lot for your help. Just went through the db and adjusted all the
tables, relationships, etc....took a while to clean it up, but it's done
now. I realised that, even though it might not be critical, it'll be much
cleaner design, and will allow for possible future events, whatever they may
be. As for the query you suggested, that works great! I appretiate your help
very much!!! It's wonderful when things start to work :-) Now i have to get
my design skills out and create nice forms.
Thanks again for your help!!!
Dejan


"HJ" <hjiscoolno@spamhotmail.com> wrote in message
news:41ad8a48$0$21106$e4fe514c@news.xs4all.nl...[color=blue]
> Hi DP,
>
> If I primarily focus at your query, and if I do not use any VBA code, you
> can have a look at http://www.xs4all.nl/~hjm/images/dp/QueryForDP.jpg (SQL
> statement is printed below; image stays online for only a few days).
>
> You see a few adjustments to the tables and one supplement to the query.
> That is the Left Join for the tbl_ClientRates table. I have now only
> adjusted the calculation for the PremiumBuildValue field; you can do the
> rest.
>
> In the example for the PremiumBuildValue you see that if the
> ClientSpecialRate Yes/No field has been selected (=Yes), then the special
> build rate is being used. If the ClientSpecialRate field has not been
> selected, then the general CountryBuildRate is used.
>
> Beware of the Left Join to the ClientRates table. You do need that type of
> relationship, because you may not have special rates for every client in
> your database.
>
> HJ
>
> To make it easier for you to rebuild the drawn query, here is the SQL
> statement:
>
> SELECT tbl_Client.ClientName, tbl_Property.PropertyName,
> tbl_Country.CountryCurrency,
>[/color]
[tbl_Property.PropertyBuildValue]*IIf([ClientSpecialRate],[tbl_ClientRates.C[color=blue]
> lientBuildRate],[tbl_Country.CountryBuildRate]) AS PremiumBuildValue,
> [tbl_Property.PropertyContValue]*[tbl_Country.CountryContRate] AS
> PremiumContValue,
> [tbl_Property.PropertyStockValue]*[tbl_Country.CountryStockRate] AS
> PremiumStockValue,
> [tbl_Property.PropertyElecEqValue]*[tbl_Country.CountryElecEqRate] AS
> PremiumEleqEqValue,
> [tbl_Property.PropertyEQCover]*[tbl_Country.CountryEQRate] AS
> PremiumEQCover, [tbl_Property.PropertySGValue]*[tbl_Country.CountrySGRate]
> AS PremiumSGValue,
> [tbl_Property.PropertyBICover]*[tbl_Country.CountryBIRate] AS
> PremiumBICover,[/color]
[tbl_Property.PropertyRoDCover]*[tbl_Country.CountryRoDRate][color=blue]
> AS PremiumRoDCover, tbl_Property.PropertyNotes
> FROM ((tbl_Country INNER JOIN tbl_Client ON tbl_Country.CountryCode =
> tbl_Client.CountryCode) INNER JOIN tbl_Property ON tbl_Client.ClientID =
> tbl_Property.PropertyClientID) LEFT JOIN tbl_ClientRates ON
> tbl_Client.ClientSpecialRateID = tbl_ClientRates.ClientRatesID;
>
> "d.p." <no@spam.never> wrote in message
> news:41ace760$0$1073$db0fefd9@news.zen.co.uk...[color=green]
> > thanks a lot HJ!
> > this sounds very possible. I haven't had a chance to mess with it the[/color][/color]
past[color=blue]
> 2[color=green]
> > days, but I'll definintely give your suggestion a go. I'm not that[/color]
> skillful[color=green]
> > at Access, but learning slowly. I hope I can figure this out. I'll give[/color][/color]
a[color=blue][color=green]
> > shout if i run into things :-)
> >
> > Oh, about the primary key for the client...I made it ClientName because[/color][/color]
I[color=blue][color=green]
> > know definitely that it'll be different for every client (it's just the[/color]
> way[color=green]
> > this company names it), although I'll consider just giving it an ID[/color][/color]
field,[color=blue][color=green]
> > just for consistency at least.
> >
> > You also said this at the end:[color=darkred]
> > > Then in your premium calculation query you have your formulas looks at[/color][/color]
> the[color=green][color=darkred]
> > > ClientSpecialRate Yes/No field. If Yes, then use the data from the
> > > corresponding row in tbl_ClientRates. If No, use the country info.[/color]
> >
> > Sorry to be a bit less knowledgable now, but how would an SQL statement[/color]
> look[color=green]
> > at a check box and then determine what to use? Here's the SQL statement[/color]
> that[color=green]
> > was generated by Access, so you can see the current query. If you have a
> > diff suggestion, please feel free to say:
> >
> > SELECT
> > tbl_Property.ClientName,
> > tbl_Property.PropertyName,
> > tbl_Country.CountryCurrency,
> > [tbl_Property.PropertyBuildValue]*[tbl_CountryInfo.CountryBuildRate] AS
> > PremiumBuildValue,
> > [tbl_Property.PropertyContValue]*[tbl_CountryInfo.CountryContRate] AS
> > PremiumContValue,
> > [tbl_Property.PropertyStockValue]*[tbl_CountryInfo.CountryStockRate] AS
> > PremiumStockValue,
> > [tbl_Property.PropertyElecEqValue]*[tbl_CountryInfo.CountryElecEqRate][/color][/color]
AS[color=blue][color=green]
> > PremiumEleqEqValue,
> > [tbl_Property.PropertyEQCover]*[tbl_CountryInfo.CountryEQRate] AS
> > PremiumEQCover,
> > [tbl_Property.PropertySGValue]*[tbl_CountryInfo.CountrySGRate] AS
> > PremiumSGValue,
> > [tbl_Property.PropertyBICover]*[tbl_CountryInfo.CountryBIRate] AS
> > PremiumBICover,
> > [tbl_Property.PropertyRoDCover]*[tbl_CountryInfo.CountryRoDRate] AS
> > PremiumRoDCover,
> > tbl_Property.PropertyNotes
> >
> > FROM
> > tbl_Country INNER JOIN ((tbl_Client INNER JOIN tbl_CountryInfo ON
> > tbl_Client.CountryCode = tbl_CountryInfo.CountryCode) INNER JOIN
> > tbl_Property ON tbl_Client.ClientName = tbl_Property.ClientName) ON
> > (tbl_Country.CountryCode = tbl_CountryInfo.CountryCode) AND
> > (tbl_Country.CountryCode = tbl_Client.CountryCode);
> >
> > Thanks so much for the help! I really appretiate it!
> >
> > Dejan
> >
> >
> >
> > "HJ" <hjiscoolno@spamhotmail.com> wrote in message
> > news:41aaf0f2$1$42417$e4fe514c@news.xs4all.nl...[color=darkred]
> > > There are a lot of ways to accomplish this. I will describe one[/color][/color][/color]
method,[color=blue][color=green][color=darkred]
> > > which may of course not be the optimal one for you; and others may[/color]
> > disagree[color=darkred]
> > > or come with alternatives.
> > >
> > > First, I would like to make a note about the relationships you have[/color]
> > defined[color=darkred]
> > > between the client, client rates and property tables. You base these
> > > relationships on the ClientName field. That is not a good thing to do,
> > > because you may have two clients with exactly the same name. It is[/color]
> > therefor[color=darkred]
> > > better to add a ClientID field to the tbl_Client table that has the
> > > AutoNumber type. And in the tbl_ClientRates and tbl_Property you add a
> > > corresponding field, e.g. ClientID or ClientRatesClientID and
> > > PropertyClientID. (The names only matter for your own reference, as[/color][/color][/color]
long[color=blue][color=green][color=darkred]
> > > they are Number (Long Integer)).
> > >
> > > Then your actual question. You may add two fields to the tbl_Client[/color][/color]
> table:[color=green][color=darkred]
> > > ClientSpecialRate (Yes/No) and ClientSpecialRateID (Number, Long[/color][/color]
> Integer).[color=green][color=darkred]
> > > On your form you add a checkbox, e.g. chkClientSpecialRate, and a[/color][/color][/color]
combo[color=blue][color=green]
> > box,[color=darkred]
> > > e.g. cboClientSpecialRate. I suppose you will have the checkbox[/color][/color]
> unselected[color=green][color=darkred]
> > > by default and the combo box disabled (greyed out) by default.
> > >
> > > The combo box' control source is your new field ClientSpecialRateID[/color][/color][/color]
and[color=blue][color=green]
> > the[color=darkred]
> > > row source is something like 'Select ClientRatesID, ClientRateName[/color][/color][/color]
From[color=blue][color=green][color=darkred]
> > > tbl_ClientRates Where ClientRatesClientID = ClientID'. When you select[/color][/color]
> the[color=green][color=darkred]
> > > checkbox you can have the combo box enabled.
> > >
> > > Then in your premium calculation query you have your formulas looks at[/color][/color]
> the[color=green][color=darkred]
> > > ClientSpecialRate Yes/No field. If Yes, then use the data from the
> > > corresponding row in tbl_ClientRates. If No, use the country info.
> > >
> > > I hope this helps.
> > >
> > > HJ
> > >
> > > "d.p." <no@spam.never> wrote in message
> > > news:41aa4454$0$1064$db0fefd9@news.zen.co.uk...
> > > > Hi all,
> > > > I'm using MS Access 2003.
> > > > Bare with me on this description....here's the situation: Imagine
> > > insurance,
> > > > and working out premiums for different insured properties. The rates[/color][/color]
> for[color=green][color=darkred]
> > > > calculating premiums are dependant on the country in which the[/color][/color][/color]
client[color=blue]
> is[color=green][color=darkred]
> > > in.
> > > > Therefore, we have a Country table, with its list of rates, a client[/color]
> > table[color=darkred]
> > > > and then the property table. Getting this is great, works fine,[/color][/color][/color]
easy![color=blue][color=green][color=darkred]
> > > > Problem is, now I need to work out a way for special cases - a[/color][/color][/color]
client[color=blue][color=green][color=darkred]
> > > within
> > > > a country might have their own rate instead of using the "global"[/color][/color][/color]
rate[color=blue][color=green]
> > for[color=darkred]
> > > > the whole country. And what's worse, a client might have several[/color][/color]
> groups[color=green]
> > of[color=darkred]
> > > > rates. So I need a system that will allow the user to be able to[/color][/color]
> choose[color=green][color=darkred]
> > > > which set of rates to use to calculate the premiums for that client.[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> > > > created another table called ClientRates, and that's linked to the[/color]
> > Client[color=darkred]
> > > > table, in a many-to-one relationship.
> > > > Creating a query that takes rates from one table and multiplies with[/color]
> > other[color=darkred]
> > > > values, and then gives you the premiums is easy, but is there a way[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > allow
> > > > the user to choose which set of rates to use for that specific[/color][/color]
> instance?[color=green][color=darkred]
> > > > Some kind of drop-down list before doing the calculation. I can't[/color]
> > imagine[color=darkred]
> > > > that's possible...but is there any other way?
> > > >
> > > > Here's an image of the tables and their relationships.
> > > > http://img106.exs.cx/my.php?loc=img1...ationships.jpg
> > > >
> > > > Thanks a lot for all the help. I hope I made enough sense.
> > > > Dejan
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]