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.
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. I'm not sure how many pages the report will
be, but let's say my Excel workbook would have 50 sheets. 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.
TIA
Mike
24  4132 
On 9 Nov 2003 20:12:26 -0800, in mailing.database.mysql mi*******@hotmail.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?
| 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.
| 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 :-)
| 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.
I seriously doubt that a trial balance would go over 32,000 cells in
the spreadsheet so all should fit on a single sheet.
--------------------------------------------------------------- jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
On 9 Nov 2003 20:12:26 -0800, in mailing.database.mysql mi*******@hotmail.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?
| 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.
| 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 :-)
| 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.
I seriously doubt that a trial balance would go over 32,000 cells in
the spreadsheet so all should fit on a single sheet.
--------------------------------------------------------------- jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<7q********************************@4ax.com>. .. On 9 Nov 2003 20:12:26 -0800, in mailing.database.mysql
mi*******@hotmail.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.
| 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)...several accounts from the trial balance will be
added together to create one result in Excel.
| 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. :-)
| 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?
I seriously doubt that a trial balance would go over 32,000 cells in
the spreadsheet so all should fit on a single sheet.
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<7q********************************@4ax.com>. .. On 9 Nov 2003 20:12:26 -0800, in mailing.database.mysql
mi*******@hotmail.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.
| 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)...several accounts from the trial balance will be
added together to create one result in Excel.
| 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. :-)
| 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?
I seriously doubt that a trial balance would go over 32,000 cells in
the spreadsheet so all should fit on a single sheet.
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
On 10 Nov 2003 19:05:48 -0800, in mailing.database.mysql mi*******@hotmail.com (Michael Malinsky) wrote: | Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<7q********************************@4ax.com>. ..
| > On 9 Nov 2003 20:12:26 -0800, in mailing.database.mysql
| > mi*******@hotmail.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.
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.
| > >| 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)...several accounts from the trial balance will be
| added together to create one result in Excel.
No you don't - trust me :-)
| > >| 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. :-)
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 :-)
| > >| 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?
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.
--------------------------------------------------------------- jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
On 10 Nov 2003 19:05:48 -0800, in mailing.database.mysql mi*******@hotmail.com (Michael Malinsky) wrote: | Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<7q********************************@4ax.com>. ..
| > On 9 Nov 2003 20:12:26 -0800, in mailing.database.mysql
| > mi*******@hotmail.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.
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.
| > >| 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)...several accounts from the trial balance will be
| added together to create one result in Excel.
No you don't - trust me :-)
| > >| 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. :-)
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 :-)
| > >| 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?
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.
--------------------------------------------------------------- jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<m1********************************@4ax.com>. .. On 10 Nov 2003 19:05:48 -0800, in mailing.database.mysql
mi*******@hotmail.com (Michael Malinsky) wrote:
| Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<7q********************************@4ax.com>. ..
| > On 9 Nov 2003 20:12:26 -0800, in mailing.database.mysql
| > mi*******@hotmail.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.
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.
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.
| > >| 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)...several accounts from the trial balance will be
| added together to create one result in Excel.
No you don't - trust me :-)
| > >| 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. :-)
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 :-)
The report is required by a third party, so internal thoughts on the
paper bill are moot.
| > >| 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?
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.
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.
Remember the K.I.S principle.
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<m1********************************@4ax.com>. .. On 10 Nov 2003 19:05:48 -0800, in mailing.database.mysql
mi*******@hotmail.com (Michael Malinsky) wrote:
| Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<7q********************************@4ax.com>. ..
| > On 9 Nov 2003 20:12:26 -0800, in mailing.database.mysql
| > mi*******@hotmail.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.
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.
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.
| > >| 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)...several accounts from the trial balance will be
| added together to create one result in Excel.
No you don't - trust me :-)
| > >| 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. :-)
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 :-)
The report is required by a third party, so internal thoughts on the
paper bill are moot.
| > >| 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?
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.
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.
Remember the K.I.S principle.
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
On 12 Nov 2003 05:06:09 -0800, in mailing.database.mysql mi*******@hotmail.com (Michael Malinsky) wrote: | Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<m1********************************@4ax.com>. ..
| > On 10 Nov 2003 19:05:48 -0800, in mailing.database.mysql
| > mi*******@hotmail.com (Michael Malinsky) wrote:
[snip]
| 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.
[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.
| 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.
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]
--------------------------------------------------------------- jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
On 12 Nov 2003 05:06:09 -0800, in mailing.database.mysql mi*******@hotmail.com (Michael Malinsky) wrote: | Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<m1********************************@4ax.com>. ..
| > On 10 Nov 2003 19:05:48 -0800, in mailing.database.mysql
| > mi*******@hotmail.com (Michael Malinsky) wrote:
[snip]
| 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.
[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.
| 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.
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]
--------------------------------------------------------------- jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<pu********************************@4ax.com>. .. On 12 Nov 2003 05:06:09 -0800, in mailing.database.mysql
mi*******@hotmail.com (Michael Malinsky) wrote:| Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<m1********************************@4ax.com>. ..
| > On 10 Nov 2003 19:05:48 -0800, in mailing.database.mysql
| > mi*******@hotmail.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. 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]
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<pu********************************@4ax.com>. .. On 12 Nov 2003 05:06:09 -0800, in mailing.database.mysql
mi*******@hotmail.com (Michael Malinsky) wrote:| Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<m1********************************@4ax.com>. ..
| > On 10 Nov 2003 19:05:48 -0800, in mailing.database.mysql
| > mi*******@hotmail.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. 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]
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
On 15 Nov 2003 16:53:20 -0800, in mailing.database.mysql mi*******@hotmail.com (Michael Malinsky) wrote: | Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<pu********************************@4ax.com>. ..
| > On 12 Nov 2003 05:06:09 -0800, in mailing.database.mysql
| > mi*******@hotmail.com (Michael Malinsky) wrote:
| > >| Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<m1********************************@4ax.com>. ..
| > >| > On 10 Nov 2003 19:05:48 -0800, in mailing.database.mysql
| > >| > mi*******@hotmail.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.
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.
| 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]
| > ---------------------------------------------------------------
| > jn****@yourpantsbigpond.net.au : Remove your pants to reply
| > ---------------------------------------------------------------
--------------------------------------------------------------- jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
On 15 Nov 2003 16:53:20 -0800, in mailing.database.mysql mi*******@hotmail.com (Michael Malinsky) wrote: | Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<pu********************************@4ax.com>. ..
| > On 12 Nov 2003 05:06:09 -0800, in mailing.database.mysql
| > mi*******@hotmail.com (Michael Malinsky) wrote:
| > >| Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<m1********************************@4ax.com>. ..
| > >| > On 10 Nov 2003 19:05:48 -0800, in mailing.database.mysql
| > >| > mi*******@hotmail.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.
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.
| 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]
| > ---------------------------------------------------------------
| > jn****@yourpantsbigpond.net.au : Remove your pants to reply
| > ---------------------------------------------------------------
--------------------------------------------------------------- jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
[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). 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.
[snip 2 end]
[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). 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.
[snip 2 end]
On 16 Nov 2003 18:39:07 -0800, in mailing.database.mysql mi*******@hotmail.com (Michael Malinsky) wrote: | [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).
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.
| 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.
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.
| I do appreciate the help your providing me with.
|
| Thanks.
|
| >
| > [snip 2 end]
--------------------------------------------------------------- jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
On 16 Nov 2003 18:39:07 -0800, in mailing.database.mysql mi*******@hotmail.com (Michael Malinsky) wrote: | [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).
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.
| 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.
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.
| I do appreciate the help your providing me with.
|
| Thanks.
|
| >
| > [snip 2 end]
--------------------------------------------------------------- jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<bn********************************@4ax.com>. .. On 16 Nov 2003 18:39:07 -0800, in mailing.database.mysql
mi*******@hotmail.com (Michael Malinsky) wrote:
| [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).
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.
| 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.
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.
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?
| I do appreciate the help your providing me with.
|
| Thanks.
|
| >
| > [snip 2 end]
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<bn********************************@4ax.com>. .. On 16 Nov 2003 18:39:07 -0800, in mailing.database.mysql
mi*******@hotmail.com (Michael Malinsky) wrote:
| [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).
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.
| 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.
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.
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?
| I do appreciate the help your providing me with.
|
| Thanks.
|
| >
| > [snip 2 end]
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
On 18 Nov 2003 07:02:41 -0800, in mailing.database.mysql mi*******@hotmail.com (Michael Malinsky) wrote: | Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<bn********************************@4ax.com>. ..
| > On 16 Nov 2003 18:39:07 -0800, in mailing.database.mysql
| > mi*******@hotmail.com (Michael Malinsky) wrote:
[snip]
| > >| 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.
Why are you re-inventing the wheel. What is wrong with the accounting
packages trial balance report.
[snip 2 end]
--------------------------------------------------------------- jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
On 18 Nov 2003 07:02:41 -0800, in mailing.database.mysql mi*******@hotmail.com (Michael Malinsky) wrote: | Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<bn********************************@4ax.com>. ..
| > On 16 Nov 2003 18:39:07 -0800, in mailing.database.mysql
| > mi*******@hotmail.com (Michael Malinsky) wrote:
[snip]
| > >| 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.
Why are you re-inventing the wheel. What is wrong with the accounting
packages trial balance report.
[snip 2 end]
--------------------------------------------------------------- jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<qt********************************@4ax.com>. .. On 18 Nov 2003 07:02:41 -0800, in mailing.database.mysql
mi*******@hotmail.com (Michael Malinsky) wrote:
| Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<bn********************************@4ax.com>. ..
| > On 16 Nov 2003 18:39:07 -0800, in mailing.database.mysql
| > mi*******@hotmail.com (Michael Malinsky) wrote:
[snip]
| > >| 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.
Why are you re-inventing the wheel. What is wrong with the accounting
packages trial balance report.
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.
[snip 2 end]
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<qt********************************@4ax.com>. .. On 18 Nov 2003 07:02:41 -0800, in mailing.database.mysql
mi*******@hotmail.com (Michael Malinsky) wrote:
| Jeff North <jn****@yourpantsbigpond.net.au> wrote in message news:<bn********************************@4ax.com>. ..
| > On 16 Nov 2003 18:39:07 -0800, in mailing.database.mysql
| > mi*******@hotmail.com (Michael Malinsky) wrote:
[snip]
| > >| 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.
Why are you re-inventing the wheel. What is wrong with the accounting
packages trial balance report.
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.
[snip 2 end]
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
--------------------------------------------------------------- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Michael Malinsky |
last post by:
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.
The general idea I...
|
by: Cern |
last post by:
Is it somebody out there who has made a migration from an Oracle server to an MySQL server??
The scenario is as simply:
I've got a Oracle 8 server with a database with content that I want to...
|
by: Ray |
last post by:
I just completed a database and would like to prepare the documentation for
it. As I have no experience to do so, can someone advise me the essential
elements for the documentation and any example...
|
by: Bart Torbert |
last post by:
For testing purposes it would be convenient to load dummy values into
a set of C structures. These structures are complex (in the sense of
a mix of data types), they are quite large (total number...
|
by: sck10 |
last post by:
Hello,
I am building a web form that will be used to gather information for
marketing plans. The form will have 15 questions which must be answered.
Each question can have large blocks of text....
|
by: Rameel |
last post by:
Friends,
I'm probably being more critical with VB.Net Windows application.
I have Developed VisualStudio 20005 VB.Net Windows application
how willl i be able to save a specific record into my...
|
by: Sport Girl |
last post by:
Hi everybody.
I am new in PERL language and working in it since little hours, actually i am still a trainee and i need help please.
In fact, i need a script in PERL that enables me to retrieve...
|
by: Sport Girl |
last post by:
Hi everybody ,
i have the task of developing in Perl a script that retrieves data from 3 tables ( bugs, profiles, products) from a MySQL database called bugs and display them in an excel sheet...
|
by: ajhayes |
last post by:
I posted a question here last week about an ordering database I'm trying to set up, and got some excellent help. So I'm hoping some of you might be able to get me on the right track with another...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |
