structure of database to prepare report

**Jeff North** · Jul 19 '05, 11:34 PM

Re: structure of database to prepare report

On 9 Nov 2003 20:12:26 -0800, in mailing.databas e.mysql
mikemalin@hotma il.com (Michael Malinsky) wrote:
[color=blue]
>| I'm attempting to create a database which will take information from
>| one (perhaps two) tables and utilize that information to return
>| queries to a report designed in Excel.[/color]

What language will you be using to access the database?
[color=blue]
>| The general idea I have is this:
>|
>| The "primary" or "source" table is easy. This is a trial balance
>| (account number as a primary key, description, amount).
>|
>| I'll need table(s) to denote which account numbers belong to which
>| page/line on the report.[/color]

No you don't.
[color=blue]
>| I'm not sure how many pages the report will
>| be, but let's say my Excel workbook would have 50 sheets.[/color]

You must have shares in the local paper-mill :-)
[color=blue]
>| My question
>| is this: What would you recommend for a table structure. The way I
>| see it, I could have a single table listing each page and each line on
>| each page (each identified by a unique id), then have another table
>| listing each account and each page/line id. The other option I see is
>| to have a separate table for each "page," but I'm not sure I see a
>| benefit in this. My first instinct is to use the first method I
>| mentioned, but I wanted someone else's opinion before I moved on.[/color]

All you need is the tables containing your raw data. A query to bring
the data together and sort it. A method of saving the query result to
a CSV file and a macro in Excel to format and set the Subtotals
grouping.

I seriously doubt that a trial balance would go over 32,000 cells in
the spreadsheet so all should fit on a single sheet.
---------------------------------------------------------------
jnorth@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

**Jeff North** · Jul 19 '05, 11:34 PM

Re: structure of database to prepare report

On 9 Nov 2003 20:12:26 -0800, in mailing.databas e.mysql
mikemalin@hotma il.com (Michael Malinsky) wrote:
[color=blue]
>| I'm attempting to create a database which will take information from
>| one (perhaps two) tables and utilize that information to return
>| queries to a report designed in Excel.[/color]

What language will you be using to access the database?
[color=blue]
>| The general idea I have is this:
>|
>| The "primary" or "source" table is easy. This is a trial balance
>| (account number as a primary key, description, amount).
>|
>| I'll need table(s) to denote which account numbers belong to which
>| page/line on the report.[/color]

No you don't.
[color=blue]
>| I'm not sure how many pages the report will
>| be, but let's say my Excel workbook would have 50 sheets.[/color]

You must have shares in the local paper-mill :-)
[color=blue]
>| My question
>| is this: What would you recommend for a table structure. The way I
>| see it, I could have a single table listing each page and each line on
>| each page (each identified by a unique id), then have another table
>| listing each account and each page/line id. The other option I see is
>| to have a separate table for each "page," but I'm not sure I see a
>| benefit in this. My first instinct is to use the first method I
>| mentioned, but I wanted someone else's opinion before I moved on.[/color]

All you need is the tables containing your raw data. A query to bring
the data together and sort it. A method of saving the query result to
a CSV file and a macro in Excel to format and set the Subtotals
grouping.

I seriously doubt that a trial balance would go over 32,000 cells in
the spreadsheet so all should fit on a single sheet.
---------------------------------------------------------------
jnorth@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

**Michael Malinsky** · Jul 19 '05, 11:34 PM

Re: structure of database to prepare report

Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<7qjuqvg9r jc4f19svl59e5pm raq3nm9p3i@4ax. com>...[color=blue]
> On 9 Nov 2003 20:12:26 -0800, in mailing.databas e.mysql
> mikemalin@hotma il.com (Michael Malinsky) wrote:
>[color=green]
> >| I'm attempting to create a database which will take information from
> >| one (perhaps two) tables and utilize that information to return
> >| queries to a report designed in Excel.[/color]
>
> What language will you be using to access the database?[/color]

I already have a user defined function in Excel to accomplish the
retreival of data.
[color=blue]
>[color=green]
> >| The general idea I have is this:
> >|
> >| The "primary" or "source" table is easy. This is a trial balance
> >| (account number as a primary key, description, amount).
> >|
> >| I'll need table(s) to denote which account numbers belong to which
> >| page/line on the report.[/color]
>
> No you don't.[/color]

Yes I do (I think)...severa l accounts from the trial balance will be
added together to create one result in Excel.
[color=blue]
>[color=green]
> >| I'm not sure how many pages the report will
> >| be, but let's say my Excel workbook would have 50 sheets.[/color]
>
> You must have shares in the local paper-mill :-)
>[/color]

Not my idea. :-)
[color=blue][color=green]
> >| My question
> >| is this: What would you recommend for a table structure. The way I
> >| see it, I could have a single table listing each page and each line on
> >| each page (each identified by a unique id), then have another table
> >| listing each account and each page/line id. The other option I see is
> >| to have a separate table for each "page," but I'm not sure I see a
> >| benefit in this. My first instinct is to use the first method I
> >| mentioned, but I wanted someone else's opinion before I moved on.[/color]
>
> All you need is the tables containing your raw data. A query to bring
> the data together and sort it. A method of saving the query result to
> a CSV file and a macro in Excel to format and set the Subtotals
> grouping.[/color]

