HELP!!! wrong design led to a too long query

Hello,

I have a big table that contains details of prosecutions.
It has the name of the player that sued, his details,
date and there are 5 types of paragraphs:
for each type we have 3 evaluations and 5 payments. Each
one of them has a date and a number. One of the 5
paragraphs has additional details.

I have a form to enter the data to the table - it has a
field for each detail - that means I have (3+5)*5 = 40
fields of dates and 40 fields of numbers.

I have another form - a menu, that the user can choose a
range of dates that - only evaluations or payments that
are between those dates should appear in the report that
is opened after pressing a button.

I made a query using "Or" on all those fields but when I
tried to run it, an error message appeared saying it's
too long.

I understand that I didn't plan it properly - this is my
first project and I learned by myself. I alreay have data
in the big table, I hope they won't have to re-enter it,
BUT the most important thing is to solve the problem.

Thanks a lot,
Liat

Not knowing all the details of what you are doing, it's a bit hard to
suggest how to "fix" the SQL.

However, a strong word of advice.... do not continue down this path. The
problem that you now face is just the beginning of the problems that you'll
face because your tables are not normalized. If you "fix" this problem, the
intertia will take you away from fixing the real problem, and then you'll
need to "fix" the next problem, and the next one, and the next one. What
will you do if you "fix" the problem, only to find out that you need another
field, and that you have the same problem all over again?

The momentary pain of fixing the real problem right now will be more than
forgotten when you exult in being able to make wonderful queries and reports
later on.

--

Ken Snell
MS ACCESS MVP

"Liat" wrote in message
...
Hello,

I have a big table that contains details of prosecutions.
It has the name of the player that sued, his details,
date and there are 5 types of paragraphs:
for each type we have 3 evaluations and 5 payments. Each
one of them has a date and a number. One of the 5
paragraphs has additional details.

I have a form to enter the data to the table - it has a
field for each detail - that means I have (3+5)*5 = 40
fields of dates and 40 fields of numbers.

I have another form - a menu, that the user can choose a
range of dates that - only evaluations or payments that
are between those dates should appear in the report that
is opened after pressing a button.

I made a query using "Or" on all those fields but when I
tried to run it, an error message appeared saying it's
too long.

I understand that I didn't plan it properly - this is my
first project and I learned by myself. I alreay have data
in the big table, I hope they won't have to re-enter it,
BUT the most important thing is to solve the problem.

Thanks a lot,
Liat

Thanks for your answer.
Could you please explain a bout the normalization?


-----Original Message-----
Not knowing all the details of what you are doing, it's
a bit hard to
suggest how to "fix" the SQL.

However, a strong word of advice.... do not continue
down this path. The
problem that you now face is just the beginning of the
problems that you'll
face because your tables are not normalized. If
you "fix" this problem, the
intertia will take you away from fixing the real
problem, and then you'll
need to "fix" the next problem, and the next one, and
the next one. What
will you do if you "fix" the problem, only to find out
that you need another
field, and that you have the same problem all over again?

The momentary pain of fixing the real problem right now
will be more than
forgotten when you exult in being able to make wonderful
queries and reports
later on.

--

Ken Snell
MS ACCESS MVP

"Liat" wrote in
message
...
Hello,

I have a big table that contains details of
prosecutions.
It has the name of the player that sued, his details,
date and there are 5 types of paragraphs:
for each type we have 3 evaluations and 5 payments.
Each
one of them has a date and a number. One of the 5
paragraphs has additional details.

I have a form to enter the data to the table - it has a
field for each detail - that means I have (3+5)*5 = 40
fields of dates and 40 fields of numbers.

I have another form - a menu, that the user can choose
a
range of dates that - only evaluations or payments that
are between those dates should appear in the report
that
is opened after pressing a button.

I made a query using "Or" on all those fields but when
I
tried to run it, an error message appeared saying it's
too long.

I understand that I didn't plan it properly - this is
my
first project and I learned by myself. I alreay have
data
in the big table, I hope they won't have to re-enter
it,
BUT the most important thing is to solve the problem.

Thanks a lot,
Liat


.

It means that your tables contain just a few fields that have distinct types
for their values, not a field for each possible value.

For example, from your description, you state that you need to store these
data items in your tables:
Player Name
Suit Identification
Suit Date
Suit Details
Paragraph Types
Evaluations for Paragraph Types
Payments for Paragraph Types

and so on.

So, you might start with tables like this:

tblPlayers
PlayerID (primary key)
PlayerName
PlayerAddress
(etc.)

