Access Tables

T

Tiago Santos

I'm working on a data base program powered my access 2003. This program will
power the sales department and the back office, because it’s a simple setup I
decided to use access. The problem I’m running in to is that my data is not
being saved in to my data table… My form ask for some information Cash sales
for the day, then it ask for cash present to determine if we are short or
over. On my cash table I have setup SHORT/OVER FIELD . using this
=([PAYMENTTOTALS]-[TOTAL]) now on my form this field is working eg if the out
put is $25.00 over then I see that CASHSALES $1000.00 VisaPayments
$1025.00 = SHORT/OVER $25.00 NOW how can I get that to be saved in to my
field on my Cash Table so that I can use this short over on my reports on
sales for the month…

Please help if you want a bitmap to see what I’m talking about please emails
[email protected] thanks a lot…
 
W

Wayne Morgan

Basically, don't. In most cases it is not necessary to store calculated
data. Instead, just calculate it when you need it. You should be able to
place a textbox on the report that will make the same calculation as the
textbox on the form. It is also possible to do the calculation in a query
based on the table that has the two fields you mentioned. This has the
advantage of giving you a field to bind to in the report so that you can
Sum() the field, although you can usually do this in a report by using the
Running Sum property of a textbox. This property isn't available in forms.
 
T

Tiago Santos

ok I have done that... =Forms![DAILY CASH SALES ENTRY]![CASH OVER/SHORT] was
hoping that this would carry over the sum this is the code I’m using but this
will not work, because my over/short is not stored anywhere and on my Daily
Cash sales Entry is the only time i use all the fields together to get my
short over. On my report it just sums all the data on a printable report for
month end...

Can you post jpegs so you can see what I’m talking about...
 
T

Tiago Santos

=([CASH]+[ORDERS]+[FRT]+[GST]+[PST]+[ROA]+[CR1]+[CR2]+[CR3]-[BANK]+[VISA]+[INTERAC]+[MC]+[WINCARD]+[AMEX]+[GCARDS]+[OTHER])

this is another way im thinking should make it all work, but at this point
the data base is so loss that its asking me to enter all the info one more
time... Im Lost :)
 
W

Wayne Morgan

If the report is pulling its data from the same table as the form, then why
not just use the same equation you're using in the form?

=([PAYMENTTOTALS]-[TOTAL])
 
T

Tiago Santos

Paymenttotals is a SUM and is not saving on the table not sure why and same
for total is a sum of some feals in the table but its only doing the
calculation and not saving the sum calculation

NOT sure what im doing wrong
 
W

Wayne Morgan

You should be able to duplicate what you did on the form by doing the same
thing in the report. You had not previously mentioned that PaymentTotals is
a Sum, not a field. However, if you duplicate this sum on the report, you
could then use the same equation.

Basically, what it comes down to is that if you can calculate it in the
form, you should be able to make the same calculation in the report. If
you've chained calculations in the form, you may have to chain them in the
report to get the same results. It may also be possible to include them all
in a single calculation, but this will depend on the calculation.
 
T

Tiago Santos

My Daily Cash Entry (form) includes the flowing fields. This is a form that
gets printed for each day we are open for sales. And data must be entered for
each day. Data gests saved on to my table called Cash & BANK

1 to 10 is from my Table called CASH
11 to 18 are from my table called BANK

1. SLIPID (AUTONUMBER)
2. DATE
3. ENTERED BY
4. CASH SALES
5. DEPOSIT ON ORDERS
6. CARTAGE
7. GST
8. PST

SUBTOTAL this is not a field that saves data
=([CASH]+[ORDERS]+[FRT]+[GST]+[PST])

9.RECIVED ON ACCOUNT
10.OTHER DR/CR1 , 2, 3

SUBTOTAL2
=([SUBTOTAL]+[ROA]+[CR1]+[CR2]+[CR3])

CASH OVERSHORT this is not a field that saves data
=([TOTAL DEPOSITS]-[CASHSUBTOTAL])

TOTAL DEPOSITS this is not a field IN A TABLE that saves data
=([BANK]+[VISA]+[INTERAC]+[MC]+[WINCARD]+[AMEX]+[GCARDS]+[OTHER])

11. BANK
12. VISA
13. INTERAC
14. MC
15. WINCARD
16. AMEX
17. GCARDS
18. OTHERS

My report is a simple month end that shows only a few fields
1. DATE
2. CASH
3. ORDERS

SUBTOTAL this is not a field that saves data
=([CASH]+[ORDERS])

CHARGESALES
STORECHARGES

TOTAL this is not a field that saves data
=([SUBTOTAL]+[CHARGESALES]+[STORECHARGES])

MARGIN

SHORT/OVER this is not a field that saves data just a sum
=([PAYMENTTOTALS]-[TOTAL])

This don’t work and this short/over is the only thing I don’t have work

Hope this now gives you a better idea of what I’m doing

Thanks so much

Tiago
 
W

Wayne Morgan

Yes, it helps. I just have a couple of questions.
SHORT/OVER this is not a field that saves data just a sum
=([PAYMENTTOTALS]-[TOTAL])
Click to expand...

I assume TOTAL in the above is this total
TOTAL this is not a field that saves data
=([SUBTOTAL]+[CHARGESALES]+[STORECHARGES])
Click to expand...

