Assets database query

[Xabu]

I am building a simple assets database to help me with my work. This is what I did:

I created a table with following columns:

1) Bill ID - unique number of the asset bill, created by the accounting software.

2) ID of the supplier

3) Month

4) Type of the bill (code such as RI for investments, RO for activated assets etc.)

5) Date

6) Basic non-taxed value.

There is a 20% tax on assets. 19% OF the 20% tax is added to the base non-taxed value - I get the starting value of the asset before amortisation begins.

So far so good, my query works perfect, does everything right, however:

Every now and then (less than 10 times per year) I get assets from suppliers that are free of tax. That means that the value on the bill = starting asset value, and there is no need for adding the 19% of the tax back to the asset value. I don't know how to exclude those bills from the process.

The way I figured it theoreticaly, it can be done like this:

1) I could store the tax-free supplier codes in a separate table and make the query perform a check against the column 2 (supplier ID), and if it findds the supplier there, the base value is taken as a final value.

2) I could make all non-taxed bills have an unique tag for the non-taxed bills(column 4 - type of the bill), and make the query check against those.

I have no clue how to make any of those, so I hope someone here could help me.

Thank you!

 

[iago18]

I would either have a different type of bill (as you suggest) or add a new Yes/No field to the table called 'ApplyTax'. Either way, if you are processing the tax *within* the query you'll probably have to use some kind of 'if-then' statement.

Something like: IIf([ApplyTax]=True,[Value]*{Tax},[Value])