Large database query issues

[jiimmyp]

I have a 65 MB database that tracks inspections. One of the options in the database allows me to forecast inspections for each inspector. Another option allows me to pull up the forecast to see where all the inspectors are with there numbers. When multiple inspectors perform the same inspection the numbers get added together. So if Inspector 1 and 2 both perform 1 inspection the tracker will show that they both performed 2 and show that as a group we performed 4. I don't want this to happen. But can't seem to figure out how to make it stop.



The "InspectionsCW" block on the forecast query is part of a union query that pulls "SumofCountofReportID".

The "SumofCountofReportID" doesn't break it down by inspector just by Report ID which is my problem. I just don't understand how to make it break that down even further.

I realize that this isn't much info to go off of but the database is too large to upload. I can post the SQL code of the queries, but once again not sure how much help that will be. To me it seems like I should be able to input another Field in one of my queries to isolate the "SumofCountofReportID" by "InspID" but I'm not really sure where to do that.

If you guys know of somewhere that will do a teamviewer session to help me out or if somehow all of this makes sense and someone can help me I would be extremely appreciative.

[June7]

Follow instructions at bottom of my post to provide db (copy, remove records, compact & repair, zip).

Alternative is upload to fileshare site such as Box.com and post link to file.

So what do you want the data to show? If two inspectors worked on the same inspection should they each get credit for .5 inspection?

[jiimmyp]

Here are the backend and frontend. If you need the password for the backend: 86MXGQAD@t@b@$3

I removed all the real records but put 2 fake ones in. I also edited the names of the inspectors and workers.

Log in as Six, Inspector 6 password 1234. From the main screen choose MSEP Reports then Forecast. On the Forecast Review Menu, change monthly dropdown to Apr 2013 and choose Forecast Status by inspector and Organization in the Forecast Status Reports box at the bottom, then choose print preview. You should see that Inspector #2 has CW 2 A01-A/C Servicing Tasks and Inspector #24 has CW 2 A01 Servicing tasks.

However this is not the case. Both Inspectors have CW 1 A01 Inspection. If you look throughout the 'rptForecastMthlyStatusbyInspWC' that you just opened you will notice that several of the Inspectors are required to do the same inspections as another inspector. This is because we have over 400 people that we inspect and we have to inspect multiple personnel on the same task at different times. When I backtrack that CW column I end up at 'qryForecastMonthlyResults' which is a Union Query of 'qryForecastMonthlyUnmatched' and qryForecastMonthlymatched'. The monthly matched query has a field called CountofReportID and when it counts those ReportID's it doesn't take into account that multiple inspectors have done the same inspection on different people and I need to figure out how to make it do that.

Once again, thank you so much for any help you may be able to provide me with.

[June7]

Neither of those links you posted open. I tried multiple browsers but they just don't go anywhere.

[jiimmyp]

Do these links work? Backend Frontend

[June7]

They both take me to a page that just wants me to update Google Chrome. I was not even using Chrome.

Here's how to get the link of file uploaded to box.com.

[jiimmyp]

https://www.box.com/s/z1fesj37gtzl5a5opy8n Frontend
https://www.box.com/s/mc31aven78vr93w7xa1d Backend

Is it possibly because I am just loading the .mdb files straight there? If these don't work I will zip them.

https://www.box.com/s/qp0td95ux70owtert8m1 here is the zipped file.

[June7]

Sorry, the first links were good, I forget sometimes I get blocked at work. The downloads took a long time at home. The frontend is an mdb and the backend is accdb. I did not know mdb could link to accdb. I will see what I can do this weekend.

[jiimmyp]

Yeah, we weren't sure either. The front end was updated to work on office 2010 and was sent to us in case we were force to upgrade. I am new to the unit and was asked to take a look at it and I found out that it would work with the backend. So now people on 2007 and 2010 can use the same backend we just have separate frontends.

[June7]

The AutoExec macro errors, can't find function ap_AppInit (). I can open the db by holding shift key and relink tables but goes downhill from there.

I opened rptForecastMthlyStatusbyInspWC in design view and several fields have error 'No such field in field list'. The union query this report is based on errors "Undefined function 'Format' in expression". I try to open the queries used in the union and get more errors.

What is the 'main screen' form? You delete all but 2 dummy records from what table?

[jiimmyp]

The AutoExec macro errors, can't find function ap_AppInit (). I can open the db by holding shift key and relink tables but goes downhill from there.

I opened rptForecastMthlyStatusbyInspWC in design view and several fields have error 'No such field in field list'. The union query this report is based on errors "Undefined function 'Format' in expression". I try to open the queries used in the union and get more errors.

What is the 'main screen' form? You delete all but 2 dummy records from what table?

The main screen is frmStartup. The AppInit() has always had an error, we always just stop all macros and continue. tblAssessments has been deleted to only 2 dummy records.