structure of database to prepare report

[Michael Malinsky]

Re: structure of database to prepare report

[snip]
[color=blue]
> Hold it right there. What is driving the data? This should be your
> Chart of Accounts. This table should contain all the account numbers
> that you need. Yes the chart will contain totaling accounts especially
> for EOP/Y (End of Period/Year) totals. But these would be flagged as
> 'special' accounts that a query can ignore or just pick out these
> values.
>
> Your Chart of Accounts table would look like
> AccNum, Desc, Current, Type, EOPAccNum, EOP, EOY, PrevYear.[/color]

My trial balance will contain the following: AccNum, Desc, CYBalance,
PYBalance.

My question still rotates around the best way to assign each account
to a group or groups (remember an account may be in more than one
place in my report). If your reference to type is meant to help group
accounts, it will not work. I'm still thinking of having all groups
in one table then having a third table containing account numbers and
line numbers which would allow me to run queries via the Excel
function to place the proper numbers in the proper places.

I do appreciate the help your providing me with.

Thanks.
[color=blue]
>
> [snip 2 end][/color]

[Michael Malinsky]

Re: structure of database to prepare report

[snip]
[color=blue]
> Hold it right there. What is driving the data? This should be your
> Chart of Accounts. This table should contain all the account numbers
> that you need. Yes the chart will contain totaling accounts especially
> for EOP/Y (End of Period/Year) totals. But these would be flagged as
> 'special' accounts that a query can ignore or just pick out these
> values.
>
> Your Chart of Accounts table would look like
> AccNum, Desc, Current, Type, EOPAccNum, EOP, EOY, PrevYear.[/color]

My trial balance will contain the following: AccNum, Desc, CYBalance,
PYBalance.

My question still rotates around the best way to assign each account
to a group or groups (remember an account may be in more than one
place in my report). If your reference to type is meant to help group
accounts, it will not work. I'm still thinking of having all groups
in one table then having a third table containing account numbers and
line numbers which would allow me to run queries via the Excel
function to place the proper numbers in the proper places.

I do appreciate the help your providing me with.

Thanks.
[color=blue]
>
> [snip 2 end][/color]

[Jeff North]

Re: structure of database to prepare report

On 16 Nov 2003 18:39:07 -0800, in mailing.databas e.mysql
mikemalin@hotma il.com (Michael Malinsky) wrote:
[color=blue]
>| [snip]
>|
>| > Hold it right there. What is driving the data? This should be your
>| > Chart of Accounts. This table should contain all the account numbers
>| > that you need. Yes the chart will contain totaling accounts especially
>| > for EOP/Y (End of Period/Year) totals. But these would be flagged as
>| > 'special' accounts that a query can ignore or just pick out these
>| > values.
>| >
>| > Your Chart of Accounts table would look like
>| > AccNum, Desc, Current, Type, EOPAccNum, EOP, EOY, PrevYear.
>|
>| My trial balance will contain the following: AccNum, Desc, CYBalance,
>| PYBalance.
>|
>| My question still rotates around the best way to assign each account
>| to a group or groups (remember an account may be in more than one
>| place in my report).[/color]

But these types of entries should have their own account numbers
within the Chart of Accounts (COA). As an extreme example: you are
receiving rent payments while paying rent yourself. One is an income
and the other is an expense. Your COA should reflect this otherwise
how do you know where the payments belong at data entry time.
[color=blue]
>| If your reference to type is meant to help group
>| accounts, it will not work. I'm still thinking of having all groups
>| in one table then having a third table containing account numbers and
>| line numbers which would allow me to run queries via the Excel
>| function to place the proper numbers in the proper places.[/color]

You're still stuck in the flat data model. SQL can help you out allot
with extracting the data, Excel can be used to format the data.

You only need 2 tables to generate the standard accounting reports,
COA and Transactions. The COA has all your account numbers, EOP/Y
figures and some grouping instructions. The transaction table lists
all data entry information. The SQL statement would join these tables,
group and sort the information. Excel would ask mySQL to run the
query, import it into a sheet. Excel would then format the data.
[color=blue]
>| I do appreciate the help your providing me with.
>|
>| Thanks.
>|
>| >
>| > [snip 2 end][/color]

---------------------------------------------------------------
jnorth@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[Jeff North]

Re: structure of database to prepare report

