Table Structure: How to avoid entering same data twice?

[JapanFreak]

Dear All,

I have a general question regarding my draft of a data model. Actually, I would like to make sure that I am not making a major mistake before I move on. Please find attached a ppt-File, which shows the relevant section and visualizes my explanations below.

Background

Table 1, called “tbl_AccountingEntry” contains accounting transactions. Table 2, called “tbl_Trades”, contains details about trades in stocks which relate to certain transactions in Table 1. Tables “tbl_Accounting” and “tbl_TransactionDetail” connect Table 1 and the various tables containing details.



Just as a remark:

• Table 2 is not the only table containing details for transactions listed in Table 1.
• Not for every transaction in Table 1 there are details in Table 2.
• Not every trade listed in Table 2 leads to a cash movement (e.g., in the case of stock splits) and thus does not necessarily lead to a entry in Table 1.

This structure allows me – inter alia – to do two things:

1) The query…

[FONT=Arial]SELECT Account, sum(Amount) AS Amount [/FONT][FONT=Arial]FROM tbl_AccountingEntry [/FONT][FONT=Arial]WHERE dDate <= #12/31/2007# [/FONT][FONT=Arial]GROUP BY Account;[/FONT]

…allows listing all accounts and their balance at a certain point in time (i.e., kind of a balance sheet and income statement).

2) The query…

[FONT=Arial]SELECT Ticker, sum(Quantity) AS Quantity [/FONT][FONT=Arial]FROM tbl_Trades [/FONT][FONT=Arial]WHERE dDate <= #12/31/2007# [/FONT][FONT=Arial]GROUP BY Ticker;[/FONT]

…allows determining the composition of the stock portfolio at a certain point in time.

Question

On back of this background information, I have the following question. If I enter a new stock trade (e.g., 100 stocks of Ticker “ABC” for a price of $20/share), I type in (amongst other details) the following three datasets:

Table 2:
Quantity = 100
Price = $20

and

Table 1:
TransactionNo = 1
Transaction ID = 1
Credit/Debit = Debit
Account Debit = Securities
Amount Debit = $2,000

and
TransactionNo = 1
TransactionID = 2
Credit/Debit = Credit
Account Credit = Cash
Amount Credit = $2000

However, Amount (in Table 1) = Quantity x Price (in Table 2) and thus in a certain sense the same data is entered twice. This is from my point of view a potential source of error but I do not find an elegant way to avoid it. (Actually, I have the similar problems with the other tables containing details.)

My solution would be to enter the data via a Userform and to make sure that this is checking the data for consistency. But: Is that an intelligent way of solving the problem or should I construct the data model completely differently?

I would be grateful for any idea or thought.

Best regards,

JapanFreak

 

[georgedwilkinson]

I'm going to be honest up front. I'm not going to directly answer your question (I don't think) because I don't understand it. I'm pretty sure though, that you don't have the tables set up correctly and it would be a major mistake to implement this (your words, not mine).

Your design and description of your design are obscure. You should use nouns that describe the whole recordset within a table as table names. You should never, ever, describe tables as Table1...TableN when you're asking for help. We shouldn't have to have a glossary to follow your logic.

On to details:
What is an "Accounting"? You have a table named that but it doesn't really describe anything you might want to put in a table. Accounting is a professional field whose participants and practices track/count profit, loss, cash, inventory, etc.

What is an "AccountingEntry"? Normally for people with an accounting background they would call that a journal entry, a ledger entry, or a transaction. You should too.

Where is your chart of accounts?

All complex data should be broken into its one to many components and named correctly to describe what is contained in the table. For instance, you probably need a table called "Transactions". My guess is that a single transaction from the "Transactions" table would have detail associated to it that wouldn't normally fit into a header type table like "Transactions". That would normally make you think of creating a detail table for transactions named "TransactionDetails". An individual transaction detail would be a further definition of one and only one transaction from the "Transactions" table. Further, each transaction in the "Transactions" table would be further defined by one or more "TransactionDetails". This is the basis for the relationship (on the relationships screen) between the 2 tables.

When you have the entities/tables laid out correctly and named correctly, you will then be able to explain them to forum members for help. However, you'll also find that Access expects the same thing we do and that the forms kinda just create themselves when you have the data modeled correctly.

If you don't have an accounting background and are building an accounting system, I totally recommend getting an education in Accounting before building the system. Or, just buy one, which would probably be much cheaper.

BTW, the accounting systems I have built have hundreds of tables.

For further help, please check the topic "data normalization" on this forum and/or google. When you're ready to move forward, please post back.