Raw data = trial balance table and table listing the pages/lines in
the report, right? But don't I need a third table that would, for
example, list that account 100 and 150 would be totaled to be on page
1 line 5?
[color=blue]
>
> I seriously doubt that a trial balance would go over 32,000 cells in
> the spreadsheet so all should fit on a single sheet.
> ---------------------------------------------------------------
> jnorth@yourpant sbigpond.net.au : Remove your pants to reply
> ---------------------------------------------------------------[/color]

**Michael Malinsky** · Jul 19 '05, 11:34 PM

Re: structure of database to prepare report

Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<7qjuqvg9r jc4f19svl59e5pm raq3nm9p3i@4ax. com>...[color=blue]
> On 9 Nov 2003 20:12:26 -0800, in mailing.databas e.mysql
> mikemalin@hotma il.com (Michael Malinsky) wrote:
>[color=green]
> >| I'm attempting to create a database which will take information from
> >| one (perhaps two) tables and utilize that information to return
> >| queries to a report designed in Excel.[/color]
>
> What language will you be using to access the database?[/color]

I already have a user defined function in Excel to accomplish the
retreival of data.
[color=blue]
>[color=green]
> >| The general idea I have is this:
> >|
> >| The "primary" or "source" table is easy. This is a trial balance
> >| (account number as a primary key, description, amount).
> >|
> >| I'll need table(s) to denote which account numbers belong to which
> >| page/line on the report.[/color]
>
> No you don't.[/color]

Yes I do (I think)...severa l accounts from the trial balance will be
added together to create one result in Excel.
[color=blue]
>[color=green]
> >| I'm not sure how many pages the report will
> >| be, but let's say my Excel workbook would have 50 sheets.[/color]
>
> You must have shares in the local paper-mill :-)
>[/color]

Not my idea. :-)
[color=blue][color=green]
> >| My question
> >| is this: What would you recommend for a table structure. The way I
> >| see it, I could have a single table listing each page and each line on
> >| each page (each identified by a unique id), then have another table
> >| listing each account and each page/line id. The other option I see is
> >| to have a separate table for each "page," but I'm not sure I see a
> >| benefit in this. My first instinct is to use the first method I
> >| mentioned, but I wanted someone else's opinion before I moved on.[/color]
>
> All you need is the tables containing your raw data. A query to bring
> the data together and sort it. A method of saving the query result to
> a CSV file and a macro in Excel to format and set the Subtotals
> grouping.[/color]

Raw data = trial balance table and table listing the pages/lines in
the report, right? But don't I need a third table that would, for
example, list that account 100 and 150 would be totaled to be on page
1 line 5?
[color=blue]
>
> I seriously doubt that a trial balance would go over 32,000 cells in
> the spreadsheet so all should fit on a single sheet.
> ---------------------------------------------------------------
> jnorth@yourpant sbigpond.net.au : Remove your pants to reply
> ---------------------------------------------------------------[/color]

**Jeff North** · Jul 19 '05, 11:34 PM

Re: structure of database to prepare report

On 10 Nov 2003 19:05:48 -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:<7qjuqvg9r jc4f19svl59e5pm raq3nm9p3i@4ax. com>...
>| > On 9 Nov 2003 20:12:26 -0800, in mailing.databas e.mysql
>| > mikemalin@hotma il.com (Michael Malinsky) wrote:
>| >
>| > >| I'm attempting to create a database which will take information from
>| > >| one (perhaps two) tables and utilize that information to return
>| > >| queries to a report designed in Excel.
>| >
>| > What language will you be using to access the database?
>|
>| I already have a user defined function in Excel to accomplish the
>| retreival of data.[/color]

Then let Excel do the rest of the work.

Have a look under Data | Subtotals.
This has numerous options for splitting, sub-totalling and page
formatting your data.

The data will appear on a single sheet but can be printed out to
different pages according to account group.

An added advantage of Subtotals is the user can expand/collapse groups
as they see fit (any you look like a genius to your boss).

If that doesn't wow the boss you can set up a repeating page header
and footer for each page of the report :-)

You can even record the steps you take within a new macro. Then, once
you are happy with the results, call the formatting macro from your
import data macro.
[color=blue]
>| > >| The general idea I have is this:
>| > >|
>| > >| The "primary" or "source" table is easy. This is a trial balance
>| > >| (account number as a primary key, description, amount).
>| > >|
>| > >| I'll need table(s) to denote which account numbers belong to which
>| > >| page/line on the report.
>| >
>| > No you don't.
>|
>| Yes I do (I think)...severa l accounts from the trial balance will be
>| added together to create one result in Excel.[/color]

No you don't - trust me :-)
[color=blue]
>| > >| I'm not sure how many pages the report will
>| > >| be, but let's say my Excel workbook would have 50 sheets.
>| >
>| > You must have shares in the local paper-mill :-)
>|
>| Not my idea. :-)[/color]

I didn't think so. But once they discover that their paper bill has
gone through the roof they'll soon want the report changed :-)

