Database Help Please

[Bellablu]

Hi,



I am creating a database for work & I an having trouble getting it to do what I want. I have three money related cells, currently I have them as text, but I have also tried writing an expression whith them as currency & as a number.

COST, GST & TOTAL

The cost figure is what is on the reports that I import into the database,

I have created a Yes/No for "GST Charged"

What I want to happen is, when I click the "GST Charged" box I want the GST field to calculate: [COST]/10, I have tried to develop expressions in the table & in the form but can not get it to work. Can anyone please advise me of the correct expression & where to put it, ie: in the table or the form & which cell.

I am currently using Access 2003

Thank you in advance.

[orange]

Could you describe your application in terms of your business? For those of us that are not familiar with your environment or your business, it would help if we knew what you were trying to do. Once we have some familiarity, then someone hopefully could advise on how to accomplish it.

[Bellablu]

Ok I will be importing depatmental financial expenditure which is downloaded from a SAP aspplication through Excel & into the database. That process works well.

Some transactions are purchases which incur GST, other transactions are payments to employees which are GST free, so I hoping to be able to just hit a check box to calculate the GST for the transactions I choose.

[orange]

Great. Do you have a method to determine which transactions are purchases and which are payments to employees?

[Bellablu]

Visual identification/user input of the data that is brought across, thats why I created a yes/no checkbox, a check yes for GST & then have it automatically calculate the 10% of the cost (which is imported) in the GST field.

Hope that makes sense.

[orange]

Are you saying that you are building a manual process?
If you are bringing data from an automated system (SAP), I would expect that some flag or condition would exist to programmatically identify records. The condition could be placed in code and all records requiring GST calculation could be processed as a group .

General syntax along this format:

SELECT the fields required and apply GST FROM ImportedSAPData
WHERE Condition for GST is True

Another question: Why isn't the calculation being done in the Financial system (SAP)?
Surely GST amounts must be accounted for in your financial system at some point.

[Bellablu]

Unfortunatly I did not set up the data extraction from the financial system, currently the extraction does not include the GST figure paid, otherwise I would have included that field, but it is something I will look into today to see if I could change the extraction (it would make life so much easier).

But in case it does not, what can I do to make the GST field calculate the GST when I click a GST Yes/No button.

Just checked the GST can not be extracted as part of the report as the report extracts expenditure against account codes, & GST when split in a transaction has it own account code. To add that code to the report would extract the GST amounts only & they would have to be matched to the original transaction.

So back to the original plan. Can anyone help with an expression or macro

[orange]

Can you show us what fields the table(s) you extract from SAP contain?
Perhaps make up a few sample transactions to help describe what exactly you want to happen to which records and fields and under what conditions.

10 percent of X is ( .1 * X)

[Bellablu]

Journal Description Cost GST GST Yes (this is a Check Box in my DB Form)
K BURNETT CPP ALLOW 292.00
T EDWARDS CPP ALLOW 292.00
F SMITH TRAINING SHOES 145.00 14.50 Yes

OK here is a mock up of my DB thus far, the yellow section is imported data from SAP. "GST Yes" is a Yes/No field (Check Box in my DB Form) in the DB that when I check "Yes" I want it to calculate the GST amount, which is 10% of the "Amount" component in the "GST" field.

[ssanfu]

Were you going to attach a MDB???

Also, you might read these:

Read the attached PDF
http://forums.aspfree.com/microsoft-...es-208217.html

Another site about calculations:
http://allenbrowne.com/casu-14.html

[Bellablu]

Nope that would just be stupid, wouldnt it!

Actually I typed it out but when I hit post it deleted all the spaces. So I just plane gave up on it.

[ssanfu]

Nope that would just be stupid, wouldnt it!

Don't see why it would be stupid...

But I guess I'll be stupid (my normal state ) and attach the mdb example I created. Maybe this will help you . Without knowing more about your table structure and your forms, this is the best I can do......

If this example doesn't help or you have questions about what I did, please post back...

[Bellablu]

Your Awesome, thats exactly what I wanted it to do. Now I will go & play with it & get back to you.

[ssanfu]

Great.

Please NOTE: the GST and TOTAL amounts are NOT stored in the table as they can be calculated at any time.

[Bellablu]

HELP - how did you do it?????

I have created my form, & my query I cant seem to get it to work in the control source. Been trying for 2 days now. beating my head against a wall now.
Tried to attach it, but its to large.