Big problems for simple things

[solbane]

Access field lookup, subtraction & sum through ODBC

Sorry for posting this here but it doesn't fit in just one catagory.

I'm having a bit of trouble using a manual input table and ODBC table to filter information through query into form view. Let me shed a little light on what's, what.

I'm looking at adding the totals of hours scanned/worked (sum of periodOfEvent) on job per "workcenternumber" (Scanning hour report - table [ODBC] SQL database on another server) then subtracting them from the hours given (job planning table - table [manual input]) to display hours remaining on project through query. [ITEMA]-[ITEMB]

Here's the problem: I can filter workcenternumber in the ODBC table "Scanning Hour Report" to display the various work center numbers, in this case it would be the following items:

BAL-HIGH
BAL-LOW
CM-TECH
CR-TECH
DW-JNR
DW-SNR
ENG
FS-JNR
FS-SNR
MS-HIGH
MS-LOW
MS-MED
MS-TECH
QA-TECH
SB-TECH
WB-BOIL
WB-HEAT
WB-WELD
WM-TECH

now I'm sitting with a query for each of these work center numbers with each query looking up hours given & subtracting hours worked to give you x amount hours remaining. Here are two example of these queries:

-------------------------------------------------------------------------------
--==1==--

SELECT DISTINCTROW [Scanning Hour Report].WBSElement, [Scanning Hour Report].WorkCentreNumber, [Job Planning].[Project Number], [Job Planning].Customer, [Job Planning].Description, [Job Planning].[Planned Start], [Job Planning].[Progress %], [Job Planning].[Planned Finish], [Job Planning].[Actual Finish], [Job Planning].Consultant, [Job Planning].Team, Sum([Scanning Hour Report].PeriodofEvent) AS [Sum Of PeriodofEvent], [Job Planning].[BAL-HIGH Hours Given]-[Sum Of PeriodofEvent] AS [Hours Remaining BAL-HIGH], [Job Planning].[BAL-HIGH Hours Given]

FROM [Job Planning] INNER JOIN [Scanning Hour Report] ON [Job Planning].[Project Number] = [Scanning Hour Report].WBSElement

GROUP BY [Scanning Hour Report].WBSElement, [Scanning Hour Report].WorkCentreNumber, [Job Planning].[Project Number], [Job Planning].Customer, [Job Planning].Description, [Job Planning].[Planned Start], [Job Planning].[Progress %], [Job Planning].[Planned Finish], [Job Planning].[Actual Finish], [Job Planning].Consultant, [Job Planning].Team, [Job Planning].[BAL-HIGH Hours Given], [Job Planning].[BAL-HIGH], [Job Planning].[MS-TECH], [Job Planning].[MS-TECH Hours Given], [Job Planning].[BAL-LOW], [Job Planning].[BAL-LOW Hours Given], [Job Planning].[CM-TECH], [Job Planning].[CM-TECH Hours Given], [Job Planning].[CR-TECH], [Job Planning].[CR-TECH Hours Given], [Job Planning].[DW-JNR], [Job Planning].[DW-JNR Hours Given], [Job Planning].[DW-SNR], [Job Planning].[DW-SNR Hours Given], [Job Planning].ENG, [Job Planning].[ENG Hours Given], [Job Planning].[FS-JNR], [Job Planning].[FS-JNR Hours Given], [Job Planning].[FS-SNR], [Job Planning].[FS-SNR Hours Given], [Job Planning].[MS-HIGH], [Job Planning].[MS-HIGH Hours Given], [Job Planning].[MS-LOW], [Job Planning].[MS-LOW Hours Given], [Job Planning].[MS-MED], [Job Planning].[MS-MED Hours Given], [Job Planning].[QA-TECH], [Job Planning].[QA-TECH Hours Given], [Job Planning].[SB-TECH], [Job Planning].[SB-TECH Hours Given], [Job Planning].[WB-BOIL], [Job Planning].[WB-BOIL Hours Given], [Job Planning].[WB-HEAT], [Job Planning].[WB-HEAT Hours Given], [Job Planning].[WB-WELD], [Job Planning].[WB-WELD Hours Given], [Job Planning].[WM-TECH], [Job Planning].[WM-TECH Hours Given]

HAVING ((([Scanning Hour Report].WorkCentreNumber)="BAL-HIGH"));


--==2==--

SELECT DISTINCTROW [Scanning Hour Report].WBSElement, [Scanning Hour Report].WorkCentreNumber, [Job Planning].[Project Number], [Job Planning].Customer, [Job Planning].Description, [Job Planning].[Planned Start], [Job Planning].[Progress %], [Job Planning].[Planned Finish], [Job Planning].[Actual Finish], [Job Planning].Consultant, [Job Planning].Team, [Job Planning].[MS-TECH Hours Given], Sum([Scanning Hour Report].PeriodofEvent) AS [Sum Of PeriodofEvent], [Job Planning].[MS-TECH Hours Given]-[Sum Of PeriodofEvent] AS [Hours Remaining]

FROM [Job Planning] INNER JOIN [Scanning Hour Report] ON [Job Planning].[Project Number] = [Scanning Hour Report].WBSElement

GROUP BY [Scanning Hour Report].WBSElement, [Scanning Hour Report].WorkCentreNumber, [Job Planning].[Project Number], [Job Planning].Customer, [Job Planning].Description, [Job Planning].[Planned Start], [Job Planning].[Progress %], [Job Planning].[Planned Finish], [Job Planning].[Actual Finish], [Job Planning].Consultant, [Job Planning].Team, [Job Planning].[MS-TECH Hours Given], [Job Planning].[MS-TECH], [Job Planning].[BAL-HIGH], [Job Planning].[BAL-HIGH Hours Given], [Job Planning].[BAL-LOW], [Job Planning].[BAL-LOW Hours Given], [Job Planning].[CM-TECH], [Job Planning].[CM-TECH Hours Given], [Job Planning].[CR-TECH], [Job Planning].[CR-TECH Hours Given], [Job Planning].[DW-JNR], [Job Planning].[DW-JNR Hours Given], [Job Planning].[DW-SNR], [Job Planning].[DW-SNR Hours Given], [Job Planning].ENG, [Job Planning].[ENG Hours Given], [Job Planning].[FS-JNR], [Job Planning].[FS-JNR Hours Given], [Job Planning].[FS-SNR], [Job Planning].[FS-SNR Hours Given], [Job Planning].[MS-HIGH], [Job Planning].[MS-HIGH Hours Given], [Job Planning].[MS-LOW], [Job Planning].[MS-LOW Hours Given], [Job Planning].[MS-MED], [Job Planning].[MS-MED Hours Given], [Job Planning].[QA-TECH], [Job Planning].[QA-TECH Hours Given], [Job Planning].[SB-TECH], [Job Planning].[SB-TECH Hours Given], [Job Planning].[WB-BOIL], [Job Planning].[WB-BOIL Hours Given], [Job Planning].[WB-HEAT], [Job Planning].[WB-HEAT Hours Given], [Job Planning].[WB-WELD], [Job Planning].[WB-WELD Hours Given], [Job Planning].[WM-TECH], [Job Planning].[WM-TECH Hours Given]

HAVING ((([Scanning Hour Report].WorkCentreNumber)="MS-TECH"));
-------------------------------------------------------------------------------

....these queries would display:

WBSElement - IT123456789012
WorkCentreNumber - BAL-HIGH
Customer - Customer000001
Description - High Speed balancing of turd filled drum
Planned Start - dd/mm/yyyy
Progress % - 95.00%
Planned Finish - dd/mm/yyyy
Actual Finish - dd/mm/yyyy
Consultant - Consultant1
Team - TEAM#
BAL-HIGH Hours Given - 20
Sum Of PeriodofEvent - 13.5
Hours Remaining - 6.5

The project number(Job planning table) and the WBSELEMENTNUMBER(Scanning Hour Report have linked via relationships tab

as an example... [see pic1]

now if the field finds no record in the ODBC it displays no information which would be correct in a sense. [see pic2]
BUT!
Here comes the complicated part.

NB!!:INSTEAD of displaying no information how would I go about making the ODBC fields display default value of 0 so when I add all the above mentioned workcenternumbers to ONE form, instead of displaying NOTHING when it finds no values for one of the workcenternumbers it will instead display that "50" hours given even if "0" hours were worked thus "50" hours still remain for that project number's work center number.
Is there an easier way of filtering different work center numbers to one form and summing figures from there or what?

To wrap it up all I'm looking for is a form that checks the workcenternumber and the hours captured on the ODBC "Scanning hour report" and subtracting it from the table "Job Planning" where the WBSELEMENT & Job Number as well as the work center numbers tie up to give me a total value of hours remaining. If nothing is found for one workcenternumber it displays value 0 instead of a entire blank page.
If you have a sample database I haven't seen that u think could come in handy it would be helpfull too.

Hope I didn't confuse you, yet...

Thanks in advance
solbane

 

[djcritter]

You could try using the IIF expression.

Not sure if it will work the way you want it to but always helps me replace a null value with a figure.

 

[solbane]

thanks djcritter,
will will give it a try.

 

[solbane]

I've tried this:

Hours Remaining BAL-HIGH:=IIF(IsNull([Job Planning].[BAL-HIGH Hours Given]-[Sum Of PeriodofEvent]),"0",[Job Planning].[BAL-HIGH Hours Given]-[Sum Of PeriodofEvent])

but with the same result.