Table or Query ?

[ashg657]

Hi there.
I have been asked to create an accounts based database.
What I need is a design which will allow me to have 2 records for each
transaction, 1 debit record and 1 credit record.
My question is, is it more efficient to have a table with double the amount
of records in it OR make both credit and debit entries go into a single
record, then get them into the correct format using a query?
I know the first solution will be more efficient and quicker execution,
however it will make the database grow in size much quicker than the proposed
query based solution.

Any help greatly appreciated.
One further question.....Again, which is more efficient? Queries setup in
the backend under queries section or select statements in design view of
forms/reports in the record source properties?

Thanks!

 

[Rick Brandt]

Hi there.
I have been asked to create an accounts based database.
What I need is a design which will allow me to have 2 records for each
transaction, 1 debit record and 1 credit record.
My question is, is it more efficient to have a table with double the
amount of records in it OR make both credit and debit entries go into
a single record, then get them into the correct format using a query?
I know the first solution will be more efficient and quicker
execution, however it will make the database grow in size much
quicker than the proposed query based solution.

The difference in size would be minimal. Two records is the better approach.

Any help greatly appreciated.
One further question.....Again, which is more efficient? Queries
setup in the backend under queries section or select statements in
design view of forms/reports in the record source properties?

No difference. SQL Statements in Forms and Reports ARE queries saved as objects
with compiled execution plans. They are just given system names and are not
shown in the db window.

 

[ashg657]

Ah excellent. Thanks for that Rick!

The difference in size would be minimal. Two records is the better approach.


No difference. SQL Statements in Forms and Reports ARE queries saved as objects
with compiled execution plans. They are just given system names and are not
shown in the db window.

 

[Al Campagna]

ashg657,
Rick is correct regarding any significant performance difference, but I'd like to
suggest the single record method.
Purely as a matter of "style" I use the 1 line...
Transaction Debit Credit Balance
1625 +200.00 200.00
1626 -50.00 150.00

I think this setup is more convenient when creating new queries, forms, reports... when
all the data to calc the balance is in one table.
Sum(Credit) - Sum(Debit)
You say "tomayto", I say "tomahto"... :-D
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Jeff Boyce]

This appears to be an "accounting" application. Is there a reason you are
re-creating a wheel (there are many relatively inexpensive accounting
software packages that could save you a great deal of time.)?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/