This question actually goes in two threads.....

[beautifulminds-us]

I am trying to build a db for my small company. I would like to set up a report that has the same functions as a bank statement so I can show my clients what they have made on a certain date and what they have spent, as well as the balance. I thought it'd be a simple SQL but was wrong. I went to several other db forums and was led only as far as I got on my own then they seemed to have left. Is it too difficult?

I need to take the [Total] of a sale, multiply it by .3 (this is our commission). What's left in the [Total] I need to subtract the various expenses (PostageCost, CopiesCost, GasCost, OtherCost, PhoneCost) to have a balance. Again, the entire report should look like a bank statement with either a debit or credit on certain dates. Each report is printed monthly for each individual clients.

Does that make sense?

 

[Moniker]

Where is the code you tried? What didn't work? You're asking for something simple, but I can't debug what I don't see. In general, it would look something like this:

Balance = (TotalCost * .3) -(PostageCost + CopiesCost + GasCost + OtherCost + PhoneCost)

Without seeing it though, there's not a clean way to explain it.

 

[Rich]

Do you have separate fields for each type of cost ?

 

[GaryPanic]

this should not be rocket science-
however ...
you will need to capture certain information -
so you either need as rich states a seperate field for each cost or a contionous form layout where you can enter in type of cost and amount and date

so basically you need
Sale £ 100 commission 0.3(is this fixed or floating= )£30.00 total for costs at £70.00 or (1-0.3 or whatever)
outgoings
postage
copiescost
whatever now you could just have fields with these on - nice and easy - set it up kinda how you want it to should on your report- a bit like a journal -BK rentry - bar accounts whatever

then you are left with a remainder which could be a negative ??

 

[GaryPanic]

ok- I have throw a sloppy idea up

have a look -
job id
cost
retained
(i have not put total in but you get the idea)
then a subform - continous option - where you pick froma drop down list and enter the amount - now you will need to autopopulate the job id from the first form/table into the other table as a FK -(as i said this is a sloppy version idea this is do-able - i have done it b4)

now you have all your data

postives in one table and negatives in the other
using the jobid you can now build some report to show what you want

total on your report one table less the other = profit etc..or whatever

plain and simple -g(Hope this gives some pointers )
doing it this way you can report on costings quite easily as they are all there



HTH

 

[GaryPanic]

oops forgot - you will need a date field in as well - to get this to run smoothly and it will have to run of a date field in a statement form between x and y
all statements

 

[beautifulminds-us]

Wow!!

Sorry, I left after waiting for some time. I am amazed at the replies and will do my best to answer them...

Each expense (gas, phone, etc.) has its own table. Each table is connected to a subform. Certain prices that are known are fixed, i.e. postage, copies, commission. All that is needed is the number of either stamps or pages(except for the commission, that's set to automatically figure its % then subtract it from the total sale price (so we know how much is our commission and what we have left)) leaving us with the Adjusted Price. From there I want to subtract the expenses our client has accumulated. But I was trying to use the sum of the expenses and subtracting it from the Adjusted Price. This would, should, give us what we owe our client. Unfortunately, it didn't all work as I had envisioned. I have most of it but am stuck at one point, the totals of the expenses.

To find the totals of the above I SQLed:

Total: Sum([CopiesNumber]+[GasCost]+[OtherCost]+[PostageCost]+[PhoneCost])

Unfortunately, nothing shows up when I run it because I need a number in all. But not all my clients have an expense in each. One might call collect, another might only use copies. How do I do this?

I would like to use either dlookup(s) or in VB to eliminate a lot of the clutter in the design view of the report. I know some of each but haven't used them in over a year so am rusty.

After I get all these numbers I should be good to go, hopefully. If not, I will be asking for your wisdom again...

 

[Rich]

You need to do some research on normalisation, you don't need a table for each type of expense, one table will do

 

[GaryPanic]

from the gist of you last post - this means some of your fields are empty ..

OK not one to harp on about basic book keeping

the use of 0.00 is vital it is a number probably the most important one without 0 in your calculations you are screwed ..


Access does not like blank values- how you get a 0.00 in your cals - various routes - some like the nz fuction others take different views -

never used the nz fuction so cannot help at this point

 

[GaryPanic]

re-read your commetns
basically the way you have this set up , is kinda wonky (not wrong, just a bit ??) anyway
they way you have this set up you need a value each costing each time - which probably works 80% of the time

now did you look at my sloppy attempt at this - doing this way (including a date field as well)if their is no charge for a period , it does not show up on the statement/invocie /balance sheet - or whatever
it also cuts the table structure down to 3 tables against a table for each type of costings - (madness- but your logic at the time might have gone this way)- i am always doing stuff and then think - do-oh should of been x or y

now i am not saying that what i surgest is best practice or even correct, its a solution desing for easy of use by a user -who does not have to be Access trained to any real level - just how to work through a form

this is not me having a pop at you , just the problem as been approached from the left hand side , and others are now looking at this from the right hand side , or from front on or fromth e rear - differnet views
how many ways can you skin a cat -theres more than one ...


G

 

[Rich]

There's only one way to skin a cat correctly and that's to design a relational db without wearing a spreadsheet hat, this db is not normalised and thus will cause no end of problems

 

[GaryPanic]

meow......

Rich its 10 oclock - you must be on your 2nd or 3rd cup of tea by now..
g
I am about to have my second

 

[Rich]

Coffee

 

[beautifulminds-us]

You need to do some research on normalisation, you don't need a table for each type of expense, one table will do

Okay, I am a moron. I don't know what I was thinking when I made each table. I remember trying to make only one initially, but thought, "This won't work...," and made several instead. Then when you mentioned the above I began banging my forehead against the desk...stupid, stupid, stupid....

Unfortunately, I still have a problem. I was able to place all the expenses on one table, but cannot figure out how to make an SQL to represent all the fields I need for the report. I have all the expenses: Phone, Gas, Postage, Copies, etc., on a expenses table. I have another for the Item itself: Price, IMG, Date, etc.

I place both tables into an SQL to group the price of each item sold, the date it was sold, all the expenses and the date each expense was made. The outcome creates a row for each entry. So, if I have sold 7 items and have 4 expenses it shows 28 rows, that's with grouping them together. I am needing the date of each item sold for a "Credit" on the statement and a date for each expense for a "Debit". Finding out the totals will be the easy part. It's finding this SQL that's tricky.