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

L

Liat

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
 
K

Ken Snell

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.
 
K

Ken Snell

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.
 
G

Guest

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?
 
K

Ken Snell

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top