Complex Importing of Excel accounting data!

[gtroiano]

Here is the set up:

Table 1:
[p]AccountsId - AutoNumber
AccountTitle - Text
AccountNumber - Long Integer
BeginningBalance - Currency
Income - Currency
Expenses - Currency
Balance - Currency

Table 2:
[p]FamsId - Autonumber
[f]AccountsId - Long Integer
FamsNumber - Long Integer

The two tables are joined on AccountsId by a Many-One relationship with referential integrity on.

The reason for the seperate tables is this: Some AccountNumbers have multiple FamsNumbers assigned to them. The amount of money in each account is then divided between the FamsNumbers, sometimes an even split and sometimes odd (i.e. 17%, 23%, 60%). if that makes sense?

The tables need to be updated automatically from excel.

The excel import data would look something like this:
AccountNumber Beginning Income Expenses Balance
049253 20,000 5,000 25,000 0
035744 2,000 500 2,500 0

Account '049253' is split 17%, 23%, 60% but '035774' is not split at all. so, the table should look like this after it is imported into access:
AN = AccountNumber
FN = FamsNumber

AN FN Title Beginning Income Expense Balance
049253 40108 xyz 3,400 850 4,250 0
049253 40107 xyz 4,600 1,150 5,750 0
049253 40106 xyz 12,000 3,000 15,000 0
035774 22823 Abc 2,000 500 2,500 0

my thinking led me to this; after the all the data is imported into a temporary table, all the accounts that split up the money need to be put in yet another temporary table and the other can just be imported into the main table. i am not sure if this is the right track, but if it is, i assume i need some kind of if...then...else to to split up the accounts. i need some serious help here, if anyone can help i would greatly appreciate it.

jerry.

 

[MichaelRed]

Hmmmmmmmmmmmm,

just two points,

First, I'm personally a BIG anti fan of excel and most of it's users (actually more of it's abusers than anything else). They (the ABUSERS) are like the reputation of defensive playsers in the NFL - more or less totally UNDICIPLINEDm so my first suggestion is to replace whatever is being done in / with excel with a robust group of forms / tables / queries / modules in MS A. or VB.

Second -IF- you persist in dealing with the present situation, the approach does not say how you get / use the family 'share' function. I would have EVERY acccount have a set of 1 or more share entries (the non-shared accounts would simply default to the 100%). Import the info and use the single process to always divde the transaction per the share information. The results shoould be same as you describe - just less logic involved.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Koolaid99]

I think I can help you here. your going to need some more fields at the look of it. I don't get what you mean by the tables need to be automatically updated by excel? Did you just want to link Table one so it is using data from the Excel file?

Anyway I'll write what I think will help you in a good explination.

 

[gtroiano]

thanks for the responses!

koolaid99:
by saying "automatically updated from excel", i mean that the file from which the data comes from is a spreadsheet, in excel format, given to me by the accounting department.

michaelred:
i personally have nothing against excel and that's what the man gives me, so i work with it.
about these "share entries", using my example of a split of 17%, 23%, 60%, would i just have a an extra field for each record wiht the percentage that it gets divided? if so, i think i understand but the code to actually perform the split eludes me.

jerry.

 

[Koolaid99]

First Table (Or link to File)
AccountTitle - Text
[P]AccountNumber - Long Interger
You shouldn't need AccountsID if your accountnumber shouldn't be reppeated in this table.
BeginningBalance - Currency
Income - Currency
Expenses - Currency
I wouldn't use balance as its a calculated field

Second Table
[P]FamsID - Autonumber
AccountNumber - Long Integer
FamsNumber - Long Integer
FamsPercent - Number default to 1

Make relation with a one to many on AccountNumber

First Query

Use wizard and choose there fields from table 100
AccountNumber
AccountTitle

And use these from table 2
FamsNumber

Then go to design view. Type this into the Field Names or just click a empty space and click build. Make the follow

Beginning:=[BeginningBalance]*[FamsPercent]/100

TotalIncome:=[Income]*[FamsPercent]/100

TotalExpenses:=[Exspenses]*[FamsPercent]/100

Balance:=[Beginning] + [TotalIncome] - [TotalExpenses]

I think that would give you your output you need.

 

[Koolaid99]

DOH!! Just read this was hoping to have blue background. Reposting hopeing for Blue. Also correcting error with set default to 1 ment 100 and choose from table 100 to table 1. Not perfect

First Table (Or link to File)
AccountTitle - Text
[P]AccountNumber - Long Interger
You shouldn't need AccountsID if your accountnumber shouldn't be reppeated in this table.
BeginningBalance - Currency
Income - Currency
Expenses - Currency
I wouldn't use balance as its a calculated field

Second Table
[P]FamsID - Autonumber
AccountNumber - Long Integer
FamsNumber - Long Integer
FamsPercent - Number default to 100

Make relation with a one to many on AccountNumber

First Query

Use wizard and choose there fields from table 1
AccountNumber
AccountTitle

And use these from table 2
FamsNumber

Then go to design view. Type this into the Field Names or just click a empty space and click build. Make the follow

Beginning:=[BeginningBalance]*[FamsPercent]/100

TotalIncome:=[Income]*[FamsPercent]/100

TotalExpenses:=[Exspenses]*[FamsPercent]/100

Balance:=[Beginning] + [TotalIncome] - [TotalExpenses]

I think that would give you your output you need.

 

[MichaelRed]

yes, but koolaid is close enough to the soloution

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over