On 16 Nov 2003 18:39:07 -0800, in mailing.databas e.mysql
mikemalin@hotma il.com (Michael Malinsky) wrote:
[color=blue]
>| [snip]
>|
>| > Hold it right there. What is driving the data? This should be your
>| > Chart of Accounts. This table should contain all the account numbers
>| > that you need. Yes the chart will contain totaling accounts especially
>| > for EOP/Y (End of Period/Year) totals. But these would be flagged as
>| > 'special' accounts that a query can ignore or just pick out these
>| > values.
>| >
>| > Your Chart of Accounts table would look like
>| > AccNum, Desc, Current, Type, EOPAccNum, EOP, EOY, PrevYear.
>|
>| My trial balance will contain the following: AccNum, Desc, CYBalance,
>| PYBalance.
>|
>| My question still rotates around the best way to assign each account
>| to a group or groups (remember an account may be in more than one
>| place in my report).[/color]

But these types of entries should have their own account numbers
within the Chart of Accounts (COA). As an extreme example: you are
receiving rent payments while paying rent yourself. One is an income
and the other is an expense. Your COA should reflect this otherwise
how do you know where the payments belong at data entry time.
[color=blue]
>| If your reference to type is meant to help group
>| accounts, it will not work. I'm still thinking of having all groups
>| in one table then having a third table containing account numbers and
>| line numbers which would allow me to run queries via the Excel
>| function to place the proper numbers in the proper places.[/color]

You're still stuck in the flat data model. SQL can help you out allot
with extracting the data, Excel can be used to format the data.

You only need 2 tables to generate the standard accounting reports,
COA and Transactions. The COA has all your account numbers, EOP/Y
figures and some grouping instructions. The transaction table lists
all data entry information. The SQL statement would join these tables,
group and sort the information. Excel would ask mySQL to run the
query, import it into a sheet. Excel would then format the data.
[color=blue]
>| I do appreciate the help your providing me with.
>|
>| Thanks.
>|
>| >
>| > [snip 2 end][/color]

---------------------------------------------------------------
jnorth@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[Michael Malinsky]

Re: structure of database to prepare report

Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<bnjirv0ku 5gf7o9154sdqn14 1e68vlcmap@4ax. com>...[color=blue]
> On 16 Nov 2003 18:39:07 -0800, in mailing.databas e.mysql
> mikemalin@hotma il.com (Michael Malinsky) wrote:
>[color=green]
> >| [snip]
> >|
> >| > Hold it right there. What is driving the data? This should be your
> >| > Chart of Accounts. This table should contain all the account numbers
> >| > that you need. Yes the chart will contain totaling accounts especially
> >| > for EOP/Y (End of Period/Year) totals. But these would be flagged as
> >| > 'special' accounts that a query can ignore or just pick out these
> >| > values.
> >| >
> >| > Your Chart of Accounts table would look like
> >| > AccNum, Desc, Current, Type, EOPAccNum, EOP, EOY, PrevYear.
> >|
> >| My trial balance will contain the following: AccNum, Desc, CYBalance,
> >| PYBalance.
> >|
> >| My question still rotates around the best way to assign each account
> >| to a group or groups (remember an account may be in more than one
> >| place in my report).[/color]
>
> But these types of entries should have their own account numbers
> within the Chart of Accounts (COA). As an extreme example: you are
> receiving rent payments while paying rent yourself. One is an income
> and the other is an expense. Your COA should reflect this otherwise
> how do you know where the payments belong at data entry time.[/color]

There are no data transactions. I am importing a trial balance from
another accounting package. So my chart of accounts is included in my
trial balance.
[color=blue]
>[color=green]
> >| If your reference to type is meant to help group
> >| accounts, it will not work. I'm still thinking of having all groups
> >| in one table then having a third table containing account numbers and
> >| line numbers which would allow me to run queries via the Excel
> >| function to place the proper numbers in the proper places.[/color]
>
> You're still stuck in the flat data model. SQL can help you out allot
> with extracting the data, Excel can be used to format the data.
>
> You only need 2 tables to generate the standard accounting reports,
> COA and Transactions. The COA has all your account numbers, EOP/Y
> figures and some grouping instructions. The transaction table lists
> all data entry information. The SQL statement would join these tables,
> group and sort the information. Excel would ask mySQL to run the
> query, import it into a sheet. Excel would then format the data.[/color]

I'm not sure I get it. Let's say account 1000 - Cash is assigned to
the Cash group of the balance sheet. Let's also say that this same
account is assigned to another page of my report that is a supporting
statement for the Cash group. How would I show both assignments in
only one table?
[color=blue]
>[color=green]
> >| I do appreciate the help your providing me with.
> >|
> >| Thanks.
> >|
> >| >
> >| > [snip 2 end][/color]
>
> ---------------------------------------------------------------
> jnorth@yourpant sbigpond.net.au : Remove your pants to reply
> ---------------------------------------------------------------[/color]

[Michael Malinsky]

Re: structure of database to prepare report

Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<bnjirv0ku 5gf7o9154sdqn14 1e68vlcmap@4ax. com>...[color=blue]
> On 16 Nov 2003 18:39:07 -0800, in mailing.databas e.mysql
> mikemalin@hotma il.com (Michael Malinsky) wrote:
>[color=green]
> >| [snip]
> >|
> >| > Hold it right there. What is driving the data? This should be your
> >| > Chart of Accounts. This table should contain all the account numbers
> >| > that you need. Yes the chart will contain totaling accounts especially
> >| > for EOP/Y (End of Period/Year) totals. But these would be flagged as
> >| > 'special' accounts that a query can ignore or just pick out these
> >| > values.
> >| >
> >| > Your Chart of Accounts table would look like
> >| > AccNum, Desc, Current, Type, EOPAccNum, EOP, EOY, PrevYear.
> >|
> >| My trial balance will contain the following: AccNum, Desc, CYBalance,
> >| PYBalance.
> >|
> >| My question still rotates around the best way to assign each account
> >| to a group or groups (remember an account may be in more than one
> >| place in my report).[/color]
>
> But these types of entries should have their own account numbers
> within the Chart of Accounts (COA). As an extreme example: you are
> receiving rent payments while paying rent yourself. One is an income
> and the other is an expense. Your COA should reflect this otherwise
> how do you know where the payments belong at data entry time.[/color]

There are no data transactions. I am importing a trial balance from
another accounting package. So my chart of accounts is included in my
trial balance.
[color=blue]
>[color=green]
> >| If your reference to type is meant to help group
> >| accounts, it will not work. I'm still thinking of having all groups
> >| in one table then having a third table containing account numbers and
> >| line numbers which would allow me to run queries via the Excel
> >| function to place the proper numbers in the proper places.[/color]
>
> You're still stuck in the flat data model. SQL can help you out allot
> with extracting the data, Excel can be used to format the data.
>
> You only need 2 tables to generate the standard accounting reports,
> COA and Transactions. The COA has all your account numbers, EOP/Y
> figures and some grouping instructions. The transaction table lists
> all data entry information. The SQL statement would join these tables,
> group and sort the information. Excel would ask mySQL to run the
> query, import it into a sheet. Excel would then format the data.[/color]

I'm not sure I get it. Let's say account 1000 - Cash is assigned to
the Cash group of the balance sheet. Let's also say that this same
account is assigned to another page of my report that is a supporting
statement for the Cash group. How would I show both assignments in
only one table?
[color=blue]
>[color=green]
> >| I do appreciate the help your providing me with.
> >|
> >| Thanks.
> >|
> >| >
> >| > [snip 2 end][/color]
>
> ---------------------------------------------------------------
> jnorth@yourpant sbigpond.net.au : Remove your pants to reply
> ---------------------------------------------------------------[/color]

[Jeff North]

Re: structure of database to prepare report

On 18 Nov 2003 07:02:41 -0800, in mailing.databas e.mysql
mikemalin@hotma il.com (Michael Malinsky) wrote:
[color=blue]
>| Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<bnjirv0ku 5gf7o9154sdqn14 1e68vlcmap@4ax. com>...
>| > On 16 Nov 2003 18:39:07 -0800, in mailing.databas e.mysql
>| > mikemalin@hotma il.com (Michael Malinsky) wrote:[/color]

[snip]
[color=blue]
>| > >| My question still rotates around the best way to assign each account
>| > >| to a group or groups (remember an account may be in more than one
>| > >| place in my report).
>| >
>| > But these types of entries should have their own account numbers
>| > within the Chart of Accounts (COA). As an extreme example: you are
>| > receiving rent payments while paying rent yourself. One is an income
>| > and the other is an expense. Your COA should reflect this otherwise
>| > how do you know where the payments belong at data entry time.
>|
>| There are no data transactions. I am importing a trial balance from
>| another accounting package. So my chart of accounts is included in my
>| trial balance.[/color]

Why are you re-inventing the wheel. What is wrong with the accounting
packages trial balance report.

[snip 2 end]

---------------------------------------------------------------
jnorth@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[Jeff North]

Re: structure of database to prepare report

On 18 Nov 2003 07:02:41 -0800, in mailing.databas e.mysql
mikemalin@hotma il.com (Michael Malinsky) wrote:
[color=blue]
>| Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<bnjirv0ku 5gf7o9154sdqn14 1e68vlcmap@4ax. com>...
>| > On 16 Nov 2003 18:39:07 -0800, in mailing.databas e.mysql
>| > mikemalin@hotma il.com (Michael Malinsky) wrote:[/color]