Just imagine printing out a trial balance with one or two entries per
page - they'll change their mind :-)
[color=blue]
>| > >| My question
>| > >| is this: What would you recommend for a table structure. The way I
>| > >| see it, I could have a single table listing each page and each line on
>| > >| each page (each identified by a unique id), then have another table
>| > >| listing each account and each page/line id. The other option I see is
>| > >| to have a separate table for each "page," but I'm not sure I see a
>| > >| benefit in this. My first instinct is to use the first method I
>| > >| mentioned, but I wanted someone else's opinion before I moved on.
>| >
>| > All you need is the tables containing your raw data. A query to bring
>| > the data together and sort it. A method of saving the query result to
>| > a CSV file and a macro in Excel to format and set the Subtotals
>| > grouping.
>|
>| Raw data = trial balance table and table listing the pages/lines in
>| the report, right? But don't I need a third table that would, for
>| example, list that account 100 and 150 would be totaled to be on page
>| 1 line 5?[/color]

Drop the concept of you having to track each entry - we are past the
days of DOS applications. You can let the receiving application (in
this case Excel) worry about that detail.

Remember the K.I.S principle.
---------------------------------------------------------------
jnorth@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

**Jeff North** · Jul 19 '05, 11:34 PM

Re: structure of database to prepare report

On 10 Nov 2003 19:05:48 -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:<7qjuqvg9r jc4f19svl59e5pm raq3nm9p3i@4ax. com>...
>| > On 9 Nov 2003 20:12:26 -0800, in mailing.databas e.mysql
>| > mikemalin@hotma il.com (Michael Malinsky) wrote:
>| >
>| > >| I'm attempting to create a database which will take information from
>| > >| one (perhaps two) tables and utilize that information to return
>| > >| queries to a report designed in Excel.
>| >
>| > What language will you be using to access the database?
>|
>| I already have a user defined function in Excel to accomplish the
>| retreival of data.[/color]

Then let Excel do the rest of the work.

Have a look under Data | Subtotals.
This has numerous options for splitting, sub-totalling and page
formatting your data.

The data will appear on a single sheet but can be printed out to
different pages according to account group.

An added advantage of Subtotals is the user can expand/collapse groups
as they see fit (any you look like a genius to your boss).

If that doesn't wow the boss you can set up a repeating page header
and footer for each page of the report :-)

You can even record the steps you take within a new macro. Then, once
you are happy with the results, call the formatting macro from your
import data macro.
[color=blue]
>| > >| The general idea I have is this:
>| > >|
>| > >| The "primary" or "source" table is easy. This is a trial balance
>| > >| (account number as a primary key, description, amount).
>| > >|
>| > >| I'll need table(s) to denote which account numbers belong to which
>| > >| page/line on the report.
>| >
>| > No you don't.
>|
>| Yes I do (I think)...severa l accounts from the trial balance will be
>| added together to create one result in Excel.[/color]

No you don't - trust me :-)
[color=blue]
>| > >| I'm not sure how many pages the report will
>| > >| be, but let's say my Excel workbook would have 50 sheets.
>| >
>| > You must have shares in the local paper-mill :-)
>|
>| Not my idea. :-)[/color]

I didn't think so. But once they discover that their paper bill has
gone through the roof they'll soon want the report changed :-)

Just imagine printing out a trial balance with one or two entries per
page - they'll change their mind :-)
[color=blue]
>| > >| My question
>| > >| is this: What would you recommend for a table structure. The way I
>| > >| see it, I could have a single table listing each page and each line on
>| > >| each page (each identified by a unique id), then have another table
>| > >| listing each account and each page/line id. The other option I see is
>| > >| to have a separate table for each "page," but I'm not sure I see a
>| > >| benefit in this. My first instinct is to use the first method I
>| > >| mentioned, but I wanted someone else's opinion before I moved on.
>| >
>| > All you need is the tables containing your raw data. A query to bring
>| > the data together and sort it. A method of saving the query result to
>| > a CSV file and a macro in Excel to format and set the Subtotals
>| > grouping.
>|
>| Raw data = trial balance table and table listing the pages/lines in
>| the report, right? But don't I need a third table that would, for
>| example, list that account 100 and 150 would be totaled to be on page
>| 1 line 5?[/color]

Drop the concept of you having to track each entry - we are past the
days of DOS applications. You can let the receiving application (in
this case Excel) worry about that detail.

Remember the K.I.S principle.
---------------------------------------------------------------
jnorth@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

**Michael Malinsky** · Jul 19 '05, 11:34 PM

Re: structure of database to prepare report

Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<m1v0rvs6a oaj8ihidcnm76d0 4raeemo63c@4ax. com>...[color=blue]
> On 10 Nov 2003 19:05:48 -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:<7qjuqvg9r jc4f19svl59e5pm raq3nm9p3i@4ax. com>...
> >| > On 9 Nov 2003 20:12:26 -0800, in mailing.databas e.mysql
> >| > mikemalin@hotma il.com (Michael Malinsky) wrote:
> >| >
> >| > >| I'm attempting to create a database which will take information from
> >| > >| one (perhaps two) tables and utilize that information to return
> >| > >| queries to a report designed in Excel.
> >| >
> >| > What language will you be using to access the database?
> >|
> >| I already have a user defined function in Excel to accomplish the
> >| retreival of data.[/color]
>
> Then let Excel do the rest of the work.
>
> Have a look under Data | Subtotals.
> This has numerous options for splitting, sub-totalling and page
> formatting your data.
>
> The data will appear on a single sheet but can be printed out to
> different pages according to account group.
>
> An added advantage of Subtotals is the user can expand/collapse groups
> as they see fit (any you look like a genius to your boss).
>
> If that doesn't wow the boss you can set up a repeating page header
> and footer for each page of the report :-)
>
> You can even record the steps you take within a new macro. Then, once
> you are happy with the results, call the formatting macro from your
> import data macro.
>[/color]

I'm familiar enough with Excel and subtotals, etc. But unless I'm
missing something (and I don't think I completely explained it in my
earlier posts), since a particular account can be used in more than
one spot on the report, I don't think subtotals will do the trick.
Let me back up a little. I started this application completely in
Excel using array formulas and vlookup formulas (I got help from the
Excel worksheet function group for these). Because of that, I needed
two sheets for each page of the report, one for the actual page to be
printed, and one to be used as a "database" in which a column was
created for each page line and all of the account numbers were listed
in the necessary columns. After about three pages, I decided that
this became somewhat unwieldy thus my attempt migrate to a mysql
backend. Does this change your perspective at all as to why I'm
trying to do it this way? If not, I'd appreciate some more details on
your thoughts because I've not found a way to do this completely in
Excel in an efficient manner.
[color=blue][color=green]
> >| > >| The general idea I have is this:
> >| > >|
> >| > >| The "primary" or "source" table is easy. This is a trial balance
> >| > >| (account number as a primary key, description, amount).
> >| > >|
> >| > >| I'll need table(s) to denote which account numbers belong to which
> >| > >| page/line on the report.
> >| >
> >| > No you don't.
> >|
> >| Yes I do (I think)...severa l accounts from the trial balance will be
> >| added together to create one result in Excel.[/color]
>
> No you don't - trust me :-)
>[color=green]
> >| > >| I'm not sure how many pages the report will
> >| > >| be, but let's say my Excel workbook would have 50 sheets.
> >| >
> >| > You must have shares in the local paper-mill :-)
> >|
> >| Not my idea. :-)[/color]
>
> I didn't think so. But once they discover that their paper bill has
> gone through the roof they'll soon want the report changed :-)
>
> Just imagine printing out a trial balance with one or two entries per
> page - they'll change their mind :-)
>[/color]

The report is required by a third party, so internal thoughts on the
paper bill are moot.
[color=blue][color=green]
> >| > >| My question
> >| > >| is this: What would you recommend for a table structure. The way I
> >| > >| see it, I could have a single table listing each page and each line on
> >| > >| each page (each identified by a unique id), then have another table
> >| > >| listing each account and each page/line id. The other option I see is
> >| > >| to have a separate table for each "page," but I'm not sure I see a
> >| > >| benefit in this. My first instinct is to use the first method I
> >| > >| mentioned, but I wanted someone else's opinion before I moved on.
> >| >
> >| > All you need is the tables containing your raw data. A query to bring
> >| > the data together and sort it. A method of saving the query result to
> >| > a CSV file and a macro in Excel to format and set the Subtotals
> >| > grouping.
> >|
> >| Raw data = trial balance table and table listing the pages/lines in
> >| the report, right? But don't I need a third table that would, for
> >| example, list that account 100 and 150 would be totaled to be on page
> >| 1 line 5?[/color]
>
> Drop the concept of you having to track each entry - we are past the
> days of DOS applications. You can let the receiving application (in
> this case Excel) worry about that detail.
>[/color]

As stated above, I've not found a way to do this in Excel efficiently.
If you could shed some light on your thoughts, I'd appreciate it.
[color=blue]
> Remember the K.I.S principle.
> ---------------------------------------------------------------
> jnorth@yourpant sbigpond.net.au : Remove your pants to reply
> ---------------------------------------------------------------[/color]

**Michael Malinsky** · Jul 19 '05, 11:34 PM

Re: structure of database to prepare report

Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<m1v0rvs6a oaj8ihidcnm76d0 4raeemo63c@4ax. com>...[color=blue]
> On 10 Nov 2003 19:05:48 -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:<7qjuqvg9r jc4f19svl59e5pm raq3nm9p3i@4ax. com>...
> >| > On 9 Nov 2003 20:12:26 -0800, in mailing.databas e.mysql
> >| > mikemalin@hotma il.com (Michael Malinsky) wrote:
> >| >
> >| > >| I'm attempting to create a database which will take information from
> >| > >| one (perhaps two) tables and utilize that information to return
> >| > >| queries to a report designed in Excel.
> >| >
> >| > What language will you be using to access the database?
> >|
> >| I already have a user defined function in Excel to accomplish the
> >| retreival of data.[/color]
>
> Then let Excel do the rest of the work.
>
> Have a look under Data | Subtotals.
> This has numerous options for splitting, sub-totalling and page
> formatting your data.
>
> The data will appear on a single sheet but can be printed out to
> different pages according to account group.
>
> An added advantage of Subtotals is the user can expand/collapse groups
> as they see fit (any you look like a genius to your boss).
>
> If that doesn't wow the boss you can set up a repeating page header
> and footer for each page of the report :-)
>
> You can even record the steps you take within a new macro. Then, once
> you are happy with the results, call the formatting macro from your
> import data macro.
>[/color]

