473,320 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Sum of Date Range

Hello all -

I would like to be able to pull my tech's production by date range and everything is working aside from the actual production number. I set it as sum and assumed by assigning the date range to the [Date] in the query that it would work. However it is taking a complete sum of the production regardless of what dates i put in. I attached images of my query and table. Please advise.

Thanks,
Daniel



Attached Images
File Type: png proddateplat.png (15.9 KB, 1324 views)
File Type: jpg querie.jpg (31.6 KB, 1331 views)
Jul 28 '14 #1
20 3817
zmbd
5,501 Expert Mod 4TB
Please open your query.
Right click in a blank area of the table section
Select SQL View from the popup
Copy
Click on the [CODE/] button in the post toolbar
Paste your SQL between the code tags.
We'll take a look...

(also would be good to visit: > Before Posting (VBA or SQL) Code as it will give you some good tips on how to format your posted SQL for easier reading in the thread (^_^) )
Jul 28 '14 #2
Expand|Select|Wrap|Line Numbers
  1. SELECT [Cost by Platform].EntryDate
  2. , [Cost by Platform].Function, [Cost by Platform].Platform
  3. , [Cost by Platform].Shift, [Cost by Platform].Associates
  4. , Sum(TechProduction.production) 
  5.   AS Production
  6. FROM [Cost by Platform] 
  7.    LEFT JOIN TechProduction 
  8.       ON [Cost by Platform].Platform 
  9.          = TechProduction.Platform
  10. GROUP BY [Cost by Platform].EntryDate
  11.    , [Cost by Platform].Function, [Cost by Platform].Platform
  12.    , [Cost by Platform].Shift, [Cost by Platform].Associates
  13. HAVING ((([Cost by Platform].EntryDate)
  14.       Between [Forms]![CPU Report]![cpudatefrom] 
  15.          And [Forms]![CPU Report]![cpudateto]) 
  16.       AND (([Cost by Platform].Platform)
  17.          =[Forms]![CPU Report]![Combo15])
  18.       AND (([Cost by Platform].Shift)
  19.          =[Forms]![CPU Report]![List26]));
  20.  
I will definitely take a look and appreciate your help and consideration.

Thanks,
Daniel

---
Jul 28 '14 #3
zmbd
5,501 Expert Mod 4TB
Just at first blush,
Field named [Date]
if possible change the name to something other than "Date" as it is a reserved token
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

2) Is the table field [date] an actual date/time datatype?

Gota step out for short time... Neopa, Rabbit, Twinnyfo might pop in and lend a hand too. (^_^)
Jul 28 '14 #4
I changed the field [Date] to [EntryDate], and yes it is set up as Date/Time datatype. Not a problem, I appreciate the help. Also, i apologize for not posting the code correctly - thank you for correcting it.

Thanks,
Daniel
Jul 28 '14 #5
Good morning,

Could anyone provide assistance with this? I've tinkered with it most the night and still have come up short.

BR,
Daniel
Jul 29 '14 #6
twinnyfo
3,653 Expert Mod 2GB
Daniel,

This may sound silly, but see if this helps out at all:

For your fields EntryDate, Platform and Shift, make duplicate fields to the right of your existing fields. Chenge their "Total" value to "Where" and clear the "Show" check box.

Then, remove the criteria from those fields which have the "Group By" in the "Total" row.

This may not work at all. But it is an idea.

Also, just to be clear, what you want your query to do, based on the table you listed is to produce:

Expand|Select|Wrap|Line Numbers
  1. Date        Platform              Prod
  2. 7/16/2014   360 Wireless Bd       10
  3. 7/18/2014   360 Wireless Bd       5
  4. 6/30/2014   360 Wireless Repair   227
Is your query producing "15" or "242" for total production?
Jul 29 '14 #7
Hi Twinnyfo - When i get to work today i will give that a try and update you. I was too busy yesterday to work on it much after my initial post. As to your last question, for the '360 Wireless Bd' my query is producing '15' on both lines. And if '227' is the only entry for '360 Wireless Repair' than that will reflect, however lets say there is another entry of lets say '25' it would reflect '252' on both lines.

BR,
Daniel
Jul 30 '14 #8
Twinnyfo - No luck :\
Jul 30 '14 #9
Rabbit
12,516 Expert Mod 8TB
The structure doesn't make sense. If you are trying to filter on the production date, why are you using a date from the cost table? You need to use a date on the production table.
Jul 30 '14 #10
Im glad you caught that Rabbit.. I fixed the date, however im still stuck with the same issue. I can upload the DB (there is no private info in it yet) if anyone is willing to look at it.
BR,
Daniel
Jul 30 '14 #11
as I was driving home I was listening to my tutorials, they started talking about d lookup and I was thinking if I stored all of my values in separate tables which is how it is currently set up I could include in my query ad lookup function that will retrieve production and number of employees. Any thoughts?
Jul 30 '14 #12
Rabbit
12,516 Expert Mod 8TB
The domain aggregate functions use a lot of overhead and are usually unnecessary. Please post your newly modified code.
Jul 31 '14 #13
I've worked on this some more, i still have the same issue as before but have been able to narrow down the results that i need, i just now need to display them properly and calculate my sum.