[snip]
[color=blue]
>| > >| My question still rotates around the best way to assign each account
>| > >| to a group or groups (remember an account may be in more than one
>| > >| place in my report).
>| >
>| > But these types of entries should have their own account numbers
>| > within the Chart of Accounts (COA). As an extreme example: you are
>| > receiving rent payments while paying rent yourself. One is an income
>| > and the other is an expense. Your COA should reflect this otherwise
>| > how do you know where the payments belong at data entry time.
>|
>| There are no data transactions. I am importing a trial balance from
>| another accounting package. So my chart of accounts is included in my
>| trial balance.[/color]

Why are you re-inventing the wheel. What is wrong with the accounting
packages trial balance report.

[snip 2 end]

---------------------------------------------------------------
jnorth@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[Michael Malinsky]

Re: structure of database to prepare report

Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<qtukrvs66 ieda0e3586ees9v jrih8mrsug@4ax. com>...[color=blue]
> On 18 Nov 2003 07:02:41 -0800, in mailing.databas e.mysql
> mikemalin@hotma il.com (Michael Malinsky) wrote:
>[color=green]
> >| Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<bnjirv0ku 5gf7o9154sdqn14 1e68vlcmap@4ax. com>...
> >| > On 16 Nov 2003 18:39:07 -0800, in mailing.databas e.mysql
> >| > mikemalin@hotma il.com (Michael Malinsky) wrote:[/color]
>
> [snip]
>[color=green]
> >| > >| My question still rotates around the best way to assign each account
> >| > >| to a group or groups (remember an account may be in more than one
> >| > >| place in my report).
> >| >
> >| > But these types of entries should have their own account numbers
> >| > within the Chart of Accounts (COA). As an extreme example: you are
> >| > receiving rent payments while paying rent yourself. One is an income
> >| > and the other is an expense. Your COA should reflect this otherwise
> >| > how do you know where the payments belong at data entry time.
> >|
> >| There are no data transactions. I am importing a trial balance from
> >| another accounting package. So my chart of accounts is included in my
> >| trial balance.[/color]
>
> Why are you re-inventing the wheel. What is wrong with the accounting
> packages trial balance report.[/color]

There is no problem with the trial balance report from the accounting
package. The problem is that the accounting package does/can not
produce the reports I need to prepare. Therefore, I am exporting the
trial balance to mysql with the thought that I can use mysql to group
my trial balance accounts into the groups that I need for this report.
As I said before, some accounts may be assigned to more than one spot
on the report which is why I wanted to create a database to store this
information. So I'm attempting to take info from the accounting
package and manipulate the data into a format that I need but the
accounting package does not provide.
[color=blue]
>
> [snip 2 end]
>
> ---------------------------------------------------------------
> jnorth@yourpant sbigpond.net.au : Remove your pants to reply
> ---------------------------------------------------------------[/color]

[Michael Malinsky]

Re: structure of database to prepare report

Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<qtukrvs66 ieda0e3586ees9v jrih8mrsug@4ax. com>...[color=blue]
> On 18 Nov 2003 07:02:41 -0800, in mailing.databas e.mysql
> mikemalin@hotma il.com (Michael Malinsky) wrote:
>[color=green]
> >| Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<bnjirv0ku 5gf7o9154sdqn14 1e68vlcmap@4ax. com>...
> >| > On 16 Nov 2003 18:39:07 -0800, in mailing.databas e.mysql
> >| > mikemalin@hotma il.com (Michael Malinsky) wrote:[/color]
>
> [snip]
>[color=green]
> >| > >| My question still rotates around the best way to assign each account
> >| > >| to a group or groups (remember an account may be in more than one
> >| > >| place in my report).
> >| >
> >| > But these types of entries should have their own account numbers
> >| > within the Chart of Accounts (COA). As an extreme example: you are
> >| > receiving rent payments while paying rent yourself. One is an income
> >| > and the other is an expense. Your COA should reflect this otherwise
> >| > how do you know where the payments belong at data entry time.
> >|
> >| There are no data transactions. I am importing a trial balance from
> >| another accounting package. So my chart of accounts is included in my
> >| trial balance.[/color]
>
> Why are you re-inventing the wheel. What is wrong with the accounting
> packages trial balance report.[/color]

There is no problem with the trial balance report from the accounting
package. The problem is that the accounting package does/can not
produce the reports I need to prepare. Therefore, I am exporting the
trial balance to mysql with the thought that I can use mysql to group
my trial balance accounts into the groups that I need for this report.
As I said before, some accounts may be assigned to more than one spot
on the report which is why I wanted to create a database to store this
information. So I'm attempting to take info from the accounting
package and manipulate the data into a format that I need but the
accounting package does not provide.
[color=blue]
>
> [snip 2 end]
>
> ---------------------------------------------------------------
> jnorth@yourpant sbigpond.net.au : Remove your pants to reply
> ---------------------------------------------------------------[/color]