A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

table redesign



 
 
Thread Tools Display Modes
  #1  
Old April 3rd, 2008, 07:17 PM posted to microsoft.public.access.tablesdbdesign
Mykas_Robi
external usenet poster
 
Posts: 12
Default table redesign

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.
  #2  
Old April 3rd, 2008, 07:56 PM posted to microsoft.public.access.tablesdbdesign
Evan Keel
external usenet poster
 
Posts: 46
Default table redesign

Don't know if I saw your original post but you have a big problem, namely
repeating columns (posted13, item13,etc.). We can't help until we know the
"facts" your database is attempting to store. What is your table about?

Evan

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



  #3  
Old April 3rd, 2008, 08:01 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default table redesign

From your description, you have ... a spreadsheet!

While you are pretty much limited to "adding columns" to add fields when you
work with a spreadsheet, Access is a relational database. You won't get
easy use of Access' relationally-oriented features/functions if you feed it
'sheet data.

If the terms "normalization" and "relational database" don't make much
sense, spend some time studying up on them before trying to design an Access
database.

Is there a reason you can't just use a spreadsheet?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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



  #4  
Old April 3rd, 2008, 10:11 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default table redesign

On Thu, 3 Apr 2008 11:17:00 -0700, Mykas_Robi
wrote:

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.


You should certainly have at least two tables in a one to many relationship.
Since you didn't indicate anything about the real-world situation or what
information this database models (other that it evidently has to do with
financial credits and debits) it's a bit hard to say specifically!

What real-life Enitity (person, thing or event) does this table represent?
What are debits and credits? Are debits and credits independent of one
another, or does each debit have an associated credit? More info please!
--

John W. Vinson [MVP]
  #5  
Old April 4th, 2008, 04:49 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default table redesign

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.


  #6  
Old April 4th, 2008, 08:16 PM posted to microsoft.public.access.tablesdbdesign
Mykas_Robi
external usenet poster
 
Posts: 12
Default table redesign

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.



  #7  
Old April 4th, 2008, 11:55 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default table redesign

On Fri, 4 Apr 2008 12:16:18 -0700, Mykas_Robi
wrote:

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.


You should consider this carefully. Those sound like two different kinds of
data, which should be in two many-side tables related to the accounts table.
--

John W. Vinson [MVP]
  #8  
Old April 5th, 2008, 03:38 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default table redesign

I think John's right: it would be better as 2 tables. What the person should
pay, and what they actually pay don't always match.

You will need to consider what possibilities you need to design for, e.g.:
- a person makes multiple payments on one policy (e.g. when struggling
financially)
- a person makes a single payment that covers multiple policies (e.g. one
check covering a guy and his wife.)
- a person prepays his policy (e.g. one check covers the next 3 payments
due, because he will be away.)
- a payment is partly check and partly cash (or card.)
and the really curly one:
- you need to receipt a payment for a policy that has not been created yet.

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

"John W. Vinson" wrote in message
...
On Fri, 4 Apr 2008 12:16:18 -0700, Mykas_Robi
wrote:

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.


You should consider this carefully. Those sound like two different kinds
of
data, which should be in two many-side tables related to the accounts
table.
--

John W. Vinson [MVP]


  #9  
Old April 5th, 2008, 06:16 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default table redesign

On Sat, 5 Apr 2008 10:38:18 +0800, "Allen Browne"
wrote:

I think John's right: it would be better as 2 tables. What the person should
pay, and what they actually pay don't always match.


Oh... you've had those clients too, Allen? g

--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.