I'm familiar enough with Excel and subtotals, etc. But unless I'm
missing something (and I don't think I completely explained it in my
earlier posts), since a particular account can be used in more than
one spot on the report, I don't think subtotals will do the trick.
Let me back up a little. I started this application completely in
Excel using array formulas and vlookup formulas (I got help from the
Excel worksheet function group for these). Because of that, I needed
two sheets for each page of the report, one for the actual page to be
printed, and one to be used as a "database" in which a column was
created for each page line and all of the account numbers were listed
in the necessary columns. After about three pages, I decided that
this became somewhat unwieldy thus my attempt migrate to a mysql
backend. Does this change your perspective at all as to why I'm
trying to do it this way? If not, I'd appreciate some more details on
your thoughts because I've not found a way to do this completely in
Excel in an efficient manner.
[color=blue][color=green]
> >| > >| The general idea I have is this:
> >| > >|
> >| > >| The "primary" or "source" table is easy. This is a trial balance
> >| > >| (account number as a primary key, description, amount).
> >| > >|
> >| > >| I'll need table(s) to denote which account numbers belong to which
> >| > >| page/line on the report.
> >| >
> >| > No you don't.
> >|
> >| Yes I do (I think)...severa l accounts from the trial balance will be
> >| added together to create one result in Excel.[/color]
>
> No you don't - trust me :-)
>[color=green]
> >| > >| I'm not sure how many pages the report will
> >| > >| be, but let's say my Excel workbook would have 50 sheets.
> >| >
> >| > You must have shares in the local paper-mill :-)
> >|
> >| Not my idea. :-)[/color]
>
> I didn't think so. But once they discover that their paper bill has
> gone through the roof they'll soon want the report changed :-)
>
> Just imagine printing out a trial balance with one or two entries per
> page - they'll change their mind :-)
>[/color]

The report is required by a third party, so internal thoughts on the
paper bill are moot.
[color=blue][color=green]
> >| > >| My question
> >| > >| is this: What would you recommend for a table structure. The way I
> >| > >| see it, I could have a single table listing each page and each line on
> >| > >| each page (each identified by a unique id), then have another table
> >| > >| listing each account and each page/line id. The other option I see is
> >| > >| to have a separate table for each "page," but I'm not sure I see a
> >| > >| benefit in this. My first instinct is to use the first method I
> >| > >| mentioned, but I wanted someone else's opinion before I moved on.
> >| >
> >| > All you need is the tables containing your raw data. A query to bring
> >| > the data together and sort it. A method of saving the query result to
> >| > a CSV file and a macro in Excel to format and set the Subtotals
> >| > grouping.
> >|
> >| Raw data = trial balance table and table listing the pages/lines in
> >| the report, right? But don't I need a third table that would, for
> >| example, list that account 100 and 150 would be totaled to be on page
> >| 1 line 5?[/color]
>
> Drop the concept of you having to track each entry - we are past the
> days of DOS applications. You can let the receiving application (in
> this case Excel) worry about that detail.
>[/color]

As stated above, I've not found a way to do this in Excel efficiently.
If you could shed some light on your thoughts, I'd appreciate it.
[color=blue]
> Remember the K.I.S principle.
> ---------------------------------------------------------------
> jnorth@yourpant sbigpond.net.au : Remove your pants to reply
> ---------------------------------------------------------------[/color]

**Jeff North** · Jul 19 '05, 11:35 PM

Re: structure of database to prepare report

On 12 Nov 2003 05:06:09 -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:<m1v0rvs6a oaj8ihidcnm76d0 4raeemo63c@4ax. com>...
>| > On 10 Nov 2003 19:05:48 -0800, in mailing.databas e.mysql
>| > mikemalin@hotma il.com (Michael Malinsky) wrote:[/color]

[snip]
[color=blue]
>| I'm familiar enough with Excel and subtotals, etc. But unless I'm
>| missing something (and I don't think I completely explained it in my
>| earlier posts), since a particular account can be used in more than
>| one spot on the report, I don't think subtotals will do the trick.[/color]

[digging deep into the old memory about accounting theory - I see lots
of rust around here LOL]
A trial balance is basically reporting on 4 groups, Income, Expense,
Assets and Liabilities. Each of these groups have sub-groups on which
you are reporting the total amounts. You shouldn't have a sub-group
name crossing any of these main groups. How does the app know which
main group the sub-group belongs to.

Or do you mean that your table holding the data has the same account
code several times (which should be normal especially with income and
expense groups). In this case you need to do a group query which will
combine all like groups and produce the total for you.
[color=blue]
>| Let me back up a little. I started this application completely in
>| Excel using array formulas and vlookup formulas (I got help from the
>| Excel worksheet function group for these). Because of that, I needed
>| two sheets for each page of the report, one for the actual page to be
>| printed, and one to be used as a "database" in which a column was
>| created for each page line and all of the account numbers were listed
>| in the necessary columns. After about three pages, I decided that
>| this became somewhat unwieldy thus my attempt migrate to a mysql
>| backend.[/color]

OK, you're still stuck in the Excel data model mode. While Excel does
have database-like capabilities they are very limited. You are
entering the world of powerful databases where data manipulation is
their forte.

Post your database schema with about 6 lines of sample data (i.e.
fudged - no real names or amounts please) for each table.

