Budget Problems

[aneilh]

Hello, I am using MS Access 2003 and creating a inventory management database for a Hotel Chain. However, i am a bit lost on creating a budget for each hotel in the chain, as each hotel has a bugdet of say £200,000, and each time an order takes place it must take this away. If anyone has any ideas...

Thanks

 

[jrbarnett]

In a table with a list of hotels, have a primary key value of name something like HotelID.

You then have another table to contain the transaction details. Put in here the HotelID value as a foreign key reference to the first table, the transaction amount, date, notes and anything else that you may need.

Your balance therefore is:

Total Budget (in the first table) - the sum of the balances for that hotel ID

If you need to keep records across budget years, then you need a separate table with a hotel ID, year ID and budget; if not, it can be just another field in the main hotel details table.

John

 

[CaptainD]

I would create one program that would work for all chains.
(Even if they are installed independently at each location)

Do you understand "Relational Databases"?

Are you taking care of all the inventory and budgets from one location?

Do you want a Client Server setup?

The Amount of the budget in immaterial, your program needs to be flexable enough to takin in Credits and subtract debits.

Needs to be able to add account numbers, inventory numbers

For a simple system I would have a support table

iSupportID	sCategory	sItem	sDescription
1	AccountPrefix	61000	For account categories
2	AccountSuffix	12345	Indivdual accounts
3	CreditDebit	1	Credit
4	CreditDebit	0	Debit
5	AccountSuffix	09876	Luandry Supplies
6	AccountSuffix	87654	Linen supplies
7	Hotel	Mariott	Hotel Chain

A budget worksheet

iBudgetID	sAccountNumber	iCreditAmount	iDebitAmount	sComment	fkHotel	dDate	CreditDebitType
1	61000-0000	$700,000.00	$0.00	Total budget	7	1/1/2008	0
2	61000-87654	$100,000.00	$0.00	Initial account balance	7	1/1/2008	1

This is very simple and would need work but might get you started.

 

[aneilh]

Thanks