Formula will not work

[Lincoln Beachy]

The query is called client query. This query has two tables in it, they
are Clients, and Vehicles tables.
The Client table has
ClientID - primary Key
Entry FeeID
FirstName
LastName
Address
City
State
PostalCode
Home Phone
Cell Phone
Fax
Email address

The Vehicle Table Has:
Vehicle ID - primary key
Client Id
Vin
year
make
Model
Units
policy number
From
To
Cancelled
Days

here is the SQL view:
SELECT [FirstName] & " " & [LastName] AS [Full Name], Vehicles.Year,
Vehicles.Make, Vehicles.Model, Vehicles.From, Vehicles.To, [To]-[From]
AS Days, Vehicles.Units, [Units]*[Days]/365 AS Equals, 164.18 AS Rate,
[Equals]*[Rate] AS Premium, IIf(([UNITS]=1 And [MODEL]="Trailer"),5000)
AS Trailer,
IIf([Units]>3.5,"35000",IIf([Units]>3,"26000",IIf([Units]>2.5,"18000",IIf([Units]>2,"11000",IIf([Units]>1,"5000",0)))))
AS [Coverage Level], ([Trailer]+[Coverage Level]) AS Total
FROM Clients INNER JOIN Vehicles ON Clients.[Client ID] =
Vehicles.[Client ID];

I add the Total field to get total cost of each person's vehicle on the
report. But I did not work the report yet.
I hope this helps you u guys.
Lincoln Beachy

 

[Tom Ellison]

Dear Lincoln:

Your query, arranged for my reading preferences, so I can study it:

SELECT [FirstName] & " " & [LastName] AS [Full Name],
Vehicles.Year, Vehicles.Make, Vehicles.Model,
Vehicles.From, Vehicles.To, [To]-[From] AS Days,
Vehicles.Units, Units * Days / 365 AS Equals,
164.18 AS Rate, Equals * Rate AS Premium,
IIf((UNITS = 1 And MODEL = "Trailer"), 5000) AS Trailer,
IIf(Units > 3.5, "35000", IIf(Units > 3,"26000", IIf(Units >
2.5,"18000",
IIf(Units > 2, "11000", IIf(Units > 1,"5000",0))))) AS [Coverage
Level],
(Trailer + [Coverage Level]) AS Total
FROM Clients
INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client ID];

It appears you are expecting to be able to use columns you derive as columns
in further derivations. That is, you have:

164.18 AS Rate

Then you proceed to use Rate as an established value:

[Equals] * [Rate] AS Premium

Similarly you use [Coverage Level].

This will now work. You can either insert the value (i.e. 164.18 instead of
Rate and that whole long IIf thing instead of [Coverage Level]) or you can
save the query without the columns Premium and Total and then create another
query based on that one which calculates further using those values.

The reason is "sequence". The query does not evaluate from top to bottom.
It is better to think of it as executing all at once. There are, therefore,
no antecedents.

"Days" has the same problem, which I didn't see at first.

Tom Ellison

 

[Lincoln Beachy]

Dear Lincoln:

Your query, arranged for my reading preferences, so I can study it:

SELECT [FirstName] & " " & [LastName] AS [Full Name],
Vehicles.Year, Vehicles.Make, Vehicles.Model,
Vehicles.From, Vehicles.To, [To]-[From] AS Days,
Vehicles.Units, Units * Days / 365 AS Equals,
164.18 AS Rate, Equals * Rate AS Premium,
IIf((UNITS = 1 And MODEL = "Trailer"), 5000) AS Trailer,
IIf(Units > 3.5, "35000", IIf(Units > 3,"26000", IIf(Units >
2.5,"18000",
IIf(Units > 2, "11000", IIf(Units > 1,"5000",0))))) AS [Coverage
Level],
(Trailer + [Coverage Level]) AS Total
FROM Clients
INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client ID];

It appears you are expecting to be able to use columns you derive as columns
in further derivations. That is, you have:

164.18 AS Rate

Then you proceed to use Rate as an established value:

[Equals] * [Rate] AS Premium

Similarly you use [Coverage Level].

This will now work. You can either insert the value (i.e. 164.18 instead of
Rate and that whole long IIf thing instead of [Coverage Level]) or you can
save the query without the columns Premium and Total and then create another
query based on that one which calculates further using those values.

The reason is "sequence". The query does not evaluate from top to bottom.
It is better to think of it as executing all at once. There are, therefore,
no antecedents.

"Days" has the same problem, which I didn't see at first.

Tom Ellison

The query is called client query. This query has two tables in it, they
are Clients, and Vehicles tables.
The Client table has
ClientID - primary Key
Entry FeeID
FirstName
LastName
Address
City
State
PostalCode
Home Phone
Cell Phone
Fax
Email address

The Vehicle Table Has:
Vehicle ID - primary key
Client Id
Vin
year
make
Model
Units
policy number
From
To
Cancelled
Days

here is the SQL view:
SELECT [FirstName] & " " & [LastName] AS [Full Name], Vehicles.Year,
Vehicles.Make, Vehicles.Model, Vehicles.From, Vehicles.To, [To]-[From] AS
Days, Vehicles.Units, [Units]*[Days]/365 AS Equals, 164.18 AS Rate,
[Equals]*[Rate] AS Premium, IIf(([UNITS]=1 And [MODEL]="Trailer"),5000) AS
Trailer,
IIf([Units]>3.5,"35000",IIf([Units]>3,"26000",IIf([Units]>2.5,"18000",IIf([Units]>2,"11000",IIf([Units]>1,"5000",0)))))
AS [Coverage Level], ([Trailer]+[Coverage Level]) AS Total
FROM Clients INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client
ID];

I add the Total field to get total cost of each person's vehicle on the
report. But I did not work the report yet.
I hope this helps you u guys.
Lincoln Beachy

Explain the buttom part more. the Equal field. here is the formula for
the equals field: Equals: [Units]*[Days]/365 then the premium field
Calcute the equals field and the rate field to get the cost for each
vehicle. If a owner has 2.5 units on his vihicle then the premium field
will calcute the equals field and the rate to get that vehicle. Each
vehicle has different units on it too. I hope this will be helpful to u
Lincoln beachy

 

[Tom Ellison]

Dear Lincoln:

You have:

[To] - [From] AS Days

Then you proceed to use Days in:

Units * Days / 365 AS Equals

This is what you must not do. So, I suggest for this you use:

Units * ([To] - [From]) / 365 AS Equals

The [Coverage Level] appears to work from a bracketed table of values:

Up To Use This
2 5000
2.5 11000
3 18000
3.5 26000
above 35000

