Sum Fields in Table, Form, Query & Report Don't Agree

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Everybody.

It seems I end here about every other day lately.

This is kind of wordy, so read on only if you like a challenge. The SUM is
not in agreement when the information shows up between the Form,
Table, Query and Report.

I've created a table where material is stored from data, entered by users
into a
form. Based on two sample entries I made today, broken out in each document,
perhaps it may help identify where I'm going wrong. I've listed the info as
dummy entry1 / dummy entry2 (hopefully) to give someone the "big picture.

Form –
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece 32 / 12
3. Total Length in Inches 64 / 24
Calculated thru expression:= [# of Pieces Scrapped]*[Length of Each
Piece]
4. Total Foot Length 5.333 / 2
Calculated thru expression: =[Total Length in Inches]/(12)
5. Cost Per Foot 3.25 / 1.00
6. Total Material Cost 17.33 / 2.00
Calculated through expression) =[Total Foot Length]*[CostPerFoot]

All fields work perfectly on the form.

Table –
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece 32 / 12
3. Total Length in Inches 0 / 24
4. Total Foot Length 0 / 24
5. Cost Per Foot 3.25 / 1.00
6. Total Material Cost $0.00 / $4.32

Query -
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece (not used in query)
3. Total Length in Inches (not used in query)
4. Total Foot Length 0 / 24
5. Cost Per Foot (not used in query)
6. Total Material Cost $0.00 / $4.32

Report -
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece (not used on report)
3. Total Length in Inches (not used on report)
4. Total Foot Length 0 / 2400
5. Cost Per Foot (not used on report)
6. Total Material Cost $0.00 / $4.32

I have gone thru everything to ensure “numbers†or “currency†fields were
selected
where needed.

I'm way-overdue for a vacation, so my brain seems to be missing some of the
most basic things in databasing.

If anyone can tell me what I'm doing wrong, I would greatly appreciate it.

Thanks,
Karen
 
You are correct, the values do not match. I suggest you take the vaction and
worry about it later :)

It is hard to tell what is going on without seeing the query or the report
info. The one thing I see that is suspect, is that the Total Length in
Inches and Total Foot Length fields in the table are 0. Why is that?
 
The Total Length in Inches and the Total Foot Length were set similar to the
form, so it is odd they do not agree. I will probably re-create the
database -
it would be quicker for me to do this.

To your knowledge, does Access have problems calculating numbers with
decimal points (other than currency, with a 2 decimal point setting). For
instance, if you set the expressions to calculate 18.338*2.44. I've set
currency calculations without a problem, but this is the first time that I'm
setting up a datase with fractions into the hundreths (?).

Thanks for your input.

Klatuu said:
You are correct, the values do not match. I suggest you take the vaction and
worry about it later :)

It is hard to tell what is going on without seeing the query or the report
info. The one thing I see that is suspect, is that the Total Length in
Inches and Total Foot Length fields in the table are 0. Why is that?

kalyhan said:
Hi Everybody.

It seems I end here about every other day lately.

This is kind of wordy, so read on only if you like a challenge. The SUM is
not in agreement when the information shows up between the Form,
Table, Query and Report.

I've created a table where material is stored from data, entered by users
into a
form. Based on two sample entries I made today, broken out in each document,
perhaps it may help identify where I'm going wrong. I've listed the info as
dummy entry1 / dummy entry2 (hopefully) to give someone the "big picture.

Form –
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece 32 / 12
3. Total Length in Inches 64 / 24
Calculated thru expression:= [# of Pieces Scrapped]*[Length of Each
Piece]
4. Total Foot Length 5.333 / 2
Calculated thru expression: =[Total Length in Inches]/(12)
5. Cost Per Foot 3.25 / 1.00
6. Total Material Cost 17.33 / 2.00
Calculated through expression) =[Total Foot Length]*[CostPerFoot]

All fields work perfectly on the form.

Table –
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece 32 / 12
3. Total Length in Inches 0 / 24
4. Total Foot Length 0 / 24
5. Cost Per Foot 3.25 / 1.00
6. Total Material Cost $0.00 / $4.32

Query -
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece (not used in query)
3. Total Length in Inches (not used in query)
4. Total Foot Length 0 / 24
5. Cost Per Foot (not used in query)
6. Total Material Cost $0.00 / $4.32

Report -
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece (not used on report)
3. Total Length in Inches (not used on report)
4. Total Foot Length 0 / 2400
5. Cost Per Foot (not used on report)
6. Total Material Cost $0.00 / $4.32

I have gone thru everything to ensure “numbers†or “currency†fields were
selected
where needed.

I'm way-overdue for a vacation, so my brain seems to be missing some of the
most basic things in databasing.

If anyone can tell me what I'm doing wrong, I would greatly appreciate it.

Thanks,
Karen
Click to expand...
Click to expand...
 
I know of no problems with calculations. The only thing you need to keep in
mind is how many decimal places you want in the results. Depending on the
situation, you may want to use the Round() or Format() functions.

So, where are we going on vacation?

kalyhan said:
The Total Length in Inches and the Total Foot Length were set similar to the
form, so it is odd they do not agree. I will probably re-create the
database -
it would be quicker for me to do this.

To your knowledge, does Access have problems calculating numbers with
decimal points (other than currency, with a 2 decimal point setting). For
instance, if you set the expressions to calculate 18.338*2.44. I've set
currency calculations without a problem, but this is the first time that I'm
setting up a datase with fractions into the hundreths (?).

Thanks for your input.

