Database Help

[tomdelonge]

I have recently been asked to make a computer file to store some basic details. It has to store some basic client details but also to store records of financial details. I initially did it in excel as it needs to find and calculate from a 'source sheet'. But excel is not good at storing records all in one place so I thought I would try do it in access. It has been a good 6 years since I have touched access in such an 'advanced' way. (this is advanced for me) So I was wondering if someone could give me any tips on how to go about doing this.
I have attatched a copy of the spreadsheet to make it easier for you to understand what I am babbling on about. It has been scanned with AVG.

I have office 2007 and 2003.

 

[Uncle Gizmo]

It is not clear to me from looking at the spreadsheet what you want to do. Please could you provide more information.

 

[tomdelonge]

I am wanting to put all the info in the spreadsheet into a database. So that the benefit can be selected for each client and then a total calculation and result for all the benefits and arreas.

I could have columns like
"Client Name"
"Arreas Amount"
"Arreas Award Date"
"Benefit Applied 1"
"Benefit Applied 2"
"Benefit Applied 3"
"Total"

So the total would be the calculation of Arreas Amount + BA1 + 2 + 3.

The database would look, like the spreadsheet, at a reference area to get the amount for BA1 + 2 + 3

The advice worker who asked me to do this wants to be able to print off a page with like 20 or so clients all on one page with all this info on. Something that is difficult to do on Excel as you have to keep duplicating the cells and I keep finding problems with this as it doesn't duplicate the vlookup function sometimes. Ideally the advice worker will have very little to do with the ins and outs of the database. Just inputting the data then printing it off. This is an alternative to carrying around books and trawling through paper records to find out history for clients.

 

[Fear Naught]

You will need 3 tables as follows:

tblClient with following columns

ClientID - PK
ClientName

tblArreas with:

ArreasID - PK
ClientID - Foreign Key on tblClient.ClientID
ArreasDate
ArreasAmount

tblBenefit with:

BenefitID - PK
ClientID - Foreign key on tblClient.ClientID
ArreasID - Foreign key on tblArreas.ArreasID (assuming there is a link required)
BenefitApplied

The totals can then be calculated by queries.

This is a very basic table structure based on the limited info you have given but it is a starter for you.

HTH

 

[tomdelonge]

Thanks for that, I will have a go at it tomorrow as I have class in an hour. I am struggling to think of what info you could need. As you can see I am not very good at explaining things.

The database is just going to be a simple one for now as it only stores basic information. I just didn't know how to get the database to do the calculations etc.

If you look at the spreadsheet I have dropdown boxes for the benefit that refers to the second sheet. Is there anyway I can do the same in the database? Have a table with all the benefits listed for a form to refer to and then have a form for inputting that looks like that excel spreadsheet?? So on the form I would have client name etc etc and a dropdown menu so that I can choose upto 3 benefits and then it will calculate the total to be put into a 'total' box.

The only way I can think of explaining is imagine my spreadsheet is the form, is there anyway I can get this into access and then have one main table with all the details on?