It is often best to put this into a separate table and look up the Level in
that. Your IIf is complex and will be difficult to maintain if the rates or
levels change.

Tom Ellison

Dear Lincoln:

Your query, arranged for my reading preferences, so I can study it:

SELECT [FirstName] & " " & [LastName] AS [Full Name],
Vehicles.Year, Vehicles.Make, Vehicles.Model,
Vehicles.From, Vehicles.To, [To]-[From] AS Days,
Vehicles.Units, Units * Days / 365 AS Equals,
164.18 AS Rate, Equals * Rate AS Premium,
IIf((UNITS = 1 And MODEL = "Trailer"), 5000) AS Trailer,
IIf(Units > 3.5, "35000", IIf(Units > 3,"26000", IIf(Units >
2.5,"18000",
IIf(Units > 2, "11000", IIf(Units > 1,"5000",0))))) AS [Coverage
Level],
(Trailer + [Coverage Level]) AS Total
FROM Clients
INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client ID];

It appears you are expecting to be able to use columns you derive as
columns in further derivations. That is, you have:

164.18 AS Rate

Then you proceed to use Rate as an established value:

[Equals] * [Rate] AS Premium

Similarly you use [Coverage Level].

This will now work. You can either insert the value (i.e. 164.18 instead
of Rate and that whole long IIf thing instead of [Coverage Level]) or you
can save the query without the columns Premium and Total and then create
another query based on that one which calculates further using those
values.

The reason is "sequence". The query does not evaluate from top to
bottom. It is better to think of it as executing all at once. There are,
therefore, no antecedents.

"Days" has the same problem, which I didn't see at first.

Tom Ellison

Explain the buttom part more. the Equal field. here is the formula for
the equals field: Equals: [Units]*[Days]/365 then the premium field
Calcute the equals field and the rate field to get the cost for each
vehicle. If a owner has 2.5 units on his vihicle then the premium field
will calcute the equals field and the rate to get that vehicle. Each
vehicle has different units on it too. I hope this will be helpful to u
Lincoln beachy

 

[John Spencer]

To get the Total, you must recalculate Trailer and Coverage Level. Also,
you must do so as a number (not a text string of number characters) so you
can add the numbers.

SELECT [FirstName] & " " & [LastName] AS [Full Name], Vehicles.Year,
Vehicles.Make, Vehicles.Model, Vehicles.From, Vehicles.To, [To]-[From]
AS Days, Vehicles.Units, [Units]*[Days]/365 AS Equals, 164.18 AS Rate,
[Equals]*[Rate] AS Premium,
IIf(([UNITS]=1 And [MODEL]="Trailer"),5000) AS Trailer,
IIf([Units]>3.5,"35000",IIf([Units]>3,"26000",IIf([Units]>2.5,"18000",IIf([Units]>2,"11000",IIf([Units]>1,"5000",0)))))
AS [Coverage Level],
IIf(([UNITS]=1 And [MODEL]="Trailer"),5000,0) +
IIf([Units]>3.5,35000,IIf([Units]>3,26000,IIf([Units]>2.5,18000,IIf([Units]>2,11000,IIf([Units]>1,5000,0)))))
AS Total
FROM Clients INNER JOIN Vehicles
ON Clients.[Client ID] = Vehicles.[Client ID];

 

[Lincoln Beachy]

Dear Tom,
I am working on making a table for the coverage Level.
I am thinking of having this in my table:
CoverageID - primary key
Units
Dollars
What was u thinking, how would u design the table if it was your
database. I can send you this database to you, if you are outsider
(living outside of OH or Holmes County.

Dear Lincoln:

You have:

[To] - [From] AS Days

Then you proceed to use Days in:

Units * Days / 365 AS Equals

This is what you must not do. So, I suggest for this you use:

Units * ([To] - [From]) / 365 AS Equals

The [Coverage Level] appears to work from a bracketed table of values:

Up To Use This
2 5000
2.5 11000
3 18000
3.5 26000
above 35000

It is often best to put this into a separate table and look up the Level in
that. Your IIf is complex and will be difficult to maintain if the rates or
levels change.

Tom Ellison

Dear Lincoln:

Your query, arranged for my reading preferences, so I can study it:

SELECT [FirstName] & " " & [LastName] AS [Full Name],
Vehicles.Year, Vehicles.Make, Vehicles.Model,
Vehicles.From, Vehicles.To, [To]-[From] AS Days,
Vehicles.Units, Units * Days / 365 AS Equals,
164.18 AS Rate, Equals * Rate AS Premium,
IIf((UNITS = 1 And MODEL = "Trailer"), 5000) AS Trailer,
IIf(Units > 3.5, "35000", IIf(Units > 3,"26000", IIf(Units >
2.5,"18000",
IIf(Units > 2, "11000", IIf(Units > 1,"5000",0))))) AS [Coverage
Level],
(Trailer + [Coverage Level]) AS Total
FROM Clients
INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client ID];

It appears you are expecting to be able to use columns you derive as
columns in further derivations. That is, you have:

164.18 AS Rate

Then you proceed to use Rate as an established value:

[Equals] * [Rate] AS Premium

Similarly you use [Coverage Level].

This will now work. You can either insert the value (i.e. 164.18 instead
of Rate and that whole long IIf thing instead of [Coverage Level]) or you
can save the query without the columns Premium and Total and then create
another query based on that one which calculates further using those
values.

The reason is "sequence". The query does not evaluate from top to
bottom. It is better to think of it as executing all at once. There are,
therefore, no antecedents.

"Days" has the same problem, which I didn't see at first.

Tom Ellison

Explain the buttom part more. the Equal field. here is the formula for
the equals field: Equals: [Units]*[Days]/365 then the premium field
Calcute the equals field and the rate field to get the cost for each
vehicle. If a owner has 2.5 units on his vihicle then the premium field
will calcute the equals field and the rate to get that vehicle. Each
vehicle has different units on it too. I hope this will be helpful to u
Lincoln beachy

 

[Tom Ellison]

Dear Lincoln:

If it were my table, I wouldn't use the CoverageID. I would make the Units
the primary key. This column would be unique, for sure. And this column is
the one by which you look up rows when you need them. So, for performance,
it would be best to have such an index. There's no need for the additional
index or column.

I do occasionally ask posters for a copy of their databases. I do this when
I need clarification of an issue and that would take too long to get
exchanging text messages, or when I want to test a difficult solution
against the actual database being built. Neither has come up yet in our
exchange here. So, I'll decline your offer for now.

The retrieval of the [Coverage Level].Dollars could be done with a subquery:

SELECT MAX(CL.Dollars)
WHERE Clients.[Units] < CL.[Up To]
FROM [Coverage Level] CL

This works with your table if you enter the data somewhat differently than
shown previously:

Up To Dollars
0 5000
2 11000
2.5 18000
3 26000
3.5 35000

I recommend you test this carefully, especially at the values in the Up To
column

Tom Ellison

Dear Tom,
I am working on making a table for the coverage Level.
I am thinking of having this in my table:
CoverageID - primary key
Units
Dollars
What was u thinking, how would u design the table if it was your database.
I can send you this database to you, if you are outsider (living outside
of OH or Holmes County.

Dear Lincoln:

You have:

[To] - [From] AS Days

Then you proceed to use Days in:

Units * Days / 365 AS Equals

This is what you must not do. So, I suggest for this you use:

Units * ([To] - [From]) / 365 AS Equals

The [Coverage Level] appears to work from a bracketed table of values:

Up To Use This
2 5000
2.5 11000
3 18000
3.5 26000
above 35000

It is often best to put this into a separate table and look up the Level
in that. Your IIf is complex and will be difficult to maintain if the
rates or levels change.

Tom Ellison

Tom Ellison wrote:
Dear Lincoln:

Your query, arranged for my reading preferences, so I can study it:

SELECT [FirstName] & " " & [LastName] AS [Full Name],
Vehicles.Year, Vehicles.Make, Vehicles.Model,
Vehicles.From, Vehicles.To, [To]-[From] AS Days,
Vehicles.Units, Units * Days / 365 AS Equals,
164.18 AS Rate, Equals * Rate AS Premium,
IIf((UNITS = 1 And MODEL = "Trailer"), 5000) AS Trailer,
IIf(Units > 3.5, "35000", IIf(Units > 3,"26000", IIf(Units >
2.5,"18000",
IIf(Units > 2, "11000", IIf(Units > 1,"5000",0))))) AS [Coverage
Level],
(Trailer + [Coverage Level]) AS Total
FROM Clients
INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client ID];

It appears you are expecting to be able to use columns you derive as
columns in further derivations. That is, you have:

164.18 AS Rate

Then you proceed to use Rate as an established value:

[Equals] * [Rate] AS Premium

Similarly you use [Coverage Level].

This will now work. You can either insert the value (i.e. 164.18
instead of Rate and that whole long IIf thing instead of [Coverage
Level]) or you can save the query without the columns Premium and Total
and then create another query based on that one which calculates
further using those values.

The reason is "sequence". The query does not evaluate from top to
bottom. It is better to think of it as executing all at once. There
are, therefore, no antecedents.

"Days" has the same problem, which I didn't see at first.

Tom Ellison

Explain the buttom part more. the Equal field. here is the formula for
the equals field: Equals: [Units]*[Days]/365 then the premium field
Calcute the equals field and the rate field to get the cost for each
vehicle. If a owner has 2.5 units on his vihicle then the premium field
will calcute the equals field and the rate to get that vehicle. Each
vehicle has different units on it too. I hope this will be helpful to u
Lincoln beachy

 

[Lincoln Beachy]

Tom
Do I need a up to field in the table or not?
I have in the table:
units field and dollars field
what else do I need yet that I do not have? Thank you for your help\
Lincoln BEachy

Dear Lincoln:

If it were my table, I wouldn't use the CoverageID. I would make the Units
the primary key. This column would be unique, for sure. And this column is
the one by which you look up rows when you need them. So, for performance,
it would be best to have such an index. There's no need for the additional
index or column.

I do occasionally ask posters for a copy of their databases. I do this when
I need clarification of an issue and that would take too long to get
exchanging text messages, or when I want to test a difficult solution
against the actual database being built. Neither has come up yet in our
exchange here. So, I'll decline your offer for now.

The retrieval of the [Coverage Level].Dollars could be done with a subquery:

SELECT MAX(CL.Dollars)
WHERE Clients.[Units] < CL.[Up To]
FROM [Coverage Level] CL

This works with your table if you enter the data somewhat differently than
shown previously:

Up To Dollars
0 5000
2 11000
2.5 18000
3 26000
3.5 35000

I recommend you test this carefully, especially at the values in the Up To
column

Tom Ellison

Dear Tom,
I am working on making a table for the coverage Level.
I am thinking of having this in my table:
CoverageID - primary key
Units
Dollars
What was u thinking, how would u design the table if it was your database.
I can send you this database to you, if you are outsider (living outside
of OH or Holmes County.

Dear Lincoln:

You have:

[To] - [From] AS Days

Then you proceed to use Days in:

Units * Days / 365 AS Equals

This is what you must not do. So, I suggest for this you use:

Units * ([To] - [From]) / 365 AS Equals

The [Coverage Level] appears to work from a bracketed table of values:

Up To Use This
2 5000
2.5 11000
3 18000
3.5 26000
above 35000

It is often best to put this into a separate table and look up the Level
in that. Your IIf is complex and will be difficult to maintain if the
rates or levels change.

Tom Ellison


Tom Ellison wrote:
Dear Lincoln:

Your query, arranged for my reading preferences, so I can study it:

SELECT [FirstName] & " " & [LastName] AS [Full Name],
Vehicles.Year, Vehicles.Make, Vehicles.Model,
Vehicles.From, Vehicles.To, [To]-[From] AS Days,
Vehicles.Units, Units * Days / 365 AS Equals,
164.18 AS Rate, Equals * Rate AS Premium,
IIf((UNITS = 1 And MODEL = "Trailer"), 5000) AS Trailer,
IIf(Units > 3.5, "35000", IIf(Units > 3,"26000", IIf(Units >
2.5,"18000",
IIf(Units > 2, "11000", IIf(Units > 1,"5000",0))))) AS [Coverage
Level],
(Trailer + [Coverage Level]) AS Total
FROM Clients
INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client ID];

It appears you are expecting to be able to use columns you derive as
columns in further derivations. That is, you have:

164.18 AS Rate

Then you proceed to use Rate as an established value:

[Equals] * [Rate] AS Premium

Similarly you use [Coverage Level].

This will now work. You can either insert the value (i.e. 164.18
instead of Rate and that whole long IIf thing instead of [Coverage
Level]) or you can save the query without the columns Premium and Total
and then create another query based on that one which calculates
further using those values.

The reason is "sequence". The query does not evaluate from top to
bottom. It is better to think of it as executing all at once. There
are, therefore, no antecedents.

"Days" has the same problem, which I didn't see at first.

Tom Ellison
Explain the buttom part more. the Equal field. here is the formula for
the equals field: Equals: [Units]*[Days]/365 then the premium field
Calcute the equals field and the rate field to get the cost for each
vehicle. If a owner has 2.5 units on his vihicle then the premium field
will calcute the equals field and the rate to get that vehicle. Each
vehicle has different units on it too. I hope this will be helpful to u
Lincoln beachy

 

