Advice on how to do this project

[psy]

We have 8 campuses (to be 9 in the next year). Each of those campuses makes purchases each week. They must submit a purchase form and the buyers go out and buy what they have listed. We want to go to a campus allottment, meaning each campus has a certain budget that they must stay within. The director wants us to keep track of the individual cost of each item, the quantity of items purchased(per campus), and how much is left in each budget. Should we:

a) Use Excel to keep a running total of the purchases and balances

b) Use Access to create a database which will allow us to keep track of items purchased per campus and have a running total on that to let us know how much is left in each budget.

I am fairly new to Access, but am usually quick to understand something if given an example. I know that the project could be done in either program, but I wanted some advice from others who might have used both programs in this fashion.

Thanks,

psy

 

[Dwight]

I'm not an expert and am just giving you my advice because no one else did.

In my opinion Excel is good if you are doing something that is a one off. It is easy to set up a workbook to get quick results and you don't have to do a lot of pre-plannning.

A database gives you a lot more control over the data and it is easier to make nice looking reports. You can prevent users from entering bad data and messing about with what you have created a lot easier. The database is a better longer term solution but the downside is it will take longer to build (my experience anyway) and you definately have to do more upfront planning.

After reading your post I would try the database solution and if it doesn't work or proves too troublesome you can just whip something up in Excel.

Dwight

 

[neileg]

I think it's unlikely that Excel will give you what you want.

However, I would suggest that there's a lot more to this than simply setting up a database and you need to review the business process first.

For example, here's some questions to consider:

- At what point are you going to test the purchase against budget
a) At the point of the campus raising the requisition? If so you'll need some way of attaching an estimated purchase price to the order
b) At the point of raising the order by the buyers? If so, you are going to have to devise a method of stopping any further requistions being raised by the campus
c) At the point of invoice? Too late!

- How and when are you going to capture the data? Ideally this needs to be as near the 'front' of the process as possible. Online requisitioning should be your aim. This will mean real time connectivity and will depend on you having a WAN coving the campuses, or providing dial up or similar connectivity

- Are you going to profile the budget? Is this going to be allocated to each month or are you going to just give an annual sum? The problem with annual budgets is that this tends to skew purchasing leading to all the money being used in the first few months, or it is 'hoarded' and spent in the last month

 

[psy]

Thanks for your replies. I have already started attempting the Access database. So far I have created a subform for the purchase orders (invoices). I am having a problem with the Total for the PO. I have a column for Quantity and one for Cost. Then I have a column for Subtotal (Quantity * Cost). This textbox is of course unbound. Then in the footer of the subform, I try to do a Total with a running sum. Knowing that you have to repeat the formula for the unbound textbox, I put =Sum(Quantity*Cost) but I get #error. I was going to put this subform into another form which keeps a running sum of the total spent and balance left, but I don't know why I am getting the error. I am using Access 2000 on an XP machine.

Dwight, I guess I am taking your advice by attempting the Access database first. What I want is something that is more difficult to mess up by someone who is not too familiar with programs. My fear with using Excel is that someone could enter something incorrectly and simply delete it, easily deleting something that they should not. We have a couple of "newbies" in our midst here, and I would like to make this as user friendly as possible.

Neil, the database would be for the purchasers to keep track of purchases and budgets. Each week they will report back to the campuses what is left in their budget. The campuses have no clue as to the price of items, so they are going to get estimates from the purchasers as they create their requisitions. The purchasers will also look at the requisition and substitute items if necessary to reduce cost. The technological knowledge of our organization does not lend itself to online requisitioning at this time.

I appreciate both your responses. I will keep trying on the Access DB and if that falls through, resort back to Excel.

Thanks again!

psy

 

[neileg]

I have a column for Quantity and one for Cost. Then I have a column for Subtotal (Quantity * Cost). This textbox is of course unbound. Then in the footer of the subform, I try to do a Total with a running sum. Knowing that you have to repeat the formula for the unbound textbox, I put =Sum(Quantity*Cost) but I get #error. I was going to put this subform into another form which keeps a running sum of the total spent and balance left, but I don't know why I am getting the error.

It is good practice to base your form on a query based on your table, not the table itself. I would include a calculated field in the query for Quantity * Cost and add a sum box for the calculated field.

 

[psy]

So I would need a query containing the Quantity and Cost fields and an unbound field of Subtotal? Then I would pull in Total in the footer by pulling in the Subtotal? Or should I put the Subtotal category and then have a query for the Total? I'm just beginning this. I did figure out why I was getting the #error. Just used wrong syntax.

 

[neileg]

No, the record source for the form should be a query. So all of your data will come from the query, including the calculated field Subtotal. Then your total box will just be =Sum([Subtotal]).

Yes, your basic approach should have worked in calculating the Total, but I wanted to point out the use of the query as the record source.

 

[psy]

OK. I understand that and have made the adjustments. Now everything works fine. The only thing I need to work out is how to establish 8 separate balances within the same database. I guess I need to find examples of invoicing and how customers are set up. I need to reflect 8 separate accounts basically. I'm off to see if I can find examples.


Thanks for all your help!!!

psy

 

[neileg]

That's why you use a query for the form. You need to select only those transactions that relate to the account you want.