[donning several bullet-proof vests and a couple of flame retardant
suites]
Why did you choose to use mySQL? Why not MSAccess? If you are new to
databases then (even for all of its short-comings) Access is a good
way to learn about databases.

[snip 2 end]
---------------------------------------------------------------
jnorth@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

**Jeff North** · Jul 19 '05, 11:35 PM

Re: structure of database to prepare report

On 12 Nov 2003 05:06:09 -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:<m1v0rvs6a oaj8ihidcnm76d0 4raeemo63c@4ax. com>...
>| > On 10 Nov 2003 19:05:48 -0800, in mailing.databas e.mysql
>| > mikemalin@hotma il.com (Michael Malinsky) wrote:[/color]

[snip]
[color=blue]
>| I'm familiar enough with Excel and subtotals, etc. But unless I'm
>| missing something (and I don't think I completely explained it in my
>| earlier posts), since a particular account can be used in more than
>| one spot on the report, I don't think subtotals will do the trick.[/color]

[digging deep into the old memory about accounting theory - I see lots
of rust around here LOL]
A trial balance is basically reporting on 4 groups, Income, Expense,
Assets and Liabilities. Each of these groups have sub-groups on which
you are reporting the total amounts. You shouldn't have a sub-group
name crossing any of these main groups. How does the app know which
main group the sub-group belongs to.

Or do you mean that your table holding the data has the same account
code several times (which should be normal especially with income and
expense groups). In this case you need to do a group query which will
combine all like groups and produce the total for you.
[color=blue]
>| Let me back up a little. I started this application completely in
>| Excel using array formulas and vlookup formulas (I got help from the
>| Excel worksheet function group for these). Because of that, I needed
>| two sheets for each page of the report, one for the actual page to be
>| printed, and one to be used as a "database" in which a column was
>| created for each page line and all of the account numbers were listed
>| in the necessary columns. After about three pages, I decided that
>| this became somewhat unwieldy thus my attempt migrate to a mysql
>| backend.[/color]

OK, you're still stuck in the Excel data model mode. While Excel does
have database-like capabilities they are very limited. You are
entering the world of powerful databases where data manipulation is
their forte.

Post your database schema with about 6 lines of sample data (i.e.
fudged - no real names or amounts please) for each table.

[donning several bullet-proof vests and a couple of flame retardant
suites]
Why did you choose to use mySQL? Why not MSAccess? If you are new to
databases then (even for all of its short-comings) Access is a good
way to learn about databases.

[snip 2 end]
---------------------------------------------------------------
jnorth@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

**Michael Malinsky** · Jul 19 '05, 11:35 PM

Re: structure of database to prepare report

Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<pup5rvolh 04r1t65iancckt6 474vn6fcbr@4ax. com>...[color=blue]
> On 12 Nov 2003 05:06:09 -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:<m1v0rvs6a oaj8ihidcnm76d0 4raeemo63c@4ax. com>...
> >| > On 10 Nov 2003 19:05:48 -0800, in mailing.databas e.mysql
> >| > mikemalin@hotma il.com (Michael Malinsky) wrote:[/color]
>
> [snip]
>[/color]
[snip]
[color=blue]
> A trial balance is basically reporting on 4 groups, Income, Expense,
> Assets and Liabilities. Each of these groups have sub-groups on which
> you are reporting the total amounts. You shouldn't have a sub-group
> name crossing any of these main groups. How does the app know which
> main group the sub-group belongs to.[/color]

