Problem with my database

[Unicon]

Dear All,

I have a database and in my database in the report I am getting total expenses by (Total of Cost per month – total expenses). But the thing is I am changing cost per month value every month with different value but rest of all details will remain same. Now the problem is if I want to check the report of January month in February than my database will give a wrong value as in February month those January value (Cost per month) has already changed with different new value.

Please advise.

 

[Galaxiom]

I am getting total expenses by (Total of Cost per month – total expenses).

That sounds like a circular reference. Perhaps you mistyped it?

The image of the wrong figure on the screen is not at all helpful.

 

[Unicon]

Hi GalaxiomAtHome,

Please attached db for your refference.

Thanks.

 

[CBrighton]

Not really sure what the problem is.

By the looks of the OP it's a control in a report which you are trying to calculate a value in?

If you confirm which report I will have a look, but you also haven't really said how you calculate it. As mentioned abiove, your post contained a circular reference.

 

[Unicon]

Hi CBrighton,

Please check Report Overall. I create a query with sum of [Cost per month] where it is showing whole total value. Which I have called in the report, from there I am getting Grand Total = ([Total Fuel Cost]-[Total of Cost per month]) but the problem will come in next month as I have to change the whole value of Cost per month for each person. And it will all mix up If I want to see the report of January month in February than definately I will get false result.

Please advise.

 

[CBrighton]

Still not completely clear.

For a start there is no control bound to or label with the caption of "Cost Per Month" or "Total Cost Per Month" in the report. Am I right in assuming "KM/AED" is "Cost Per Month"?


By the sounds of it the problem is that you have a single record holding the current cost per month but you want to view historic data.

This can be resolved by splitting the main table into two tables, one for cars and a second for costs (with a field to record which month / year that cost relates to). Alternatively you could add a date field to the current table and have multiple entries of the car in the table, one for each month.

I'd favour the option of splitting the table though. If the cost is going to change and you want to view historic costs then you need to be able to store them somewhere seperate from the car details (which will not change from month to month).

Looking at your table you may want another field for fuel cards too if they regularly expire & are replaced, maybe even another for customer details (i assume that's what the last fields are for, purpose, contact no, etc).

Think of the data you want to hold then think how it relates to each other. It's best to hold all data about one thing in it's own table. A table for cars, a table for costs, etc. Mixing data can work if there will only ever be a 1 to 1 relationship, but in your case it sounds like you want multiple costs for one car. That's a straightforward 1 to many relationship.

 

[Unicon]

Thanks a lot CBrighton,

Will start doing same in future I thought in that way too that I will put those monthly values in one table with field of Month / Total amount and will update every month but how can I get those details monthly basis in the report from table?? That is another question for me now J. I have just do it like way (Please find attachment again for ur reference) and advice is there any way I can make it more effecter.

 

[CBrighton]

Not sure what you've done...

You don't have permission to read 'Report overall.'

To read this object you must have Read Design permission for it. For more information on permissions and who can set then, click Help.

Can't even access the report anymore!

 

[Unicon]

Sorry

Please find the attachment again.

 

[CBrighton]

I think you may have mistook my advice.

I meant to set it up in a similar way to your Fuel Cost table.

A table with fields like vehicle number, month, year, cost.

Then if you wanted the data from June to Dec your query would just specify the vehicle number (or not to see all cars) set the year to whichever year you are looking at and set the month to between 1 and 6. Then a simple sum on the report would give you the total for the 6 month period.

 

[Unicon]

Thanks CBrighton,

Will try in that way