Sum of Date Range

[danielfinzel]

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

[zmbd]

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 (^_^) )

[danielfinzel]

SELECT [Cost by Platform].EntryDate
, [Cost by Platform].Function, [Cost by Platform].Platform
, [Cost by Platform].Shift, [Cost by Platform].Associates
, Sum(TechProduction.production) 
  AS Production
FROM [Cost by Platform] 
   LEFT JOIN TechProduction 
      ON [Cost by Platform].Platform 
         = TechProduction.Platform
GROUP BY [Cost by Platform].EntryDate
   , [Cost by Platform].Function, [Cost by Platform].Platform
   , [Cost by Platform].Shift, [Cost by Platform].Associates
HAVING ((([Cost by Platform].EntryDate)
      Between [Forms]![CPU Report]![cpudatefrom] 
         And [Forms]![CPU Report]![cpudateto]) 
      AND (([Cost by Platform].Platform)
         =[Forms]![CPU Report]![Combo15])
      AND (([Cost by Platform].Shift)
         =[Forms]![CPU Report]![List26]));
 

I will definitely take a look and appreciate your help and consideration.

Thanks,
Daniel

---

[zmbd]

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. (^_^)

[danielfinzel]

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

[danielfinzel]

Good morning,

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

BR,
Daniel

[twinnyfo]

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:

Date        Platform              Prod
7/16/2014   360 Wireless Bd       10
7/18/2014   360 Wireless Bd       5
6/30/2014   360 Wireless Repair   227

Is your query producing "15" or "242" for total production?

[danielfinzel]

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

[danielfinzel]

Twinnyfo - No luck :\

[Rabbit]

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.

[danielfinzel]

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

[danielfinzel]

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?

[Rabbit]

The domain aggregate functions use a lot of overhead and are usually unnecessary. Please post your newly modified code.

[danielfinzel]

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.

SELECT 
TechProduction.DateProd, TechProduction.Platform, TechProduction.Production, TechProduction.Shift
FROM 
TechProduction
GROUP BY
 TechProduction.DateProd, TechProduction.Platform,TechProduction.Production, TechProduction.Shift, 
HAVING (((TechProduction.DateProd) 
Between 
[Forms]![Date Range Production]![From] 
And 
[Forms]![Date Range Production]![To])
 AND 
((TechProduction.Platform)=[Forms]![Date Range Production]![PlatProd]));
 

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

[danielfinzel]

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)

[Rabbit]

Use the SUM function on the production field.

[danielfinzel]

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.

[danielfinzel]

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.

[Rabbit]

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.

[danielfinzel]

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

[NeoPa]

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 :

SELECT   [Forms]![Date Range Production]![From] AS [DateFrom]
       , [Forms]![Date Range Production]![To] AS [DateTo]
       , [Platform]
       , [Shift]
       , Sum([Production]) AS [ProdSum]
FROM     [TechProduction]
WHERE    ([DateProd] Between [Forms]![Date Range Production]![From] And [Forms]![Date Range Production]![To])
GROUP BY [Platform]
       , [Shift]