Klatuu said:
You are correct, the values do not match. I suggest you take the vaction and
worry about it later :)

It is hard to tell what is going on without seeing the query or the report
info. The one thing I see that is suspect, is that the Total Length in
Inches and Total Foot Length fields in the table are 0. Why is that?

kalyhan said:
Hi Everybody.

It seems I end here about every other day lately.

This is kind of wordy, so read on only if you like a challenge. The SUM is
not in agreement when the information shows up between the Form,
Table, Query and Report.

I've created a table where material is stored from data, entered by users
into a
form. Based on two sample entries I made today, broken out in each document,
perhaps it may help identify where I'm going wrong. I've listed the info as
dummy entry1 / dummy entry2 (hopefully) to give someone the "big picture.

Form –
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece 32 / 12
3. Total Length in Inches 64 / 24
Calculated thru expression:= [# of Pieces Scrapped]*[Length of Each
Piece]
4. Total Foot Length 5.333 / 2
Calculated thru expression: =[Total Length in Inches]/(12)
5. Cost Per Foot 3.25 / 1.00
6. Total Material Cost 17.33 / 2.00
Calculated through expression) =[Total Foot Length]*[CostPerFoot]

All fields work perfectly on the form.

Table –
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece 32 / 12
3. Total Length in Inches 0 / 24
4. Total Foot Length 0 / 24
5. Cost Per Foot 3.25 / 1.00
6. Total Material Cost $0.00 / $4.32

Query -
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece (not used in query)
3. Total Length in Inches (not used in query)
4. Total Foot Length 0 / 24
5. Cost Per Foot (not used in query)
6. Total Material Cost $0.00 / $4.32

Report -
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece (not used on report)
3. Total Length in Inches (not used on report)
4. Total Foot Length 0 / 2400
5. Cost Per Foot (not used on report)
6. Total Material Cost $0.00 / $4.32

I have gone thru everything to ensure “numbers†or “currency†fields were
selected
where needed.

I'm way-overdue for a vacation, so my brain seems to be missing some of the
most basic things in databasing.

If anyone can tell me what I'm doing wrong, I would greatly appreciate it.

Thanks,
Karen
Click to expand...
Click to expand...
Click to expand...
 
I will double check all my decimal settings. Where this is the first time I
have
had to deal with measurements and not currency, could explain why I'm
getting so many errors. I usually do not encounter errors with any other
databases.

As far as vacations are concerned, the question wouldn't be where I would
vacation, the question is when will I get a vacation!! I don't think I've
taken a vacation in a few years.

Klatuu said:
I know of no problems with calculations. The only thing you need to keep in
mind is how many decimal places you want in the results. Depending on the
situation, you may want to use the Round() or Format() functions.

So, where are we going on vacation?

kalyhan said:
The Total Length in Inches and the Total Foot Length were set similar to the
form, so it is odd they do not agree. I will probably re-create the
database -
it would be quicker for me to do this.

To your knowledge, does Access have problems calculating numbers with
decimal points (other than currency, with a 2 decimal point setting). For
instance, if you set the expressions to calculate 18.338*2.44. I've set
currency calculations without a problem, but this is the first time that I'm
setting up a datase with fractions into the hundreths (?).

Thanks for your input.

Klatuu said:
You are correct, the values do not match. I suggest you take the vaction and
worry about it later :)

It is hard to tell what is going on without seeing the query or the report
info. The one thing I see that is suspect, is that the Total Length in
Inches and Total Foot Length fields in the table are 0. Why is that?

:

Hi Everybody.

It seems I end here about every other day lately.

This is kind of wordy, so read on only if you like a challenge. The SUM is
not in agreement when the information shows up between the Form,
Table, Query and Report.

I've created a table where material is stored from data, entered by users
into a
form. Based on two sample entries I made today, broken out in each document,
perhaps it may help identify where I'm going wrong. I've listed the info as
dummy entry1 / dummy entry2 (hopefully) to give someone the "big picture.

Form –
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece 32 / 12
3. Total Length in Inches 64 / 24
Calculated thru expression:= [# of Pieces Scrapped]*[Length of Each
Piece]
4. Total Foot Length 5.333 / 2
Calculated thru expression: =[Total Length in Inches]/(12)
5. Cost Per Foot 3.25 / 1.00
6. Total Material Cost 17.33 / 2.00
Calculated through expression) =[Total Foot Length]*[CostPerFoot]

All fields work perfectly on the form.

Table –
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece 32 / 12
3. Total Length in Inches 0 / 24
4. Total Foot Length 0 / 24
5. Cost Per Foot 3.25 / 1.00
6. Total Material Cost $0.00 / $4.32

Query -
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece (not used in query)
3. Total Length in Inches (not used in query)
4. Total Foot Length 0 / 24
5. Cost Per Foot (not used in query)
6. Total Material Cost $0.00 / $4.32

Report -
1. # of Pieces Scrapped 2 / 2
2. Length of Each Piece (not used on report)
3. Total Length in Inches (not used on report)
4. Total Foot Length 0 / 2400
5. Cost Per Foot (not used on report)
6. Total Material Cost $0.00 / $4.32

I have gone thru everything to ensure “numbers†or “currency†fields were
selected
where needed.

I'm way-overdue for a vacation, so my brain seems to be missing some of the
most basic things in databasing.

If anyone can tell me what I'm doing wrong, I would greatly appreciate it.

Thanks,
Karen
Click to expand...
Click to expand...
Click to expand...
 
Back
Top