Maybe I didn't explain this well enough earlier. You have the basic
form for financial statements correct. However, in addition to basic
financial statements (balance sheet and income statement), there are
several other schedules. So while a single account will be used only
once in the basic financial statements, it may be used again in
another schedule. This is why the Excel group function will not work
for me. The basic idea is to have one table for the trial balance
(account number is primary key) and one or more tables to indicate the
groups (essentially the page and line number assignments with each
line having a UID for the primary key (this was the basic content of
my original question). I would then have a table which would contain
two fields, account number and the UID for the line assignment. I
currently have a user-defined function in Excel that runs a query to
pull info from a mysql database based on an account number but could
be easily amended to do what I need.

So basically, I'm hung up on whether to have all pages/lines in one
table (which is where I'm leaning) or to segregate them into several
tables (which would require additional programming in Excel VBA I
think).
[color=blue]
> Or do you mean that your table holding the data has the same account
> code several times (which should be normal especially with income and
> expense groups). In this case you need to do a group query which will
> combine all like groups and produce the total for you.[/color]

I think the above answers this.

[snip]
[color=blue]
> OK, you're still stuck in the Excel data model mode. While Excel does
> have database-like capabilities they are very limited. You are
> entering the world of powerful databases where data manipulation is
> their forte.
>
> Post your database schema with about 6 lines of sample data (i.e.
> fudged - no real names or amounts please) for each table.[/color]

I don't have a formal database schema as of yet. I explained the
general idea above. Once I figure out the best way to to the
groupings and assignments, I should be able to get the database
structure created myself (but who knows).
[color=blue]
> [donning several bullet-proof vests and a couple of flame retardant
> suites][/color]

{Well I do accounting stuff for a living...how do you think I feel
:-)}
[color=blue]
> Why did you choose to use mySQL? Why not MSAccess? If you are new to
> databases then (even for all of its short-comings) Access is a good
> way to learn about databases.
>[/color]

I don't have access to Access.
[color=blue]
>
> [snip 2 end]
> ---------------------------------------------------------------
> jnorth@yourpant sbigpond.net.au : Remove your pants to reply
> ---------------------------------------------------------------[/color]

**Michael Malinsky** · Jul 19 '05, 11:35 PM

Re: structure of database to prepare report

Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<pup5rvolh 04r1t65iancckt6 474vn6fcbr@4ax. com>...[color=blue]
> On 12 Nov 2003 05:06:09 -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:<m1v0rvs6a oaj8ihidcnm76d0 4raeemo63c@4ax. com>...
> >| > On 10 Nov 2003 19:05:48 -0800, in mailing.databas e.mysql
> >| > mikemalin@hotma il.com (Michael Malinsky) wrote:[/color]
>
> [snip]
>[/color]
[snip]
[color=blue]
> A trial balance is basically reporting on 4 groups, Income, Expense,
> Assets and Liabilities. Each of these groups have sub-groups on which
> you are reporting the total amounts. You shouldn't have a sub-group
> name crossing any of these main groups. How does the app know which
> main group the sub-group belongs to.[/color]

Maybe I didn't explain this well enough earlier. You have the basic
form for financial statements correct. However, in addition to basic
financial statements (balance sheet and income statement), there are
several other schedules. So while a single account will be used only
once in the basic financial statements, it may be used again in
another schedule. This is why the Excel group function will not work
for me. The basic idea is to have one table for the trial balance
(account number is primary key) and one or more tables to indicate the
groups (essentially the page and line number assignments with each
line having a UID for the primary key (this was the basic content of
my original question). I would then have a table which would contain
two fields, account number and the UID for the line assignment. I
currently have a user-defined function in Excel that runs a query to
pull info from a mysql database based on an account number but could
be easily amended to do what I need.

So basically, I'm hung up on whether to have all pages/lines in one
table (which is where I'm leaning) or to segregate them into several
tables (which would require additional programming in Excel VBA I
think).
[color=blue]
> Or do you mean that your table holding the data has the same account
> code several times (which should be normal especially with income and
> expense groups). In this case you need to do a group query which will
> combine all like groups and produce the total for you.[/color]

I think the above answers this.

[snip]
[color=blue]
> OK, you're still stuck in the Excel data model mode. While Excel does
> have database-like capabilities they are very limited. You are
> entering the world of powerful databases where data manipulation is
> their forte.
>
> Post your database schema with about 6 lines of sample data (i.e.
> fudged - no real names or amounts please) for each table.[/color]

I don't have a formal database schema as of yet. I explained the
general idea above. Once I figure out the best way to to the
groupings and assignments, I should be able to get the database
structure created myself (but who knows).
[color=blue]
> [donning several bullet-proof vests and a couple of flame retardant
> suites][/color]

{Well I do accounting stuff for a living...how do you think I feel
:-)}
[color=blue]
> Why did you choose to use mySQL? Why not MSAccess? If you are new to
> databases then (even for all of its short-comings) Access is a good
> way to learn about databases.
>[/color]

I don't have access to Access.
[color=blue]
>
> [snip 2 end]
> ---------------------------------------------------------------
> jnorth@yourpant sbigpond.net.au : Remove your pants to reply
> ---------------------------------------------------------------[/color]

**Jeff North** · Jul 19 '05, 11:35 PM

Re: structure of database to prepare report

On 15 Nov 2003 16:53:20 -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:<pup5rvolh 04r1t65iancckt6 474vn6fcbr@4ax. com>...
>| > On 12 Nov 2003 05:06:09 -0800, in mailing.databas e.mysql
>| > mikemalin@hotma il.com (Michael Malinsky) wrote:
>| > >| Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<m1v0rvs6a oaj8ihidcnm76d0 4raeemo63c@4ax. com>...
>| > >| > On 10 Nov 2003 19:05:48 -0800, in mailing.databas e.mysql
>| > >| > mikemalin@hotma il.com (Michael Malinsky) wrote:
>| >
>| > [snip]
>| >
>| [snip]
>|
>| > A trial balance is basically reporting on 4 groups, Income, Expense,
>| > Assets and Liabilities. Each of these groups have sub-groups on which
>| > you are reporting the total amounts. You shouldn't have a sub-group
>| > name crossing any of these main groups. How does the app know which
>| > main group the sub-group belongs to.
>|
>| Maybe I didn't explain this well enough earlier. You have the basic
>| form for financial statements correct. However, in addition to basic
>| financial statements (balance sheet and income statement), there are
>| several other schedules. So while a single account will be used only
>| once in the basic financial statements, it may be used again in
>| another schedule.[/color]

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=blue]
>| This is why the Excel group function will not work
>| for me. The basic idea is to have one table for the trial balance
>| (account number is primary key) and one or more tables to indicate the
>| groups (essentially the page and line number assignments with each
>| line having a UID for the primary key (this was the basic content of
>| my original question). I would then have a table which would contain
>| two fields, account number and the UID for the line assignment. I
>| currently have a user-defined function in Excel that runs a query to
>| pull info from a mysql database based on an account number but could
>| be easily amended to do what I need.
>|
>| So basically, I'm hung up on whether to have all pages/lines in one
>| table (which is where I'm leaning) or to segregate them into several
>| tables (which would require additional programming in Excel VBA I
>| think).
>|
>| > Or do you mean that your table holding the data has the same account
>| > code several times (which should be normal especially with income and
>| > expense groups). In this case you need to do a group query which will
>| > combine all like groups and produce the total for you.
>|
>| I think the above answers this.
>|
>| [snip]
>|
>| > OK, you're still stuck in the Excel data model mode. While Excel does
>| > have database-like capabilities they are very limited. You are
>| > entering the world of powerful databases where data manipulation is
>| > their forte.
>| >
>| > Post your database schema with about 6 lines of sample data (i.e.
>| > fudged - no real names or amounts please) for each table.
>|
>| I don't have a formal database schema as of yet. I explained the
>| general idea above. Once I figure out the best way to to the
>| groupings and assignments, I should be able to get the database
>| structure created myself (but who knows).
>|
>| > [donning several bullet-proof vests and a couple of flame retardant
>| > suites]
>|
>| {Well I do accounting stuff for a living...how do you think I feel
>| :-)}
>|
>| > Why did you choose to use mySQL? Why not MSAccess? If you are new to
>| > databases then (even for all of its short-comings) Access is a good
>| > way to learn about databases.
>| >
>|
>| I don't have access to Access.
>|
>| >
>| > [snip 2 end]
>| > ---------------------------------------------------------------
>| > jnorth@yourpant sbigpond.net.au : Remove your pants to reply
>| > ---------------------------------------------------------------[/color]

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

**Jeff North** · Jul 19 '05, 11:35 PM

Re: structure of database to prepare report

On 15 Nov 2003 16:53:20 -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:<pup5rvolh 04r1t65iancckt6 474vn6fcbr@4ax. com>...
>| > On 12 Nov 2003 05:06:09 -0800, in mailing.databas e.mysql
>| > mikemalin@hotma il.com (Michael Malinsky) wrote:
>| > >| Jeff North <jnorth@yourpan tsbigpond.net.a u> wrote in message news:<m1v0rvs6a oaj8ihidcnm76d0 4raeemo63c@4ax. com>...
>| > >| > On 10 Nov 2003 19:05:48 -0800, in mailing.databas e.mysql
>| > >| > mikemalin@hotma il.com (Michael Malinsky) wrote:
>| >
>| > [snip]
>| >
>| [snip]
>|
>| > A trial balance is basically reporting on 4 groups, Income, Expense,
>| > Assets and Liabilities. Each of these groups have sub-groups on which
>| > you are reporting the total amounts. You shouldn't have a sub-group
>| > name crossing any of these main groups. How does the app know which
>| > main group the sub-group belongs to.
>|
>| Maybe I didn't explain this well enough earlier. You have the basic
>| form for financial statements correct. However, in addition to basic
>| financial statements (balance sheet and income statement), there are
>| several other schedules. So while a single account will be used only
>| once in the basic financial statements, it may be used again in
>| another schedule.[/color]

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=blue]
>| This is why the Excel group function will not work
>| for me. The basic idea is to have one table for the trial balance
>| (account number is primary key) and one or more tables to indicate the
>| groups (essentially the page and line number assignments with each
>| line having a UID for the primary key (this was the basic content of
>| my original question). I would then have a table which would contain
>| two fields, account number and the UID for the line assignment. I
>| currently have a user-defined function in Excel that runs a query to
>| pull info from a mysql database based on an account number but could
>| be easily amended to do what I need.
>|
>| So basically, I'm hung up on whether to have all pages/lines in one
>| table (which is where I'm leaning) or to segregate them into several
>| tables (which would require additional programming in Excel VBA I
>| think).
>|
>| > Or do you mean that your table holding the data has the same account
>| > code several times (which should be normal especially with income and
>| > expense groups). In this case you need to do a group query which will
>| > combine all like groups and produce the total for you.
>|
>| I think the above answers this.
>|
>| [snip]
>|
>| > OK, you're still stuck in the Excel data model mode. While Excel does
>| > have database-like capabilities they are very limited. You are
>| > entering the world of powerful databases where data manipulation is
>| > their forte.
>| >
>| > Post your database schema with about 6 lines of sample data (i.e.
>| > fudged - no real names or amounts please) for each table.
>|
>| I don't have a formal database schema as of yet. I explained the
>| general idea above. Once I figure out the best way to to the
>| groupings and assignments, I should be able to get the database
>| structure created myself (but who knows).
>|
>| > [donning several bullet-proof vests and a couple of flame retardant
>| > suites]
>|
>| {Well I do accounting stuff for a living...how do you think I feel
>| :-)}
>|
>| > Why did you choose to use mySQL? Why not MSAccess? If you are new to
>| > databases then (even for all of its short-comings) Access is a good
>| > way to learn about databases.
>| >
>|
>| I don't have access to Access.
>|
>| >
>| > [snip 2 end]
>| > ---------------------------------------------------------------
>| > jnorth@yourpant sbigpond.net.au : Remove your pants to reply
>| > ---------------------------------------------------------------[/color]

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

structure of database to prepare report

structure of database to prepare report

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment