Desgin issue

[kfiore]

I need help in designing an expense table in Access 2003.
I have created a Database for keeping track of all of my Personal expenses
and Reimbursements for business. I work for a company that owns many
different types of Business under different company names, which is becoming
a problem when I turn in Expense/Reimbursement report to our controller. I
end up doing a simple query and dividing everything up in Excel. I want to
find an efficient way to divide up the data by Company->Project->Expense
Type->(and if the expense type is by credit card then by credit card-> credit
card statement). Currently I have one table that handles all of my expense
entries. The main table has the following Fields:
Date:
Ammount:
Store:
Type: (looks up information from a Type Table) example: gas, hotel, etc
Category: (looks up information from a Category Table) example: personal,
Business 1, business 2 etc..
Trip/Location: (looks up information from Trip/Location Table) example:
Florida, VT, Vegas, etc
Description:
Payment Type: (looks up information from a Payment Type Table) example:
Cash, Credit Card, Check, ACH, Wire, etc
Credit Card: (looks up information from a CC information Table) example:
1234, 4534, 2342 (it the last 4 digits of the credit card used)

I am having issues in the way I designed this Table and would like some
suggestions on how to proceed.

1st. If the [Payment Type] field is anything othere than "Credit Card" then
[Credit Card] field isn't filled in, which doesn't seem efficeint.

2nd. I am wondering If it would be better to have 2 seperate tables, one
for Personal expenses, the other for Reimbursements, becasue I would like to
be able to divide my Reimbursemnts into sub categories for each business
entity they are for and then to specific Jobs/Projects that we are currently
working on.

This is really giving me a headache and creating a lot of wasted time spent
dividing everything up in excel. I would appreciate any suggestions on how
to redesign the table/Database. Thank you.

 

[mnature]

Microsoft posts a number of different templates. I would suggest looking
under Business or Finance and Accounting. Once they are downloaded, you can
customize them, also.

http://office.microsoft.com/en-us/templates/CT011359381033.aspx

 

[kfiore]

That database really doesn't address any of the issues I am having. Anyone
else?

Microsoft posts a number of different templates. I would suggest looking
under Business or Finance and Accounting. Once they are downloaded, you can
customize them, also.

http://office.microsoft.com/en-us/templates/CT011359381033.aspx

I need help in designing an expense table in Access 2003.
I have created a Database for keeping track of all of my Personal expenses
and Reimbursements for business. I work for a company that owns many
different types of Business under different company names, which is becoming
a problem when I turn in Expense/Reimbursement report to our controller. I
end up doing a simple query and dividing everything up in Excel. I want to
find an efficient way to divide up the data by Company->Project->Expense
Type->(and if the expense type is by credit card then by credit card-> credit
card statement). Currently I have one table that handles all of my expense
entries. The main table has the following Fields:
Date:
Ammount:
Store:
Type: (looks up information from a Type Table) example: gas, hotel, etc
Category: (looks up information from a Category Table) example: personal,
Business 1, business 2 etc..
Trip/Location: (looks up information from Trip/Location Table) example:
Florida, VT, Vegas, etc
Description:
Payment Type: (looks up information from a Payment Type Table) example:
Cash, Credit Card, Check, ACH, Wire, etc
Credit Card: (looks up information from a CC information Table) example:
1234, 4534, 2342 (it the last 4 digits of the credit card used)

I am having issues in the way I designed this Table and would like some
suggestions on how to proceed.

1st. If the [Payment Type] field is anything othere than "Credit Card" then
[Credit Card] field isn't filled in, which doesn't seem efficeint.

2nd. I am wondering If it would be better to have 2 seperate tables, one
for Personal expenses, the other for Reimbursements, becasue I would like to
be able to divide my Reimbursemnts into sub categories for each business
entity they are for and then to specific Jobs/Projects that we are currently
working on.

This is really giving me a headache and creating a lot of wasted time spent
dividing everything up in excel. I would appreciate any suggestions on how
to redesign the table/Database. Thank you.

 

[Jeff Boyce]

If you have spent considerable time and effort trying to make this work,
have you considered discarding it and using one of the fairly inexpensive
off-the-shelf accounting solutions?

Regards

Jeff Boyce
<Office/Access MVP>

 

[kfiore]

I would rather do it myself. There is nothing out there that can divide up
the data I have and do the kind of reports my boss/controller wants. I buy a
lot of things for a lot of different companies. I would just end doing them
in excel so I need to find a solution. anyone else....

If you have spent considerable time and effort trying to make this work,
have you considered discarding it and using one of the fairly inexpensive
off-the-shelf accounting solutions?

Regards

Jeff Boyce
<Office/Access MVP>

I need help in designing an expense table in Access 2003.
I have created a Database for keeping track of all of my Personal expenses
and Reimbursements for business. I work for a company that owns many
different types of Business under different company names, which is
becoming
a problem when I turn in Expense/Reimbursement report to our controller.
I
end up doing a simple query and dividing everything up in Excel. I want
to
find an efficient way to divide up the data by Company->Project->Expense
Type->(and if the expense type is by credit card then by credit card->
credit
card statement). Currently I have one table that handles all of my
expense
entries. The main table has the following Fields:
Date:
Ammount:
Store:
Type: (looks up information from a Type Table) example: gas, hotel, etc
Category: (looks up information from a Category Table) example: personal,
Business 1, business 2 etc..
Trip/Location: (looks up information from Trip/Location Table) example:
Florida, VT, Vegas, etc
Description:
Payment Type: (looks up information from a Payment Type Table) example:
Cash, Credit Card, Check, ACH, Wire, etc
Credit Card: (looks up information from a CC information Table) example:
1234, 4534, 2342 (it the last 4 digits of the credit card used)

I am having issues in the way I designed this Table and would like some
suggestions on how to proceed.

1st. If the [Payment Type] field is anything othere than "Credit Card"
then
[Credit Card] field isn't filled in, which doesn't seem efficeint.

2nd. I am wondering If it would be better to have 2 seperate tables, one
for Personal expenses, the other for Reimbursements, becasue I would like
to
be able to divide my Reimbursemnts into sub categories for each business
entity they are for and then to specific Jobs/Projects that we are
currently
working on.

This is really giving me a headache and creating a lot of wasted time
spent
dividing everything up in excel. I would appreciate any suggestions on
how
to redesign the table/Database. Thank you.

 

[mnature]

Since you are bound-and-determined to do this yourself, I would suggest
starting from the beginning. It could be that you just got a bad start on
designing your tables. You mention that you divide up the information by
Company, then Project, then Expense Type. My suggestion would be that you
start out by having separate tables for each of these, and then relate those
tables to each other. These tables are just the bare-bones, to illustrate
how they relate to each other. You will also need a table that just has all
of your expenses, which will then be linked through foreign keys to the other
tables.

tblCompany
CompanyID (PK)
CompanyName

tblProject
ProjectID (PK)
ProjectName
CompanyID

tblExpenseType
ExpenseTypeID (PK)
ExpenseTypeName

tblLocation
LocationID (PK)
LocationName

tblExpenses
ExpensesID (PK)
ExpenseTypeID
ProjectID
LocationID
ExpenseAmount

Each expense is a separate record, which is then associated with a project,
a location, and an expense type. This would be where the amount of the
expense is recorded.

I know this does not cover all of your details, but it is one way to set
this up as a true relational database. All the information about various
entities should be grouped in their own tables. So you need tables for
companies, projects, expense types, locations, and the expenses themselves.
Any details will fall naturally into those tables. Of course, you will need
to link these tables in the relationships window. Once you have the
relationships in place, your can set up queries, forms and reports to gather
together the data in any form you need it. You should not need Excel to do
that.

I need help in designing an expense table in Access 2003.
I have created a Database for keeping track of all of my Personal expenses
and Reimbursements for business. I work for a company that owns many
different types of Business under different company names, which is becoming
a problem when I turn in Expense/Reimbursement report to our controller. I
end up doing a simple query and dividing everything up in Excel. I want to
find an efficient way to divide up the data by Company->Project->Expense
Type->(and if the expense type is by credit card then by credit card-> credit
card statement). Currently I have one table that handles all of my expense
entries. The main table has the following Fields:
Date:
Ammount:
Store:
Type: (looks up information from a Type Table) example: gas, hotel, etc
Category: (looks up information from a Category Table) example: personal,
Business 1, business 2 etc..
Trip/Location: (looks up information from Trip/Location Table) example:
Florida, VT, Vegas, etc
Description:
Payment Type: (looks up information from a Payment Type Table) example:
Cash, Credit Card, Check, ACH, Wire, etc
Credit Card: (looks up information from a CC information Table) example:
1234, 4534, 2342 (it the last 4 digits of the credit card used)