Here is what I'm currently trying to resolve. I have one column that displays a platform such as "360 controller repair", I have 15 techs working on the controllers so minimum of 15 entries on the same platform. When i query production by platform i would like to see:
"360 controller repair 1st Shift Date Production"

Instead my report displays 15 lines of "360 controller repair 1st shift date and 1 of the production number entries, like 35. Basically im looking for a way to consolidate it down to one line only displaying a complete sum of production. Here is my coding, formatted to the best of my ability.
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. TechProduction.DateProd, TechProduction.Platform, TechProduction.Production, TechProduction.Shift
  3. FROM 
  4. TechProduction
  5. GROUP BY
  6.  TechProduction.DateProd, TechProduction.Platform,TechProduction.Production, TechProduction.Shift, 
  7. HAVING (((TechProduction.DateProd) 
  8. Between 
  9. [Forms]![Date Range Production]![From] 
  10. And 
  11. [Forms]![Date Range Production]![To])
  12.  AND 
  13. ((TechProduction.Platform)=[Forms]![Date Range Production]![PlatProd]));
  14.  
I'm at a stand still with this, i was reading on Union Query and thought that may be viable, but as far as setting one up i can't seem to wrap my brain around it as of yet. If you know a more effective solution please advise.

BR,
Daniel
Aug 1 '14 #14
Oh and what i meant about wrapping my head around the union query.. I dont understand how trying to create a sum in one query and combine it with another query will circumvent me from the issue im having where i can't create the sum. (If that makes any sense at all)
Aug 1 '14 #15
Rabbit
12,516 Expert Mod 8TB
Use the SUM function on the production field.
Aug 1 '14 #16
When i do that, it ignores the Date range criteria.. it ends up giving me the sum of the total platform production that is in the table.
Aug 1 '14 #17
Rabbit/twinnyfo- Would you be opposed to me emailing (or any method you prefer, i.e. dropbox..ect..) you the database to let you take a look? I'm not asking for you to do it for me, just to give you a better viewpoint on what I'm trying to achieve.
Aug 1 '14 #18
Rabbit
12,516 Expert Mod 8TB
It shouldn't do that, you're no longer joining to another table, unless you're changing something else other than the sum.

You can attach a zipped copy to the thread if you want but I won't have Access until I get to the office in a couple of days.
Aug 1 '14 #19
Oh wait youre correct, it was a different issue I had when I did that. When I change the Total from Group By to Sum, i then get a pop up box when I run the report asking me the production sum
Aug 2 '14 #20
NeoPa
32,556 Expert Mod 16PB
From your latest posted SQL you seem to be GROUPing BY [Production] instead of SUMming it. You're also trying to SELECT [DateProd] which is (or should be at least) aggregated (so that makes no sense). Try :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Forms]![Date Range Production]![From] AS [DateFrom]
  2.        , [Forms]![Date Range Production]![To] AS [DateTo]
  3.        , [Platform]
  4.        , [Shift]
  5.        , Sum([Production]) AS [ProdSum]
  6. FROM     [TechProduction]
  7. WHERE    ([DateProd] Between [Forms]![Date Range Production]![From] And [Forms]![Date Range Production]![To])
  8. GROUP BY [Platform]
  9.        , [Shift]
Aug 3 '14 #21

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Sunny K | last post by:
Hi guys, whilst working on a project which I thought was nearly complete I have come across a problem which was some how over seen, which I am hoping one of you guys know how to resovle. ...
4
by: Mark | last post by:
Hi I have been trying to convert the week number to a range of dates that I can use. It should be fairly simple for you guru's out there but for us mere mortals it is beyond our grasp. I know...
19
by: ali3n8 | last post by:
Hello I have attempted to create a date range report from a query called qrycustomerinformation. The field that contains the value of my date is called Followup. When i run a report on this it is...
0
by: mwalsh62 | last post by:
Greetings all! My first post here, and my mind is pudding at this point (any flavor you like)! I have been searching for days, and still can't figure out the proper syntax that I require. This...
12
smithj14
by: smithj14 | last post by:
I have a form to enter start and end dates then select a worker name to filter a report. This all works fine and when the report is open in preview mode it shows the date range in the txtboxes on the...
1
by: dlouche | last post by:
I want to get all the records from a table (no grouping) and order them first by a date range and then within that range order them by another column. For example, I have a table called Events: ID...
19
by: phill86 | last post by:
Hi I am re-posting this thread because it has become very confusing and I have got some way to solving the problem so it is a slightly different question from the initial thread. here is the...
3
by: Vinda | last post by:
Hi Bytes, Using a previous question as a base Access 2000 Inserting multiple rows based on a date range. I also wanted to insert multiple rows into a table according to a date range supplied by a...
3
by: anuragrathor | last post by:
I have a "Date Range" Section, with these three items:- Last full week; Last full month; Last full quarter. I want to get the data depending upon the selection of the Date Range. If today is...
1
by: kffacs | last post by:
Multiple rows based on a date range I have an MSAccess 2007 DB to record our employees Personal Days Off (PDO). Until now I have only had a form to record each single day taken. This results in...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.