Solved

Sum in my query not working.

Posted on 2012-12-26
10
326 Views
Last Modified: 2013-01-04
I am trying to sum to fields in a query and it keeps going in a parameter code.

WaitingShop: DSum("OnSite","tblTempleFuelcellOnly")+([InShop])
SUMNOTWORKING.JPG
0
Comment
Question by:gigifarrow
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 38722228
use sum instead of dsum
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38722237
What does this return?
DSum("OnSite","tblTempleFuelcellOnly")

What does this return?:
[InShop]
...is this a text value, or a string?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38722242
Please clearly define: "not working"

In most cases a Parameter prompt is indicative of a misspelling.
So check your object names carefully, ...and make sure you are using the actual field name form the table, not the "caption".

I also see that there is no Join between the two tables in your query there...?
0
 

Author Comment

by:gigifarrow
ID: 38722261
On site means the vehicles that are at the company parkinglot. in shop are the ones that are inside being worked on. in shop is found by getting the total of dates. because there are dates when they go in shop. Onsite is found by getting the total of units because every vehicle has a unit whether it is in shop or on site. I see why i get a parameter is because I have the wrong name of the field it should be in shop date.

So how would I add a total of dates minus  a total of units to get waiting shop? or should I put this as another question?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38722483
Try:

Select DSum("f1 + f2","tbl")
From tbl;
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38722565
The questions still remain:

What does this return?
DSum("OnSite","tblTempleFuelcellOnly")

What does this return?:
[InShop]
...is this a text value, or a string?

Why is there no join between the two tables?

If you do this as a query, what is returned?:
SELECT DSum("OnSite","tblTempleFuelcellOnly") AS SumOnsite, Inshop, DSum("OnSite","tblTempleFuelcellOnly") + InShop
FROM YourTable


This why we always ask for sample files.

It is always much faster than us spending time guessing what a simple query should return.
Make sense?

JeffCoachman
0
 

Author Comment

by:gigifarrow
ID: 38724058
Sorry jeff I am totally wrong.

I have been asked  to take a excel spreadsheet and put it  into access. There about eight calculations that I need to do.
Here are my fields that I am dealing with:
Field                                    Example          
Production Number         135
In Shop Date                      12/12/12
Unit                                      A company
18 Steps                              That are checked off with a X
Complete                             an x is put there for that
Percentage Complete         x represent percentage and totaled to 100%
Date complete                         Date vehicle completed
Date returned                      Date returned to customer
Scope                                       amount of vehicles suppose to get that is entered in
Based on these fields I have to answer the following questions in a query that is going to probably by a cross tab query because I need it broken down by units.
1.Received by  each unit (which can be done by totaling the serial numbers for each separate unit)
2. Onsite(total of in shop and vehicles that don’t  have a shop date)
3. Waiting shop[in shope date minus vehicles that don’t have a in shop date.
4.inshop(count of dates in shop)
5. vehicle complete(total of dates completed
6. equalvelent vehicle:total of completed
7. percentage complete:scope divided by total completed
8. Awaiting return(perctage complete – the date that have been returned)
9. Returned (counted by the  total date returned)
So here is a snip of the excel spreadsheet
and the database with the query I am trying to do.
bingochartexample.xlsx
forhelponcalculations.zip
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38724433
<Sorry jeff I am totally wrong.>
No need to apologize...
;-)

I see that hnasr is back with us, ...so I will leave this to him...

I will monitor, just to see how he solves this, ..in case I misunderstood something.

Jeff
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38728682
Sorry boag2000,
Looks like my understanding of the problem is incomplete and the uploaded sample added to the confusion.
You can greatly help in rephrasing and presenting the problem so tthat he author can verify it.
Few records and expected output will be helpful.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 200 total points
ID: 38728692
gigifarrow,

Two issues:
1. As I stated, there is no Join between the two tables in the query.
In order to get any correct results, there must be a Join.

2. The Parameter prompt is for "InShop"
You reference this field in the query, yet no field by this name exists in either table.

Not sure what this has to do with:
WaitingShop: DSum("OnSite","tblTempleFuelcellOnly")+([InShop])
...?
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now