[Tom Ellison]

Dear Lincoln:

You can call this column [Up To] or Units. It doesn't matter very much
which. It's just one column.

Tom Ellison

Tom
Do I need a up to field in the table or not?
I have in the table:
units field and dollars field
what else do I need yet that I do not have? Thank you for your help\
Lincoln BEachy

Dear Lincoln:

If it were my table, I wouldn't use the CoverageID. I would make the
Units the primary key. This column would be unique, for sure. And this
column is the one by which you look up rows when you need them. So, for
performance, it would be best to have such an index. There's no need for
the additional index or column.

I do occasionally ask posters for a copy of their databases. I do this
when I need clarification of an issue and that would take too long to get
exchanging text messages, or when I want to test a difficult solution
against the actual database being built. Neither has come up yet in our
exchange here. So, I'll decline your offer for now.

The retrieval of the [Coverage Level].Dollars could be done with a
subquery:

SELECT MAX(CL.Dollars)
WHERE Clients.[Units] < CL.[Up To]
FROM [Coverage Level] CL

This works with your table if you enter the data somewhat differently
than shown previously:

Up To Dollars
0 5000
2 11000
2.5 18000
3 26000
3.5 35000

I recommend you test this carefully, especially at the values in the Up
To column

Tom Ellison

Dear Tom,
I am working on making a table for the coverage Level.
I am thinking of having this in my table:
CoverageID - primary key
Units
Dollars
What was u thinking, how would u design the table if it was your
database. I can send you this database to you, if you are outsider
(living outside of OH or Holmes County.
Tom Ellison wrote:
Dear Lincoln:

You have:

[To] - [From] AS Days

Then you proceed to use Days in:

Units * Days / 365 AS Equals

This is what you must not do. So, I suggest for this you use:

Units * ([To] - [From]) / 365 AS Equals

The [Coverage Level] appears to work from a bracketed table of values:

Up To Use This
2 5000
2.5 11000
3 18000
3.5 26000
above 35000

It is often best to put this into a separate table and look up the
Level in that. Your IIf is complex and will be difficult to maintain
if the rates or levels change.

Tom Ellison


Tom Ellison wrote:
Dear Lincoln:

Your query, arranged for my reading preferences, so I can study it:

SELECT [FirstName] & " " & [LastName] AS [Full Name],
Vehicles.Year, Vehicles.Make, Vehicles.Model,
Vehicles.From, Vehicles.To, [To]-[From] AS Days,
Vehicles.Units, Units * Days / 365 AS Equals,
164.18 AS Rate, Equals * Rate AS Premium,
IIf((UNITS = 1 And MODEL = "Trailer"), 5000) AS Trailer,
IIf(Units > 3.5, "35000", IIf(Units > 3,"26000", IIf(Units >
2.5,"18000",
IIf(Units > 2, "11000", IIf(Units > 1,"5000",0))))) AS
[Coverage Level],
(Trailer + [Coverage Level]) AS Total
FROM Clients
INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client
ID];

It appears you are expecting to be able to use columns you derive as
columns in further derivations. That is, you have:

164.18 AS Rate

Then you proceed to use Rate as an established value:

[Equals] * [Rate] AS Premium

Similarly you use [Coverage Level].

This will now work. You can either insert the value (i.e. 164.18
instead of Rate and that whole long IIf thing instead of [Coverage
Level]) or you can save the query without the columns Premium and
Total and then create another query based on that one which
calculates further using those values.

The reason is "sequence". The query does not evaluate from top to
bottom. It is better to think of it as executing all at once. There
are, therefore, no antecedents.

"Days" has the same problem, which I didn't see at first.

Tom Ellison
Explain the buttom part more. the Equal field. here is the formula
for the equals field: Equals: [Units]*[Days]/365 then the premium
field Calcute the equals field and the rate field to get the cost for
each vehicle. If a owner has 2.5 units on his vihicle then the premium
field will calcute the equals field and the rate to get that vehicle.
Each vehicle has different units on it too. I hope this will be
helpful to u
Lincoln beachy

 

[Lincoln Beachy]

Tom,
I did what you told me to do. I went in and query named CL and went into
the test query and typed in the code that u provided to me but I am
having trouble with the code. How should I do it. I tried somethings but
I can not get around the Problem.
Lincoln beachy

Dear Lincoln:

You can call this column [Up To] or Units. It doesn't matter very much
which. It's just one column.

Tom Ellison

Tom
Do I need a up to field in the table or not?
I have in the table:
units field and dollars field
what else do I need yet that I do not have? Thank you for your help\
Lincoln BEachy

Dear Lincoln:

If it were my table, I wouldn't use the CoverageID. I would make the
Units the primary key. This column would be unique, for sure. And this
column is the one by which you look up rows when you need them. So, for
performance, it would be best to have such an index. There's no need for
the additional index or column.

I do occasionally ask posters for a copy of their databases. I do this
when I need clarification of an issue and that would take too long to get
exchanging text messages, or when I want to test a difficult solution
against the actual database being built. Neither has come up yet in our
exchange here. So, I'll decline your offer for now.

The retrieval of the [Coverage Level].Dollars could be done with a
subquery:

SELECT MAX(CL.Dollars)
WHERE Clients.[Units] < CL.[Up To]
FROM [Coverage Level] CL

This works with your table if you enter the data somewhat differently
than shown previously:

Up To Dollars
0 5000
2 11000
2.5 18000
3 26000
3.5 35000

I recommend you test this carefully, especially at the values in the Up
To column

Tom Ellison


Dear Tom,
I am working on making a table for the coverage Level.
I am thinking of having this in my table:
CoverageID - primary key
Units
Dollars
What was u thinking, how would u design the table if it was your
database. I can send you this database to you, if you are outsider
(living outside of OH or Holmes County.
Tom Ellison wrote:
Dear Lincoln:

You have:

[To] - [From] AS Days

Then you proceed to use Days in:

Units * Days / 365 AS Equals

This is what you must not do. So, I suggest for this you use:

Units * ([To] - [From]) / 365 AS Equals

The [Coverage Level] appears to work from a bracketed table of values:

Up To Use This
2 5000
2.5 11000
3 18000
3.5 26000
above 35000

It is often best to put this into a separate table and look up the
Level in that. Your IIf is complex and will be difficult to maintain
if the rates or levels change.

Tom Ellison


Tom Ellison wrote:
Dear Lincoln:

Your query, arranged for my reading preferences, so I can study it:

SELECT [FirstName] & " " & [LastName] AS [Full Name],
Vehicles.Year, Vehicles.Make, Vehicles.Model,
Vehicles.From, Vehicles.To, [To]-[From] AS Days,
Vehicles.Units, Units * Days / 365 AS Equals,
164.18 AS Rate, Equals * Rate AS Premium,
IIf((UNITS = 1 And MODEL = "Trailer"), 5000) AS Trailer,
IIf(Units > 3.5, "35000", IIf(Units > 3,"26000", IIf(Units >
2.5,"18000",
IIf(Units > 2, "11000", IIf(Units > 1,"5000",0))))) AS
[Coverage Level],
(Trailer + [Coverage Level]) AS Total
FROM Clients
INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client
ID];

It appears you are expecting to be able to use columns you derive as
columns in further derivations. That is, you have:

164.18 AS Rate

Then you proceed to use Rate as an established value:

[Equals] * [Rate] AS Premium

Similarly you use [Coverage Level].

This will now work. You can either insert the value (i.e. 164.18
instead of Rate and that whole long IIf thing instead of [Coverage
Level]) or you can save the query without the columns Premium and
Total and then create another query based on that one which
calculates further using those values.

The reason is "sequence". The query does not evaluate from top to
bottom. It is better to think of it as executing all at once. There
are, therefore, no antecedents.

"Days" has the same problem, which I didn't see at first.

Tom Ellison
Explain the buttom part more. the Equal field. here is the formula
for the equals field: Equals: [Units]*[Days]/365 then the premium
field Calcute the equals field and the rate field to get the cost for
each vehicle. If a owner has 2.5 units on his vihicle then the premium
field will calcute the equals field and the rate to get that vehicle.
Each vehicle has different units on it too. I hope this will be
helpful to u
Lincoln beachy

 

[Tom Ellison]

Dear Lincoln:

If you do not post the code you have currently and explain just what the
problem is, it will not be possible for me to help. Just saying you are
"having trouble with the code" doesn't tell me much at all.

Tom Ellison

Tom,
I did what you told me to do. I went in and query named CL and went into
the test query and typed in the code that u provided to me but I am having
trouble with the code. How should I do it. I tried somethings but I can
not get around the Problem.
Lincoln beachy

Dear Lincoln:

You can call this column [Up To] or Units. It doesn't matter very much
which. It's just one column.

Tom Ellison

Tom
Do I need a up to field in the table or not?
I have in the table:
units field and dollars field
what else do I need yet that I do not have? Thank you for your help\
Lincoln BEachy
Tom Ellison wrote:
Dear Lincoln:

If it were my table, I wouldn't use the CoverageID. I would make the
Units the primary key. This column would be unique, for sure. And
this column is the one by which you look up rows when you need them.
So, for performance, it would be best to have such an index. There's
no need for the additional index or column.

I do occasionally ask posters for a copy of their databases. I do this
when I need clarification of an issue and that would take too long to
get exchanging text messages, or when I want to test a difficult
solution against the actual database being built. Neither has come up
yet in our exchange here. So, I'll decline your offer for now.

The retrieval of the [Coverage Level].Dollars could be done with a
subquery:

SELECT MAX(CL.Dollars)
WHERE Clients.[Units] < CL.[Up To]
FROM [Coverage Level] CL

This works with your table if you enter the data somewhat differently
than shown previously:

Up To Dollars
0 5000
2 11000
2.5 18000
3 26000
3.5 35000

I recommend you test this carefully, especially at the values in the Up
To column

Tom Ellison


Dear Tom,
I am working on making a table for the coverage Level.
I am thinking of having this in my table:
CoverageID - primary key
Units
Dollars
What was u thinking, how would u design the table if it was your
database. I can send you this database to you, if you are outsider
(living outside of OH or Holmes County.
Tom Ellison wrote:
Dear Lincoln:

You have:

[To] - [From] AS Days

Then you proceed to use Days in:

Units * Days / 365 AS Equals

This is what you must not do. So, I suggest for this you use:

Units * ([To] - [From]) / 365 AS Equals

The [Coverage Level] appears to work from a bracketed table of
values:

Up To Use This
2 5000
2.5 11000
3 18000
3.5 26000
above 35000

It is often best to put this into a separate table and look up the
Level in that. Your IIf is complex and will be difficult to maintain
if the rates or levels change.

Tom Ellison


Tom Ellison wrote:
Dear Lincoln:

Your query, arranged for my reading preferences, so I can study it:

SELECT [FirstName] & " " & [LastName] AS [Full Name],
Vehicles.Year, Vehicles.Make, Vehicles.Model,
Vehicles.From, Vehicles.To, [To]-[From] AS Days,
Vehicles.Units, Units * Days / 365 AS Equals,
164.18 AS Rate, Equals * Rate AS Premium,
IIf((UNITS = 1 And MODEL = "Trailer"), 5000) AS Trailer,
IIf(Units > 3.5, "35000", IIf(Units > 3,"26000", IIf(Units >
2.5,"18000",
IIf(Units > 2, "11000", IIf(Units > 1,"5000",0))))) AS
[Coverage Level],
(Trailer + [Coverage Level]) AS Total
FROM Clients
INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client
ID];

It appears you are expecting to be able to use columns you derive
as columns in further derivations. That is, you have:

164.18 AS Rate

Then you proceed to use Rate as an established value:

[Equals] * [Rate] AS Premium

Similarly you use [Coverage Level].

This will now work. You can either insert the value (i.e. 164.18
instead of Rate and that whole long IIf thing instead of [Coverage
Level]) or you can save the query without the columns Premium and
Total and then create another query based on that one which
calculates further using those values.

The reason is "sequence". The query does not evaluate from top to
bottom. It is better to think of it as executing all at once.
There are, therefore, no antecedents.

"Days" has the same problem, which I didn't see at first.

Tom Ellison
Explain the buttom part more. the Equal field. here is the formula
for the equals field: Equals: [Units]*[Days]/365 then the premium
field Calcute the equals field and the rate field to get the cost
for each vehicle. If a owner has 2.5 units on his vihicle then the
premium field will calcute the equals field and the rate to get that
vehicle. Each vehicle has different units on it too. I hope this
will be helpful to u
Lincoln beachy

 

[Lincoln Beachy]

Tom,
Sorry, about this problem: The message: The syntax of the subquery in
this expression is incorrect.
Check the subquery's syntax and enclose the subquery in the parantheses.
am I not doing something right or what? I will be checking this
newsqroup later on today.
Lincoln Beachy

Dear Lincoln:

If you do not post the code you have currently and explain just what the
problem is, it will not be possible for me to help. Just saying you are
"having trouble with the code" doesn't tell me much at all.

Tom Ellison

Tom,
I did what you told me to do. I went in and query named CL and went into
the test query and typed in the code that u provided to me but I am having
trouble with the code. How should I do it. I tried somethings but I can
not get around the Problem.
Lincoln beachy

Dear Lincoln:

You can call this column [Up To] or Units. It doesn't matter very much
which. It's just one column.

Tom Ellison


Tom
Do I need a up to field in the table or not?
I have in the table:
units field and dollars field
what else do I need yet that I do not have? Thank you for your help\
Lincoln BEachy
Tom Ellison wrote:
Dear Lincoln:

If it were my table, I wouldn't use the CoverageID. I would make the
Units the primary key. This column would be unique, for sure. And
this column is the one by which you look up rows when you need them.
So, for performance, it would be best to have such an index. There's
no need for the additional index or column.

I do occasionally ask posters for a copy of their databases. I do this
when I need clarification of an issue and that would take too long to
get exchanging text messages, or when I want to test a difficult
solution against the actual database being built. Neither has come up
yet in our exchange here. So, I'll decline your offer for now.

The retrieval of the [Coverage Level].Dollars could be done with a
subquery:

SELECT MAX(CL.Dollars)
WHERE Clients.[Units] < CL.[Up To]
FROM [Coverage Level] CL

This works with your table if you enter the data somewhat differently
than shown previously:

Up To Dollars
0 5000
2 11000
2.5 18000
3 26000
3.5 35000

I recommend you test this carefully, especially at the values in the Up
To column

Tom Ellison


Dear Tom,
I am working on making a table for the coverage Level.
I am thinking of having this in my table:
CoverageID - primary key
Units
Dollars
What was u thinking, how would u design the table if it was your
database. I can send you this database to you, if you are outsider
(living outside of OH or Holmes County.
Tom Ellison wrote:
Dear Lincoln:

You have:

[To] - [From] AS Days

Then you proceed to use Days in:

Units * Days / 365 AS Equals

This is what you must not do. So, I suggest for this you use:

Units * ([To] - [From]) / 365 AS Equals

The [Coverage Level] appears to work from a bracketed table of
values:

Up To Use This
2 5000
2.5 11000
3 18000
3.5 26000
above 35000

It is often best to put this into a separate table and look up the
Level in that. Your IIf is complex and will be difficult to maintain
if the rates or levels change.

Tom Ellison


Tom Ellison wrote:
Dear Lincoln:

Your query, arranged for my reading preferences, so I can study it:

SELECT [FirstName] & " " & [LastName] AS [Full Name],
Vehicles.Year, Vehicles.Make, Vehicles.Model,
Vehicles.From, Vehicles.To, [To]-[From] AS Days,
Vehicles.Units, Units * Days / 365 AS Equals,
164.18 AS Rate, Equals * Rate AS Premium,
IIf((UNITS = 1 And MODEL = "Trailer"), 5000) AS Trailer,
IIf(Units > 3.5, "35000", IIf(Units > 3,"26000", IIf(Units >
2.5,"18000",
IIf(Units > 2, "11000", IIf(Units > 1,"5000",0))))) AS
[Coverage Level],
(Trailer + [Coverage Level]) AS Total
FROM Clients
INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client
ID];

It appears you are expecting to be able to use columns you derive
as columns in further derivations. That is, you have:

164.18 AS Rate

Then you proceed to use Rate as an established value:

[Equals] * [Rate] AS Premium

Similarly you use [Coverage Level].

This will now work. You can either insert the value (i.e. 164.18
instead of Rate and that whole long IIf thing instead of [Coverage
Level]) or you can save the query without the columns Premium and
Total and then create another query based on that one which
calculates further using those values.

The reason is "sequence". The query does not evaluate from top to
bottom. It is better to think of it as executing all at once.
There are, therefore, no antecedents.

"Days" has the same problem, which I didn't see at first.

Tom Ellison
Explain the buttom part more. the Equal field. here is the formula
for the equals field: Equals: [Units]*[Days]/365 then the premium
field Calcute the equals field and the rate field to get the cost
for each vehicle. If a owner has 2.5 units on his vihicle then the
premium field will calcute the equals field and the rate to get that
vehicle. Each vehicle has different units on it too. I hope this
will be helpful to u
Lincoln beachy

 

[Tom Ellison]

Dear Lincoln:

My post asked you to post your code as well as giving the error message. I
cannot find a possible error without anywhere to look for it.

Tom Ellison

Tom,
Sorry, about this problem: The message: The syntax of the subquery in this
expression is incorrect.
Check the subquery's syntax and enclose the subquery in the parantheses.
am I not doing something right or what? I will be checking this newsqroup
later on today.
Lincoln Beachy

Dear Lincoln:

If you do not post the code you have currently and explain just what the
problem is, it will not be possible for me to help. Just saying you are
"having trouble with the code" doesn't tell me much at all.

Tom Ellison

Tom,
I did what you told me to do. I went in and query named CL and went into
the test query and typed in the code that u provided to me but I am
having trouble with the code. How should I do it. I tried somethings but
I can not get around the Problem.
Lincoln beachy
Tom Ellison wrote:
Dear Lincoln:

You can call this column [Up To] or Units. It doesn't matter very much
which. It's just one column.

Tom Ellison


Tom
Do I need a up to field in the table or not?
I have in the table:
units field and dollars field
what else do I need yet that I do not have? Thank you for your help\
Lincoln BEachy
Tom Ellison wrote:
Dear Lincoln:

If it were my table, I wouldn't use the CoverageID. I would make the
Units the primary key. This column would be unique, for sure. And
this column is the one by which you look up rows when you need them.
So, for performance, it would be best to have such an index. There's
no need for the additional index or column.

I do occasionally ask posters for a copy of their databases. I do
this when I need clarification of an issue and that would take too
long to get exchanging text messages, or when I want to test a
difficult solution against the actual database being built. Neither
has come up yet in our exchange here. So, I'll decline your offer
for now.

The retrieval of the [Coverage Level].Dollars could be done with a
subquery:

SELECT MAX(CL.Dollars)
WHERE Clients.[Units] < CL.[Up To]
FROM [Coverage Level] CL

This works with your table if you enter the data somewhat differently
than shown previously:

Up To Dollars
0 5000
2 11000
2.5 18000
3 26000
3.5 35000

I recommend you test this carefully, especially at the values in the
Up To column

Tom Ellison


Dear Tom,
I am working on making a table for the coverage Level.
I am thinking of having this in my table:
CoverageID - primary key
Units
Dollars
What was u thinking, how would u design the table if it was your
database. I can send you this database to you, if you are outsider
(living outside of OH or Holmes County.
Tom Ellison wrote:
Dear Lincoln:

You have:

[To] - [From] AS Days

Then you proceed to use Days in:

Units * Days / 365 AS Equals

This is what you must not do. So, I suggest for this you use:

Units * ([To] - [From]) / 365 AS Equals

The [Coverage Level] appears to work from a bracketed table of
values:

Up To Use This
2 5000
2.5 11000
3 18000
3.5 26000
above 35000

It is often best to put this into a separate table and look up the
Level in that. Your IIf is complex and will be difficult to
maintain if the rates or levels change.

Tom Ellison


Tom Ellison wrote:
Dear Lincoln:

Your query, arranged for my reading preferences, so I can study
it:

SELECT [FirstName] & " " & [LastName] AS [Full Name],
Vehicles.Year, Vehicles.Make, Vehicles.Model,
Vehicles.From, Vehicles.To, [To]-[From] AS Days,
Vehicles.Units, Units * Days / 365 AS Equals,
164.18 AS Rate, Equals * Rate AS Premium,
IIf((UNITS = 1 And MODEL = "Trailer"), 5000) AS Trailer,
IIf(Units > 3.5, "35000", IIf(Units > 3,"26000", IIf(Units >
2.5,"18000",
IIf(Units > 2, "11000", IIf(Units > 1,"5000",0))))) AS
[Coverage Level],
(Trailer + [Coverage Level]) AS Total
FROM Clients
INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client
ID];

It appears you are expecting to be able to use columns you derive
as columns in further derivations. That is, you have:

164.18 AS Rate

Then you proceed to use Rate as an established value:

[Equals] * [Rate] AS Premium

Similarly you use [Coverage Level].

This will now work. You can either insert the value (i.e. 164.18
instead of Rate and that whole long IIf thing instead of
[Coverage Level]) or you can save the query without the columns
Premium and Total and then create another query based on that one
which calculates further using those values.

The reason is "sequence". The query does not evaluate from top
to bottom. It is better to think of it as executing all at once.
There are, therefore, no antecedents.

"Days" has the same problem, which I didn't see at first.

Tom Ellison
Explain the buttom part more. the Equal field. here is the
formula for the equals field: Equals: [Units]*[Days]/365 then the
premium field Calcute the equals field and the rate field to get
the cost for each vehicle. If a owner has 2.5 units on his vihicle
then the premium field will calcute the equals field and the rate
to get that vehicle. Each vehicle has different units on it too. I
hope this will be helpful to u
Lincoln beachy

 

[Lincoln Beachy]

Tom,
here is the code: SELECT MAX(CL.Dollars)
WHERE Clients.[Units] < CL.[units]
FROM [Coverage Level] units
I hit run button than that message comes up, when I hit ok button then the
select is highlighted in the code. I am here at work and the database is at
home so if you are going to send some more code(s) I can not test it out
until I come home.
Lincoln

Dear Lincoln:

My post asked you to post your code as well as giving the error message. I
cannot find a possible error without anywhere to look for it.

Tom Ellison

Tom,
Sorry, about this problem: The message: The syntax of the subquery in this
expression is incorrect.
Check the subquery's syntax and enclose the subquery in the parantheses.
am I not doing something right or what? I will be checking this newsqroup
later on today.
Lincoln Beachy

Dear Lincoln:

If you do not post the code you have currently and explain just what the
problem is, it will not be possible for me to help. Just saying you are
"having trouble with the code" doesn't tell me much at all.

Tom Ellison


Tom,
I did what you told me to do. I went in and query named CL and went into
the test query and typed in the code that u provided to me but I am
having trouble with the code. How should I do it. I tried somethings but
I can not get around the Problem.
Lincoln beachy
Tom Ellison wrote:
Dear Lincoln:

You can call this column [Up To] or Units. It doesn't matter very much
which. It's just one column.

Tom Ellison


Tom
Do I need a up to field in the table or not?
I have in the table:
units field and dollars field
what else do I need yet that I do not have? Thank you for your help\
Lincoln BEachy
Tom Ellison wrote:
Dear Lincoln:

If it were my table, I wouldn't use the CoverageID. I would make the
Units the primary key. This column would be unique, for sure. And
this column is the one by which you look up rows when you need them.
So, for performance, it would be best to have such an index. There's
no need for the additional index or column.

I do occasionally ask posters for a copy of their databases. I do
this when I need clarification of an issue and that would take too
long to get exchanging text messages, or when I want to test a
difficult solution against the actual database being built. Neither
has come up yet in our exchange here. So, I'll decline your offer
for now.

The retrieval of the [Coverage Level].Dollars could be done with a
subquery:

SELECT MAX(CL.Dollars)
WHERE Clients.[Units] < CL.[Up To]
FROM [Coverage Level] CL

This works with your table if you enter the data somewhat differently
than shown previously:

Up To Dollars
0 5000
2 11000
2.5 18000
3 26000
3.5 35000

I recommend you test this carefully, especially at the values in the
Up To column

Tom Ellison


Dear Tom,
I am working on making a table for the coverage Level.
I am thinking of having this in my table:
CoverageID - primary key
Units
Dollars
What was u thinking, how would u design the table if it was your
database. I can send you this database to you, if you are outsider
(living outside of OH or Holmes County.
Tom Ellison wrote:
Dear Lincoln:

You have:

[To] - [From] AS Days

Then you proceed to use Days in:

Units * Days / 365 AS Equals

This is what you must not do. So, I suggest for this you use:

Units * ([To] - [From]) / 365 AS Equals

The [Coverage Level] appears to work from a bracketed table of
values:

Up To Use This
2 5000
2.5 11000
3 18000
3.5 26000
above 35000

It is often best to put this into a separate table and look up the
Level in that. Your IIf is complex and will be difficult to
maintain if the rates or levels change.

Tom Ellison


Tom Ellison wrote:
Dear Lincoln:

Your query, arranged for my reading preferences, so I can study
it:

SELECT [FirstName] & " " & [LastName] AS [Full Name],
Vehicles.Year, Vehicles.Make, Vehicles.Model,
Vehicles.From, Vehicles.To, [To]-[From] AS Days,
Vehicles.Units, Units * Days / 365 AS Equals,
164.18 AS Rate, Equals * Rate AS Premium,
IIf((UNITS = 1 And MODEL = "Trailer"), 5000) AS Trailer,
IIf(Units > 3.5, "35000", IIf(Units > 3,"26000", IIf(Units >
2.5,"18000",
IIf(Units > 2, "11000", IIf(Units > 1,"5000",0))))) AS
[Coverage Level],
(Trailer + [Coverage Level]) AS Total
FROM Clients
INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client
ID];