tblSuits
SuitID (primary key)
SuitDate
SuitComments
(etc.)

tblPlayerSuits
PlaySuitID (primary key)
PlayerID
SuitID

tblSuitParas
SuitParasID (primary key)
PlaySuitID (composite primary key with ParagraphNum)
ParagraphNum (composite primary key with PlaySuitID)

tblSuitEvals
SuitParasID (composite primary key with EvalID)
EvalID (composite primary key with SuitParasID)
EvalQuantity

tblSuitPays
SuitParasID (composite primary key with PaymentID)
PaymentID (composite primary key with SuitParasID)
PaymentAmt

and so on. These tables would be linked using the fields that have the same
names.

I urge you to get a book on relational database design and use its
recommendations for setting up your tables. This normalized structure makes
it extremely easy to write queries for the different criteria that you want
to use.
--

Ken Snell
MS ACCESS MVP


"Liat" wrote in message
...
Thanks for your answer.
Could you please explain a bout the normalization?


-----Original Message-----
Not knowing all the details of what you are doing, it's
a bit hard to
suggest how to "fix" the SQL.

However, a strong word of advice.... do not continue
down this path. The
problem that you now face is just the beginning of the
problems that you'll
face because your tables are not normalized. If
you "fix" this problem, the
intertia will take you away from fixing the real
problem, and then you'll
need to "fix" the next problem, and the next one, and
the next one. What
will you do if you "fix" the problem, only to find out
that you need another
field, and that you have the same problem all over again?

The momentary pain of fixing the real problem right now
will be more than
forgotten when you exult in being able to make wonderful
queries and reports
later on.

--

Ken Snell
MS ACCESS MVP

"Liat" wrote in
message
...
Hello,

I have a big table that contains details of
prosecutions.
It has the name of the player that sued, his details,
date and there are 5 types of paragraphs:
for each type we have 3 evaluations and 5 payments.
Each
one of them has a date and a number. One of the 5
paragraphs has additional details.

I have a form to enter the data to the table - it has a
field for each detail - that means I have (3+5)*5 = 40
fields of dates and 40 fields of numbers.

I have another form - a menu, that the user can choose
a
range of dates that - only evaluations or payments that
are between those dates should appear in the report
that
is opened after pressing a button.

I made a query using "Or" on all those fields but when
I
tried to run it, an error message appeared saying it's
too long.

I understand that I didn't plan it properly - this is
my
first project and I learned by myself. I alreay have
data
in the big table, I hope they won't have to re-enter
it,
BUT the most important thing is to solve the problem.

Thanks a lot,
Liat


.

Thanks. Will I need another table that combines all of
these?
How does it help me with the form and the query? I will
still need to use "Or", will I?
And with the form, I will need to have it using sub-forms
that will bring all the information?


-----Original Message-----
It means that your tables contain just a few fields that
have distinct types
for their values, not a field for each possible value.

For example, from your description, you state that you
need to store these
data items in your tables:
Player Name
Suit Identification
Suit Date
Suit Details
Paragraph Types
Evaluations for Paragraph Types
Payments for Paragraph Types

and so on.

So, you might start with tables like this:

tblPlayers
PlayerID (primary key)
PlayerName
PlayerAddress
(etc.)

tblSuits
SuitID (primary key)
SuitDate
SuitComments
(etc.)

tblPlayerSuits
PlaySuitID (primary key)
PlayerID
SuitID

tblSuitParas
SuitParasID (primary key)
PlaySuitID (composite primary key with
ParagraphNum)
ParagraphNum (composite primary key with
PlaySuitID)

tblSuitEvals
SuitParasID (composite primary key with EvalID)
EvalID (composite primary key with SuitParasID)
EvalQuantity

tblSuitPays
SuitParasID (composite primary key with
PaymentID)
PaymentID (composite primary key with
SuitParasID)
PaymentAmt

and so on. These tables would be linked using the fields
that have the same
names.

I urge you to get a book on relational database design
and use its
recommendations for setting up your tables. This
normalized structure makes
it extremely easy to write queries for the different
criteria that you want
to use.
--

Ken Snell
MS ACCESS MVP


"Liat" wrote in
message
...
Thanks for your answer.
Could you please explain a bout the normalization?


-----Original Message-----
Not knowing all the details of what you are doing,
it's
a bit hard to
suggest how to "fix" the SQL.

