this is actually an insurance policy administration database application that
i maintain and am running into problems adding additional features.

the table i illustrated is called group accounts receivable another table is
used to house tax information (group tax info), and the main table(physician
rate information) houses policy information.

all three tables are keyed off an "advice number" which is unique. I think I
do have a big problem because I have to modify this thing to answer questions
that would be easy to answer if the application was normalized or relational.

You point me in the right direction because I think I should have the
illustrated table have as fields the advice#, pmt amt (amount insured should
pay), pmt due date, date posted, item (check #), debit(amount actualy paid),
credit (returned check, etc) and then I would have a 1 to many relationship.

The table I illustratied which is called the group accounts receivable table
is very similar in design to the group tax info table. for instance in the
group tax info table i house taxes for each debit.

I will check back later on and submit my new table design. Based on the
information you proved I can probably have the accounts receivable and tax
info tables in one table.

Thanks again

all of you imparted extremly helpful information. I know I will need
additional assistance.




"Allen Browne" wrote:

The repeating fields appear to be:
Date
Posted
Item
Debit
Credit

Therefore you will need those fields in a related table, so that one of your
orignal entries can have many rows in this related table.

It's not clear where else the one-to-many relations may be needed. What's
the "name" and "number"? Is this like a client and an account number? If so,
could there be cases where one client could have multiple accounts? If so,
you need to start with a tblClient (client information, with a ClientID
primary key), and a tblAccount (with an AccountID primary key, and a
ClientID field to tell which client this account belongs to.)

Now your main table seems to be for transactions on these accounts. One
account has many transactions over time. I'm not sure if there is any
meaning to the different Item columns, or if you just use the next available
one. If they are for recording different kinds of items, you would need a
table of items with an ItemID primary key.

Now the transaction table would have fields like this:
- AccountID relates to tblAccount.AccountID
- TransDate Date/Time when this transaction occurred.
- Posted whatever this is (a date?)
- TransDirection Number 1 or -1 (for credit or debit.) Required.
- Amount Currency how much
- ItemID relates to the Item table if you need to record this.

You have now set up relationships so that:
- one client can have many accounts;
- one account can have many transactions;
and possibly:
- one type of item can appear in many transactions.

You will interface this with a main form bound to the account table, and a
subform bound to the transaction table. For each transaction, you enter
another row in the continuous view subform. So you have solved the problem
with many rows in the transaction table rather than many columns as in your
spreadsheet-like table.

The benefit of this relational design is that it's dead easy to query stuff.
For example, you have just one column to search to find all transactions for
a date.

In query design, type an expression like this into the Field row:
[TransDirection] * [Amount]
Since TransDirection is either 1 or -1 (for credit or debit), this gives you
a column you can sum to get the net transactions.

Well, I've made lots of assumptions about your data, but hopefully that's
enough of an example to set you thinking in a useful direction to redesign
your table.

BTW, when you redesign your tables, you want to avoid some of those field
names, such as Name, Number, and Date:
http://allenbrowne.com/Ap****ueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mykas_Robi" wrote in message
...
I have a table that consists of the following fields
name, number, effect date, term date, date posted1, item1, debit1,
credit1...date posted13, item13, debit13, credit13. I submitted a
question
on query design and was informed that my table is poorly designed.

I need to know what to break this table up that so that it is designed
properly. Please any assistance would be greatly appreciated.

Thanks and have a nice day.