I am having issues in the way I designed this Table and would like some
suggestions on how to proceed.

1st. If the [Payment Type] field is anything othere than "Credit Card" then
[Credit Card] field isn't filled in, which doesn't seem efficeint.

2nd. I am wondering If it would be better to have 2 seperate tables, one
for Personal expenses, the other for Reimbursements, becasue I would like to
be able to divide my Reimbursemnts into sub categories for each business
entity they are for and then to specific Jobs/Projects that we are currently
working on.

This is really giving me a headache and creating a lot of wasted time spent
dividing everything up in excel. I would appreciate any suggestions on how
to redesign the table/Database. Thank you.

 

[kfiore]

Thank you, I do appreciate it. I will add a Company and Project Table ( I
already have the other tables mentioned in your post). What do you
recommend I do about dealing with the different ways I pay things (Credit
Cards, Cash, Wire, Etc)? Below is a list of my expense and credit card table
I currently have.

tblExpenses:
ExpenseID (PK)
Date
Ammount
Store
Type
Category
Location
Description
Payment Type (If the payment is with a credit card, I use the additional
Credit Card field to specify which one).
Credit Card

tblCreditCards
Card Number (PK)
Type of Card (Visa, MasterCard, etc)
Bank

Once again thank you for your help.

Since you are bound-and-determined to do this yourself, I would suggest
starting from the beginning. It could be that you just got a bad start on
designing your tables. You mention that you divide up the information by
Company, then Project, then Expense Type. My suggestion would be that you
start out by having separate tables for each of these, and then relate those
tables to each other. These tables are just the bare-bones, to illustrate
how they relate to each other. You will also need a table that just has all
of your expenses, which will then be linked through foreign keys to the other
tables.

tblCompany
CompanyID (PK)
CompanyName

tblProject
ProjectID (PK)
ProjectName
CompanyID

tblExpenseType
ExpenseTypeID (PK)
ExpenseTypeName

tblLocation
LocationID (PK)
LocationName

tblExpenses
ExpensesID (PK)
ExpenseTypeID
ProjectID
LocationID
ExpenseAmount

Each expense is a separate record, which is then associated with a project,
a location, and an expense type. This would be where the amount of the
expense is recorded.

I know this does not cover all of your details, but it is one way to set
this up as a true relational database. All the information about various
entities should be grouped in their own tables. So you need tables for
companies, projects, expense types, locations, and the expenses themselves.
Any details will fall naturally into those tables. Of course, you will need
to link these tables in the relationships window. Once you have the
relationships in place, your can set up queries, forms and reports to gather
together the data in any form you need it. You should not need Excel to do
that.

I need help in designing an expense table in Access 2003.
I have created a Database for keeping track of all of my Personal expenses
and Reimbursements for business. I work for a company that owns many
different types of Business under different company names, which is becoming
a problem when I turn in Expense/Reimbursement report to our controller. I
end up doing a simple query and dividing everything up in Excel. I want to
find an efficient way to divide up the data by Company->Project->Expense
Type->(and if the expense type is by credit card then by credit card-> credit
card statement). Currently I have one table that handles all of my expense
entries. The main table has the following Fields:
Date:
Ammount:
Store:
Type: (looks up information from a Type Table) example: gas, hotel, etc
Category: (looks up information from a Category Table) example: personal,
Business 1, business 2 etc..
Trip/Location: (looks up information from Trip/Location Table) example:
Florida, VT, Vegas, etc
Description:
Payment Type: (looks up information from a Payment Type Table) example:
Cash, Credit Card, Check, ACH, Wire, etc
Credit Card: (looks up information from a CC information Table) example:
1234, 4534, 2342 (it the last 4 digits of the credit card used)

I am having issues in the way I designed this Table and would like some
suggestions on how to proceed.

1st. If the [Payment Type] field is anything othere than "Credit Card" then
[Credit Card] field isn't filled in, which doesn't seem efficeint.

2nd. I am wondering If it would be better to have 2 seperate tables, one
for Personal expenses, the other for Reimbursements, becasue I would like to
be able to divide my Reimbursemnts into sub categories for each business
entity they are for and then to specific Jobs/Projects that we are currently
working on.

This is really giving me a headache and creating a lot of wasted time spent
dividing everything up in excel. I would appreciate any suggestions on how
to redesign the table/Database. Thank you.