However, a strong word of advice.... do not continue
down this path. The
problem that you now face is just the beginning of the
problems that you'll
face because your tables are not normalized. If
you "fix" this problem, the
intertia will take you away from fixing the real
problem, and then you'll
need to "fix" the next problem, and the next one, and
the next one. What
will you do if you "fix" the problem, only to find out
that you need another
field, and that you have the same problem all over
again?

The momentary pain of fixing the real problem right
now
will be more than
forgotten when you exult in being able to make
wonderful
queries and reports
later on.

--

Ken Snell
MS ACCESS MVP

"Liat" wrote in
message
...
Hello,

I have a big table that contains details of
prosecutions.
It has the name of the player that sued, his
details,
date and there are 5 types of paragraphs:
for each type we have 3 evaluations and 5 payments.
Each
one of them has a date and a number. One of the 5
paragraphs has additional details.

I have a form to enter the data to the table - it
has a
field for each detail - that means I have (3+5)*5 =
40
fields of dates and 40 fields of numbers.

I have another form - a menu, that the user can
choose
a
range of dates that - only evaluations or payments
that
are between those dates should appear in the report
that
is opened after pressing a button.

I made a query using "Or" on all those fields but
when
I
tried to run it, an error message appeared saying
it's
too long.

I understand that I didn't plan it properly - this
is
my
first project and I learned by myself. I alreay have
data
in the big table, I hope they won't have to re-enter
it,
BUT the most important thing is to solve the
problem.

Thanks a lot,
Liat


.



.

No, you would not have another table that combines all these tables. These
tables should exist on their own.

You would use a query to select the values from the different tables. As for
whether you'd need an "Or" in the query, I cannot say, as I have no idea of
what your query is trying to do.

I recommend that you obtain a book on ACCESS and read about setting up
tables and queries. I think that will help you get started in the best
direction possible.

--

Ken Snell
MS ACCESS MVP

wrote in message
...
Thanks. Will I need another table that combines all of
these?
How does it help me with the form and the query? I will
still need to use "Or", will I?
And with the form, I will need to have it using sub-forms
that will bring all the information?


-----Original Message-----
It means that your tables contain just a few fields that
have distinct types
for their values, not a field for each possible value.

For example, from your description, you state that you
need to store these
data items in your tables:
Player Name
Suit Identification
Suit Date
Suit Details
Paragraph Types
Evaluations for Paragraph Types
Payments for Paragraph Types

and so on.

So, you might start with tables like this:

tblPlayers
PlayerID (primary key)
PlayerName
PlayerAddress
(etc.)

tblSuits
SuitID (primary key)
SuitDate
SuitComments
(etc.)

tblPlayerSuits
PlaySuitID (primary key)
PlayerID
SuitID

tblSuitParas
SuitParasID (primary key)
PlaySuitID (composite primary key with
ParagraphNum)
ParagraphNum (composite primary key with
PlaySuitID)

tblSuitEvals
SuitParasID (composite primary key with EvalID)
EvalID (composite primary key with SuitParasID)
EvalQuantity

tblSuitPays
SuitParasID (composite primary key with
PaymentID)
PaymentID (composite primary key with
SuitParasID)
PaymentAmt

and so on. These tables would be linked using the fields
that have the same
names.

I urge you to get a book on relational database design
and use its
recommendations for setting up your tables. This
normalized structure makes
it extremely easy to write queries for the different
criteria that you want
to use.
--

Ken Snell
MS ACCESS MVP


"Liat" wrote in
message
...
Thanks for your answer.
Could you please explain a bout the normalization?


-----Original Message-----
Not knowing all the details of what you are doing,
it's
a bit hard to
suggest how to "fix" the SQL.

However, a strong word of advice.... do not continue
down this path. The
problem that you now face is just the beginning of the
problems that you'll
face because your tables are not normalized. If
you "fix" this problem, the
intertia will take you away from fixing the real
problem, and then you'll
need to "fix" the next problem, and the next one, and
the next one. What
will you do if you "fix" the problem, only to find out
that you need another
field, and that you have the same problem all over
again?

The momentary pain of fixing the real problem right
now
will be more than
forgotten when you exult in being able to make
wonderful
queries and reports
later on.

--

Ken Snell
MS ACCESS MVP

"Liat" wrote in
message
...
Hello,

I have a big table that contains details of
prosecutions.
It has the name of the player that sued, his
details,
date and there are 5 types of paragraphs:
for each type we have 3 evaluations and 5 payments.
Each
one of them has a date and a number. One of the 5
paragraphs has additional details.