Is that correct? If so, that part of what you have should work. Now for
PAYMENTTOTALS. What does it consist of? What equation do you use to arrive
at the value for PAYMENTTOTALS? Is PAYMENTTOTALS a calculated field in the
report's query or is this something you're calculating in the report itself?

--
Wayne Morgan
MS Access MVP


Tiago Santos said:
My Daily Cash Entry (form) includes the flowing fields. This is a form
that
gets printed for each day we are open for sales. And data must be entered
for
each day. Data gests saved on to my table called Cash & BANK

1 to 10 is from my Table called CASH
11 to 18 are from my table called BANK

1. SLIPID (AUTONUMBER)
2. DATE
3. ENTERED BY
4. CASH SALES
5. DEPOSIT ON ORDERS
6. CARTAGE
7. GST
8. PST

SUBTOTAL this is not a field that saves data
=([CASH]+[ORDERS]+[FRT]+[GST]+[PST])

9.RECIVED ON ACCOUNT
10.OTHER DR/CR1 , 2, 3

SUBTOTAL2
=([SUBTOTAL]+[ROA]+[CR1]+[CR2]+[CR3])

CASH OVERSHORT this is not a field that saves data
=([TOTAL DEPOSITS]-[CASHSUBTOTAL])

TOTAL DEPOSITS this is not a field IN A TABLE that saves data
=([BANK]+[VISA]+[INTERAC]+[MC]+[WINCARD]+[AMEX]+[GCARDS]+[OTHER])

11. BANK
12. VISA
13. INTERAC
14. MC
15. WINCARD
16. AMEX
17. GCARDS
18. OTHERS

My report is a simple month end that shows only a few fields
1. DATE
2. CASH
3. ORDERS

SUBTOTAL this is not a field that saves data
=([CASH]+[ORDERS])

CHARGESALES
STORECHARGES

TOTAL this is not a field that saves data
=([SUBTOTAL]+[CHARGESALES]+[STORECHARGES])

MARGIN

SHORT/OVER this is not a field that saves data just a sum
=([PAYMENTTOTALS]-[TOTAL])

This don't work and this short/over is the only thing I don't have work

Hope this now gives you a better idea of what I'm doing

Thanks so much

Tiago
Click to expand...
 
T

Tiago Santos

My report is a simple month end that shows only a few fields
1. DATE
2. CASH
3. ORDERS
SUBTOTAL this is not a field that saves data
=([CASH]+[ORDERS])

CHARGESALES
STORECHARGES

TOTAL this is not a field that saves data to sum my sales for the day cash
and charge
=([SUBTOTAL]+[CHARGESALES]+[STORECHARGES])

MARGIN = margin for the day

CASH OVERSHORT this is not a field that saves data
=([TOTAL DEPOSITS]-[CASHSUBTOTAL])

Total deposits are all my payment Visa/MC/DC etc

Cashsubtotal is the addition of sales and ROA etc
 
W

Wayne Morgan

Tiago,

We can keep going around in circles, but I don't think it's getting us any
where. You've now come up with new field names that don't show where they
come from. You say that CASHSUBTOTAL is "the addition of sales and ROA etc."
I don't see Sales or ROA in the rest of the items you mention. I'm not an
accountant. I don't know what these fields are, what they mean, or what
other fields may go into making them. I can do the math if I know which
fields need to be included in the calculation(s).
 
T

Tiago Santos

OK I guess I’m not saying this right… on my REPORT I want to get this
SHORT/OVER sum this is a calculation of the data I entered in my form. Called
cash that gest saved on my table called CASH and my table called BANK.

ADD A

CASH SALES
DEPOSIT ON ORDERS
CARTAGE
GST
PST
RECIVED ON ACCOUNT
OTHER DR/CR
Add all of this up.

SHORTOVER is the difference between this 2 how do i work this number.
becouse this is not stored anyplace. on my forms this is just a text box with
a sum...

ADD B Add all of this up

BANK
VISA
INTERAC
MC
WINCARD
AMEX
GCARDS
OTHERS

The difference between A and B = C
becouse i have enterd this information for each day of the month this all
gets saved becouse its fields in my 2 Tables. Now my Report gets printed one
time a month or so and i would like to keep record of shorts and overs for
each day of the month...
all the other fields...

Hope you understend what im asking...
 
W

Wayne Morgan

Ok, I think I understand.

If c = a + b and a = d + e then c = d + e + b. If the result you want is
from calculated values that aren't available to you but the values used to
make those other calculated values are available to you then you can
calculate the current value that you're looking for.

If you want to store the calculated value (and I'll admit, that this is
sometimes the best answer, just not usually the best answer) then that can
be done. To do so, place a hidden textbox on the form and set its control
source to the field that you want to store the calculated value in. In the
form's BeforeUpdate event, set the value of this new, hidden textbox to the
calculated textbox's value. This value will now be stored as the form saves
the record.
 
T

Tiago Santos

can you give me a example of a expresion i should be using dont know anything
about Beforeupdate Event
 
W

Wayne Morgan

If txtA is the textbox with the calculation and txtB is the hidden textbox
then in the BeforeUpdate event,

txtB = txtA
 
Top