Here is my SQL Query in Access:

SELECT [Alljobs In Zeke].ID, [Alljobs In Zeke].Name, Occurs.YearlyFrequency, Left([Alljobs In Zeke]!Name,2) AS AppPrefix, ProdJobABEND.DATE, ProdJobABEND.ID, IIf(ProdJobABEND!DATE>=#1/1/2008# And ProdJobABEND!DATE<=#1/30/2008#,Count(ProdJobABEND!ID),0) AS [Count Jan 2008], IIf(ProdJobABEND!DATE>=#2/1/2008# And ProdJobABEND!DATE<=#2/29/2008#,Count(ProdJobABEND!ID),0) AS [Count Feb 2008], IIf(ProdJobABEND!DATE>=#3/1/2008# And ProdJobABEND!DATE<=#3/31/2008#,Count(ProdJobABEND!ID),0) AS [Count Mar 2008], IIf(ProdJobABEND!DATE>=#4/1/2008# And ProdJobABEND!DATE<=#4/30/2008#,Count(ProdJobABEND!ID),0) AS [Count Apr 2008], IIf(ProdJobABEND!DATE>=#5/1/2008# And ProdJobABEND!DATE<=#5/31/2008#,Count(ProdJobABEND!ID),0) AS [Count May 2008], IIf([ProdJobABEND]![DATE]>=#6/1/2008# And [ProdJobABEND]![DATE]<=#6/30/2008#,Count([ProdJobABEND]![ID]),0) AS [Count Jun 2008], IIf([ProdJobABEND]![DATE]>=#7/1/2008# And [ProdJobABEND]![DATE]<=#7/31/2008#,Count([ProdJobABEND]![ID]),0) AS [Count Jul 2008], IIf([ProdJobABEND]![DATE]>=#8/1/2008# And [ProdJobABEND]![DATE]<=#8/31/2008#,Count([ProdJobABEND]![ID]),0) AS [Count Aug 2008], IIf([ProdJobABEND]![DATE]>=#9/1/2008# And [ProdJobABEND]![DATE]<=#9/30/2008#,Count([ProdJobABEND]![ID]),0) AS [Count Sep 2008], IIf([ProdJobABEND]![DATE]>=#10/1/2008# And [ProdJobABEND]![DATE]<=#10/31/2008#,Count([ProdJobABEND]![ID]),0) AS [Count Oct 2008]
FROM ([Alljobs In Zeke] LEFT JOIN ProdJobABEND ON [Alljobs In Zeke].Name = ProdJobABEND.JOB) LEFT JOIN Occurs ON [Alljobs In Zeke].Occurs = Occurs.Occurs
GROUP BY [Alljobs In Zeke].ID, [Alljobs In Zeke].Name, Occurs.YearlyFrequency, Left([Alljobs In Zeke]!Name,2), ProdJobABEND.DATE, ProdJobABEND.ID;

The problem is, in the first column, the [Alljobs In Zeke].ID is duplicated for each time the count in one of the month columns is 1 or higher.

Any idea?

 

Im not a SQL expert but I will help you... Let me send this to my friend..

 

Cool - I think I need a SELECT DISTINCT (can't recall the syntax)

 

and you may be right. Hold on, he is trying to help you He is looking for logic behind this...

 

SELECT [Alljobs In Zeke].ID, [Alljobs In Zeke].Name, Occurs.YearlyFrequency, Left([Alljobs In Zeke]!Name,2) AS AppPrefix, ProdJobABEND.DATE, ProdJobABEND.ID, IIf(ProdJobABEND!DATE>=#1/1/2008# And ProdJobABEND!DATE<=#1/30/2008#,Count(ProdJobABEND!ID),0) AS [Count Jan 2008], IIf(ProdJobABEND!DATE>=#2/1/2008# And ProdJobABEND!DATE<=#2/29/2008#,Count(ProdJobABEND!ID),0) AS [Count Feb 2008], IIf(ProdJobABEND!DATE>=#3/1/2008# And ProdJobABEND!DATE<=#3/31/2008#,Count(ProdJobABEND!ID),0) AS [Count Mar 2008], IIf(ProdJobABEND!DATE>=#4/1/2008# And ProdJobABEND!DATE<=#4/30/2008#,Count(ProdJobABEND!ID),0) AS [Count Apr 2008], IIf(ProdJobABEND!DATE>=#5/1/2008# And ProdJobABEND!DATE<=#5/31/2008#,Count(ProdJobABEND!ID),0) AS [Count May 2008], IIf([ProdJobABEND]![DATE]>=#6/1/2008# And [ProdJobABEND]![DATE]<=#6/30/2008#,Count([ProdJobABEND]![ID]),0) AS [Count Jun 2008], IIf([ProdJobABEND]![DATE]>=#7/1/2008# And [ProdJobABEND]![DATE]<=#7/31/2008#,Count([ProdJobABEND]![ID]),0) AS [Count Jul 2008], IIf([ProdJobABEND]![DATE]>=#8/1/2008# And [ProdJobABEND]![DATE]<=#8/31/2008#,Count([ProdJobABEND]![ID]),0) AS [Count Aug 2008], IIf([ProdJobABEND]![DATE]>=#9/1/2008# And [ProdJobABEND]![DATE]<=#9/30/2008#,Count([ProdJobABEND]![ID]),0) AS [Count Sep 2008], IIf([ProdJobABEND]![DATE]>=#10/1/2008# And [ProdJobABEND]![DATE]<=#10/31/2008#,Count([ProdJobABEND]![ID]),0) AS [Count Oct 2008]
FROM [Alljobs In Zeke], ProdJobABEND, Occurs
WHERE [Alljobs In Zeke].Name = ProdJobABEND.JOB
AND [Alljobs In Zeke].Occurs = Occurs.Occurs

GROUP BY [Alljobs In Zeke].ID, [Alljobs In Zeke].Name, Occurs.YearlyFrequency, Left([Alljobs In Zeke]!Name,2), ProdJobABEND.DATE, ProdJobABEND.ID;

 

to use LEFT JOIN. I am not saying you didn't have to, because I don't know your clear goal what to retrieve from the tables.

What was modified:
"all LEFT JOIN ON" were replaced with typical "WHERE" clause.

Let me know if that help

 

hire an SQL person to help you, check out Craigslist - LOL

Hope you find your solution buddy!

 

[All Jobs in Zeke].NAME may or maynot exist within [ProdJobAbend].JOB, therefore the LEFT JOIN from All [All Jobs in Zeke].NAME -=> [ProdJobAbend].JOB and the other LEFT JOIN Occurs ON [Alljobs In Zeke].Occurs = Occurs.Occurs is there so I can display the value for the Occurs vs. the text value. The data is still displaying like this:

ID, AllJob.NAME, CountJan, CountFeb......
10,blah,0,0
11,blah1,0,1
11,blah1,1,0
.
.
.

I'm thinking of jumping right into the reporting tool (not a chance will I use Access's reporting tool - tis a joke) and see if I can group by AppPrefix then do sum for each month.

 

But make sure he/she's not a prostitute or Dart will be on your ***.