It appears you are expecting to be able to use columns you derive
as columns in further derivations. That is, you have:

164.18 AS Rate

Then you proceed to use Rate as an established value:

[Equals] * [Rate] AS Premium

Similarly you use [Coverage Level].

This will now work. You can either insert the value (i.e. 164.18
instead of Rate and that whole long IIf thing instead of
[Coverage Level]) or you can save the query without the columns
Premium and Total and then create another query based on that one
which calculates further using those values.

The reason is "sequence". The query does not evaluate from top
to bottom. It is better to think of it as executing all at once.
There are, therefore, no antecedents.

"Days" has the same problem, which I didn't see at first.

Tom Ellison
Explain the buttom part more. the Equal field. here is the
formula for the equals field: Equals: [Units]*[Days]/365 then the
premium field Calcute the equals field and the rate field to get
the cost for each vehicle. If a owner has 2.5 units on his vihicle
then the premium field will calcute the equals field and the rate
to get that vehicle. Each vehicle has different units on it too. I
hope this will be helpful to u
Lincoln beachy

 

[Tom Ellison]

Dear Lincoln:

I don't think this is a complete query, but perhaps just a subquery. I'll
need the whole thing.

Tom Ellison

Tom,
here is the code: SELECT MAX(CL.Dollars)
WHERE Clients.[Units] < CL.[units]
FROM [Coverage Level] units
I hit run button than that message comes up, when I hit ok button then the
select is highlighted in the code. I am here at work and the database is
at
home so if you are going to send some more code(s) I can not test it out
until I come home.
Lincoln

Dear Lincoln:

My post asked you to post your code as well as giving the error message.
I
cannot find a possible error without anywhere to look for it.

Tom Ellison

Tom,
Sorry, about this problem: The message: The syntax of the subquery in
this
expression is incorrect.
Check the subquery's syntax and enclose the subquery in the parantheses.
am I not doing something right or what? I will be checking this
newsqroup
later on today.
Lincoln Beachy

Tom Ellison wrote:
Dear Lincoln:

If you do not post the code you have currently and explain just what
the
problem is, it will not be possible for me to help. Just saying you
are
"having trouble with the code" doesn't tell me much at all.

Tom Ellison


Tom,
I did what you told me to do. I went in and query named CL and went
into
the test query and typed in the code that u provided to me but I am
having trouble with the code. How should I do it. I tried somethings
but
I can not get around the Problem.
Lincoln beachy
Tom Ellison wrote:
Dear Lincoln:

You can call this column [Up To] or Units. It doesn't matter very
much
which. It's just one column.

Tom Ellison


Tom
Do I need a up to field in the table or not?
I have in the table:
units field and dollars field
what else do I need yet that I do not have? Thank you for your help\
Lincoln BEachy
Tom Ellison wrote:
Dear Lincoln:

If it were my table, I wouldn't use the CoverageID. I would make
the
Units the primary key. This column would be unique, for sure. And
this column is the one by which you look up rows when you need
them.
So, for performance, it would be best to have such an index.
There's
no need for the additional index or column.

I do occasionally ask posters for a copy of their databases. I do
this when I need clarification of an issue and that would take too
long to get exchanging text messages, or when I want to test a
difficult solution against the actual database being built.
Neither
has come up yet in our exchange here. So, I'll decline your offer
for now.

The retrieval of the [Coverage Level].Dollars could be done with a
subquery:

SELECT MAX(CL.Dollars)
WHERE Clients.[Units] < CL.[Up To]
FROM [Coverage Level] CL

This works with your table if you enter the data somewhat
differently
than shown previously:

Up To Dollars
0 5000
2 11000
2.5 18000
3 26000
3.5 35000

I recommend you test this carefully, especially at the values in
the
Up To column

Tom Ellison


Dear Tom,
I am working on making a table for the coverage Level.
I am thinking of having this in my table:
CoverageID - primary key
Units
Dollars
What was u thinking, how would u design the table if it was your
database. I can send you this database to you, if you are outsider
(living outside of OH or Holmes County.
Tom Ellison wrote:
Dear Lincoln:

You have:

[To] - [From] AS Days

Then you proceed to use Days in:

Units * Days / 365 AS Equals

This is what you must not do. So, I suggest for this you use:

Units * ([To] - [From]) / 365 AS Equals

The [Coverage Level] appears to work from a bracketed table of
values:

Up To Use This
2 5000
2.5 11000
3 18000
3.5 26000
above 35000

It is often best to put this into a separate table and look up
the
Level in that. Your IIf is complex and will be difficult to
maintain if the rates or levels change.

Tom Ellison


Tom Ellison wrote:
Dear Lincoln:

Your query, arranged for my reading preferences, so I can study
it:

SELECT [FirstName] & " " & [LastName] AS [Full Name],
Vehicles.Year, Vehicles.Make, Vehicles.Model,
Vehicles.From, Vehicles.To, [To]-[From] AS Days,
Vehicles.Units, Units * Days / 365 AS Equals,
164.18 AS Rate, Equals * Rate AS Premium,
IIf((UNITS = 1 And MODEL = "Trailer"), 5000) AS Trailer,
IIf(Units > 3.5, "35000", IIf(Units > 3,"26000", IIf(Units >
2.5,"18000",
IIf(Units > 2, "11000", IIf(Units > 1,"5000",0))))) AS
[Coverage Level],
(Trailer + [Coverage Level]) AS Total
FROM Clients
INNER JOIN Vehicles ON Clients.[Client ID] = Vehicles.[Client
ID];

It appears you are expecting to be able to use columns you
derive
as columns in further derivations. That is, you have:

164.18 AS Rate

Then you proceed to use Rate as an established value:

[Equals] * [Rate] AS Premium

Similarly you use [Coverage Level].

This will now work. You can either insert the value (i.e.
164.18
instead of Rate and that whole long IIf thing instead of
[Coverage Level]) or you can save the query without the columns
Premium and Total and then create another query based on that
one
which calculates further using those values.

The reason is "sequence". The query does not evaluate from top
to bottom. It is better to think of it as executing all at
once.
There are, therefore, no antecedents.

"Days" has the same problem, which I didn't see at first.

Tom Ellison
Explain the buttom part more. the Equal field. here is the
formula for the equals field: Equals: [Units]*[Days]/365 then
the
premium field Calcute the equals field and the rate field to get
the cost for each vehicle. If a owner has 2.5 units on his
vihicle
then the premium field will calcute the equals field and the
rate
to get that vehicle. Each vehicle has different units on it too.
I
hope this will be helpful to u
Lincoln beachy