I have a form to enter the data to the table - it
has a
field for each detail - that means I have (3+5)*5 =
40
fields of dates and 40 fields of numbers.

I have another form - a menu, that the user can
choose
a
range of dates that - only evaluations or payments
that
are between those dates should appear in the report
that
is opened after pressing a button.

I made a query using "Or" on all those fields but
when
I
tried to run it, an error message appeared saying
it's
too long.

I understand that I didn't plan it properly - this
is
my
first project and I learned by myself. I alreay have
data
in the big table, I hope they won't have to re-enter
it,
BUT the most important thing is to solve the
problem.

Thanks a lot,
Liat


.



.

OK, I better do that.
thank you very much for your help,
Liat
-----Original Message-----
No, you would not have another table that combines all
these tables. These
tables should exist on their own.

You would use a query to select the values from the
different tables. As for
whether you'd need an "Or" in the query, I cannot say,
as I have no idea of
what your query is trying to do.

I recommend that you obtain a book on ACCESS and read
about setting up
tables and queries. I think that will help you get
started in the best
direction possible.

--

Ken Snell
MS ACCESS MVP

wrote in message
...
Thanks. Will I need another table that combines all of
these?
How does it help me with the form and the query? I will
still need to use "Or", will I?
And with the form, I will need to have it using sub-
forms
that will bring all the information?


-----Original Message-----
It means that your tables contain just a few fields
that
have distinct types
for their values, not a field for each possible value.

For example, from your description, you state that you
need to store these
data items in your tables:
Player Name
Suit Identification
Suit Date
Suit Details
Paragraph Types
Evaluations for Paragraph Types
Payments for Paragraph Types

and so on.

So, you might start with tables like this:

tblPlayers
PlayerID (primary key)
PlayerName
PlayerAddress
(etc.)

tblSuits
SuitID (primary key)
SuitDate
SuitComments
(etc.)

tblPlayerSuits
PlaySuitID (primary key)
PlayerID
SuitID

tblSuitParas
SuitParasID (primary key)
PlaySuitID (composite primary key with
ParagraphNum)
ParagraphNum (composite primary key with
PlaySuitID)

tblSuitEvals
SuitParasID (composite primary key with
EvalID)
EvalID (composite primary key with
SuitParasID)
EvalQuantity

tblSuitPays
SuitParasID (composite primary key with
PaymentID)
PaymentID (composite primary key with
SuitParasID)
PaymentAmt

and so on. These tables would be linked using the
fields
that have the same
names.

I urge you to get a book on relational database design
and use its
recommendations for setting up your tables. This
normalized structure makes
it extremely easy to write queries for the different
criteria that you want
to use.
--

Ken Snell
MS ACCESS MVP


"Liat" wrote in
message
...
Thanks for your answer.
Could you please explain a bout the normalization?


-----Original Message-----
Not knowing all the details of what you are doing,
it's
a bit hard to
suggest how to "fix" the SQL.

However, a strong word of advice.... do not
continue
down this path. The
problem that you now face is just the beginning of
the
problems that you'll
face because your tables are not normalized. If
you "fix" this problem, the
intertia will take you away from fixing the real
problem, and then you'll
need to "fix" the next problem, and the next one,
and
the next one. What
will you do if you "fix" the problem, only to find
out
that you need another
field, and that you have the same problem all over
again?

The momentary pain of fixing the real problem right
now
will be more than
forgotten when you exult in being able to make
wonderful
queries and reports
later on.

--

Ken Snell
MS ACCESS MVP

"Liat" wrote
in
message
...
Hello,

I have a big table that contains details of
prosecutions.
It has the name of the player that sued, his
details,
date and there are 5 types of paragraphs:
for each type we have 3 evaluations and 5
payments.
Each
one of them has a date and a number. One of the 5
paragraphs has additional details.

I have a form to enter the data to the table - it
has a
field for each detail - that means I have (3+5)
*5 =
40
fields of dates and 40 fields of numbers.

I have another form - a menu, that the user can
choose
a
range of dates that - only evaluations or
payments
that
are between those dates should appear in the
report
that
is opened after pressing a button.

I made a query using "Or" on all those fields but
when
I
tried to run it, an error message appeared saying
it's
too long.

I understand that I didn't plan it properly -
this
is
my
first project and I learned by myself. I alreay
have
data
in the big table, I hope they won't have to re-
enter
it,
BUT the most important thing is to solve the
problem.

Thanks a lot,
Liat


.



.



.